Auditing SELECT statements on SQL Server (4213464)

Although SELECT statements are not destructive by nature, nor they can change either data or schema, there are several cases that require their auditing on SQL Server. Executed SELECT statements can indicate various current or potential issues, and this is the reason why it’s important to know who-saw-what and when

In general, two major use cases require tracking SELECT statements:

• To find out who and when is accessing your data for troubleshooting security, application, or performance issues
• To provide a necessary requirement (e.g. to comply with HIPAA or other regulations)

There are several SQL Server native solutions for auditing SELECT statements

Stored procedures and functions

Tracking who-saw-what can be done through specially developed stored procedures and functions. In order to use this auditing method, access to the database must be limited through use of stored procedures (allowing EXEC statements only, while prohibiting all queries and DML operations). The result from a query is returned by a stored procedure, while at the same time the stored procedure logs the access in an appropriate auditing repository table along with additional information of interest (e.g. the time of the execution)

However, this auditing method requires T-SQL programming and additional maintaining of the stored procedures and functions (e.g. in case of the database schema change). Moreover, ad hoc queries executed by trusted parties (e.g. members of the sysadmin role) easily override the rule that SELECT statements must be parsed by a stored procedure, and therefore cannot be tracked. This sets the use of stored procedures and functions as an audit method unacceptable for any of compliance regulations

SQL Server trace technology

SQL Server provides monitoring of selected events via the trace technology. It’s available through Application Programmer Interface (API), SQL Server Profiler as a GUI for viewing traces in real time, or by accessing saved trace files

To audit the execution of SELECT statements on a specific database:

1. Start SQL Server Profiler and provide credentials for the SQL Server instance that holds the database
2. Type the name of the trace (e.g. TraceSELECTs) you are about to create

3. Select the Blank template in the Use the template dropdown menu; we’ll use the blank one as we want to capture the SELECT statements only, and not to fill the audit repository with other information

   

4. Check the Save to table option, and provide the information for the destination audit repository table. The table can be located on another SQL Server instance than the one audited; in this example, we’ll use the same one

   

We will use the AUDITDB database (a blank database) and specify an appropriate repository table to be created (TraceSELECTsTable)

5. Now, using the Events Selection tab, find and check the SQL:StmtStarting item in the Events column

   

6. Click Column Filters to open the Edit Filter dialog

7. Select the TextData filter property, and enter “SELECT%” as the value for the Like filter

   

8. As we want to monitor only the specific database (the ACMEDB database in our example), we’ll specify an additional filter. Select the DatabaseName filter property, and enter “ACMEDB” as the value for the Like filter

   

9. Click OK to confirm filter changes
10. Select the Run option to start auditing via the defined TraceSELECTs trace

Any executed SELECT statement on the ACME database will be traced and captured in the TraceSELECTsTable table. When queried, the following information is returned:

The audited information contains the relevant information required for this type of auditing (e.g. the database and login name, the time of execution and the exact executed SELECT statement). However, there are several downsides of auditing using the native SQL Server trace technology

This auditing method is not an out-of-the-box solution. It requires precise manual set up using filters, it doesn’t provide a solution against tampering with the captured information by trusted parties, and there’s no archiving ability. Additionally, in order to export data and create appropriate auditing reports, this auditing method requires T-SQL knowledge

SQL Server Audit feature

The SQL Server Audit feature was introduced in SQL Server 2008. The feature utilizes the Extended Events technology, and audits both server and database events. However, auditing on database level is supported by the Enterprise and Developer editions only

Using SQL Server Audit as the method for tracking execution of SELECT statements produces less overhead than the trace technology, but depending on how busy the database is, an impact on server performance might occur

Either T-SQL or SQL Server Management Studio options can be used to set up the SQL Server Audit feature. In this article we’ll focus on SQL Server Management Studio; you can easily create and see the corresponding T-SQL scripts by using the CREATE TO option in SQL Server Management Studio

To audit the execution of SELECT statements on a specific database:

1. Expand the Security folder

2. Select New Audit and set the Audit name (e.g. AuditSELECTsServerSpecification) and the File path (e.g. C:\AUDITs) in the Create Audit dialog

   

   A corresponding .sqlaudit file will be generated in the specified folder (C:\AUDITs) that will be used as an audit repository

3. Confirm the SQL Server audit object creation by clicking OK
4. Right-click the created audit and select the Enable Audit option

The following steps describe how to create a database audit specification, which requires a previously created SQL Server audit object (AuditSELECTsServerSpecification). It’s the database audit specification that will fine-tune tracking of SELECT statements

5. Expand the database you want to audit in Object Explorer
6. Expand the Security folder
7. Right-click the Database Audit Specification folder and select New Database Audit Specification
8. Type in the name of the new database audit specification (e.g. AuditSELECTsDatabaseSpecification) and select the SQL Server audit object previously created using the Audit drop-down menu. In order to fine-tune the auditing, set the following:
   
   1. Audit Action Type: SELECT
   2. Object Class: DATABASE
   3. Object Name: ACMEDB

   4. Principal: public - the audits are logged only if the principal name executes a SELECT statement. As we want to audit all SELECT statements, regardless of who executed them, the principal name should be “public”

      

9. Confirm the database audit specification creation by clicking OK
10. Finally, right-click the created audit and select the Enable Database Audit Specification option

After the audit object and database audit specification are set and enabled, every SELECT executed on any table, will be audited and saved in the .sqlaudit file

To view the audited SELECT statements:

1. Right click the AuditSELECTsServerSpecification audit and select the View Audit Logs option from the context menu to launch the audit log viewer dialog

2. The dialog will show the information about the SELECT statements issued on the particular database

   

Of course, this is neither a convenient method, nor a good format for providing audited data. The other way to export and provide captured data is via the fn_get_file_audit function. The fn_get_audit_file function reads the *.sqlaudit files created by the SQL Server Audit feature

The following script queries the captured information related to SELECT statements executed on the ACMEDB database:

SELECT
       event_time ,
       session_server_principal_name AS UserName ,
       server_instance_name ,
       database_name ,
       object_name ,
       statement
  FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE
      action_id = 'SL'
  AND
       database_name = 'ACMEDB';

The SQL Server Auditing feature has less impact on server performance and provides better fine-tuning of the auditing than the trace technology. On the other hand, it’s available only in two SQL Server editions, there’s no archiving nor tamper evident repository, it requires manual set up for each SQL Server instance and database, and T-SQL is a must for deeper analysis and reporting

The out-of-the-box solution

ApexSQL Audit is a SQL Server auditing and compliance tool that tracks and reports events on SQL Server by auditing access and changes to the SQL Server instance and its objects. It provides a range of built-in reports, along with the custom report designer to create custom SQL Server auditing reports by using logical conditions in combination with all event sources and possible events

To audit the execution of SELECT statements on specific databases on multiple SQL Server instances:

1. Start the ApexSQL Audit GUI

2. Select the SQL Server instance, check the database you want to audit for SELECT statements, and check the Query option.

3. Additionally, check the tables that can be a part of an audited SELECT statement – if you don’t want to audit SELECT statements on specific tables, you can exclude them, by selecting the Exclude objects radio button, in order to preserve HDD space occupied by the ApexSQL central repository database:

   

4. Confirm your selection by selecting the Apply option on the yellow pop-up ribbon

   

ApexSQL Audit built-in reports that can provide reports with captured SELECT statements are Complete audit trail and Access history.

To see the captured SELECT statements, click the Reports button on the ApexSQL Audit main toolbar, and use any of the previously listed reports

We can also use Advanced reports to specify report items more granularly:

In the process of auditing SELECT statements ApexSQL Audit provides:

• Point-and-click set up audit configuration
• Auditing and reporting without use or T-SQL knowledge
• Support for all SQL Server editions, except the SQL Server Express edition
• Automatic monitoring and recording of user actions that include execution of SELECT statements, regardless of the SQL role user belongs to
• Security risks and performance impact identification by reporting all SELECT statements in their original T-SQL form
• One centralized audit repository with the archiving feature
• Tamper evident, accurate, and comprehensive reports for reviews

Auditing of SELECT statements can be achieved using either native SQL Server methods or using 3rd party tools like ApexSQL Audit. Although native methods are built-in (but not in every SQL Server edition), they do not offer tamper evident repositories, and require various advanced knowledge for setting up and reporting. On the other hand, ApexSQL Audit provides a user friendly and efficient method for auditing and reporting of SELECT statements, along with all listed additional benefits

Useful resources
Auditing SELECT Statements in SQL Server 2008 Standard using SQL Trace
Auditing in SQL Server 2008
MSDN - SQL Server Audit (Database Engine)
MSDN - SQL Server Audit Action Groups and Actions
MSDN - SQL Server Audit Records
MSDN - Create a Server Audit and Server Audit Specification