column based auditing?
in [Server Configuration]
|
Prev: Maintenance scripts
Next: SQL Server cannot connect
From: Craig Lister on 4 Jul 2010 21:02 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: Uri Dimant on 5 Jul 2010 00:46 Craig what versrion of SQL Server are you using? "Craig Lister" <cdotlister(a)gmail.com> wrote in message 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: Erland Sommarskog on 5 Jul 2010 04:57 Craig Lister (cdotlister(a)gmail.com) writes: > 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. If you are on SQL 2008 and have Enterprise Edition you could consider using SQL Server Audit for the task. Else a solution which is available with SQL 2005 and with any edition is to use XML. Here is a quick example from a database that I have: INSERT auditlog (tablename, keyvalue1, action, beforeimage, afterimage) SELECT 'accounts', coalesce(i.accno, d.accno), CASE WHEN i.accno IS NULL THEN 'D' WHEN d.accno IS NULL THEN 'I' ELSE 'U' END, (SELECT * FROM deleted d2 WHERE d2.accno = i.accno FOR XML RAW, TYPE), (SELECT * FROM inserted i2 WHERE i2.accno = i.accno FOR XML RAW, TYPE) FROM inserted i FULL JOIN deleted d ON i.accno = d.accno As you can tell from the appearance of inserted/deleted, this is part of a trigger. In this example, accno is the primary key of the table. That is, in this table we store a copy of the row as it looked before and after the update. Currently we don't use this table for anything but manual investigations, but an idea is to write a generic GUI which permits the user to browse changes. The GUI would present the table names and the field names, but only show the differences. It would may be not be the best user experience, but it would be a lot easier to implement. (In the example above, you don't see username, the time for the operation etc. There are column for this in the table, but as they have default values, you don't see them in the SELECT statement.) > 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... This is certainly an extra challenge, and it does not sound like a requirement that is particularly cheap to implement. You could use the audit table to find out if the most recent change was made by automatic user, but in the model above it's not easy to dig out this information. You should probably track this status separately from auditing. Maybe a table keyed by tablename, keyvalue and column name. You add a row when a column gets locked. But I cannot say that I like this terribly much. Having metadata as data in the user tables is OK for auditing, but when you use it for enforcing business rules, it's less appealing. But the alternative is to have a "is_locked" column for every column which is unpleasant as well. Usually, these kind of ugly requirments tends to evaporate. If you tell the people who decides that this requirement will cost an arm and a leg to implment they may reconsider. Maybe locking is only needed on row level? -- 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
From: Jan Waiz on 5 Jul 2010 08:43 Hi Craig, if i understand you problem right, may be the OUTPUT clause will help you tracking all that individual values. USE AdventureWorks; GO DECLARE @MyTableVar table( ScrapReasonID smallint, Name varchar(50), ModifiedDate datetime); INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE()); --Display the result set of the table variable. SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar; --Display the result set of the table. SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason; GO Above Example (from Help *s*) will show you how you can use it. In that example only inserted rows and columns (!) are tracked in the temp-table. You can use any other valid technic instead of a temp-table - but do so is the most flexibal one. The OUTPUT-Clause will work on an UPDATE and DELETE also. And via SQL-Functions like SUSER_SNAME and/or SUSER_SID you can track additional information about the current logged in user. Try out and let me know if that helps Regards Jan Waiz "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 5 Jul 2010 19:30
On Jul 5, 2:46 pm, "Uri Dimant" <u...(a)iscar.co.il> wrote: > Craig what versrion of SQL Server are you using? Microsoft SQL Server 2005 - 9.00.4226.00 (Intel X86) Just reading the other replies.... thanks guys. |