GRANT <role>

GRANT <role> 语句用于将之前创建的角色授予给现有用户。用户可以通过 SET ROLE <rolename> 语句拥有角色权限,或者通过 SET ROLE ALL 语句拥有被授予的所有角色。

语法图

GrantRoleStmt

GRANT <role> - 图1

RolenameList

GRANT <role> - 图2

UsernameList

GRANT <role> - 图3

  1. GrantRoleStmt ::=
  2. 'GRANT' RolenameList 'TO' UsernameList
  3. RolenameList ::=
  4. Rolename ( ',' Rolename )*
  5. UsernameList ::=
  6. Username ( ',' Username )*

示例

root 用户连接 TiDB:

  1. mysql -h 127.0.0.1 -P 4000 -u root

创建新角色 analyticsteam 和新用户 jennifer

  1. CREATE ROLE analyticsteam;
  2. Query OK, 0 rows affected (0.02 sec)
  3. GRANT SELECT ON test.* TO analyticsteam;
  4. Query OK, 0 rows affected (0.02 sec)
  5. CREATE USER jennifer;
  6. Query OK, 0 rows affected (0.01 sec)
  7. GRANT analyticsteam TO jennifer;
  8. Query OK, 0 rows affected (0.01 sec)

jennifer 用户连接 TiDB:

  1. mysql -h 127.0.0.1 -P 4000 -u jennifer

需要注意的是,默认情况下,用户 jennifer 需要执行 SET ROLE analyticsteam 语句才能使用与 analyticsteam 角色相关联的权限:

  1. SHOW GRANTS;
  2. +---------------------------------------------+
  3. | Grants for User |
  4. +---------------------------------------------+
  5. | GRANT USAGE ON *.* TO 'jennifer'@'%' |
  6. | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' |
  7. +---------------------------------------------+
  8. 2 rows in set (0.00 sec)
  9. SHOW TABLES in test;
  10. ERROR 1044 (42000): Access denied for user 'jennifer'@'%' to database 'test'
  11. SET ROLE analyticsteam;
  12. Query OK, 0 rows affected (0.00 sec)
  13. SHOW GRANTS;
  14. +---------------------------------------------+
  15. | Grants for User |
  16. +---------------------------------------------+
  17. | GRANT USAGE ON *.* TO 'jennifer'@'%' |
  18. | GRANT SELECT ON test.* TO 'jennifer'@'%' |
  19. | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' |
  20. +---------------------------------------------+
  21. 3 rows in set (0.00 sec)
  22. SHOW TABLES IN test;
  23. +----------------+
  24. | Tables_in_test |
  25. +----------------+
  26. | t1 |
  27. +----------------+
  28. 1 row in set (0.00 sec)

root 用户连接 TiDB:

  1. mysql -h 127.0.0.1 -P 4000 -u root

执行 SET DEFAULT ROLE 语句将用户 jenniferanalyticsteam 角色相关联:

  1. SET DEFAULT ROLE analyticsteam TO jennifer;
  2. Query OK, 0 rows affected (0.02 sec)

jennifer 用户连接 TiDB:

  1. mysql -h 127.0.0.1 -P 4000 -u jennifer

此时 jennifer 用户无需执行 SET ROLE 语句就能拥有 analyticsteam 角色相关联的权限:

  1. SHOW GRANTS;
  2. +---------------------------------------------+
  3. | Grants for User |
  4. +---------------------------------------------+
  5. | GRANT USAGE ON *.* TO 'jennifer'@'%' |
  6. | GRANT SELECT ON test.* TO 'jennifer'@'%' |
  7. | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' |
  8. +---------------------------------------------+
  9. 3 rows in set (0.00 sec)
  10. SHOW TABLES IN test;
  11. +----------------+
  12. | Tables_in_test |
  13. +----------------+
  14. | t1 |
  15. +----------------+
  16. 1 row in set (0.00 sec)

MySQL 兼容性

GRANT <role> 语句与 MySQL 8.0 的角色功能完全兼容。如发现任何兼容性差异,请尝试 TiDB 支持资源

另请参阅