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
- CREATE ROLE: creates a role.
- GRANT: assigns roles to users or other roles.
- ALTER USER: modifies roles.
- SHOW ROLES: show all roles in the system.
- current_role: show roles of the current user.
- is_role_in_session: verifies whether a role (or a nested role) is active in the current session.
- DROP ROLE: drops a role.