grant后权限好像覆盖了

【 TiDB 使用环境】线上、测试、调研
【 TiDB 版本】
v5.4.1

【遇到的问题】
tidb中的grant与mysql中不同,好像是grant后权限覆盖了。

【复现路径】做过哪些操作出现的问题
在tidb中创建测试库与测试用户如下:
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)

另开一个会话,测试如下:
[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 © 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; 这里可以看到所有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 切换到syk_db_test后却不能创建表
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]>
好像是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’@’%’,但是在show grants时却可以看到两条grant。

下面是在mysql5.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)

另开一个会话,测试如下:
[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 © 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; 这里可以看到所有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 切换到syk_db_test是可以创建表的
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 切换到syk_db_temp是不可以创建的,因为只有select权限
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]>

mysql中的grant没有问题。

请帮忙看看是不是触发bug了,还是有什么地方我这边没有注意到,比如是不是有什么相关参数可以控制,等等,谢谢!

【问题现象及影响】

【附件】

请提供各个组件的 version 信息,如 cdc/tikv,可通过执行 cdc version/tikv-server --version 获取。

TiDB 中的 GRANT 与 MySQL 中的 GRANT 确实有一些不同之处。在 MySQL 中,GRANT 语句会为用户授予一组权限,并将这些权限添加到用户的权限列表中。如果用户已经拥有某个权限,再次执行 GRANT 语句时,该权限不会被重复授予。

在 TiDB 中,GRANT 语句的行为与 MySQL 有所不同。TiDB 中的 GRANT 语句会为用户授予一组权限,并将这些权限覆盖用户的权限列表。也就是说,如果用户已经拥有某个权限,再次执行 GRANT 语句时,该权限会被覆盖,而不是被重复授予。

这种行为的原因是 TiDB 的权限管理系统与 MySQL 有所不同。在 TiDB 中,权限是以角色为单位进行管理的。用户可以被授予一个或多个角色,每个角色包含一组权限。当用户登录 TiDB 时,系统会根据用户所属的角色来确定其权限列表。因此,TiDB 中的 GRANT 语句实际上是为用户授予一个角色,而不是为用户授予一组权限。如果用户已经拥有某个权限,再次执行 GRANT 语句时,该权限会被覆盖,因为 TiDB 认为用户已经拥有了该角色,不需要重复授予。

需要注意的是,TiDB 中的 GRANT 语句只能为用户授予角色,而不能为用户直接授予权限。如果需要为用户直接授予权限,可以先创建一个包含该权限的角色,然后为用户授予该角色。

明白了,感谢!

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。