Audit Trail in web application using sql server
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
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.