MySQL Bugs: #15196: Wrong select permission required to execute triggers

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

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

Setting Lead to Kostja since this is a split of 15166

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

OK to push after fixing/discussing minor issues mentioned in e-mail.

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

Pushed into 5.0 tree, currently tagged 5.0.19.

Please set back to Documenting status when 5.1 push is completed.

Merged into 5.1 tree.

The 5.1 tree is currently tagged 5.1.7.

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)