|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
generic audit trail best practice questionsso i have the need for an audit trail for certain activities. ideally i would like a generic audit trail table, that denotes the kind of activity, the entity reference it was performed on, and the entity reference it was performed by. for example, there are currently entities like so (greatly simplified): create table user ( userid uniqueidentifier primary key not null, name varchar(50), email varchar(50)) create table employee ( employeeid uniqueidentifier primary key not null, name varchar(50), role varchar(50)) create table businessthingy ( thingyid uniqueidentifier primary key not null, description varchar(50), modifiedby uniqueidentifier not null) create table otherbusinessthingy ( otherthingyid uniqueidentifier primary key not null, quantity int) create table audit ( auditid uniqueidentifier primary key not null, activity varchar(50), performedby uniqueidentifier not null, performedon uniqueidentifier not null) so that's a rough sketch. some activities are frequent enough, and unimportant enough that just capturing rowlevel audits is fine, which is why there is a 'modifiedby' in the businessthingy table. we don't want or need to keep track of a full history of edits, just knowing the last person to edit something is good enough. the audit table will capture more important (and less frequent) activities though, such as inserts and deletes. so my first question is: is it even possible to impose referential integrity in this scheme? for example, the businessthingy can be modified by both users and employees. also, the audit event can be performed by both users and employees, and they can be performed on both businesthingies and otherbusinessthingies. i can freely insert the id's in appropriately, but can i enforce referential integrity across multiple possible tables? my second question is: are there better practices for this kind of thing in order to ensure referential integrity? or is this a typical hurdle for generic activity auditing? thanks for any help / advice, jason jason (iae***@yahoo.com) writes:
> so my first question is: is it even possible to impose referential I don't think you should have referential integrity for users. Those should> integrity in this scheme? for example, the businessthingy can be > modified by both users and employees. also, the audit event can be > performed by both users and employees, and they can be performed on > both businesthingies and otherbusinessthingies. i can freely insert the > id's in appropriately, but can i enforce referential integrity across > multiple possible tables? be readable ids or names. Think sa and Query Analyzer. That's also stuff you need to audit. References for the audited entities is another matter. Here it's more relevant to referential integrity. I think we do this in most of our auditing. Important thing if you don't you need to store something that users can read later. Storing a GUID which means nothing is not a good thing. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx we have deliberately chosen guid's for all of our entity relational
keys for a variety of reasons, so wherever we are referring to another singular entity, guid is how we want to do it. if we want to pull user digestible information, then we do that as reporting functionality through the foreign relationship of entities. so i'm curious, how do you enforce referential integrity for diverse audited entities? jason (iae***@yahoo.com) writes:
> we have deliberately chosen guid's for all of our entity relational OK. But keep in mind what said about the user names.> keys for a variety of reasons, so wherever we are referring to another > singular entity, guid is how we want to do it. if we want to pull user > digestible information, then we do that as reporting functionality > through the foreign relationship of entities. > so i'm curious, how do you enforce referential integrity for diverse So, I lied a bit. :-)> audited entities? Some of our autiding stuff are specific to a process, so there are no generic columns, but all columns have a unique meaning. Ref. integrity is trivial. Then we have at least one generic log table where the keys are "tablename", "keyvalue1", "keyvalue2", and "columnname". This table has no referential integrity. For SQL 2005 our idea is to improve our auditing (which is a bit substandard), by using the xml datatype, and simply log an after- image of the row with XML. A generic client could then easily show the user only differences between two updates. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx interesting. is there any concern whether the XML after-image affects
the throughput of each transaction significantly? thanks very much for this information. your practices make a lot of sense, in terms of which audits can and cannot have referential integrity. very helpful! jason jason (iae***@yahoo.com) writes:
> interesting. is there any concern whether the XML after-image affects I will have to admit that we have not analysed this. This far it is only> the throughput of each transaction significantly? an idea that we have. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I've used trigger / audit table based auditing in the past, but have found
it to be cumbersome to program and brittle, especially in systems that are complex or in a constant state of development. There were occasions where trigger caused transactions to fail, becuase of flaws in the audit programming or when the data model changed. Instead, consider using SQL Server Profiler for auditing: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto15.asp Unlike triggers, it requires no programming and, it can be configured to audit most any event at a ganular level: including table updates, table selects, execs; even deadlocks, locking. The output can be directed to a SQL Server table, and a scheduled job can query for specific red flag events and send notifications. You can get it up and running in a single afternoon, and it's simple to enable / disable events on specific objects or users, if needed. Also, it can be used for analyzing system performance. Show quote "jason" <iae***@yahoo.com> wrote in message news:1137171394.413876.147000@z14g2000cwz.googlegroups.com... > hello everyone, > > so i have the need for an audit trail for certain activities. ideally i > would like a generic audit trail table, that denotes the kind of > activity, the entity reference it was performed on, and the entity > reference it was performed by. > > for example, there are currently entities like so (greatly simplified): > > create table user ( > userid uniqueidentifier primary key not null, > name varchar(50), > email varchar(50)) > > create table employee ( > employeeid uniqueidentifier primary key not null, > name varchar(50), > role varchar(50)) > > create table businessthingy ( > thingyid uniqueidentifier primary key not null, > description varchar(50), > modifiedby uniqueidentifier not null) > > create table otherbusinessthingy ( > otherthingyid uniqueidentifier primary key not null, > quantity int) > > create table audit ( > auditid uniqueidentifier primary key not null, > activity varchar(50), > performedby uniqueidentifier not null, > performedon uniqueidentifier not null) > > so that's a rough sketch. some activities are frequent enough, and > unimportant enough that just capturing rowlevel audits is fine, which > is why there is a 'modifiedby' in the businessthingy table. we don't > want or need to keep track of a full history of edits, just knowing the > last person to edit something is good enough. > > the audit table will capture more important (and less frequent) > activities though, such as inserts and deletes. > > so my first question is: is it even possible to impose referential > integrity in this scheme? for example, the businessthingy can be > modified by both users and employees. also, the audit event can be > performed by both users and employees, and they can be performed on > both businesthingies and otherbusinessthingies. i can freely insert the > id's in appropriately, but can i enforce referential integrity across > multiple possible tables? > > my second question is: are there better practices for this kind of > thing in order to ensure referential integrity? or is this a typical > hurdle for generic activity auditing? > > thanks for any help / advice, > > jason > interesting. i'm not sure this would have the 'user' level that we
would need though. you see users of the application do not map to database users. we use the application profile for sql identification, and rely on data content to define the state of user entities (ergo the user table). could we, in that setup, use profiler to capture data at a user level, when the user entities are not database users, but application users stored in a table? When the application performs an operation on the database, do you call a
stored procedure with the user's id as a parameter like so? exec usp_EditInvoice @InvoiceID=3607, @UserID=8 Show quote "jason" <iae***@yahoo.com> wrote in message news:1137182806.078657.32390@f14g2000cwb.googlegroups.com... > interesting. i'm not sure this would have the 'user' level that we > would need though. you see users of the application do not map to > database users. we use the application profile for sql identification, > and rely on data content to define the state of user entities (ergo the > user table). could we, in that setup, use profiler to capture data at a > user level, when the user entities are not database users, but > application users stored in a table? > yes, something quite like that. we call it an 'authorityid' but
basically equates to either a userid or an employeeid, depending on who is interacting with the entity SQL Server Profiler can trace the command text of stored procedure calls,
including what parameters were passed. This will tell you what was executed, when, and by whom. It can also trace other event attributes such as the duration of the execution and application name. For example: 2005/01/16 10:31am exec usp_EditInvoice @InvoiceID=3607, @AuthroityID=8 However, using seperate SQL Server logins for each user (especially Windows Authenitcated accounts) makes for easier and more robust auditing. Show quote "jason" <iae***@yahoo.com> wrote in message news:1137421941.558008.58370@z14g2000cwz.googlegroups.com... > yes, something quite like that. we call it an 'authorityid' but > basically equates to either a userid or an employeeid, depending on who > is interacting with the entity > ahh, i see, that does sound potentially useful. i'll see what we can do
with that. thanks very much. there are several different priorities to consider, such as
1. Speed of retrieving a historical row. If you go for a generic audit table Erland was speaking about, then assembling a historical row might take quite a time. That might be OK. Yet if you need to get old versions as fast as the current one, that's a different story. 2. Storage space and maintenance. If you go for a generic audit table, you might (or might not) end up with a very large table, which could be a pain in the neck to maintain. Consider what indexes do you want on it. Consider possible fragmentation. Good luck! thank you very much for these considerations!
i think right now we aren't even looking for something as thorough as historical row recreation, honestly. we would just like to say "for entity X, who performed updates on it?" maybe once we got something like that in place we could start wishing we had historical row auditing :) thanks again! jason Alexander Kuznetsov (AK_TIREDOFSPAM@hotmail.COM) writes:
> 1. Speed of retrieving a historical row. If you go for a generic audit That's a good point. I should maybe develop the idea of our generic> table Erland was speaking about, then assembling a historical row might > take quite a time. That might be OK. Yet if you need to get old > versions as fast as the current one, that's a different story. loggin with xml a bit. The table would look something like this: CREATE TABLE xmllogs (logid int IDENTITY, tablename sysname NOT NULL, keyvalue1 sql_variant NOT NULL, keyvalue2 sql_variant NULL, moduser sysname NOT NULL CONSTRAINT df_moduser DEFAULT original_login(), moddate datetime NOT NULL CONSTRAINT df_moddate DEFAULT getdate(), appname sysname NOT NULL CONSTRAINT df_appname DEFAULT app_name(), hostname sysname NOT NULL CONSTRAINT df_hostame DEFAULT host_name(), afterimage xml NULL, CONSTRAINT pk_xmllogs PRIMARY KEY NONCLUSTERED (logid) Then there would be a generic application where you can select on table name, and optionally specify a key value to search for. To see changes of an entity, the generic application would read each afterimage, compare them, and then only display the diferences. If the afterimage was NULL, this means that the row was deleted. Since xml is self-describing, the application would not have to know anything about the tables. Of course, for an end-user interface, table and column names could be bit rough, but you could add some look-up table for some nice names. Anyway, if the application itself has need to know things like "which category did Customer A belong to 2005-06-08, the this is not a very good table to search in. To that end, we have other historic tables. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics