SQL Server - How to use auditing to map actual required permissions on a user

Reading Time: 6 minutes

Speak guys!
In this article, I will demonstrate how to use auditing to map a user's actual required permissions, identifying everything that that user actually did in the database over the observed time, to generate a script granting only the permissions he really needs, thus eliminating need to have integration users and users other than the main application with permissions on all database objects such as db_owner, db_datareader and / or db_datawriter, for example.

During customer service, it is very common to see situations where users of integrations between systems or processes are created in the database exclusively for this process (good practice), but even having to access / change few tables, they end up being granted permissions on all. objects of this database. Especially in integration users, which are not always developed by the internal team and in many cases, the integration users' system is not even the responsibility of the local IT team and can be accessed via the internet, for example, a breach in this system. External marketing can end up being catastrophic for your business.

To mitigate this issue, I will show you how to log everything these users do into the database and allow you to apply only the required permissions. For this need, I will use the Server Audit feature, available since SQL Server 2008 in the Enterprise version and from SQL Server 2012 in the Standard version. I will also use the Database Audit feature, available in the Standard version only from SQL Server 2016 SP1.

In short, to be able to run the scripts in this article, you will need to meet one of the following conditions:

• SQL Server 2012 or above, Enterprise, Datacenter (2008), or Developer Edition
• SQL Server 2016 SP1 or above (any edition, up to Express)

Creating the table to store access history

After the introduction above, let's now start monitoring table accesses. First of all, I will create the table that will store the history of the accesses collected by the audit.

Creating Server Audit by Filtering Users

The second step to being able to audit the hits made by certain users, let's create a server audit on the instance that will be used in the next topic to capture these events. Remember that the Server Audit feature is available since SQL Server 2008 in the Enterprise version and SQL Server 2012 in the Standard version. Also, you can only use filters in Server Audit from SQL Server 2012.

In the example below, I am defining that auditing will create up to 16 files of 10 MB each. As files are being filled, new files with the collected data will be created until they reach the set limit (16 files). When all files have already been created and are fully populated, then the oldest files will be overwritten with the new information. For this reason, it is important to have a routine for collecting data from files and storing in tables.

In addition, starting with SQL Server 2012, we can apply a number of filters to refine our searches, such as capturing user data that ends with '% User' or that starts with LS_% and also bypass SSMS Intellisense and RedGate tools, in addition to not collecting data from users who are in the domain “MEUDOMINIO” and also not collecting data from the user “usrDirceuResende”.

In this step we can filter the names of users, software, IP's or Hostnames that will be used to refine the searches to return only the desired data.

Creating Database Audit capturing accesses

In the third step in setting up our monitoring, let's now create a database audit for each database that you want to monitor for permissions. In the example below, I will monitor the INSERT / DELETE / UPDATE / SELECT / EXECUTE events performed by any bank user (public).

Remember that while Database Audit seems to capture the events of all users, in Server Audit we already limit the users that must be returned and this filter is also respected in Database Audit.

The code below will go through all databases not in the exceptions list (master, tempdb, msdb and model) and will create the database audit.

Creating a Stored Procedure to Store Collected Data

In the 4 step of the routine, we will create the Stored Procedure responsible for reading the data from the audit files and writing the data to the table created in step 1. Remember to create a SQL Agent job to perform the procedure below every X minutes.

Querying the collected data

Finally, in the 5 step of this routine, we will read the data collected and stored in the dirceuresende.dbo.Audit_Access table and identify the accesses made by each user in each database. This will help you identify what these users are currently accessing in the database. No more excuse for all users to be db_owner from databases.

If you also want to generate the scripts to grant current permissions, you can use this script:

Result:

Ready! Access audit routine implemented. The next step now is to use the collected data to grant the permissions users actually used during the time Audit was enabled and remove elevated permissions from those users, such as sysadmin and db_owner.

Observation: A very important point to keep in mind is how many records this table will keep. In environments that have many concurrent users, this access data collection can end up generating a very large data volume and consuming a lot of disk space. Because of this, it is always important to keep track of data volume and create a table cleanup routine, keeping only X days of history.

Besides, I don't need to mention that I recommend that you try it a lot before applying it in production, right? Depending on the amount of transactions per second, this can even have a performance impact by enabling this feature (although I've already tested in very critical environments and haven't noticed any changes).

Well guys, I hope it helped you have a way to validate all objects accessed by certain users, helping you in mapping the required permissions.
A big hug and even more!