Skip to content

Latest commit

 

History

History
146 lines (109 loc) · 3.81 KB

sql-statement-create-role.md

File metadata and controls

146 lines (109 loc) · 3.81 KB
Raw
title summary
CREATE ROLE | TiDB SQL Statement Reference
An overview of the usage of CREATE ROLE for the TiDB database.

CREATE ROLE

This statement creates a new role, which can be assigned to users as part of role-based access control.

Synopsis

CreateRoleStmt ::=
    'CREATE' 'ROLE' IfNotExists RoleSpec (',' RoleSpec)*

IfNotExists ::=
    ('IF' 'NOT' 'EXISTS')?

RoleSpec ::=
    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)

MySQL compatibility

This statement is understood to be fully compatible with roles, which are a feature of MySQL 8.0. Any compatibility differences should be reported via an issue on GitHub.

See also