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
>

Generating an audit trail in SQLS 2000

Hi,
Following on from a recent post regarding Identity fields with David Portas,
I was discussing a few issues with a collegue and an interesting issue was
raised.
Namely, creating an audit trail in which every row is assigned a unique,
gapless sequential reference. For example, our accountancy package (Sage)
has an audit trail in which every transaction has a numbered reference. Each
number is unique and there are no missing numbers. Obviously some
transactions are cancelled, others may not be assigned in exactly the order
that they were created, etc etc.
I'm just wondering how I would achieve such a thing in SQLS. From talking
with David Portas in my last thread I understand that IDENTITY is not the
way ot achieve this, but I'm curious as to how exactly to achieve this in a
multi-user concurrent environment?
For example, have a unique ID table in which numbers are assigned as they
are required - although I imagine that this would require extensive use of
transactions and locking...
Alternatively would be to have an ON INSERT trigger which simply assigns the
last row reference plus one to the reference field?
Any pointers / sites / etc?
I appreciate that this is a big vague but it's something that I can see
being very useful in the near future.
Any and all advice is gratefully received.
Regards
Chris.One possible solution is at
http://solidqualitylearning.com/blo...04/04/446.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Chris Strug" <hotmail@.solace1884.com> wrote in message
news:%23r5dC30PFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Following on from a recent post regarding Identity fields with David
Portas,
> I was discussing a few issues with a collegue and an interesting issue was
> raised.
> Namely, creating an audit trail in which every row is assigned a unique,
> gapless sequential reference. For example, our accountancy package (Sage)
> has an audit trail in which every transaction has a numbered reference.
Each
> number is unique and there are no missing numbers. Obviously some
> transactions are cancelled, others may not be assigned in exactly the
order
> that they were created, etc etc.
> I'm just wondering how I would achieve such a thing in SQLS. From talking
> with David Portas in my last thread I understand that IDENTITY is not the
> way ot achieve this, but I'm curious as to how exactly to achieve this in
a
> multi-user concurrent environment?
> For example, have a unique ID table in which numbers are assigned as they
> are required - although I imagine that this would require extensive use of
> transactions and locking...
> Alternatively would be to have an ON INSERT trigger which simply assigns
the
> last row reference plus one to the reference field?
> Any pointers / sites / etc?
> I appreciate that this is a big vague but it's something that I can see
> being very useful in the near future.
> Any and all advice is gratefully received.
> Regards
> Chris.
>
>|||If you don't mind having gaps in numbers i.e reserve IDs before you
write. Sticking the next available ID in a one row table is good
performance wise. You only lock one very small table rather than the
table you are inserting data into.
The processes that will write can take their time because they have
their unique IDs already and so don't stop other writes reserving
their IDs. Also you can reserve 100 IDs very easily at no cost just by
adding 100.
But you will get gaps, after reserving ID if your write fails.