Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: grant后权限好像覆盖了
|
username: qhd2004
【TiDB Usage Environment】Production, Testing, Research
【TiDB Version】
v5.4.1
【Encountered Problem】
The grant command in TiDB is different from MySQL. It seems that after granting, the permissions are overwritten.
【Reproduction Steps】What operations were performed to encounter the problem
Create test databases and test user in TiDB as follows:
mysql [root@tidbdev.syk:(none)]> create database syk_db_test;
Query OK, 0 rows affected (0.16 sec)
mysql [root@tidbdev.syk:(none)]> create database syk_db_temp;
Query OK, 0 rows affected (0.11 sec)
mysql [root@tidbdev.syk:(none)]> create database syk_db_dev;
Query OK, 0 rows affected (0.13 sec)
mysql [root@tidbdev.syk:(none)]> create database syk_db_sit;
Query OK, 0 rows affected (0.12 sec)
mysql [root@tidbdev.syk:(none)]> CREATE USER IF NOT EXISTS 'syk_usr_test'@'%' IDENTIFIED BY 'PWD_syk_test';
Query OK, 0 rows affected (0.06 sec)
mysql [root@tidbdev.syk:(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE VIEW,SHOW VIEW ON syk_db_test.* TO 'syk_usr_test'@'%';
Query OK, 0 rows affected (0.05 sec)
mysql [root@tidbdev.syk:(none)]> GRANT select ON `syk_db_%`.* TO 'syk_usr_test'@'%';
Query OK, 0 rows affected (0.07 sec)
mysql [root@tidbdev.syk:(none)]> show grants for syk_usr_test;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for syk_usr_test@% |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'syk_usr_test'@'%' |
| GRANT SELECT ON syk_db_%.* TO 'syk_usr_test'@'%' |
| GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE VIEW,SHOW VIEW ON syk_db_test.* TO 'syk_usr_test'@'%' |
+------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.02 sec)
Open another session and test as follows:
[syk@syk ~]$ mysql -usyk_usr_test -pPWD_syk_test -h tidbdev.syk
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4917801
Server version: 5.7.25-TiDB-v5.4.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [syk_usr_test@tidbdev.syk:(none)]> show databases; Here you can see all databases starting with syk_db_
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| syk_db_dev |
| syk_db_sit |
| syk_db_temp |
| syk_db_test |
+--------------------+
5 rows in set (0.01 sec)
mysql [syk_usr_test@tidbdev.syk:(none)]> use syk_db_test; After switching to syk_db_test, you cannot create a table
Database changed
mysql [syk_usr_test@tidbdev.syk:syk_db_test]> create table syk_test(id int,name varchar(10));
ERROR 1142 (42000): CREATE command denied to user 'syk_usr_test'@'%' for table 'syk_test'
mysql [syk_usr_test@tidbdev.syk:syk_db_test]>
It seems that GRANT SELECT ON syk_db_%.* TO 'syk_usr_test'@'%' overwrote GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE VIEW,SHOW VIEW ON syk_db_test.* TO 'syk_usr_test'@'%', but both grants are visible when using show grants.
Below is the test on MySQL 5.7.38:
mysql mysql.sock [(none)]> create database syk_db_test;
Query OK, 1 row affected (0.00 sec)
mysql mysql.sock [(none)]> create database syk_db_temp;
Query OK, 1 row affected (0.00 sec)
mysql mysql.sock [(none)]> create database syk_db_dev;
Query OK, 1 row affected (0.00 sec)
mysql mysql.sock [(none)]> create database syk_db_sit;
Query OK, 1 row affected (0.00 sec)
mysql mysql.sock [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE VIEW,SHOW VIEW ON syk_db_test.* TO 'syk_usr_test'@'%' identified by 'PWD_syk_test';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql mysql.sock [(none)]> GRANT select ON `syk_db_%`.* TO 'syk_usr_test'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql mysql.sock [(none)]> show grants for syk_usr_test;
+-----------------------------------------------------------------------------------------------------------------------------------+
| Grants for syk_usr_test@% |
+-----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'syk_usr_test'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `syk_db_test`.* TO 'syk_usr_test'@'%' |
| GRANT SELECT ON `syk_db_%`.* TO 'syk_usr_test'@'%' |
+-----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Open another session and test as follows:
[root@localhost ~]# mysql -usyk_usr_test -pPWD_syk_test -h 127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 3306 [(none)]> show databases; Here you can see all databases starting with syk_db_
+--------------------+
| Database |
+--------------------+
| information_schema |
| syk_db_dev |
| syk_db_sit |
| syk_db_temp |
| syk_db_test |
+--------------------+
5 rows in set (0.00 sec)
mysql 3306 [(none)]> use syk_db_test; Switching to syk_db_test allows table creation
Database changed
mysql 3306 [syk_db_test]> create table syk_test(id int,name varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql 3306 [syk_db_test]> use syk_db_temp; Switching to syk_db_temp does not allow table creation because only select permission is granted
Database changed
mysql 3306 [syk_db_temp]> create table syk_test(id int,name varchar(10));
ERROR 1142 (42000): CREATE command denied to user 'syk_usr_test'@'127.0.0.1' for table 'syk_test'
mysql 3306 [syk_db_temp]>
There is no problem with the grant in MySQL.
Please help check if this is a bug or if there is something I missed, such as any related parameters that can control this behavior. Thank you!
【Problem Phenomenon and Impact】
【Attachments】
Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing
cdc version/tikv-server --version.
|
username: 
|
username: