Skip to main content
Version: 3.0

SET ROLE

Description​

Activates roles, along with all of its associated privileges, for the current session. After the role is activated, users can use this role to perform operations.

This command is supported from v3.0.

Usage notes​

Users can only activate roles that have been assigned to them.

You can query the roles of a user using SHOW GRANTS.

You can query the active roles of the current user using SELECT CURRENT_ROLE(). For more information, see current_role.

Syntax​

-- Active specific roles and perform operations as this role.
SET ROLE <role_name>[,<role_name>,..];
-- Activate all roles of a user, except for specific roles.
SET ROLE ALL EXCEPT <role_name>[,<role_name>,..]; 
-- Activate all roles of a user.
SET ROLE ALL;

Parameters​

role_name: the role name

Examples​

Query all the roles of the current user.

SHOW GRANTS;
+--------------+---------+----------------------------------------------+
| UserIdentity | Catalog | Grants                                       |
+--------------+---------+----------------------------------------------+
| 'test'@'%'   | NULL    | GRANT 'db_admin', 'user_admin' TO 'test'@'%' |
+--------------+---------+----------------------------------------------+

Activate the db_admin role.

SET ROLE db_admin;

Query active roles of the current user.

SELECT CURRENT_ROLE();
+--------------------+
| CURRENT_ROLE()     |
+--------------------+
| db_admin           |
+--------------------+

References​