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:

SetRoleStmt

SetRoleOpt:

SetRoleOpt

SetDefaultRoleOpt:

SetDefaultRoleOpt

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'@'%'.

CREATE USER 'u1'@'%'; CREATE ROLE 'r1', 'r2', 'r3'; GRANT 'r1', 'r2', 'r3' TO 'u1'@'%'; SET DEFAULT ROLE 'r1' TO 'u1'@'%';

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

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

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

SET ROLE 'r2', 'r3'; SELECT CURRENT_ROLE();
+-------------------+ | CURRENT_ROLE() | +-------------------+ | `r2`@`%`,`r3`@`%` | +-------------------+ 1 row in set (0.000 sec)

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

SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+----------------+ | CURRENT_ROLE() | +----------------+ | `r1`@`%` | +----------------+ 1 row in set (0.000 sec)

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

SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+ | CURRENT_ROLE() | +----------------+ | | +----------------+ 1 row in set (0.000 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

Was this page helpful?