Skip to content

Latest commit

 

History

History
177 lines (132 loc) · 4.39 KB

sql-statement-revoke-role.md

File metadata and controls

177 lines (132 loc) · 4.39 KB
Raw
title summary
REVOKE <role> | TiDB SQL Statement Reference
An overview of the usage of REVOKE <role> for the TiDB database.

REVOKE <role>

This statement removes a previously assigned role from a specified user (or list of users).

Synopsis

RevokeRoleStmt ::=
    'REVOKE' RolenameList 'FROM' UsernameList

RolenameList ::=
    Rolename ( ',' Rolename )*

UsernameList ::=
    Username ( ',' Username )*

Examples

Connect to TiDB as the root user:

mysql -h 127.0.0.1 -P 4000 -u root

Create a new role analyticsteam and a new user 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)

Connect to TiDB as the jennifer user:

mysql -h 127.0.0.1 -P 4000 -u jennifer

Note that by default jennifer needs to execute SET ROLE analyticsteam in order to be able to use the privileges associated with the analyticsteam role:

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)

Connect to TiDB as the root user:

mysql -h 127.0.0.1 -P 4000 -u root

The statement SET DEFAULT ROLE can be used to associate the role analyticsteam to jennifer:

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

Connect to TiDB as the jennifer user:

mysql -h 127.0.0.1 -P 4000 -u jennifer

After this, the user jennifer has the privileges associated with the role analyticsteam and jennifer does not have to execute the statement 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)

Connect to TiDB as the root user:

mysql -h 127.0.0.1 -P 4000 -u root

Revoke the role of analyticsteam from jennifer:

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

Connect to TiDB as the jennifer user:

mysql -h 127.0.0.1 -P 4000 -u jennifer

Show the privileges of jennifer:

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

MySQL compatibility

The REVOKE <role> statement in TiDB is fully compatible with the roles feature in MySQL 8.0. If you find any compatibility differences, report a bug.

See also