Trigger Audit Trails - who deleted my record?

  • Eric Swanson-355778

    Say Hey Kid

    Points: 666

    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

    SSC Guru

    Points: 993770

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • rstone

    SSCertifiable

    Points: 6011

    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

    SSChampion

    Points: 13623

    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

    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++;

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

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