SET ROLE

The SET ROLE statement is used to enable roles in the current session. After enabling roles, users can use the privileges of the role(s).

Synopsis

SetRoleStmt

SET ROLE - 图1

  1. SetRoleStmt ::=
  2.     "SET" "ROLE" ( "DEFAULT" | "ALL" ( "EXCEPT" Rolename ("," Rolename)* )? | "NONE" | Rolename ("," Rolename)* )?

Examples

Create a user 'u1'@'%' and three roles: 'r1'@'%', 'r2'@'%' and 'r3'@'%'. Grant these roles to 'u1'@'%' and set 'r1'@'%' as the default role of 'u1'@'%'.

  1. CREATE USER 'u1'@'%';
  2. CREATE ROLE 'r1', 'r2', 'r3';
  3. GRANT 'r1', 'r2', 'r3' TO 'u1'@'%';
  4. SET DEFAULT ROLE 'r1' TO 'u1'@'%';

Log in as 'u1'@'%' and execute the following SET ROLE statement to enable all roles.

  1. SET ROLE ALL;
  2. SELECT CURRENT_ROLE();
  1. +----------------------------+
  2. | CURRENT_ROLE()             |
  3. +----------------------------+
  4. | `r1`@`%`,`r2`@`%`,`r3`@`%` |
  5. +----------------------------+
  6. 1 row in set (0.000 sec)

Execute the following SET ROLE statement to enable 'r2' and 'r3'.

  1. SET ROLE 'r2', 'r3';
  2. SELECT CURRENT_ROLE();
  1. +-------------------+
  2. | CURRENT_ROLE()    |
  3. +-------------------+
  4. | `r2`@`%`,`r3`@`%` |
  5. +-------------------+
  6. 1 row in set (0.000 sec)

Execute the following SET ROLE statement to enable the default role(s).

  1. SET ROLE DEFAULT;
  2. SELECT CURRENT_ROLE();
  1. +----------------+
  2. | CURRENT_ROLE() |
  3. +----------------+
  4. | `r1`@`%`       |
  5. +----------------+
  6. 1 row in set (0.000 sec)

Execute the following SET ROLE statement to cancel all enabled role(s).

  1. SET ROLE NONE;
  2. SELECT CURRENT_ROLE();
  1. +----------------+
  2. | CURRENT_ROLE() |
  3. +----------------+
  4. |                |
  5. +----------------+
  6. 1 row in set (0.000 sec)

MySQL compatibility

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