Bug #100742 Access denied for multi-table fully qualified DELETE if default database is I_S
Description:
If I use multiple-table DELETE syntax with a fully qualified table name while the default database is Information Schema I get the error "ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'" However, the same query in different database works. And single-table DELETE also works inside Information Schema. The table does not belong to Information Schema. The user has DELETE permission on it.
How to repeat:
mysql> drop table test.t1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test.t1(f1 int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test.t1 values(1),(2),(3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete from test.t1 where f1=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete foo from test.t1 as foo where f1=2;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete from test.t1 where f1=3;
Query OK, 1 row affected (0.00 sec)
mysql> delete foo from test.t1 as foo where f1=4;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delete foo from test.t1 as foo where f1=4;
Query OK, 1 row affected (0.00 sec)
Suggested fix:
Allow deleting from the table in a different database regardless the syntax.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.