MySQL Bugs: #30915: on duplicate key update: needs update privileges for primary key

Bug #30915	on duplicate key update: needs update privileges for primary key
Submitted:	8 Sep 2007 14:33	Modified:	8 Jan 2008 20:32
Reporter:	Richard Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	FreeBSD (6.2)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I believe that the "INSERT ... ON DUPLICATE KEY UPDATE" statement should only require INSERT privilege and in addition, UPDATE privilege for each column affected.

"ERROR 1143 (42000): UPDATE command denied to user ..." will be generated unless the UPDATE privilege has also been granted to the table's primary key.

How to repeat:
/* as root */
CREATE DATABASE test2; /* to avoid `test` database */
USE test2;
CREATE TABLE test (x INT PRIMARY KEY, y INT);
GRANT SELECT, INSERT, UPDATE(y) ON test2.test TO 'test'@'localhost';

/* as the new user test@localhost */
USE test2;
INSERT INTO test (x,y) VALUES (1,1) ON DUPLICATE KEY UPDATE y=2;
ERROR 1143 (42000): UPDATE command denied to user 'test'@'localhost' for column 'x' in table 'test'

Suggested fix:
Use UPDATE table privileges instead of column privileges.

Thank you for the bug report. Verified as described.

This needs to be documented: ON DUPLICATE KEY UPDATE converts an INSERT statement to an UPDATE statement upon a primary key conflict. An update statement in such case requires a privilege.

A normal UPDATE would require this privilege, even though the data is not changed:

mysql> insert into test values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> update test  set x=1, y=2;
ERROR 1143 (42000): UPDATE command denied to user 'test'@'localhost' for column 'x' in table 'test'
mysql> select * from test;
+---+------+
| x | y    |
+---+------+
| 1 |    1 | 
+---+------+
1 row in set (0.00 sec)

That's fine if the table has one row. If the table has more than one row you get:

  mysql> update test set x = 1, y = 2;
  ERROR 1062 (23000): Duplicate entry '1' for key 1

Surely the INSERT must be converted to an UPDATE of the form:

  update test set y = 2 where x = 1;

Which clearly does not need column privileges on the primary key.

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.

Not sure how this ticket can be marked as closed.  The documentation doesn't seem to have ever been updated to specifically address this reported bug.

Also, updating the documentation doesn't address the underlying compromise to data security if we're forced to provide UPDATE access to the entire table instead of being able to define more finely grained UPDATE access to just specific columns when this SQL syntax is used.