Description:
When doing DML statements such as UPDATE and DELETE, if we use a WHERE clause that reads data from a table, we need Select_priv on that table. This isn't always clear in the docs as some places partially mention this and others do not:
=============================
"Inserting into a table requires the INSERT privilege for the table. If
the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes an
UPDATE to be performed instead, the statement requires the UPDATE
privilege for the columns to be updated. For columns that are read but
not modified you need only the SELECT privilege (such as for a column
referenced only on the right hand side of an col_name=expr assignment in
an ON DUPLICATE KEY UPDATE clause)."
http://dev.mysql.com/doc/refman/5.1/en/insert.html
"You need the UPDATE privilege only for columns referenced in a multiple-table UPDATE that are actually updated. You need only the SELECT privilege for any columns that are read but not modified."
http://dev.mysql.com/doc/refman/5.1/en/update.html says:
No mention in:
http://dev.mysql.com/doc/refman/5.1/en/delete.html
Incomplete in:
"The UPDATE privilege enables rows to be updated in tables in a database."
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_update says:
==============================
How to repeat:
Examples of when this occurs:
===========================================================
mysql> DELETE FROM t1 WHERE i=1;
ERROR 1143 (42000): SELECT command denied to user 'jim'@'localhost' for
column 'i' in table 't1'
mysql> INSERT INTO t1 VALUES (1) ON DUPLICATE KEY UPDATE i=2;
ERROR 1143 (42000): SELECT command denied to user 'jim'@'localhost' for
column 'i' in table 't1'
===========================================================
If there is no WHERE clause or it doesn't read a value then no Select_priv is
needed:
===========================================================
mysql> UPDATE t1 SET i = 3;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> UPDATE t1 SET i = i + 1;
ERROR 1143 (42000): SELECT command denied to user 'jim'@'localhost' for
column 'i' in table 't1'
mysql> DELETE FROM t1;
Query OK, 1 row affected (0.00 sec)
===========================================================
While it's obvious after thinking a bit and doing some test (you can use
the WHERE clause to match values, effectively SELECTing them), this behaviour should be explicitly documented. Someone will read that UPDATE requires only Selec the Update_priv and will forget about any WHERE clauses and related privs.
Suggested fix:
Make it clear in the docs that any WHERE clause that reads values from a table/column/etc object will need Select_priv on that respective object.
