Role-Based Access Control

The implementation of TiDB's role-based access control (RBAC) system is similar to that of MySQL 8.0. TiDB is compatible with most RBAC syntax of MySQL.

This document introduces TiDB RBAC-related operations and implementation.

RBAC operations

A role is a collection of a series of privileges. You can do the following operations:

Create a role.
Delete a role.
Grant a privilege to a role.
Grant a role to another user. That user can obtain the privileges involved in the role, after enabling the role.

Create a role

For example, you can use the following statement to create the roles app_developer, app_read, and app_write:

CREATE ROLE 'app_developer', 'app_read', 'app_write';

For the role naming format and rule, see TiDB User Account Management.

Roles are stored in the mysql.user table and the host name part of the role name (if omitted) defaults to '%'. The name of the role you are trying to create must be unique; otherwise, an error is reported.

To create a role, you need the CREATE ROLE or CREATE USER privilege.

Grant a privilege to a role

The operation of granting a privilege to a role is the same with that of granting a privilege to a user. For details, see TiDB Privilege Management.

For example, you can use the following statement to grant the app_read role the privilege to read the app_db database:

GRANT SELECT ON app_db.* TO 'app_read'@'%';

You can use the following statement to grant the app_write role the privilege to write data to the app_db database:

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write'@'%';

You can use the following statement to grant the app_developer role all privileges on the app_db database:

GRANT ALL ON app_db.* TO 'app_developer';

Grant a role to a user

Assume that a user dev1 has the developer role with all the privileges on app_db; two users read_user1 and read_user2 have the read-only privilege on app_db; and a user rw_user1 has read and write privileges on app_db.

Use CREATE USER to create the users:

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

Then use GRANT to grant roles to users

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

To grant a role to another user or revoke a role, you need the SUPER privilege.

Granting a role to a user does not mean enabling the role immediately. Enabling a role is another operation.

The following operations might form a "relation loop:"

CREATE USER 'u1', 'u2';
CREATE ROLE 'r1', 'r2';

GRANT 'u1' TO 'u1';
GRANT 'r1' TO 'r1';

GRANT 'r2' TO 'u2';
GRANT 'u2' TO 'r2';

TiDB supports this multi-level authorization relationship. You can use it to implement privilege inheritance.

Check a role's privileges

You can use the SHOW GRANTS statement to check what privileges have been granted to the user.

To check privilege-related information of another user, you need the SELECT privilege on the mysql database.

SHOW GRANTS FOR 'dev1'@'localhost';

+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+

You can use the USING option in SHOW GRANTS to check a role's privileges:

SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';

+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';

+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+

SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';

+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+

You can use SHOW GRANTS or SHOW GRANTS FOR CURRENT_USER() to check the current user's privileges. SHOW GRANTS and SHOW GRANTS FOR CURRENT_USER() are different in the following aspects:

SHOW GRANTS shows the privilege of the enabled role for the current user.
SHOW GRANTS FOR CURRENT_USER() does not show the enabled role's privilege.

Set the default role

After a role is granted to a user, it does not take effect immediately. Only after the user enables this role, he can use the privilege the role owns.

You can set default roles for a user. When the user logs in, the default roles are automatically enabled.

SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ]

For example, you can use the following statement to set default roles of rw_user1@localhost to app_read and app_write:

SET DEFAULT ROLE app_read, app_write TO 'rw_user1'@'localhost';

You can use the following statement to set default roles of dev1@localhost to all roles:

SET DEFAULT ROLE ALL TO 'dev1'@'localhost';

You can use the following statement to disable all default roles of dev1@localhost:

SET DEFAULT ROLE NONE TO 'dev1'@'localhost';

Note

You need to grant the role to the user before you set the default role to this role.

Enable a role in the current session

You can enable some role(s) in the current session.

SET ROLE {
    DEFAULT
  | NONE
  | ALL
  | ALL EXCEPT role [, role ] ...
  | role [, role ] ...
}

For example, after rw_user1 logs in, you can use the following statement to enable roles app_read and app_write that are valid only in the current session:

SET ROLE 'app_read', 'app_write';

You can use the following statement to enable the default role of the current user:

SET ROLE DEFAULT

You can use the following statement to enable all roles granted to the current user:

SET ROLE ALL

You can use the following statement to disable all roles:

SET ROLE NONE

You can use the following statement to enable roles except app_read:

SET ROLE ALL EXCEPT 'app_read'

Note

If you use SET ROLE to enable a role, this role is valid only in the current session.

Check the current enabled role

The current user can use the CURRENT_ROLE() function to check which role has been enabled by the current user.

For example, you can grant default roles to rw_user1'@'localhost:

SET DEFAULT ROLE ALL TO 'rw_user1'@'localhost';

After rw_user1@localhost logs in, you can execute the following statement:

SELECT CURRENT_ROLE();

+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

SET ROLE 'app_read'; SELECT CURRENT_ROLE();

+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+

Revoke a role

You can use the following statement to revoke the app_read role granted to the users read_user1@localhost and read_user2@localhost:

REVOKE 'app_read' FROM 'read_user1'@'localhost', 'read_user2'@'localhost';

You can use the following statement to revoke the roles app_read and app_write granted to the rw_user1@localhost user:

REVOKE 'app_read', 'app_write' FROM 'rw_user1'@'localhost';

The operation of revoking a role from a user is atomic. If you fail to revoke a role, this operation rolls back.

Revoke a privilege

The REVOKE statement is reverse to GRANT. You can use REVOKE to revoke the privileges of app_write.

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

For details, see TiDB Privilege Management.

Delete a role

You can use the following statement to delete roles app_read and app_write:

DROP ROLE 'app_read', 'app_write';

This operation deletes the role records of app_read and app_write in the mysql.user table and related records in the authorization table, and terminates the authorization related to the two roles.

To delete a role, you need the DROP ROLE or DROP USER privilege.

Authorization table

In addition to four system privilege tables, the RBAC system introduces two new system privilege tables:

mysql.role_edges: records the authorization relationship of the role and user.
mysql.default_roles: records default roles of each user.

`mysql.role_edges`

mysql.role_edges contains the following data:

SELECT * FROM mysql.role_edges;

+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | r_1       | %       | u_1     | N                 |
+-----------+-----------+---------+---------+-------------------+
1 row in set (0.00 sec)

FROM_HOST and FROM_USER indicate the role's host name and user name respectively.
TO_HOST and TO_USER indicate the host name and user name of the user to which a role is granted.

`mysql.default_roles`

mysql.default_roles shows which roles have been enabled by default for each user.

SELECT * FROM mysql.default_roles;

+------+------+-------------------+-------------------+
| HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+------+-------------------+-------------------+
| %    | u_1  | %                 | r_1               |
| %    | u_1  | %                 | r_2               |
+------+------+-------------------+-------------------+
2 rows in set (0.00 sec)

HOST and USER indicate the user's host name and user name respectively.
DEFAULT_ROLE_HOST and DEFAULT_ROLE_USER indicate the host name and user name of the default role respectively.

References

Because RBAC, user management, and privilege management are closely related, you can refer to operation details in the following resources: