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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.7,8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 May 2024 7:03]
mingm Tang
[13 May 2024 11:13]
MySQL Verification Team
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.
[13 May 2024 13:19]
mingm Tang
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'
[13 May 2024 13:22]
mingm Tang
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'@'%';
[13 May 2024 13:34]
MySQL Verification Team
Hi Mr. Tang, That is OK. It can happen to anyone of us ......