📣

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

CREATE ROLE

此语句用于创建一个新角色,可以作为基于角色的访问控制的一部分分配给用户。

概述

CreateRoleStmt
CREATEROLEIfNotExistsRoleSpec,
IfNotExists
IFNOTEXISTS
RoleSpec
Rolename

示例

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 中的 CREATE ROLE 语句与 MySQL 8.0 中的角色功能完全兼容。如果发现任何兼容性差异,请报告 bug

相关链接

文档内容是否有帮助?