MySQL Bugs: #114965: The user has permission to the DB, but denied to SELECT it

Bug #114965	The user has permission to the DB, but denied to SELECT it
Submitted:	11 May 2024 7:03	Modified:	13 May 2024 13:22
Reporter:	mingm Tang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7,8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The user has all permissions for DB `dap_khtyb_chanpinchuangxin` and `dap\_khtyb_\chanpinchuangxin` , but is denied when querying the dap_khtyb_chanpinchuangxin database

How to repeat:
1、I created a DB and table and inserted the test data
> create database dap_khtyb_chanpinchuangxin;
> create table dap_khtyb_chanpinchuangxin.t1(id int);
> insert into dap_khtyb_chanpinchuangxin.t1 values(1);

2、grant all permissions for the DB to the specified user
> create user 'dapapp'@'%' identified with mysql_native_password by "123qqq...A";
> GRANT ALL PRIVILEGES ON `dap_khtyb_chanpinchuangxin`.* TO 'dapapp'@'%';

3、gives the user access to other DB 【note, backslash】
> GRANT CREATE, DROP, ALTER ON `dap\_khtyb_\chanpinchuangxin`.* TO 'dapapp'@'%';

4、Log in as the user, view the table, and report permission denied 
# mysql -udapapp -p123qqq...A -h192.168.111.10 -P3306 
> use dap_khtyb_chanpinchuangxin
> select * from dap_khtyb_chanpinchuangxin.t1;
ERROR 1142 (42000): SELECT command denied to user 'dapapp'@'192.168.111.10' for table 't1'

5、When I revoke the "DB with backslash grants", everything worked fine
> REVOKE CREATE, DROP, ALTER ON `dap\_khtyb_\chanpinchuangxin`.* FROM 'dapapp'@'%';

6、Attach the stack for the function
sql_authorization.cc: | | | | | | | <check_access
sql_authorization.cc:  3759: | | | | | | | >check_grant
sql_security_ctx.cc:   778: | | | | | | | | >Security_context::priv_user
sql_security_ctx.cc: | | | | | | | | <Security_context::priv_user
sql_security_ctx.cc:   918: | | | | | | | | >Security_context::ip
sql_security_ctx.cc: | | | | | | | | <Security_context::ip
sql_security_ctx.cc:   851: | | | | | | | | >Security_context::host
sql_security_ctx.cc: | | | | | | | | <Security_context::host
sql_authorization.cc:  3875: | | | | | | | | info: Table t1 didn't exist in the legacy table acl cache
        mdl.cc:  4096: | | | | | | | | >MDL_context::release_lock
        mdl.cc:  4097: | | | | | | | | | enter: db= name=
        mdl.cc: | | | | | | | | <MDL_context::release_lock
sql_security_ctx.h:   415: | | | | | | | | >Security_context::host_or_ip
sql_security_ctx.h: | | | | | | | | <Security_context::host_or_ip
sql_security_ctx.cc:   778: | | | | | | | | >Security_context::priv_user
sql_security_ctx.cc: | | | | | | | | <Security_context::priv_user
   my_error.cc:   218: | | | | | | | | >my_error
   my_error.cc:   219: | | | | | | | | | my: nr: 1142  MyFlags: 0  errno: 11
     mysqld.cc:  3758: | | | | | | | | | >my_message_sql
     mysqld.cc:  3759: | | | | | | | | | | error: error: 1142  message: 'SELECT command denied to user 'dapapp'@'localhost' for table 't1''

Suggested fix:
There should be no impact on the permissions of the correct resources.

HI Mr. Tang,

Thank you for your bug report.

However, we could not repeat it with mysql-8.0.37:

mysql> create user 'dap'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on dap.* to 'dap'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant create, drop, alter  on dap.* to 'dap'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

mysql -udap -p

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11

mysql> select * from t1;
Empty set (0.00 sec)

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

Can't repeat.

Please, do note that native password is deprecated since 8.0.34 and current release  is 8.0.37. Version 5.7 is no longer supported.

OK, I tested again on version 8.0.37, But I find the problem still exists, 

You can directly execute the test sample I provided here

1、create db and tables, 
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.37    |
+-----------+
1 row in set (0.00 sec)

mysql> create database dap_khtyb_chanpinchuangxin;
Query OK, 1 row affected (0.13 sec)

mysql> create table dap_khtyb_chanpinchuangxin.t1(id int);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into dap_khtyb_chanpinchuangxin.t1 values(1);
Query OK, 1 row affected (0.01 sec)

2、create users, and give user rights
mysql> create user 'dapapp'@'%' identified with mysql_native_password by "123qqq...A";
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON `dap_khtyb_chanpinchuangxin`.* TO 'dapapp'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT CREATE, DROP, ALTER ON `dap\_khtyb_\chanpinchuangxin`.* TO 'dapapp'@'%';
Query OK, 0 rows affected (0.00 sec)

3、Log in as the user, view the table, and report permission denied 
[root@localhost debug_8.0.37]# /data/mysql_binary/8.0.37/bin/mysql -udapapp -p123qqq...A -S /tmp/mysql_sandbox8037.sock -A
mysql> select * from dap_khtyb_chanpinchuangxin.t1;
ERROR 1142 (42000): SELECT command denied to user 'dapapp'@'localhost' for table 't1'

It looks like I gave the wrong DB permission，Note that the DB name here is `dap\_khtyb_\chanpinchuangxin`:
GRANT CREATE, DROP, ALTER ON `dap\_khtyb_\chanpinchuangxin`.* TO 'dapapp'@'%';

Hi Mr. Tang,

That is OK.

It can happen to anyone of us ......