Tuesday, April 17, 2012

SQL Audit Trail with pass through user credentials

For a customer we needed to add an audit trail to the database.

When you first start looking on the internet, you will probably find this link: http://www.nigelrivett.net/AuditTrailTrigger.html. The mayor problem with this audit trail is that the table will be huge because it stores the change of every field in a table in a seperate row. For our audit trail we needed a more simple approach: Just a shadow copy of the record which was edited. After some investigation I've found the build in SQL feature called 'Change Data Capture', which is only available in the enterprise edition. This is a little above our budget of 0 euro (as usual in IT it may cost nothing) ;). The other problem I found with Change Data Capture is that it isn't transparent enough. It does it's job, but nobody knows exactly how it works, I find this a bit tricky for our production environment.

So after just some more (2 hours!) searching I've come to the magnificent piece of SQL you'll find below from http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL.

After some minor adjustments it was ready to go!

But wait...

Our website users don't connect directly to the database, they use LINQ to SQL which has the user credentials of our Application Pool, so we still can't see who did the change :( StackOverflow has the answer: http://stackoverflow.com/questions/3197607/how-to-pass-out-of-band-current-user-id-data-to-sql-server-2008. Apparently there is an option to set CONTEXT_INFO on a connection so we can pass some extra data from the website user.

Time to hack LINQ! We want to execute the following statement everytime a new connection is used by the user:
SET CONTEXT_INFO 0x<binaryusername>

First we create a base class for our DataContext on which we fetch all Connection StateChange events. When the connection is opened send the 'SET CONTEXT_INFO' statement.
public class DataContextAuditBase
{
    public DataContextAuditBase(string connection) : base(connection)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(IDbConnection connection) : base(connection)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(string connection,
             MappingSource mappingSource) : base(connection, mappingSource)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(IDbConnection connection,
             MappingSource mappingSource) : base(connection, mappingSource)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    private string UserName
    {
        get { return (HttpContext.Current != null) ? HttpContext.Current.User.Identity.Name : Environment.UserName; }
    }

    private string StringToHex(string convert)
    {
        System.Text.ASCIIEncoding  encoding = new System.Text.ASCIIEncoding();
        return BitConverter.ToString(encoding.GetBytes(convert)).Replace("-","").ToLower();
    }

    private void ConnectionStateChange(object sender, StateChangeEventArgs e)
    {
        if( e.CurrentState == ConnectionState.Open)
        {
            ExecuteCommand("SET CONTEXT_INFO 0x" + StringToHex(UserName));
        }
    }
}

And change the BaseClass of the DataContext:


Run the newly created website and see the magic happening, right before your eyes!


The used audit trail stored procedure (used to create tables on which you want to use the audit):
/******************************
** Name: sp_GenerateAuditTable
** Desc: creates an audit table in a database of  your choosing from a user 
**       table that you specify in whatever database you run the sproc against
** Original Author: Cedric Baelemans
** Modifying Author: Andrew Tappert (2011-3-17)
** Modifying Author: Luuk Sommers (2012-04-17)
** Date: 2012-04-17
** 
** Found on: http://sameproblemmorecode.blogspot.com/2012/04/sql-audit-trail-with-pass-through-user.html
** More info on original: http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL
**************************/
ALTER PROCEDURE [dbo].[sp_GenerateAuditTable]
 @TableName varchar(128),
 @Owner varchar(128) = 'dbo',
 @AuditNameExtension varchar(128) = '_shadow',
 @DropAuditTable bit = 0,
 @AuditDatabaseName varchar(128) = null
AS
BEGIN
 declare @sql nvarchar(4000)
 
 if not exists( SELECT schema_name FROM information_schema.schemata WHERE schema_name = @Owner )
 BEGIN
  PRINT 'Creating chema [' + @Owner + ']'
  set @sql = 'CREATE SCHEMA [' + @Owner + '] AUTHORIZATION [dbo]'
  print @sql
  EXEC (@sql)
 END

 -- Check if table exists
 IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
  PRINT 'ERROR: Table does not exist'
  RETURN
 END
 
 IF @AuditDatabaseName is null
 BEGIN
   set @AuditDatabaseName = (select DB_NAME())
 END
 
 -- Check @AuditNameExtension
 IF @AuditNameExtension is null
 BEGIN
  PRINT 'ERROR: @AuditNameExtension cannot be null'
  RETURN
 END
 
 -- Drop audit table if it exists and drop should be forced
 IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
 BEGIN
  PRINT 'Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  set @sql = 'drop table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  print @sql
  EXEC (@sql)
 END
 
 -- Declare cursor to loop over columns
 DECLARE TableColumns CURSOR Read_Only
 FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
  FROM sysobjects a 
  inner join syscolumns b on a.id = b.id 
  inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname' 
  WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']') 
  and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 
  ORDER BY b.colId
 
 OPEN TableColumns
 
 -- Declare temp variable to fetch records into
 DECLARE @ColumnName varchar(128)
 DECLARE @ColumnType varchar(128)
 DECLARE @ColumnLength smallint
 DECLARE @ColumnNullable int
 DECLARE @ColumnCollation sysname
 DECLARE @ColumnPrecision tinyint
 DECLARE @ColumnScale tinyint
 
 -- Declare variable to build statements
 DECLARE @CreateStatement varchar(8000)
 DECLARE @ListOfFields varchar(2000)
 SET @ListOfFields = ''
 
 declare @AuditTableExists int
 declare @existsParams nvarchar(4000)
 set @existsParams='@Exists int output'
 set @sql = 'if exists (SELECT * FROM [' + @AuditDatabaseName + '].information_schema.tables  WHERE table_schema=N''' + @Owner + ''' and table_name=''' + @TableName + @AuditNameExtension + ''') set @Exists=1 else set @Exists=0'
 -- Check if audit table exists
 print @sql
 Exec sp_executesql  @sql, @existsParams, @Exists=@AuditTableExists output
 print @AuditTableExists
 IF @AuditTableExists = 1
 BEGIN
  -- AuditTable exists, update needed
  PRINT 'Table already exists. Only triggers will be updated.'
 
  FETCH Next FROM TableColumns
  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
   BEGIN
    SET @ListOfFields = @ListOfFields + @ColumnName + ','
   END
 
   FETCH Next FROM TableColumns
   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
 
  END
 END
 ELSE
 BEGIN
  -- AuditTable does not exist, create new

  -- Start of create table
  SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ('
  SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
 
  FETCH Next FROM TableColumns
  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
   BEGIN
    SET @ListOfFields = @ListOfFields + @ColumnName + ','
  
    SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
    
    IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
    BEGIN
     IF (@ColumnLength = -1)
      Set @CreateStatement = @CreateStatement + '(max) '   
     ELSE
      SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '   
    END
  
    IF @ColumnType in ('decimal', 'numeric')
     SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '   
  
    IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
     SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
  
    IF @ColumnNullable = 0
     SET @CreateStatement = @CreateStatement + 'NOT '   
  
    SET @CreateStatement = @CreateStatement + 'NULL, '   
   END
 
   FETCH Next FROM TableColumns
   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  END
  
  -- Add audit trail columns
  SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
  SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
  SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
  SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,' 
  SET @CreateStatement = @CreateStatement + '[AuditContext] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)' 
 
  -- Create audit table
  PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  print @CreateStatement
  EXEC (@CreateStatement)
 
  -- Set primary key and default values
  SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD '
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY  CLUSTERED '
  SET @CreateStatement = @CreateStatement + '([AuditId])  ON [PRIMARY], '
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp]  DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp],'
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditContext]  DEFAULT (CAST(CONTEXT_INFO() AS VARCHAR)) for [AuditContext]'
 
  EXEC (@CreateStatement)
 
 END
 
 CLOSE TableColumns
 DEALLOCATE TableColumns
 
 /* Drop Triggers, if they exist */
 PRINT 'Dropping triggers'
 
 
 set @sql = '
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Insert]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]'')
 
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Update]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]'')
 
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Delete]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]'')'
 
 
 exec (@sql)
 
 /* Create triggers */
 PRINT 'Creating triggers' 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Insert ON [' + @Owner + '].[' + @TableName + '] FOR INSERT AS INSERT INTO ['+ @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted'
 print @sql
 EXEC (@sql)
 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Update ON [' + @Owner + '].[' + @TableName + '] FOR UPDATE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted'
 print @sql
 EXEC (@sql)
 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Delete ON [' + @Owner + '].[' + @TableName + '] FOR DELETE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted'
 print @sql
 EXEC (@sql)
 
END

Use the comments for any issues, questions and of course comments :)

Happy auditing!

Luuk