Bug #5861 Triggers are executed under wrong privileges.
Submitted: 2 Oct 2004 16:40 Modified: 22 Nov 2005 17:09
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 (1.1629) OS:
Assigned to: Alexander Nozdrin CPU Architecture:Any

[2 Oct 2004 16:40] Dean Ellis
Description:
CURRENT_USER() is optimized away as a constant instead of being stored as a function call in the trigger definition.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a char(30) );
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET new.a=CURRENT_USER();
INSERT INTO t1 VALUES  ('');

connect as different user:

INSERT INTO t1 VALUES  ('');
SELECT * FROM t1;
DROP TRIGGER t1.tr1;
DROP TABLE t1;

Suggested fix:
Do not optimize CURRENT_USER() away as a constant.
[7 Sep 2005 20:28] Dmitry Lenev
Hi, Dean!

Now (in 5.0.13) this trigger behaves like you expect.

But this is actually wrong, becuase:

1) On one hand value of CURRENT_USER() function corresponds to the MySQL account that determines your _current_ access privileges (and AFAIK this is always true now and in line with SQL standard).
2) On the other hand SQL standard says that: "A triggered action is always executed under the authorization of the owner of the schema that includes the trigger." This means that in MySQL we should execute trigger body under authorization of user who
created trigger and not the one who issued statement which invoked this trigger.

So according to standard CURRENT_USER() in your trigger should always return the same value which will correspond to creator of trigger.

This does not happen because we don't follow the second rule I have mentioned. I think this is _real_ bug. I have updated bug synopsys to reflect this.
[16 Nov 2005 13:05] Alexander Nozdrin
Fixed in 5.0 tree. Currently tagged 5.0.16.
[16 Nov 2005 13:13] Alexander Nozdrin
A mistake in the previous comment:
Currently tagged 5.0.17
[22 Nov 2005 17:09] Paul DuBois
Note in 5.0.17 changelog and in CREATE TRIGGER section.
[22 Nov 2005 22:00] Beat Vontobel
I just noted this fix in the changelog for 5.0.17 and wondered: As one of the key applications of triggers is auditing, how can I now determine the user that triggered the action (INSERT or UPDATE) in the trigger? As this is usually what I need to know inside a trigger - and not who created it (as this is static and redundant information anyway). If the answer is "you can't" then I don't think this bugfix was really an improvement...
[23 Nov 2005 1:34] Omer Barnir
Note that the fix to this bug results in bug 15166
[23 Nov 2005 13:09] 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.
[24 Nov 2005 17:48] Trudy Pelzer
Alik is correct: To record the user who caused a trigger to activate, look at
the USER() function, rather than the CURRENT_USER function. For
example:

-- Open a session as root

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

mysql> create table trig1 (col1 int, col2 char(5));
Query OK, 0 rows affected (0.01 sec)

mysql> create table trig2 (col1 int, col2 char(5), definer varchar(50), invoker varchar(50));
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger trig1_ai after insert on trig1 for each row insert into trig2 values (new.col1,new.col2,current_user,user());
Query OK, 0 rows affected (0.00 sec)

mysql> insert into trig1 values (10,'hello');
Query OK, 1 row affected (0.00 sec)

mysql> select * from trig1;
+------+-------+
| col1 | col2  |
+------+-------+
|   10 | hello |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from trig2;
+------+-------+----------------+----------------+
| col1 | col2  | definer        | invoker        |
+------+-------+----------------+----------------+
|   10 | hello | root@localhost | root@localhost |
+------+-------+----------------+----------------+
1 row in set (0.00 sec)
-- shows that CURRENT_USER correctly returns the ID of the
user who created the trigger (and thus whose privileges
are checked when it is activated); shows that this same
user caused the trigger to activate

mysql> grant select,insert on trig1 to tp@localhost;
Query OK, 0 rows affected (0.00 sec)

-- Open new session as user tp

mysql> select current_user;
+--------------+
| current_user |
+--------------+
| tp@localhost |
+--------------+
1 row in set (0.00 sec)

mysql> insert into trig1 values (20,'bye');
Query OK, 1 row affected (0.00 sec)

mysql> select * from trig1;
+------+-------+
| col1 | col2  |
+------+-------+
|   10 | hello |
|   20 | bye   |
+------+-------+
2 rows in set (0.00 sec)

-- Verify trigger activation from root user

mysql> select * from trig2;
+------+-------+----------------+----------------+
| col1 | col2  | definer        | invoker        |
+------+-------+----------------+----------------+
|   10 | hello | root@localhost | root@localhost |
|   20 | bye   | root@localhost | tp@localhost   |
+------+-------+----------------+----------------+
2 rows in set (0.00 sec)
-- shows that CURRENT_USER correctly returns the ID of the
user who created the trigger (and thus whose privileges
are checked when it is activated); shows that USER()
correctly returns the ID of the user who caused the trigger 
to activate
[24 Nov 2005 18:08] Beat Vontobel
Trudy, Alexander, thank you so much for taking the time to give such excellent support to an ignorant user even in the bug database! I did actually the right thing in my triggers and used the SESSION_USER() aka USER() function. But when I saw the note to this bug in the changelog I didn't realize that CURRENT_USER() was a completely different function but thought it was just another alias for the already big family of USER()/SESSION_USER()/SYSTEM_USER(), hence my mistake. I just realized this after Alexander's comment and immediately posted a short article to my blog yesterday as a compensation (see http://www.planetmysql.org or http://www.futhark.ch/mysql/116.html). I should have posted a comment here too, to save Trudy from all her work. Sorry and thanks again!