Bug #59042 INSERT ... ON DUPLICATE KEY UPDATE shouldn;t need UPDATE access to key columns
Submitted: 19 Dec 2010 18:44
Reporter: Chris Thompson Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.1.52 OS:Any
Assigned to: CPU Architecture:Any

[19 Dec 2010 18:44] Chris Thompson
Description:
INSERT ... ON DUPLICATE KEY UPDATE ...

on a table with a primary key requires UPDATE (not just INSERT)
access to the primary key columns, even though the UPDATE half
of the statement doesn't attempt to update them.

This is inconvenient if one wants a table whose primary keys are
to be immutable once set.

How to repeat:

CREATE TABLE t1 (k1 varchar(10) primary key, v1 varchar(10));

GRANT select,insert(k1,v1),update(v1) ON TABLE t1 TO user1;

(then as user1)

INSERT INTO t1(k1,v1) VALUES('key','value') 
 ON DUPLICATE KEY UPDATE v1='value';

This fails if the only relevant grants to user1 are as above.