MySQL Bugs: #15166: Wrong update [was: select/update] permissions required to execute triggers

Bug #15166	Wrong update [was: select/update] permissions required to execute triggers
Submitted:	23 Nov 2005 0:39	Modified:	14 Feb 2006 3:15
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
This bug is a result of the fix to bug 5861 http://bugs.mysql.com/bug.php?id=5861

The system always requires the user creating triggers to have both select and update privs on the table the trigger is defined on regardless of the the action performed by the trigger.

Case #1 - only an 'select' priv is needed but an 'update' one is required as well
---------------------------------------------------------------------------------------------
omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>         drop database if exists priv_db;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>         create database priv_db;
Query OK, 1 row affected (0.00 sec)

mysql>         use priv_db;
Database changed
mysql>         create table t1 (f1 char(20));
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>         create user u_select@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>         revoke ALL PRIVILEGES, GRANT OPTION FROM u_select@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>         grant SUPER on *.* to u_select@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql>         grant SELECT on priv_db.t1 to u_select@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=u_select
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use priv_db;
Database changed

mysql> create trigger trg1_2 after INSERT  on t1 for each row set @aaa = new.f1;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use priv_db;
Database changed

mysql> insert into t1 values ('insert test');
ERROR 1142 (42000): UPDATE command denied to user 'u_select'@'localhost' for table 't1'

mysql> quit
ERROR: >>> Although the above trigger only requires u_select to have 'select' privs on t1 (reading a variable) the system is erroring on needing an 'update priv as well.

Similarly:
Case #2 - only an 'update' priv is needed but a 'select' one is required as well
---------------------------------------------------------------------------------------------
omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>         drop database if exists priv_db;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql>         create database priv_db;
Query OK, 1 row affected (0.01 sec)

mysql>         use priv_db;
Database changed
mysql>         create table t1 (f1 char(20));
Query OK, 0 rows affected (0.04 sec)

mysql>         create user u_update@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>         revoke ALL PRIVILEGES, GRANT OPTION FROM u_update@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>         grant SUPER on *.* to u_update@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>         grant UPDATE on priv_db.t1 to u_update@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=u_update
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use priv_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create trigger trg1_2 before INSERT  on t1 for each row set new.f1='Test';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use priv_db
Database changed

mysql> insert into t1 values ('test');
ERROR 1142 (42000): SELECT command denied to user 'u_update'@'localhost' for table 't1'

How to repeat:
Run the above scripts

Suggested fix:
The system should not require privs that are not needed. 
This especialy wrong when the extra priv required is an 'update' on a table since this can be viewed as a security issue

I just copied over my comment from Bug #5861 on CURRENT_USER() in TRIGGERs. As 5.0.15 was already declared production (and I actually use it on my production servers and rely on existing functionality) I hope you'll be careful not to break existing things with release 5.0.17 as a lot of users will need this release for other (more critical) bug fixes.

--- Comment from Bug #5861 ---
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...

Case #2 in this bug was moved and is now tracked in bug 15196
http://bugs.mysql.com/bug.php?id=15196

This bug can be closed once #1 is addressed
Changed Synopsis and 'removed' 'select' referance

Just in case somebody hits this page when looking for information on CURRENT_USER in TRIGGERs I should probably correct my ignorant comment above. Use USER() or SESSION_USER() in TRIGGERs for auditing instead of CURRENT_USER. As a compensation i put up some more information on this on my blog:

http://www.futhark.ch/mysql/116.html
http://www.futhark.ch/mysql/123.html

Sorry about my mistake!

Pushed into 5.0 tree, currently tagged 5.0.19.

Please set back to Documenting status when pushed into 5.1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2188

Merged into 5.1 tree.

The 5.1 tree is currently tagged 5.1.7.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2238

Noted in 5.0.19, 5.1.7 changelogs.

          The <literal>UPDATE</literal> privilege was being required
          even for triggers that performed no updates. (Bug #15166)