Audit Trail in web application using sql server

Question

We are developing a web application using asp.net and sql server. We are required to do an Audit trail for the application. As I understand this, an audit trail would basically be for all the Inserts, Updates and Deletes on the data base right? Now the way to approach this is that I have an Audit Trail Table in the DB that populates after every insert,update or delete is fired (Manually writing the script within the DAL). However any DB changes directly fired from SQL Management studio will NOT be recorded (for obvious reasons :P).

To cater for that I could create a trigger and that takes care of everything. I also did some googling and found out that SQL server has the ability to do audit trail. However the problem with using triggers is that I will NOT get the user information that logged in the website. I will get the sql user but I dont give two hoots about that, I am concerned about the website user.

A solution that I figured out was either a) Have an audit trail from my web application and also have trigger set up. On the audit report, I simply show an audit log from web application and and audit log from sql server. Obvious problems with this approach: over head. Writing to two different sets of tables on EVERY DB CHANGE.

b) I have a column called UserId ON EVERY DB TABLE. Then I create a trigger to capture all the DB changes. I pass this userId on every table I change (insert,update,delete) and the get this id from the trigger. Obvious setbacks: unneccesary userid column in every table

I do appologize for the long post. Basically I need an audit log that does log all the db changes (including direct hack to db) but at the same times gives me user login information for those db changes that were made FROM the web application.

Will appreciate any input in this regard.

Many thanks

xeshu

Answer 1

How likely is it that there are going to be legitimate changes made to the DB by directly executing SQL queries against the database either through SQL management studio or otherwise. I would recomend assuming that all data in the data is entered via your application and have the auditing in your BL layer. You can then simply restrict access to the database to trusted users. Ultimately there has to be one or more users with permsion to alter the database schema, if those users wanted to bypass the auditing they could simply disable the triggers or fake the audit trail. If there are ever legitimate reasons for running direct SQL queries against the DB, e.g. infrequent data imports from other systems etc, then you can limit this acitivity to the trusted users and ensure their import scripts correctly populate the audit table. Anything that would put too much workload on your DBAs or whoever the trusted users are should be built into the appllication anyhow.

Answer 2

Thank you all for your replies. After some googling this is the approach that I think would be appropriate: Generic Audit Table

Audit_Table ( Id, TableName, RecordId, (link to the record in question) ModifiedBy, ModifiedOn, Type (I, U or D) )

Audit_Details_Table ( Id, AuditId, FieldName, OldValue, NewValue )

I think this should about do it. Any thoughts?

Answer 3

It sounds like you're on the right lines. However, you would generally not have a single audit trail table, but rather an audit table for every table. Thus, for every modification to a row in TableA, a new row is added to TableA_Audit containing the new state in TableA, plus the date and the name of the user.

A trigger is normally used for this, but if you're storing the web app's username, I don't know how to pass this data into a trigger (can anyone else help?) In this case, I might be tempted to use stored procedures. For each table, have stored procedures to insert, update and delete rows. These stored procedures would each call another stored procedure which inserts the row into the audit table. This way, you easily pass in the web app username to the stored procedure which inserts the row into the audit table. Obviously the downside is to have to maintain a bunch of stored procedures for each table, which can be a bit tedious as you have to ensure they all keep in step with the tables (and the application's data access layer) as schema changes are inevitably required.

Note that you don't need a Username column in every table, only in every audit table.

Hope some of that was of use.

Cheers

David

Answer 4

I agree with both the other posters. Bottom line is that if you want to store your web app user's username (ie. your custom authentication) then triggers are NOT going to help you audit what's going on. - Caveat unless you can use Integrated Authentication

This is really important if you want to also use the audit trails for monitoring volumes of activity by user for example. The solution to this is to either perform all DDL via Stored Procedures and within those stored procedures add in your audit logic (if you want all of the logging written in T-SQL). Alternatively do it from the application and look at one of the many logging libraries available for ASP.Net such as NLog.