MySQL Bugs: #60339: UPDATE privilege is not sufficient to do an update

Bug #60339	UPDATE privilege is not sufficient to do an update
Submitted:	4 Mar 2011 15:14	Modified:	7 Mar 2011 12:45
Reporter:	Thomas Bouchet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	Windows (easyphp 5.3 install)
Assigned to:		CPU Architecture:	Any

Description:
When granting only UPDATE privilege on a specific database for a specific user, it seems that this user cannot update a table of this database.
If I add to him the SELECT privilege, it works fine.

It's important to note that the user has no global privilege.

But, when trying to see if the query has succeeded, mysql_error is empty while the result is false (using mysql_query with PHP).

How to repeat:
Scripts copy-pasted from my phpmyadmin and website :

// After this command, the user can't do any updates
REVOKE ALL PRIVILEGES ON `z` . * FROM 'update_Z'@'%';
GRANT UPDATE ON `z` . * TO 'update_Z'@'%';

// After this command, it works fine, the user can do updates
REVOKE ALL PRIVILEGES ON `z` . * FROM 'update_Z'@'%';
GRANT SELECT , UPDATE ON `z` . * TO 'update_Z'@'%';

I think our manual (http://dev.mysql.com/doc/refman/5.1/en/update.html) explains your case:

"You need the UPDATE privilege only for columns referenced in an UPDATE that are actually updated. You need only the SELECT privilege for any columns that are read but not modified." 

You need SELECT privilege to find/read the data you are going to update...

So, for an update command like this :

UPDATE table
SET column1 = newvalue
WHERE column2 = value

I need SELECT privilege for the WHERE clause, but if I do this :

UPDATE table
SET column1 = newvalue
, column2 = value
WHERE column2 = value

I won't need the SELECT privilege ?
Seems strange for me, since I will nearly always do an update based on a where clause which will maybe not involve an update column.
For a DELETE statement, I have to use SELECT privilege, since I don't want to delete all the content of the table, true ?

Fir this statement:

UPDATE table
SET column1 = newvalue
WHERE column2 = value

you need SELECT privilege for the column2 at least (SELECT for entire table will also work) and UPDATE privilege for column1 at least. While for this statement:

UPDATE table
SET column1 = newvalue
, column2 = value
WHERE column2 = value

you need UPDATE privilege for column1 and SELECT + UPDATE privileges for column2.

Do you see something different in your tests?

As for DELETE, manual (http://dev.mysql.com/doc/refman/5.1/en/delete.html) is also clear:

"You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause."

Ok, seemed a little confused since in my though I have to put a WHERE clause and wanted just an UPDATE privilege. This is not very serious since the SELECT privilege is not a security issue.
Thanks anyway for spending your time anserwing me.