Bug #59042 INSERT ... ON DUPLICATE KEY UPDATE shouldn;t need UPDATE access to key columns

• Submitted: 19 Dec 2010 18:44
• Reporter: Chris Thompson
• Status: Open
• Category: MySQL Server: Security: Privileges
• Severity: S4 (Feature request)
• Version: 5.1.52
• OS: Any
• 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.