MySQL Bugs: #28588: triggers are executed always as root so current

Bug #28588	triggers are executed always as root so current_user() returns always root
Submitted:	22 May 2007 10:27	Modified:	22 May 2007 21:14
Reporter:	Gaetano Piazza	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I would like to use trigger to register user name who insert or modify a row of a table.
I have created a trigger with the function current_user() inside it to obtain the user name but that function return always root as current user that is the creator of trigger instead of real user that invokes the statement.
Following there are details on how to repeat the bug:

How to repeat:
I have created a table and a trigger as root with following statements:

----------+--------------+------------+

# mysql -uroot
mysql> create database dbtest;
mysql> use dbtest;
mysql> grant all privileges on dbtest.* to 'testuser'@'localhost';
mysql> create table test (creator varchar(30), modificator varchar(30), text varchar(100));
mysql> create trigger ins_test before insert on test for each row set new.modificator=current_user();
mysql> quit;

----------+--------------+------------+

Then I execute the following statements as user testuser witch has all privileges on database dbtest:

----------+--------------+------------+

# mysql -utestuser dbtest
mysql> insert into test (text) values ('first'),('second'),('third');
mysql> select * from test;
+---------+----------------+--------+
| creator | modificator    | text   |
+---------+----------------+--------+
| NULL    | root@localhost | first  | 
| NULL    | root@localhost | second | 
| NULL    | root@localhost | third  | 
+---------+----------------+--------+
3 rows in set (0.00 sec)
----------+--------------+------------+

As you can see above the field modificator has value root@localhost instead testuser@localhost as I expected.

A similar behavior there is by using update triggers.

How can I obtain that a field contain the name of user that execute insert or update statements?

Thank you
Gaetano Piazza

Please, use explicit DEFINER clause. Read http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html for the details. This is not a bug.

Valeriy Kravchuk invite me to use explicit DEFINER clause.
Really I have alredy do some tests with DEFINER clause, and that tests confirms that is reported in manual also:
"Note that the introduction of the DEFINER  clause changes the meaning of CURRENT_USER()  within trigger definitions: The CURRENT_USER()  function evaluates to the trigger DEFINER value as of MySQL 5.0.17 and to the user whose actions caused the trigger to be activated before 5.0.17."
For this reason I don't still know how to assign to a field falue the name of the invoker user at the trigger activation time, as can be done with SQL SECURITY INVOKER characteristics clause in CREATE PROCEDURE/FUNCTION sintax.

I have found the answer to my question in a comment to Bug report #5861 for the same bug written last 23 Nov 2005 from Alexander Nozdrin:
+------+---------+---------+----------+----------+-----------+-------+
In order to get the user that triggered the action, USER() function
(or SESSION_USER() that is basically an alias for USER()) can be used.

However, this will return the current/actual user, not the user under
whose authorization the action was triggered.