Auditing triggers in SQL Server databases

One of the essential SQL Server security topics is finding out who did something, what and when. Being able to provide a history of data for various auditing purposes, some of which are enforced by US laws through compliance regulations, is a serious task for any DBA. Since business policies or the compliance regulations require the auditing of data changes within a database, various techniques adapting to the requirements are used to perform database auditing. The common one is using auditing triggers in SQL Server databases.

A trigger is a special type of a database object which is automatically executed upon certain conditions – e.g. actions performed by the user. Currently, four types of triggers exist in SQL Server, and the first two are the most commonly used:

• DDL triggers (auditing CREATE, ALTER, DROP and several system defined stored procedures which perform DDL operations) are often used for administrative tasks like regulating and auditing database operations

• DML triggers – our focus will be on this type of triggers as they provide auditing of data changes. This type of audit triggers fires upon DML statements (like INSERT, UPDATE and DELETE) as well on stored procedures that execute DML-like operations

• CLR triggers are a special type of auditing triggers which enables trigger technique to be used directly within .NET languages, and they support SQL Server 2008 and above

• Logon triggers fire when a SQL Server LOGON event occurs. It can be used to track successful authentication and control and audit server sessions

There is a wide range of the use cases where triggers can be used, and they are especially useful for enforcing business policies. For example, some business might have a rule that, whenever they get a new client, this client has to be assigned to a consultant within a company. When this business rule is translated to databases, it means that each time a new customer is added to a database (by using the INSERT operation), a trigger can be used to assign the customer to the consultant who has the lowest number of customers or is not currently busy, or whatever business policy requires.

In this article we’ll focus on their data auditing capabilities. What auditing triggers basically must provide while auditing data changes are answers to the following questions:

1. Who changed the data?

2. What was the date and time when a change occurred?

3. Which client software was used to access the data?

4. In case the request was data modification (the UPDATE statement), what was the data value prior and after the change?

It means that when a trigger is fired, all this info has to be collected from a database or SQL Server instance and stored in an auditing repository.

For example, a trigger fired when a record is inserted into the Currency. Sales table is created using the following SQL:

CREATE TRIGGER Sales.tr_i_AUDIT_Currency
ON Sales.Currency
    FOR INSERT
    NOT FOR REPLICATION
AS

Then, the trigger must insert the computer name where INSERT was executed, the user name of the person who inserted the record and the name of the application used to insert the record. Triggers leverage built-in SQL Server functions to get these values:

• HOST_NAME () returns the workstation name

• APP_NAME()returns a name of the application in the current session

• SUSER_SNAME() – returns the login name

• GETDATE() – returns the system timestamp for the database currently connected to

These values are stored in a pre-defined table used as an auditing data repository:

INSERT
INTO AdventureWorks2012.dbo.AUDIT_LOG_TRANSACTIONS
(
     TABLE_NAME,
     TABLE_SCHEMA,
     AUDIT_ACTION_ID,
     HOST_NAME,
     APP_NAME,
     MODIFIED_BY,
     MODIFIED_DATE,
     [DATABASE]
)
VALUES(
'Currency',
'Sales',
2, --	ACTION ID For INSERT
CASE
WHEN
       LEN(HOST_NAME())
       <
       1 THEN ' '
    ELSE HOST_NAME()
END,
CASE
WHEN
       LEN(APP_NAME())
       <
       1 THEN ' '
    ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
'AdventureWorks2012'
);

Organizing auditing triggers

As auditing triggers are basically a part of a production SQL Server instance, they fire and perform in real time along with actual database changes. This, by nature of triggers, means additional SQL Server resources usage which entails some planning.

Auditing triggers should be organized in an efficient manner to audit sufficient changes and yet not to cause unnecessary SQL Server overheads. This can be achieved through careful planning and the analysis of previous actions of database users. Any unnecessary auditing should be avoided for the sake of simplifying the trigger creation and maintenance process as well as for preventing unnecessary SQL Server resources utilization. Sadly, SQL Server does not provide any visual means to help the auditing triggers organization and overview. Eventually, the SQL Server Management Studio’s Object Explorer can be used for a triggers overview in a tree-like form.

Advantages and disadvantages of using auditing triggers

There are several advantages if triggers are used for auditing database changes:

• Using auditing triggers provides faster application development because these are scripted in a database; therefore, there is no need to code trigger actions for each application which uses them

• Once defined trigger will be re-used by any application which modifies database data thus enforcing defined business rules

• If a business data policy changes, it’s sufficient to modify appropriate auditing trigger instead of changing all applications

• The client/server environment’s performances are not affected (there is no impact on the network traffic) as auditing is performed locally on the database server

On the other hand, a significant impact on the SQL Server performance may occur as auditing triggers can utilize resources equally as a user performed operation. This is the main triggers’ disadvantage – the SQL Server overhead. For example, auditing change of one record field will additionally perform at least one more change while saving captured data.

Saving captured information

Compliance regulations require reliable and human-readable auditing information on demand. The most common practice is to use SQL Server as a storage. This way, similar techniques (i.e. T-SQL) are used for auditing, storing and maintaining captured information. Moreover, audited information is saved in a form of SQL Server tables/databases, so the benefits SQL Server provides for any stored information are available for handling captured data, too.

Of course, in some particular cases, other formats for storing captured data are used. For example, if no additional data querying is required, standard file formats are used – like XLSX or PDF. This surely can provide easy accessible auditing reports, but any additional processing or querying might not be possible at all.

Auditing triggers and ApexSQL Trigger

ApexSQL Trigger is a SQL Server auditing tool which captures data and schema changes that have occurred on a database including the information on who made the change, which objects were affected, when it was made, as well as the information on the SQL login, application and host used to make the change. It stores all captured information in a central repository and exports them in print friendly formats.

The technology which ApexSQL Trigger uses for auditing is based on triggers. These are created using its visual interface, without any need for T-SQL knowledge. Simply, after connecting to a database, the tool enlists all existing user tables in the database user tables with their current auditing status.

The grid clearly shows which table is being audited and for what operation – INSERT, UPDATE, DELETE or all of these. Additionally, the Filter row may be used to narrow down the list. The Prepared column indicates whether there is a proper selection of columns being audited for a particular table. Details about columns and their auditing status are shown in the Columns panel:

Using this visual overview, auditing triggers may be easily created, modified and removed. Upon finalizing selection of tables and their rows to be audited for a particular operation, simply by using the Create option on the main application’s toolbar, auditing triggers are created.

ApexSQL Trigger enables you to review and edit a trigger creation script prior to its execution. Besides the possibility of modifying the script that generates triggers, it is also possible to customize the template ApexSQL Trigger uses to generate them.

Additional improvements of captured data may be achieved by using Watches and Lookups.

When created, auditing triggers may be disabled, enabled or deleted by selection or all at once using the trigger management module. This also doesn’t require manual scripting:

When it comes to presenting captured data, the built-in reporting module provides comprehensive filtering of captured data while, at the same time, can export reports as a CSV, TXT, PDF, Microsoft Word and Microsoft Excel file.

Finally, let us turn to the subject of saving captured data using auditing triggers. ApexSQL Trigger stores captured information in three additional tables:

• The AUDIT_LOG_DATA table is used to store the information about data that was changed – the initial data value prior to a change and data value after the change has occurred

• The AUDIT_LOG_TRANSACTIONS table stores the information about the transaction such as the login name, application name, table name and date and time when the transaction was committed

• The AUDIT_LOG_DDL table stores the information about database schema changes

All tables are created within each database being audited. This can be customized so that all captured data from all audited databases is saved within one separate database.

So, as the captured information is saved in SQL Server database, it is not only available through reporting module and its exports, but also as a source of the additional querying via user SQL scripts.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.