July 8, 2010 at 12:56 am
I've an SP which updates few columns through an application. But there are chances that the admin guys can update these columns also.
I've added a column which will log the user who updates these column/s using query analyzer. How would I differentiate if the some one has updated the table column/s through query in query analyzer?
July 8, 2010 at 1:24 am
This is a tough question, for the simple reason that admins can do anything in a database. You've mentioned Query Analyzer by name - are you working with 2000, or is that just habit?
In 2005, you can create auditing DDL triggers to log this sort of activity, but these triggers can fail (potentially breaking your application) if SET options are significantly different, so they're not always the most reliable. Even if you do this, it's still possible for an admin to disable the trigger while they make the change.
Another option, (2000+) is to remove all access to the tables and process everything via stored procedures. However, again, admins can get around this.
Even if you use DEFAULT values to automatically put in the application name, or the username of the user making the change, it's still possible to override these.
Another option is to use a row-level checksum (2005/2008, but possible in 2000 if you have a hashing function). I've detailed this process in a blog post (http://www.jimmcleod.net/blog/index.php/2009/04/23/applications-using-hashing-with-sql-server-20052008/ in section "Data Integrity"), but the general outline is that you code up a hashed value in your application, and create a hash from all your columns (including your ModifiedBy column). If the DBA has no way of knowing how the hash was generated, then he cannot update a row without triggering an alarm next time your application accesses that row.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply