Skip to content

Latest commit

 

History

History
179 lines (133 loc) · 4.5 KB

sql-statement-drop-role.md

File metadata and controls

179 lines (133 loc) · 4.5 KB
Raw
title summary
DROP ROLE | TiDB SQL Statement Reference
An overview of the usage of DROP ROLE for the TiDB database.

DROP ROLE

This statement removes a role, that was previously created with CREATE ROLE.

Synopsis

DropRoleStmt ::=
    'DROP' 'ROLE' ( 'IF' 'EXISTS' )? RolenameList

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

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

Drop the role for the analyticsteam:

DROP ROLE analyticsteam;
Query OK, 0 rows affected (0.02 sec)

jennifer no longer has the default role of analyticsteam associated, nor can set the role to analyticsteam.

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)

SET ROLE analyticsteam;
ERROR 3530 (HY000): `analyticsteam`@`%` is is not granted to jennifer@%

MySQL compatibility

The DROP 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