Trigger Audit Trails - who deleted my record?

Question

Trigger Audit Trails - who deleted my record?

Eric Swanson-355778

Say Hey Kid

Points: 666
More actions
August 23, 2006 at 12:13 pm

#114099

I want to keep an audit log of updates and deletes to a table. The table has a column "created_by" (an identifier of the user). So, the update trigger can use the "INSERTED.created_by" to determine who updated the old record. However, a delete does not have a new record to look at.
A few options I've considered:
- Add a nullable "deleted_by" column
Although quick and simple, it makes selects more complex and greatly affects performance if the records remain in the table. An automatic and scheduled service to log and remove these records would improve this option.
*This option could be improved by requiring an UPDATE of "deleted_by" before a DELETE. An "INSTEAD OF DELETE" trigger would check that "deleted_by IS NOT NULL" and then log the record(s). Then, I could use an "INSTEAD OF UPDATE" trigger to log old records (using "INSERTED.created_by"). Additionally, the update trigger could check if the only column updated was "deleted_by" and ignore the logging (assuming that a "DELETE" would follow).
- Stored Procedure
Complete control, but every transaction would be singular, requiring all DELETE operations to use the sproc.
- Audit Meta-Data Tables
- Use SQL 2005 .NET Logic
I haven't really thought through all of the options here and I'm attempting to keep my solution as platform-independant as possible.
Comments? Other options?
--Eric Swanson
http://www.ericis.com/
Posts++;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

That won't help either (the Created_by) because it is not automatically updated during an update unless you have a trigger to do that... and, as you pointed out, even that won't help with DELETE's.

You need to include a couple of things in your trigger...

USER - Returns the system login name of the user. If you allow Windows Logins to the database, it will return that name, otherwise, it will return the SQL Server login name.

HOST_Name - Returns the name of the machine that the user was logged into whether by Windows or by SQL Server.

You can also get a huge amount of information by including something like the following in the trigger including the name of the program that may have been running (if it was a program that deleted it)...

SELECT *

FROM Master.dbo.SysProcesses

WHERE SPID = @@SPID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rstone SSCertifiable Points: 6209 More actions · Answer 2

Sometimes the only information available to identify the source of the process will be the net_address (MAC). As a last resort, it can be used to track down the network card if no other info is available. Unfortunately, new MACs can be created often for some users (e.g., wireless). This value is available in sysprocesses.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Here are a couple of links which may be of use...

http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html

http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Eric Swanson-355778 Say Hey Kid Points: 666 More actions · Answer 4

I should have noted that users are using connection pooling on shared connections. Primarily web applications. So, SQL Server user and process functions will not properly identify a user.

The column "created_by" and the proposed column "deleted_by" are pointers to an internal "user_id" in a separate table. Client applications are expected to properly record the user's id. Additionally, these columns are monitored for possible misuse and each user can report activity they did not initiate.

Logging UPDATE and DELETE transactions to a record would further allow us to monitor the types of changes to any given record for each user.

It would be easy to create an interface to the database through stored procedures that requires a "user_id" for each transaction. However, this would eliminate bulk operations on multiple records.

My solution thus far is to add the "deleted_by" column, *trust* that client applications will populate the column correctly, deny attempts to delete any records with where "deleted_by IS NULL" through an "INSTEAD OF DELETE" trigger and log all other deletes, and use an "INSTEAD OF UPDATE" trigger to log all updates, but ignore updates limited to the "deleted_by" column alone.

The only other option I can think of is to introduce the logging into the data access layer code. However, I would like to attempt to keep this logic in the database, since I feel the concept deals with data security and integrity rather than data gathering and communication.

--Eric Swanson
http://www.ericis.com/

Posts++;