SET SECONDARY ROLES

Activates all secondary roles for the current session. This means that all secondary roles granted to the user will be active, extending the user's privileges. For more information about the active role and secondary roles, see Active Role & Secondary Roles.

See also: SET ROLE

Syntax

SET SECONDARY ROLES { ALL | NONE }

Examples

This example shows how secondary roles work and how to active/deactivate them.

1. Creating roles as user root.

   First, let's create two roles, admin and analyst:

   CREATE ROLE admin;
   
   CREATE ROLE analyst;
   

2. Granting privileges.

   Next, let's grant some privileges to each role. For example, we'll grant the admin role the ability to create databases, and the analyst role the ability to select from tables:

   GRANT CREATE DATABASE ON *.* TO ROLE admin;
   
   GRANT SELECT ON *.* TO ROLE analyst;
   

3. Creating a user.

   Now, let's create a user:

   CREATE USER 'user1' IDENTIFIED BY 'password';
   

4. Assigning roles.

   Assign both roles to the user:

   GRANT ROLE admin TO 'user1';
   
   GRANT ROLE analyst TO 'user1';
   

5. Setting active role.

   Now, let's log in to TiDB Cloud Lake as user1, the set the active role to analyst.

   SET ROLE analyst;
   

   All secondary roles are activated by default, so we can create a new database:

   CREATE DATABASE my_db;
   

6. Deactivate secondary roles.

The active role analyst does not have the CREATE DATABASE privilege. When all secondary roles are deactivated, creating a new database will fail.

SET SECONDARY ROLES NONE;

CREATE DATABASE my_db2;
error: APIError: ResponseError with 1063: Permission denied: privilege [CreateDatabase] is required on *.* for user 'user1'@'%' with roles [analyst,public]