generic audit trail best practice questions

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,
jasonjason (iaesun@.yahoo.com) writes:
> 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?
I don't think you should have referential integrity for users. Those should
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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/d...
ethowto15.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.
"jason" <iaesun@.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
>|||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?|||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
"jason" <iaesun@.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?
>|||jason (iaesun@.yahoo.com) writes:
> 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.
OK. But keep in mind what said about the user names.

> so i'm curious, how do you enforce referential integrity for diverse
> audited entities?
So, I lied a bit. :-)
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||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|||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.
"jason" <iaesun@.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
>