Audit SQL Server permission changes to improve overall security

One of the most important tasks for a DBA aiming to keep database and the data in it secure and away from unauthorized access or, heaven forbid, malicious changes is to always stay on top of the effective SQL Server permissions his users have over the SQL instances as well as the databases, database objects and data stored in them. Although this might seem like a pretty straightforward task, as the number of database users grows on one hand, and the number of databases and objects on the other things can get really complicated. Add to that the ever changing business requirements, and soon, unless you have some kind of documenting system in place, you can end up with users not having sufficient permissions or even worse – users having more permissions that they actually require

SQL Server security 101

When it comes to SQL Server permissions, the configuration which seems the most tempting as it looks like requires the least amount of effort to maintain is to simply add all of your users to the fixed sysadmin role. Please, never do that. While it’s true that this way you won’t have to worry about each of your users being able to access the data and objects they need – the far greater concern in this scenario should be that your users will be able to access database objects and data they don’t need to, or even worse, shouldn’t. Even in the highly unlikely scenario, that every user needs to access and even manipulate each of the database objects, the sysadmin role encompasses way more privilege than simply manipulating with the objects. The solution in this (and any other) situation is – give your users only those permissions that they require to do their jobs. Not less, and certainly not more

What users can’t do, doesn’t hurt the database

There are two main reasons behind this school of thought. First, this policy minimizes accidental data and objects loss and/or corruption. If you should be able to delete data from a particular table, there is no guarantee that you won’t delete data you didn’t intend to. However, DENY DELETE will guarantee that you won’t accidentally delete any data from a table that stores data which has never been intended for deletion

On the other hand, keeping the permissions at the minimal level will make the job more difficult for anyone with malicious intent who managed to get their hands on a set of SQL Server credentials or managed to get access to your SQL Server otherwise. As a matter of fact, some common techniques attackers use once they’ve obtained access to the system, is creating a new login and assigning it administrative privileges while attempting to lock out the real administrative accounts. A compromised account with minimal permissions would make this scenario much harder for the attacker as he or she would need to resort to other methods to get elevated privileges. Therefore, verifying the permission changes that have occurred on your SQL instance, especially if you don’t remember doing them should be very high on ever checklist for suspicious activities

Now, here comes the tricky part

How can you track all the permission changes on your SQL instance?

If you have SQL Server 2008 and above you can set up SQL Server auditing. It writes all of the events that you’ve selected to be audited in an audit log. The caveat of this approach is that once the attacker gains elevated privileges over the SQL instance, auditing can be stopped and the audit log destroyed. Although finding out that auditing has been stopped and that the log is missing will undoubtedly raise a red flag, as far as information on what exactly has been compromised and to which extent, you’ll still be in the dark

Alternatively, if a trace is running at the time of the attack, you will be able to identify the exact actions the perpetrator took, again, under the condition that the attacker doesn’t destroy the trace file. In addition, this approach requires the trace to be running at all the time, thus impacting SQL Server performance

Now, the good news is that each database change is recorded in the online transaction log. However, the transaction log was never intended to be humanly readable. This is where a transaction log reader, like ApexSQL Log can help

ApexSQL Log is an auditing and recovery tool for SQL Server databases which reads transaction logs, transaction log backups, detached transaction logs and database backups, and audits, reverts or replays data and object changes that have affected the database, including the ones that have occurred before the product was installed

To determine all GRANT, REVOKE and DENY permission changes that have occurred on your database using ApexSQL Log

1. Start ApexSQL Log
2. Connect to the database for which you want to audit permission changes
3. If you have any transaction log backups or detached transaction logs made before permissions have been changed, click on “Add File” button in the Data Sources dialog. Adding a database backup may help you with the performance and quality of the audit trail
• Click Add File in order to add a detached transaction log

• Database backups, Transaction Logs and Differential Database backups should automatically be shown and you can select them by ticking the box next to them

  

4. In the Filter setup dialog you can narrow down the results to a specific point in time, user or object affected by the permission change

   

5. Click “Finish”

All of the permissions changes that meet the criteria you’ve specified above, along with their details, will be listed in the application’s main grid. If you need to narrow down this result set further, use the Grid Filter in the left pane

In summary, keeping track of the effective SQL permissions of any given user without regularly updated documentation can be a daunting task. But on the other hand, this task is paramount in keeping your SQL objects and data safe, and can help you significantly in examining your SQL Server for suspicious activities. Although you can utilize several approaches in to audit any permissions changes the safest one, by design, is to query the database transaction log directly for such changes. If you want to ensure that no suspicious permissions change to your SQL database made past you, examine its transaction log with ApexSQL Log