Bug #15196 Wrong select permission required to execute triggers
Submitted: 23 Nov 2005 18:34 Modified: 14 Feb 2006 3:25
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Alexander Nozdrin CPU Architecture:Any

[23 Nov 2005 18:34] Omer Barnir
Description:
This bug is a result of the fix to bug 5861
http://bugs.mysql.com/bug.php?id=5861

This issue was origially tracked as part of bug 15166 
http://bugs.mysql.com/bug.php?id=15166
the 'update' is tracked in 15166 and the 'select' issue is tracked here

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

only an 'update' priv is needed but a 'select' one is required aswell
--------------------------------------------------------------------------------
-------------
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:
See above

Suggested fix:
The 'select' priv is not required in thsi case and the trigger should work even when it is not set
[23 Nov 2005 18:36] Omer Barnir
Setting Lead to Kostja since this is a split of 15166
[22 Dec 2005 21:48] Bugs System
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/376
[24 Jan 2006 10:43] Dmitry Lenev
OK to push after fixing/discussing minor issues mentioned in e-mail.
[24 Jan 2006 17:16] Bugs System
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/1579
[24 Jan 2006 17:20] Alexander Nozdrin
Pushed into 5.0 tree, currently tagged 5.0.19.
[2 Feb 2006 5:01] Mike Hillyer
Please set back to Documenting status when 5.1 push is completed.
[6 Feb 2006 14:00] Alexander Nozdrin
Merged into 5.1 tree.
[7 Feb 2006 0:32] Alexander Nozdrin
The 5.1 tree is currently tagged 5.1.7.
[14 Feb 2006 3:25] Paul Dubois
Noted in 5.0.19, 5.1.7 changelogs.

          The <literal>SELECT</literal> privilege was being required
          even for triggers that performed no selects. (Bug #15196)