📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

GRANT <role>

将之前创建的角色分配给现有用户。用户随后可以使用语句 SET ROLE <rolename> 来假设该角色的权限,或使用 SET ROLE ALL 来假设已被分配的所有角色。

概要

GrantRoleStmt
GRANTRolenameListTOUsernameList
RolenameList
Rolename,
UsernameList
Username,

示例

root 用户连接到 TiDB:

mysql -h 127.0.0.1 -P 4000 -u root

创建一个新角色 analyticsteam 和一个新用户 jennifer

CREATE ROLE analyticsteam; Query OK, 0 rows affected (0.02 sec) GRANT SELECT ON test.* TO analyticsteam; Query OK, 0 rows affected (0.02 sec) CREATE USER jennifer; Query OK, 0 rows affected (0.01 sec) GRANT analyticsteam TO jennifer; Query OK, 0 rows affected (0.01 sec)

jennifer 用户连接到 TiDB:

mysql -h 127.0.0.1 -P 4000 -u jennifer

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

SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 2 rows in set (0.00 sec) SHOW TABLES in test; ERROR 1044 (42000): Access denied for user 'jennifer'@'%' to database 'test' SET ROLE analyticsteam; Query OK, 0 rows affected (0.00 sec) SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT SELECT ON test.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 3 rows in set (0.00 sec) SHOW TABLES IN test; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

root 用户连接到 TiDB:

mysql -h 127.0.0.1 -P 4000 -u root

可以使用 SET DEFAULT ROLE 语句将 analyticsteam 角色关联到 jennifer

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

jennifer 用户连接到 TiDB:

mysql -h 127.0.0.1 -P 4000 -u jennifer

之后,用户 jennifer 将拥有与 analyticsteam 角色相关联的权限,且无需执行 SET ROLE 语句:

SHOW GRANTS; +---------------------------------------------+ | Grants for User | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | | GRANT SELECT ON test.* TO 'jennifer'@'%' | | GRANT 'analyticsteam'@'%' TO 'jennifer'@'%' | +---------------------------------------------+ 3 rows in set (0.00 sec) SHOW TABLES IN test; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec)

MySQL 兼容性

TiDB 中的 GRANT <role> 语句与 MySQL 8.0 中的角色功能完全兼容。如果发现任何兼容性差异,请报告一个 bug

相关链接

文档内容是否有帮助?