Determine the current user in MySQL 5.0 (Part 2: Appliance in triggers and a change in MySQL 5.0.17)
In one of my previous blog posts I showed you the difference between the functions CURRENT_USER() and SESSION_USER() and promised to show you how to use them for auditing with triggers and what you have to watch out for when updating to MySQL 5.0.17. With this release now being just a few hours old, let's finally do that and set up a log that stores all the changes your users apply to a given table.
I'll use a pretty simple base table for the purpose of this article and will set up all the tables and triggers as user root@localhost.
CREATE TABLE t (
t_id INT PRIMARY KEY AUTO_INCREMENT,
v VARCHAR(32)
);
Now we need to create the table for auditing. In addition to all the columns from the base table it will usually contain additional columns for a time stamp, the operation performed on the base table (INSERT, UPDATE or DELETE) and the user who performed it (we even have two user columns in our example to show the difference between CURRENT_USER() and SESSION_USER(), however in your real world applications one of them will usually be enough).
CREATE TABLE t_audit (
tstamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
op ENUM('INSERT', 'UPDATE', 'DELETE'),
user VARCHAR(255),
cur_user VARCHAR(255),
t_id INT,
v VARCHAR(32),
KEY t_id (t_id)
);
You should of course make sure that your users don't have any kind of write access to this table or the purpose behind it will quickly be defeated.
Note as well that the t_id column from the base table no longer holds the PRIMARY KEY in the audit table. This wouldn't allow us to log more than one change per row, so we just apply a normal INDEX to be able to quickly look for any changes done to a specific row. The same would apply to any columns with an index of type UNIQUE in your base table.
If you think it would be a good idea to have a PRIMARY KEY in your audit table just add an AUTO_INCREMENT column for that.
Now we're ready to log updates with a trigger.
CREATE TRIGGER t_upd AFTER UPDATE ON t FOR EACH ROW
INSERT INTO t_audit (op, user, cur_user, t_id, v)
VALUES ('UPDATE', SESSION_USER(), CURRENT_USER(), OLD.t_id, OLD.v);
We use a trigger that fires AFTER UPDATE here as we only want to log a row when it really has been changed in the base table (whereas a BEFORE UPDATE trigger would be executed even if an error after it prevented the update).
You may also wonder why I keep the OLD values and don't use NEW for the audit table. As we still have the new values in the base table (or in a later update entry in the audit log, if the same row has been changed more than once) it makes more sense to keep the old values. This eases the process of an undo on the base table as well. (But you could of course do it the other way round.)
This information is not enough if your users might change the PRIMARY KEY column for a row in your base table. Then you'd have to log the OLD as well as the NEW value at least for that PRIMARY KEY column.
But let us keep this aside for the example here and instead just add the other triggers to log delete and insert operations.
CREATE TRIGGER t_del AFTER DELETE ON t FOR EACH ROW
INSERT INTO t_audit (op, user, cur_user, t_id, v)
VALUES ('DELETE', SESSION_USER(), CURRENT_USER(), OLD.t_id, OLD.v);
CREATE TRIGGER t_ins AFTER INSERT ON t FOR EACH ROW
INSERT INTO t_audit (op, user, cur_user, t_id, v)
VALUES ('INSERT', SESSION_USER(), CURRENT_USER(), NEW.t_id, NEW.v);
No real surprise here after we already did the update trigger, just that for the insert trigger you have to use NEW of course as no old value exists. The NEW values for all but the PRIMARY KEY columns of a base table are actually redundant here, if you think about what I've just said for the update trigger (the inserted values could be determined from what exists in the base table or from later update or delete log entries). But instead of inserting NULL values into the audit table we can just as well store the real values.
So let's do some work on the table. I do the login this time as a user named test from my laptop.
INSERT INTO t (v) VALUES ('one');
UPDATE t SET v = 'two' WHERE v = 'one';
DELETE FROM t WHERE v = 'two';
We inserted a row, changed and deleted it again, so we actually end up with an empty table t again. But all our actions should be logged in table t_audit. Let's check that with a simple SELECT * FROM t_audit:
+---------------------+--------+------------------+------------------+------+------+ | tstamp | op | user | cur_user | t_id | v | +---------------------+--------+------------------+------------------+------+------+ | 2005-12-19 21:42:29 | INSERT | test@192.168.1.5 | test@192.168.1.% | 1 | one | | 2005-12-19 21:42:29 | UPDATE | test@192.168.1.5 | test@192.168.1.% | 1 | one | | 2005-12-19 21:42:29 | DELETE | test@192.168.1.5 | test@192.168.1.% | 1 | two | +---------------------+--------+------------------+------------------+------+------+
This is what you get when you run the example on a release up to version 5.0.16. With the brand new release 5.0.17 you'll get the following result:
+---------------------+--------+------------------+----------------+------+------+ | tstamp | op | user | cur_user | t_id | v | +---------------------+--------+------------------+----------------+------+------+ | 2005-12-19 22:18:08 | INSERT | test@192.168.1.5 | root@localhost | 1 | one | | 2005-12-19 22:18:08 | UPDATE | test@192.168.1.5 | root@localhost | 1 | one | | 2005-12-19 22:18:08 | DELETE | test@192.168.1.5 | root@localhost | 1 | two | +---------------------+--------+------------------+----------------+------+------+
The function SESSION_USER() (or any of the aliases USER() or SYSTEM_USER()) still return the same value, as you can see in the user column. It's still the credentials used for login, namely the user test logged in from 192.168.1.5 (the current IP address of my laptop) in this example. The behaviour of CURRENT_USER() (or its alias CURRENT_USER without parentheses) on the other hand changed inside triggers with the new release 5.0.17. (You should probably read the first part of this article if you didn't do so yet and are completely puzzled by now.)
What happened? With MySQL 5.0.17 triggers now have a DEFINER setting (see the manual) and are executed with the access rights of this DEFINER (before it was the rights of the user that caused the trigger to fire). As CURRENT_USER() by definition of the SQL standard always has to return the privileges currently in use, this will be the GRANT name of the DEFINER even during execution of a trigger (and that was root@localhost).
What you want to use inside a trigger for auditing is therefore definitely one of USER(), SESSION_USER() or SYSTEM_USER(), as CURRENT_USER() will always return a constant value for any given trigger.
