Banking

Using SQL Server 2005 to document Sarbanes-Oxley compliance: Part 2

Are you prepared for a Sarbanes-Oxley audit? Can you provide an audit trail of modifications to your production database environment? Do you have the funds to purchase a third-party auditing tool? If you answered "no" to any of those questions, read on to find out how to create your own database audit trail in SQL Server 2005.

By Tim Chapman

In Using SQL Server 2005 to document Sarbanes-Oxley compliance, I outlined how to create a "home-grown" auditing system to document security changes in SQL Server 2005. In that article, we used DDL triggers to track login activities, which are server-level events. In this article, we'll demonstrate how to use DDL triggers to track database-level changes.

Know your events

To implement an effective custom auditing solution, you must first understand the scope of the events that can be caught via DDL triggers. The Server scope includes events specific to the SQL Server instance, such as dropping, altering, or creating Databases, Logins, or Endpoints. The Database scope includes events that are database specific, such as dropping, altering, or creating Stored Procedures, Views, Tables, Users, or Triggers. A large majority of the events you will be writing triggers for in your auditing solution will be using the Database-scope events.

What to watch for

Let's assume your ultimate goal is to be able to provide auditors with a list of any code changes that have occurred in your production environment. What types of changes should you document for them? SQL Server 2005 provides numerous database-level DDL events to allow you to track code changes that Sarbanes-Oxley auditors will want to examine. To demonstrate our ability to monitor unauthorized code changes, let’s create a DDL trigger to track changes to stored procedures.

We will track our changes using the same table we used in the first article , but we’ll add a column for us to indicate the trigger scope. As a reminder, our original DDLTriggerTest table contained the following fields: IDCol SMALLINT IDENTITY(1,1) PRIMARY KEY, XMLEvent XML, DatabaseName VARCHAR(50), SystemUser VARCHAR(50), and EntryDate DATETIME DEFAULT (GETDATE()).

To add the new column, run this script:

ALTER TABLE EventTableData
ADD TriggerScope VARCHAR(10)

By specifying the trigger scope in our audit table, we can use the same audit table for our tracking server-level and database-level DDL events.

Once you have the necessary table structure in place to track our database events, you’re ready to create the trigger to catch these events. Run this script to create the trigger:

CREATE TRIGGER tr_ProcedureEvents
ON DATABASE
FOR DDL_PROCEDURE_EVENTS 
AS
BEGIN
      DECLARE @Scope VARCHAR(10)
      SET @Scope = 'Database'

      INSERT INTO DDLTriggerTest..EventTableData
(XMLEvent, DatabaseName, SystemUser, TriggerScope)
      VALUES
(EVENTDATA(), DB_NAME(), SYSTEM_USER, @Scope)
END

There two are important differences  in this trigger compared to the trigger we created to track logins. The first difference is the ON DATABASE statement, which indicates that this trigger will be listening for database-level events only. The second difference is the statement FOR DDL_PROCEDURE_EVENTS, which specifies that we will be monitoring any statement ran on the database that intends to ALTER, CREATE, or DROP a stored procedure.

After you create the tr_ProcedureEvents trigger on your production database, all stored procedure modifications will be logged for later review. If necessary, you can alter this trigger to send yourself an email to let you know someone is trying to modify your production database.

An ounce of prevention

Configuring the trigger to send an e-mail alert is a good idea, but you will only receive the e-mail after the action has occurred. In many instances, the time lag between the time the trigger fires and when you receive the alert can be disastrous--For example, if someone hacks into your system and deletes a critical table before you get the alert.

Fortunately, DDL triggers can not only detect events, they can prevent them from being executed in the first place. That’s one of the most useful features of the Database and Server level DDL triggers, and one that will make any Sarbanes-Oxley auditor smile: the ability to ROLLBACK events that you do not want to occur.

For example, if our production environment is at a point where we know we don't want any stored procedure modifications to occur, or we do not want any more logins created on our server, we can include logic in our trigger to reverse anything that the DDL statement was attempting to accomplish. So, not only do you have the ability to track any changes to your production environment, you can also prevent things from happening.

The trigger we created above can be altered to not allow any stored procedure modifications in the database. Here's the altered version:

ALTER
TRIGGER tr_ProcedureEvents
ON DATABASE
FOR DDL_PROCEDURE_EVENTS 
AS
BEGIN
          ROLLBACK
          PRINT 'Procedure modifications are not allowed on this database.'
          DECLARE @Scope VARCHAR(10)
          SET @Scope = 'Database'

          INSERT INTO DDLTriggerTest..EventTableData
(XMLEvent, DatabaseName, SystemUser, TriggerScope)
          VALUES
(EVENTDATA(), DB_NAME(), SYSTEM_USER, @Scope)
END

Now, if we try to create a procedure on the database, we get the following message:

Procedure modifications are not allowed on this database.

(1 row(s) affected)

Msg 3609, Level 16, State 2, Procedure Test, Line 3

The transaction ended in the trigger. The batch has been aborted.

Our home-grown trigger allows us to log the attempt to create a stored procedure, and prevent that change from occurring.

Planning ahead

The key to creating an effective system for auditing security events on your server is to design a strategy for how best to use DDL triggers. Your strategy will involve balancing the actions you want to monitor and the actions you want to prevent from occurring on your server. If you prevent too many things from occurring, you may wind up prohibiting necessary production tasks. The right combination of monitoring and prevention will help generate an audit trail you can use to document compliance with Sarbanes-Oxley security requirements.

Tim Chapman is a Database Administrator for a large company in the financial services industry. Post a comment below or write to Tim at chapman.tim@gmail.com .


1 comments
karine.sogomonian
karine.sogomonian

Hi, The only question I have, how will having a trigger, tracking all activites affect performance on heavily updated databases? Thank you, Karine Sr SQL Server DBA

Editor's Picks