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.

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

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.

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

In this article
Copyright © 2024 CelerData