Bug #100742 Access denied for multi-table fully qualified DELETE if default database is I_S

Submitted: 4 Sep 2020 14:52 Modified: 4 Sep 2020 15:13
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.7.30, 8.0.20 OS:Any
Assigned to: CPU Architecture:Any

[4 Sep 2020 14:52] Sveta Smirnova
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.
[4 Sep 2020 15:13] MySQL Verification Team
Thank you for the bug report.

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.