column based auditing?
in [Server Configuration]
|
Prev: Maintenance scripts
Next: SQL Server cannot connect
From: Jan Waiz on 6 Jul 2010 07:51 Hi Craig, did you find a solution? regards "Craig Lister" <cdotlister(a)gmail.com> schrieb im Newsbeitrag news:ad51480b-80ab-4859-8290-81d80aeffd19(a)x18g2000pro.googlegroups.com... > Hi guys. > > On a previous project, the requirement was just to store who updated a > row, and when. So, we have a: > > created_date, created_user_id, modified_date and modified_user_id > > However, phase 2 of this project - they want to know which fields in > the row were edited by which user! So, basically, per field auditing. > This is something I have never done. There's also two ways a field can > be updated. It can be updated by a user, or via an automated process, > at which point, a user should be stopped from editing it (client side > enforcement). So, I was thinking that we could make a nullable field > for the updated_user.. and if it's null, it was automated, and > therefore locked... > > But.. this has to cover all the columns! It would be very messy to > have, for example, a 'description' field, and then a > 'description_modified_date', and 'description_modified_user_id'.... > > I was thinking maybe a separate table that covered all tables and > columns that we need to log, and then a trigger to update these? But > am not sure if this is a good move, or the most effective way of > handling this requirement. > > When we display the screen, it would be a hefty procedure to populate > the screen! We'd not only have to get the data, but then get all the > audit data - so that we can show the developer if the user can edit > the data or not. > > Headache! Unless there is a way that this is done previously?
From: Craig Lister on 19 Jul 2010 01:13 I haven't yet found anything reusable and simple to impliment. I think it's going to require a major rework of what we're doing at the moment. I'm leaning towards triggers.. and then having a 'ghost' table, matching the table that's being edited... and it will basically write a new row to the ghost table, everytime a change is made. So, lets say we have a table named Applications. I think we'll add an 'Application_change_history' table. Both tables have a 'Last_Update_User field, and a Last_Update_Date field. So, when the user clicks Save (or what ever), it generates an UPDATE into the Applications table... and then does a 'SELECT FROM Applications INTO Application_change_history. Then, to find out who made a change to a particular field... we can then compare 'versions' in the Application_change_history table... and look for what fields have changed. We can then somehow find the name of the user who changed those fields. Issue with this is that this history table will grow a lot! For every update, there will be a new row. Also, it won't be very easy to query. That is, a business rule we have is that if a field was altered by an external source, then users can no longer edit it. it must be read- only. So, when we build the screen, it will be pretty inefficient for the database to work out who last changed which fields... So, a bit stuck right now...
From: Erland Sommarskog on 19 Jul 2010 04:44
Craig Lister (cdotlister(a)gmail.com) writes: > Issue with this is that this history table will grow a lot! For every > update, there will be a new row. Yes, but the data may be archivable? > Also, it won't be very easy to query. Depends for what. For a generic comparison of data, that could be done. > That is, a business rule we have is that if a field was altered by an > external source, then users can no longer edit it. it must be read- > only. So, when we build the screen, it will be pretty inefficient for > the database to work out who last changed which fields... But this is a killer requirement, and whatever the solution it will be expensive. Personally, I lean towards that should keep this disctinct from the auditing. Maybe even as heavyduty like a xxxisfrozen column for every column to which this rules apply. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |