Description:
When deleting from a table when the delete statement has a where clause, the user is required to have 'select' privs in addition to 'delete' privs. This is not according to the SQL standard.
The extra 'select' priv is not required when deleting from the table witout including a where clause.
1) 'select' priv required when deleting from a table with a where clause
=============================================
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> create database omerdb;
Query OK, 1 row affected (0.00 sec)
mysql> use omerdb
Database changed
mysql> create table omertb (f1 int);
Query OK, 0 rows affected (0.00 sec)
mysql> create user omer@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> grant delete on omerdb.* to omer@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into omertb values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into omertb values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into omertb values (3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into omertb values (4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from omertb;
+------+
| f1 |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> quit
Bye
omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=omer
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> show grants;
+--------------------------------------------------+
| Grants for omer@localhost |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'omer'@'localhost' |
| GRANT DELETE ON `omerdb`.* TO 'omer'@'localhost' |
+--------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use omerdb;
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> delete from omertb where f1=3;
ERROR 1143 (42000): SELECT command denied to user 'omer'@'localhost' for column 'f1' in table 'omertb'
>>> OBN: The select priv should not be needed.
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 5 to server version: 5.0.17-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant select on omerdb.* to omer@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=omer
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.17-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants;
+----------------------------------------------------------+
| Grants for omer@localhost |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'omer'@'localhost' |
| GRANT SELECT, DELETE ON `omerdb`.* TO 'omer'@'localhost' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use omerdb;
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> delete from omertb where f1=3;
Query OK, 1 row affected (0.00 sec)
mysql> quit
2) Showing that the select priv is not required when there is no where clause
(as expected)
=================================================
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 7 to server version: 5.0.17-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> revoke select on omerdb.* from omer@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=omer
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.17-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants;
+--------------------------------------------------+
| Grants for omer@localhost |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'omer'@'localhost' |
| GRANT DELETE ON `omerdb`.* TO 'omer'@'localhost' |
+--------------------------------------------------+
2 rows in set (0.00 sec)
mysql> use omerdb;
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> delete from omertb where f1=3;
ERROR 1143 (42000): SELECT command denied to user 'omer'@'localhost' for column 'f1' in table 'omertb'
>>> OBN: Same as above.
mysql> delete from omertb;
Query OK, 3 rows affected (0.00 sec)
>>> OBN: Now there is no problem deleting.
mysql> quit
Bye
omer@linux:~/source/src50_1121/client> omer@linux
How to repeat:
See above
Suggested fix:
Acording to the SQL standard a 'select' priv should not be required when performing delete operations
Description: When deleting from a table when the delete statement has a where clause, the user is required to have 'select' privs in addition to 'delete' privs. This is not according to the SQL standard. The extra 'select' priv is not required when deleting from the table witout including a where clause. 1) 'select' priv required when deleting from a table with a where clause ============================================= 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> create database omerdb; Query OK, 1 row affected (0.00 sec) mysql> use omerdb Database changed mysql> create table omertb (f1 int); Query OK, 0 rows affected (0.00 sec) mysql> create user omer@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant delete on omerdb.* to omer@localhost; Query OK, 0 rows affected (0.00 sec) mysql> insert into omertb values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into omertb values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into omertb values (3); Query OK, 1 row affected (0.00 sec) mysql> insert into omertb values (4); Query OK, 1 row affected (0.00 sec) mysql> select * from omertb; +------+ | f1 | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set (0.00 sec) mysql> quit Bye omer@linux:~/source/src50_1121/client> ./mysql --socket=../mysql-test/var/tmp/master.sock --user=omer 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> show grants; +--------------------------------------------------+ | Grants for omer@localhost | +--------------------------------------------------+ | GRANT USAGE ON *.* TO 'omer'@'localhost' | | GRANT DELETE ON `omerdb`.* TO 'omer'@'localhost' | +--------------------------------------------------+ 2 rows in set (0.00 sec) mysql> use omerdb; 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> delete from omertb where f1=3; ERROR 1143 (42000): SELECT command denied to user 'omer'@'localhost' for column 'f1' in table 'omertb' >>> OBN: The select priv should not be needed. 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 5 to server version: 5.0.17-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant select on omerdb.* to omer@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=omer Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 5.0.17-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show grants; +----------------------------------------------------------+ | Grants for omer@localhost | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO 'omer'@'localhost' | | GRANT SELECT, DELETE ON `omerdb`.* TO 'omer'@'localhost' | +----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> use omerdb; 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> delete from omertb where f1=3; Query OK, 1 row affected (0.00 sec) mysql> quit 2) Showing that the select priv is not required when there is no where clause (as expected) ================================================= 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 7 to server version: 5.0.17-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> revoke select on omerdb.* from omer@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=omer Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 5.0.17-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show grants; +--------------------------------------------------+ | Grants for omer@localhost | +--------------------------------------------------+ | GRANT USAGE ON *.* TO 'omer'@'localhost' | | GRANT DELETE ON `omerdb`.* TO 'omer'@'localhost' | +--------------------------------------------------+ 2 rows in set (0.00 sec) mysql> use omerdb; 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> delete from omertb where f1=3; ERROR 1143 (42000): SELECT command denied to user 'omer'@'localhost' for column 'f1' in table 'omertb' >>> OBN: Same as above. mysql> delete from omertb; Query OK, 3 rows affected (0.00 sec) >>> OBN: Now there is no problem deleting. mysql> quit Bye omer@linux:~/source/src50_1121/client> omer@linux How to repeat: See above Suggested fix: Acording to the SQL standard a 'select' priv should not be required when performing delete operations