MySQL Bugs: #236: Multi-table update denied to user who has all privleges

Bug #236	Multi-table update denied to user who has all privleges
Submitted:	6 Apr 2003 13:56	Modified:	15 May 2003 7:59
Reporter:	Jesse Bangs	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.11-gamma-max	OS:	FreeBSD (FreeBSD 4.7-RELEASE)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
I have all privleges (including update privlege) on the database in question. I wish to update the contents of one column to the contents of another column using a standard multi-column update. When I try to do this, I'm told that I don't have privleges on the second of the two named tables:

Example 1:
mysql> update shows, artist set artist.mp3_url=shows.mp3_url where artist.name = shows.band_1;
ERROR 1142: update command denied to user: 'paradox@localhost' for table 'artist'

Example 2:
mysql> update artist,shows set artist.mp3_url=shows.mp3_url where artist.name = shows.band_1;
ERROR 1142: update command denied to user: 'paradox@localhost' for table 'shows'

The root user is able to execute the command successfully.

How to repeat:
Create a non-root user
Create a database with at least two tables
Give the user all privleges on the table
Throw some data in
Execute an update query naming two tables, writing the contents of tbl1.xyz to tbl2.xyz (as shown above)

Suggested fix:
???
Check privileges correctly.

As our documentation clearly depicts, when UPDATE command
is specified as in example, UPDATE and SELECT privileges are required for both tables.

I just tried it with 4.0.12 and it works.

> As our documentation clearly depicts, when UPDATE command
> is specified as in example, UPDATE and SELECT privileges > are required for both tables.

And as I clearly stated, I *have* UPDATE and SELECT privileges for both tables, unless "GRANT ALL PRIVILEGES ON db.* TO user" doesn't give them to me. Or my sysadmin is lying and didn't actually give me all privileges.

The previous comment was not correct as the user had update and select privilege on the column.

We did however just fix a privilege related bug in 4.0.12, which will be in 4.0.13.  Could you please try if this version fixes your problem.  If not, please reopen this bug again and provide us with an 'copy and past example' that doesn't work.

Here is what I tested, which worked in 4.0.13 without any problems:
As root:
create database new;
grant all privileges on new.* to test_user;

as test_user:

create table t1 (a int, b int);
create table t2 (a int, b int);
update t1,t2 set t1.b=t2.b where t1.a=t2.a;