MySQL Bugs: #5861: Triggers are executed under wrong privileges.

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

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.

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.

Fixed in 5.0 tree. Currently tagged 5.0.16.

A mistake in the previous comment:
Currently tagged 5.0.17

Note in 5.0.17 changelog and in CREATE TRIGGER section.

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...

Note that the fix to this bug results in bug 15166

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.

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

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!