A Little Noise

November 9, 2006

Audit Trail

Filed under: MySQL — snoyes @ 8:14 pm

More an exercise in stored procedures, triggers, and building queries culled from the information_schema than anything else, here’s an audit trail setup.

Audit trails let you see how data has changed over the course of time, so you know who to blame when something goes wrong.

This particular setup works by adding an AFTER UPDATE trigger to each table to be audited. That trigger looks at the OLD and NEW values of each field, and if there is a difference, inserts a record into the auditLog.

  • auditTrail.sql – Creates the table to hold the audit data and the stored procedure which is called to add a trigger to each table you wish to audit.
  • setupTest.sql – Creates an example data table such as you might want to audit, and calls the stored procedure to build the trigger for it. The output from that stored procedure should be piped back in to MySQL to actually create the trigger. Why doesn’t it do this automatically? Because prepared statements don’t support CREATE TRIGGER statements as of 5.0.27.
  • runTest.sql – Inserts a few rows of data into the example data table, updates them, and displays the changes recorded in the audit table.

Here’s the Windows command line to run the test in the `test` database:
mysql test < auditTrail.sql && mysql -N test < setupTest.sql | mysql --delimiter=$ test && mysql -t test < runTest.sql

For my purposes, watching updates was sufficient. You could extend this to cover inserts and deletes too.

There are a number of drawbacks to this particular method:

  • It's a bit unwieldy, since the SQL output has to be piped back in to create the triggers. But hey, if the Wizard can do it, it's ok for us mere mortals too. This could be wrapped in a script. Or maybe it's a good thing, because then you can go edit the output and exclude fields you don't want audited.
  • The tigger has to be created for each table separately. Again, handy if there are only a couple tables you want to audit, but a real pain if you wanted to audit all 297 tables in your database.
  • If the table structure changes, you'll need to drop and recreate the trigger.
  • The stored procedure assumes the current database. Again, something trivial to change.
  • Hope you didn't want to use your AFTER UPDATE trigger for something else.

My next trick will be to add something so that we know which user made the change. Not which MySQL user, mind you, since these changes are issued from PHP scripts which all run as the same MySQL user. Somehow I need to pass in my application user id. Any suggestions?

18 Comments »

  1. Hello,

    To pass the application user id, just set a variable on the server when you connect
    “SET @userid = ” and use that in your trigger to identify the user.
    I use this method in a perl web app and it works like charm in insert and update triggers.

    Regards,

    Stefan – ZipKid – Goethals.

    Comment by Stefan -ZipKid - Goethals — November 10, 2006 @ 9:10 am

  2. Hi Scott,

    interesting!, well done.

    If you’re interested, here’s a similar solution that creates a historic database for an existing one:

    http://www.quest-pipelines.com/newsletter-v6/1005_A.htm

    good luck, and keep it up!

    Comment by Roland Bouman — November 10, 2006 @ 6:47 pm

  3. The issue with this is that it doubles the traffic and storage requirements on your MySQL server. You can get around it by some hacky stuff, but it’s also not good audit practice to do this — it takes out the independence factor. Audits should not interfere with the data.

    That being said, it’s better than nothing if you need auditing and are OK with those issues. It won’t pass Sarbanes-Oxley compliance, though.

    Comment by Sheeri — November 10, 2006 @ 10:33 pm

  4. Hi Scott,

    Thanks for this – was very useful information.

    If you find however that all updates aren’t logging, here’s why.

    On my version of MySQL (5.0.54) the script didn’t log updates where the original value was NULL, or the field gets changed from something to NULL. That is because (OLD.fld != NULL) or (NULL != NEW.fld) returns “NULL” and not “FALSE”; and ( ! NULL ) evaluates to (NULL) and not to (TRUE) as the script probably intends.

    To fix this – I changed the “IF” test from:-

    ‘IF OLD.’, column_name, ‘ != NEW.’, column_name, ‘ THEN INSERT INTO auditLog (‘

    …to…

    ‘IF IFNULL( OLD.’, column_name, ‘,””) != IFNULL( NEW.’, column_name, ‘,””) THEN INSERT INTO auditLog (‘

    …and that did the trick.

    (WARNING: If a field contains blank text that is changed to NULL, or NULL to a blank string then it won’t log with this fix, although that’s ok for me. You can change the two “”‘s (blank strings) in the fix to an random value [like a GUID] that won’t be found in your fields if you prefer and it’ll take care of that.)

    Comment by Andre Sharpe — January 6, 2008 @ 9:22 am

  5. Andre: Good catch. You could also use the NULL-safe equal to operator:

    IF NOT(OLD.column_name <=> NEW.column_name)

    Comment by snoyes — January 6, 2008 @ 9:21 pm

  6. Thanx for the useful information,
    But i don’t get the expected results, trying to retrieve data from auditlog table gives me an empty set. Any ideas where did i go wrong

    Comment by John Mduda — August 25, 2009 @ 12:13 am

  7. I am a Java/Perl programmer, who does what SQL he needs to but has never really delved into it. I found the above code very useful, but wonder how it could handle multiple-field primary keys? And how does the code interact with the ON UPDATE as below?

    CREATE TABLE `eg` (
    `id` varchar(100) NOT NULL DEFAULT ”,
    `uri` mediumtext NOT NULL,
    `text` varchar(255) DEFAULT NULL,
    `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`,`uri`(200)),
    UNIQUE KEY `id` (`id`,`uri`(200))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    Comment by Lee — November 2, 2009 @ 1:33 am

  8. Just for the posterity, I had to hack the original sql script to make it work with MySQL 5.1.

    I replaced the original SELECT CONCAT( with this:

    SELECT CONCAT(
    ‘DELIMITER $\n’, ‘CREATE TRIGGER ‘, tableName, ‘_AU AFTER UPDATE ON ‘, tableName, ‘ FOR EACH ROW BEGIN ‘,
    GROUP_CONCAT(
    CONCAT(
    ‘IF NOT( OLD.’, column_name, ‘ NEW.’, column_name, ‘) THEN INSERT INTO auditLog (‘,

    Comment by Etienne Savard — November 5, 2009 @ 1:15 pm

  9. I am getting an empty set too. Nice job, btw!

    Comment by Cindy Lai — February 1, 2010 @ 1:03 pm

  10. After many many attempts, I still can’t get this too work. Here’s my queries:

    DROP PROCEDURE IF EXISTS addLogTrigger;
    DELIMITER $
    CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255))
    BEGIN
    SELECT CONCAT(
    ‘DELIMITER $\n’, ‘CREATE TRIGGER ‘, tableName, ‘_AU BEFORE UPDATE ON ‘, tableName, ‘ FOR EACH ROW BEGIN ‘,
    GROUP_CONCAT(
    CONCAT(
    ‘IF ( OLD.’, column_name, ‘ != NEW.’, column_name, ‘) THEN INSERT INTO auditLog (‘,
    ‘tableName, ‘,
    ‘rowPK, ‘,
    ‘fieldName, ‘,
    ‘old_value, ‘,
    ‘new_value’
    ‘) VALUES ( ”’,
    table_name, ”’, NEW.’,
    pkField, ‘, ”’,
    column_name, ”’, OLD.’,
    column_name, ‘, NEW.’,
    column_name,
    ‘); END IF;’
    )
    SEPARATOR ‘ ‘
    ), ‘ END;’
    )
    FROM
    information_schema.columns
    WHERE
    table_schema = database()
    AND table_name = tableName;
    END$
    DELIMITER ;

    Any idea what I might have done wrong? I have MySQL version 5.1.

    Comment by Cindy — February 2, 2010 @ 12:51 am

  11. The script seems to work for the most part. The only problem I’m encountering is when you CALL the procedure, the output gets truncated if you have something like 5 or more columns in a table. Any ideas?

    Comment by cary — April 24, 2010 @ 7:17 pm

  12. cary: You might need to increase group_concat_max_len.

    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

    Comment by snoyes — April 24, 2010 @ 9:14 pm

  13. snoyes: that did the trick. thanks.

    here it is for reference:

    SET @@group_concat_max_len = 9999999;

    Comment by cary — April 25, 2010 @ 12:06 am

  14. […] script is based in the code published in the bolg A little noise http://thenoyes.com/littlenoise/?p=43, it adds some […]

    Pingback by Audit Trail in Mysql | jelsoluciones.com.ar — August 18, 2010 @ 9:48 am

  15. Great contribution, I’ve added the insert and delete triggers and some other functionality: http://jelsoluciones.com.ar/?p=6

    Comment by Javier — August 18, 2010 @ 1:51 pm

  16. Great procedure. Exactly what I am looking for. I created the procedure. I CALL procedure and I see the trigger statement on the screen but it does not execute.

    Comment by doneill — December 17, 2010 @ 9:26 am

  17. This is what i am looking for, have very limited database knowledge was able to run the script but not able to call the stored procedure, can some one help with a generic script for this part, want this for a single table with results both on screen and export. Thanks in advance

    KP

    “Hi, just run the script, then call the stored procuedure
    addLogTrigger with two parameters:
    1st the table you wan to add the audit trail to (‘%’ for all tables)
    2nd the script file name (Null if you want the result on the screen)

    This will output another script that you have to run to get the audit trail working. Take into account that if you change a table structure you will need to run the stored procedure again for that specific table.

    Hope it helps…”

    Comment by KP — September 10, 2011 @ 11:09 am

  18. Hey people, I need help on how to create an audit trail for my database using Mysql version 5.0. If anyone can help please do.

    Comment by Thuto — June 9, 2014 @ 5:35 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress