SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger Audit Trails - who deleted my record?


Trigger Audit Trails - who deleted my record?

Author
Message
Eric Swanson-355778
Eric Swanson-355778
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 1
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++;
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (913K reputation)SSC Guru (913K reputation)SSC Guru (913K reputation)SSC Guru (913K reputation)SSC Guru (913K reputation)SSC Guru (913K reputation)SSC Guru (913K reputation)SSC Guru (913K reputation)

Group: General Forum Members
Points: 913168 Visits: 48759

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Randy
Randy
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4641 Visits: 965
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.

Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. ;-)
RyanRandall
RyanRandall
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12857 Visits: 4652

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
Eric Swanson-355778
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 1
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++;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search