MySQL Bugs: #768: Db privilege table not being used with multi-table updates

Bug #768	Db privilege table not being used with multi-table updates
Submitted:	30 Jun 2003 16:51	Modified:	19 Jul 2003 8:16
Reporter:	Cory Twibell	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0-alpha-max	OS:	SunOS 5.8
Assigned to:		CPU Architecture:	Any

Description:
When executing a multi-table update statement, the user is presented with 
ERROR 1142: update command denied to user: '@localhost' for table 'test2'

How to repeat:
Create an anonymous user in the user table with NO golbal privileges.
Create the same anonymous user in the Db table for Databases 'Test1' and 'Test2'.

Create table Test1 (
  id int,
  value varchar(10) );

Create table Test2 (
   id int );

mysql> update Test1 A, Test2, B
mysql> set A.value = 'value'
mysql> where A.id = B.id;

Suggested fix:
It seems like the privilege tables are not working correctly for Db table.
Give the user the global insert and update privilege in the user table, BUT very bad!

The users privileges in the Db table should be all 'Y'.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Please note that Privilege tables should be never changed directly. GRANT/REVOKE commands should be used instead.

Information in bug report is not clear enough so no way to repeat it.

Test1 and Test2 are Tables, not Databases. It is not clear how exactly you changed privilege tables.

Alexander,

I did use the GRANT syntax to the user: GRANT ALL ON DB1.* to ''@localhost
then FLUSH PRIVILEGES (just to be sure)

Then, create the tables in the DB1 database:
   Create table table1(int id, value varchar(10) );
   Create table table2(int id );

Then on the mysql command line> 
   Use DB1;
   Update table1 A, table2 B set A.value = "empty" where A.id = B.id;

Then receive the following error:
   ERROR 1142: update command denied to user: '@localhost' for table 'table2'

The user does have ALL privileges for the database in the mysql.db table.
When I add the global update and select to the mysql.user table, it works.

With latest 4.1.1 it worked like a charm for me:

mysql> create table t1( id int, value varchar(10) );
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2( id int);
Query OK, 0 rows affected (0.01 sec)
mysql> Update t1, t2 set t1.value = "empty" where t1.id = t2.id;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)

And user had the following privs on that database (telcent):

+-------------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost                                                                                         |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost'                                                                          |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `telcent`.* TO 'user'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+