📣

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

REVOKE <role>

该语句用于撤销之前分配给指定用户(或用户列表)的角色。

概要

RevokeRoleStmt
REVOKERolenameListFROMUsernameList
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 ROLEanalyticsteam 角色关联到 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)

root 用户连接到 TiDB:

mysql -h 127.0.0.1 -P 4000 -u root

撤销 jenniferanalyticsteam 角色:

REVOKE analyticsteam FROM jennifer; Query OK, 0 rows affected (0.01 sec)

jennifer 用户连接到 TiDB:

mysql -h 127.0.0.1 -P 4000 -u jennifer

显示 jennifer 的权限:

SHOW GRANTS; +--------------------------------------+ | Grants for User | +--------------------------------------+ | GRANT USAGE ON *.* TO 'jennifer'@'%' | +--------------------------------------+ 1 row in set (0.00 sec)

MySQL 兼容性

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

相关链接

文档内容是否有帮助?