MySQL Bugs: #2377: UPDATE privilege BUG

Bug #2377	UPDATE privilege BUG
Submitted:	13 Jan 2004 11:49	Modified:	3 Feb 2004 7:51
Reporter:	JOrdan L	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.16	OS:	Linux (Linux)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I testing this on mysql  Ver 12.22 Distrib 4.0.16, for pc-linux (i686) /w innodb tables.  And had someone #mysql on irc.freenode.com test on 4.0.18 OS unknow /w MyISAM tables.  Searched for UPDATE PRIVILEDGE in bugs.mysql.com database and found nothing.

- Priviledges:
table foo has SELECT/UPDATE Privs
table bar has SELECT Privs

- Query Tried:
UPDATE foo f,bar b
SET f.this=1
WHERE f.that=2
  AND b.that=3

- Problem:
UPDATE Permision to table bar DENIED.

- Desired output
Since table bar is not in the "SET" section of the UPDATE query i assumed that
something like this would have worked with out a problem.  I do NOT want my database user to have update privs on table bar, but i am forced to give as temporary fix.

How to repeat:
i described the problem to users (Therion) in #mysql and he was able to repeat with the lastest version of mysql 4.0.x.  You should be able to duplicate this error with the steps described above.

Suggested fix:
temporary fix is to give update privs to table bar.
This is a pretty nasty bug from someone trying to implement permision system so i suggest it be fixed in server if possible.

typo in title

Simply put, you must have UPDATE privileges on every table in a multi-table UPDATE statement, even if you do not actually UPDATE all tables:

CREATE DATABASE bt;
USE bt;
CREATE TABLE up1 ( a int );
CREATE TABLE up2 ( a int, b int );
INSERT INTO up1 VALUES ( 1 );
INSERT INTO up2 VALUES ( 1, 0 );
GRANT SELECT ON bt.up1 TO bugtest@localhost;
GRANT SELECT, UPDATE ON bt.up2 TO bugtest@localhost;

...

UPDATE up1, up2 SET up2.b = up1.a + up2.a WHERE up1.a = up2.a;

ERROR 1142 (42000): update command denied to user: 'bugtest'@'localhost' for table 'up1'

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be in 4.0.18 and 4.1.2

i <3 you, thanks.