SQL Audit Part 9: Wrapping It Up

So, to warp up, we have a SQL level audit that fires a tigger to save the audited data.  You will notice that I get the primary key, so using various tools you could set up the UX to notice that the unerlynig objects are audited and then return the audit trail for a specific object.

Anyway, the "install" file is located at the following location:
MediaFire File Download: CompleteSQLDataAudit_v1.0.sq

Do let me know your thoughts or if you have any questions with the source code.  You should be able to open it in MS SQL Server Management Studio and run it in any 2008 database (assuming it works in other versions I don't have, see the first post about that).  The audit control tables and stored procedures do not need to be in the same database as the tables to be audit.  And you can audit tables in more than one database.

To start auditing a table you can just run the StartAuditFor stored procedure, and so long as the account has the access to the source table it will all work.

To re-compile the audit structure after updating a source table structure, execute the ReCompileAuditStructure stored procedure and it will check all currently audited tables.

To archive the old audit records, run the ArchiveAuditTables stored procedure. I would recommend this be done in a SQL Job, which I would run before the nightly backup for your SQL database (might as well clean up the data before you back it up).

SQL Audit Part 8: Data Archiving Script

The archiving of the records is a simple affair.  All that we need to make sure of is that we don't archiving something twice, or that we don't delete something we haven't archived.  For high volume tables, this can get interesting, especially if you want to remove the audit records as soon as they are written. (That is, every time that the archive script is run.)


So, to get around that we mark all of the records we want to copy first, then we copy all the marked records, then delete the audit records that are marked.  Makes it nice and neat. While it creates an extra query, it is a safegard that is worth it.  While playing with audits you can never be to careful.


So that about wraps up the project, more or less.  The last part in the series details where you can find the source code (complete) and some other odds and ends.

SQL Audit Part 7: Audit Table Maintenance Script

Now we have the audit started and working, what is needed now is to manage it.  By manage, I mean that when we change the source table structure, we don't want to have to manually alter the tables that store the audit data.

Rather than recreate slightly modified code to manage the the tables, I coded the creation script so that it will do both.  Yes, I know that this is cheating (I planned the development that way) but coding is meant to be done smartly, not just with brute force but also finesse.  So, to manage both in the one script, there are a few principals to use.

1. Check before create.

Before you do what you need to, check that it hasn't already been done.  In some cases this will mean that if it is there you do nothing, in other cases you will delete it so that you can recreate it, and in more you update instead of delete.  That is a decision that you can make based on your code, and what your wanting to achieve.

2. Understand both requirements.

Updating code and creating code can be combined, but there are some cases where this is not advisable.  You need to understand both how the creation needs to work and how the update needs to work to be able to know if the functions can be combined, and then the best way to achieve that.

3. Make your code manageable.

This is more of a generic coding principal, but it still applies.  When you combine 2 functionalities into the one piece of code, it makes it that much more complex. The best way to manage the complexity it so make sure that your code is readable, and to make sure that another can come along and understand it with without you pointing out whats what.  On this one I am continually learning, but I think I am getting better.

Then, with that in mind, I can just create a cursor to loop though the tables that have been started, and re-run the creation stored procedure over it, and were done.

SQL Audit Part 6: Creating The Trigger

So, now we have the database tables created for the audit and the archive, now we need to create the trigger to perform the audit. Now the code for this, based on what I have done to date, is what you would expect.

Set @sql = '';
Set @sql = @sql + N'IF EXISTS (SELECT name FROM sysobjects
WHERE name = ''adt_tgr_'+@auditTableName+''' AND type = ''TR'')
DROP TRIGGER adt_tgr_'+@auditTableName
SET @sql = 'EXEC '+cast(@databaseName as nvarchar(255))+'.sys.sp_executesql N'''+REPLACE(@sql,'''','''''')+'''';
exec sp_executesql @sql;

However there is this part that is the check for the existing trigger. We cant drop a trigger that doesn't exist, but where it does exist we want to drop it so that we recreate it. It is pretty basic, but gets the job done, and also shows the use of the sp_executesql in the source database.

With this in place, the audit is now running.  Any insert, update or delete command on the source database table will create the audit record in the audit table.  But there are still 2 things missing from the original brief, to be able to maintain the structures of the audit and archive tables at the database level, as well as the ability to archive the audit records after a set time so that we don't keep records to the database we don't need.  And these will be covered in the next 2 posts.

SQL Audit Part 5: Altering the Tables - Source Defined Fields

So, we have now got a manageable setup that will allow us to add the audit required fields to the audit and archive tables. Now we need to replicate the source table structure in those tables. The basics are the same with the audit fields, but we get the structure from the source table.

CREATE TABLE #adtSourceTable (
FieldName varchar(255),
FieldType varchar(255),
FieldSize int
);
SET @sql = N'Insert into #adtSourceTable
Select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from '+@databaseName+'.information_schema.columns
where TABLE_NAME like '''+@TableName+'''';
EXEC sp_executesql @sql;

If you look above you will see how we get the list of fields and their definition.  We look at a table called information_schema.columns to get the information that we need.  Now this table will get more fields than we actually need, so we only get the fields that we do need, namely the name, type and size or length.

The rest of this part of the code is almost an exact mirror of the code that creates the audit fields in both of the tables.

SQL Audit Part 4: Altering the Tables - Audit Defined Fields

So, we have our tables created with an ID field, now we need to add to those tables the basic fields to store information about the audit of each record, such as action, date time, status etc.  Now, for ease of altering the set of required fields, I have placed the field names and specifications into another table.  When I need to add a new field, I can just add a new record to the table.


SET @sql = N'IF EXISTS(SELECT * FROM '+@databaseName+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='''+@dbSchema+''' AND CONSTRAINT_NAME=''DF_'+@auditTableName+'_'+@FieldName+''' AND TABLE_NAME='''+@auditTableName+''')
BEGIN
alter table '+@databaseName+'.'+@dbSchema+'.'+@auditTableName+' drop constraint DF_'+@auditTableName+'_'+@FieldName+';
END';
SET @sql = 'EXEC '+cast(@databaseName as nvarchar(255))+'.sys.sp_executesql N'''+REPLACE(@sql,'''','''''')+'''';
EXEC sp_executesql @sql;


One of the issues I needed to address was adding constraints to the audit table in the source database.  To do that, I found out that the sp_executesql command actually was an object in each database, so to call the instance of the stored procedure in another database you can use <database>.sys.sp_executesql <nvarchar execute to>

Yes, I know that for some, this isn't earth shattering, but for me who was doing this to improve my SQL skills, it was a revelation.  Even helped me to re-factor my earlier code to the manner that it is now (more reliable and better performing).  I used the same thoughts on getting the table list to see if the table existed in the database.

So, the above code will remove a constraint on a table in a different database.  A very handy concept on cross database management.

Again, I will try to find somewhere to upload all of the code, but for now I am just pointing out the smart (in my opinion) aspects of each functionality.

SQL Audit Part 3: Creating the Tables

The first thing I needed to do was to determine that the source table does indeed exist.  Now, assuming that @databaseName and @tableName are inputs to the stored procedure, look at the following code:


CREATE TABLE #adtMyTableListCheck ( 
dbName varchar(255), 
dbSchema varchar(255),
tblName varchar(255), 
tblType varchar(255)
);

SET @sql = N'Insert into #adtMyTableListCheck 
Select * From '+@databaseName+'.INFORMATION_SCHEMA.TABLES 
where TABLE_NAME like '''+@tablename+'''';

EXEC sp_executesql @sql; 
SELECT TOP 1 @dbSchema = IsNull(dbSchema,''), @dbTableType = IsNull(tblType,'')  FROM #adtMyTableListCheck;
DROP TABLE #adtMyTableListCheck;


First we create a temp table to store our results into.  Then we search the database in question for the table we are after.  If an object is found, we get the type and Schema of the object (the object owner).  The object could be view or a table, so later on we check that using the table type.  But, you might ask why do we get the object owner? By using the object owner in the queries it allows the queries execution plan to be cached.  Now, for the queries that we are talking about it probably .002 of a second at most, but if you do that 100 times a second, you start to get some savings.  If you do that 1000 times a second even more, and so on.

That is about all that I need to explain from the start part the overall flow of the stored procedure, which is:

• Gather inputs, sort out table names to use etc.
• Determine existence of the source table
• Insert a record into the audited table list
• Make sure that the database tables for the audit and archive tables have the required audit fields
• Make sure that the tables have the correct fields for the source table
• Create the trigger for the audit

So, we have created the audit and archive tables, as well as checked that the table exists, now we need to create the fields required for the database audit.

SQL Audit Part 2: Overall Description of the Flow of Data

Now that we have set the ground rules for our little discussion, let me take some time to explain how I wanted the data to flow.

The image above shows you what I aim to do.  On inserting, deleting or updating data in the Source Table, I want to store that change in the Source Database (the Audit Table in the Source Database above).  Then, for "old" audit entries, I want to move them off the source database and onto an archive database (in this case called the Audit Controller Database) so that they don't take up space in the source database and could in theory be saved to disk to be kept for prosperity.  "Old" can be a variable time, in my mind calculated in days, some source tables would have a small "old" value, whilst others would have a large one, all in the same application.

All of this would be managed by triggers (to audit the data and store it in the audit table) and stored procedures to create the audit table and archive tables, to create the triggers, to maintain the triggers and tables, and to archive the audit table data to the controllers database.

SQL Audit Part 1: The Aims

It has been a long time since my last post, but I think some people will think it is worth the wait.  Over the past few months, when I have had the time, I have worked on improving my SQL skills and understanding of databases.  Now, if your like me and you hate just reading things with no hands on practice, you will understand that I gave myself a little project definition to help me in my learning's.  I decided to write a SQL audit project.

The requirements of the project were as follows:

• Managed by stored procedures;
• Database level auditing (for speed);
• Always accessible via SQL (that is, I wanted a store somewhere where I would review the audited information without having to involve a DBA);
• I wanted the ability to archive the data after a set time.

I am just finishing up some final refactoring and logging parts, but I think I have something that will achieve this.

Some disclaimers before I continue I think are in order.  I am not a SQL DBA, just a developer wanting to improve his database skills.  The code I have written has not been thoroughly tested, nor would I recommend using it for a production environment without some form of testing in your Staging or User Acceptance Testing environments for speed and to make sure it doesn't break existing systems.  I have written this in Microsoft SQL Server 2008 (Express Edition), so there may be more features in other versions (such as Standard or Enterprise) that would break the code in this project, but again I have not tested this against other editions so am unaware of any issues with them.  Whilst you are free to use this for yourself, you do so at your own risk.

Now that that is out of the way, let me detail the parts of the "series" that I am going to write:

• The Aims (This post)
• Overall Description of the Flow of Data
• Creating the Tables
• Altering the Tables - Audit Defined Fields
• Altering the Tables - Source Defined Fields
• Creating The Trigger
• Audit Table Maintenance Script
• Data Archiving Script
• Wrapping It Up

I will try to find a location to upload the various files to create and review the various parts of what I have done, just looking into which source repository I should use, Codeplex or code.google.com.

OK, now without further ado let me begin!