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.