Skip to main content

SET ROLE

Description​

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

After running this command, you can run select is_role_in_session("<role_name>"); to verify whether this role is active in the current session.

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​