Bug #45374 Documentation: need to document that certain WHERE clauses require Select_priv
Submitted: 8 Jun 2009 13:24 Modified: 26 Jun 2009 17:28
Reporter: Leandro Morgado Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[8 Jun 2009 13:24] Leandro Morgado
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.
[15 Jun 2009 16:25] Leandro Morgado
This is similar to bug 15193 , however, this bug report is really a request to make this clearer in the manual.
[26 Jun 2009 17:28] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated description of SELECT privilege at:
http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

Add note about WHERE clause for DELETE at:
http://dev.mysql.com/doc/refman/5.1/en/delete.html