Audit Trail
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?
November 10th, 2006 - 09:10
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.
November 10th, 2006 - 18:47
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!
November 10th, 2006 - 22:33
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.
January 6th, 2008 - 09:22
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.)
January 6th, 2008 - 21:21
Andre: Good catch. You could also use the NULL-safe equal to operator:
IF NOT(OLD.column_name <=> NEW.column_name)August 25th, 2009 - 00:13
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
November 2nd, 2009 - 01:33
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
November 5th, 2009 - 13:15
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 (‘,
February 1st, 2010 - 13:03
I am getting an empty set too. Nice job, btw!
February 2nd, 2010 - 00:51
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.
April 24th, 2010 - 19:17
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?
April 24th, 2010 - 21:14
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
April 25th, 2010 - 00:06
snoyes: that did the trick. thanks.
here it is for reference:
SET @@group_concat_max_len = 9999999;
August 18th, 2010 - 13:51
Great contribution, I’ve added the insert and delete triggers and some other functionality: http://jelsoluciones.com.ar/?p=6