# **SET ROLE**

## **Grammar Description**

Sets the activity/current main role of the session. Sets the context for the currently active main role to determine whether the current user has the permissions required to perform the `CREATE <object>` statement or to perform any other SQL operation.

In addition to creating objects, authorization for any SQL operation can be performed by a secondary role.

## **Grammar Structure**

```sql
SET SECONDARY ROLE {
    NONE
  | ALL
}
SET ROLE role
```

### Syntax Description

Roles are a collection of permissions, and a user can correspond to multiple roles.

For example, user1 has the primary role1, the secondary roles role2 and role3, role1 are granted pri1 and pri2 permissions; role2 is granted pri3; role3 is granted pri4, and the authorization example table is as follows:

|Username | Rolename | Permissionname |
|---|---|---|
|user1|role1|pri1,pri2|
||role2|pri3|
||role3|pri4|

For easier understanding, you can refer to the following example:

|User | Role | Permission Name |
|---|---|---|
|Tom|Application Developer|Read Data, Write Data|
||O&M expert | Read data |
||Database Administrator | Administrator Privileges |

At this time, Tom's main role is the application developer. Tom needs to call *administrator permissions*, so Tom can use the following two methods:

—Switch its role to "Database Administrator" using the `SET role role` statement.

—If you need to use all permissions for the master and slave roles, you can use `SET secondary ROLE all`.

These two statements are explained as follows:

#### SET SECONDARY ROLE ALL

Unify all ROLEs of this user.

#### SET SECONDARY ROLE NONE

Removes all roles except PRIMARY ROLE from the current session.

#### SET ROLE role

Switch the current character to the new character.

## **Example**

```sql
drop role if exists use_role_1,use_role_2,use_role_3,use_role_4,use_role_5;
drop user if exists use_user_1,use_user_2;
drop database if exists use_db_1;
create role use_role_1,use_role_2,use_role_3,use_role_4,use_role_5;
create database use_db_1;
create user use_user_1 identified by '123456' default role use_role_1;
-- Authorize the select, insert and update permissions of all tables to use_role_1
grant select ,insert ,update on table *.* to use_role_1;
-- Authorize all permissions of the database to use_role_2
grant all on database * to use_role_2;
-- Assign role use_role_2 to user use_user_1
grant use_role_2 to use_user_1;
-- Create table use_table_1
create table use_db_1.use_table_1(a int,b varchar(20),c double );
-- Set user use_user_1 All major and minor roles are available
set secondary role all;
-- View the permissions that user use_user_1 now has
show grants for 'use_user_1'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Grants for use_user_1@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GRANT select ON table *.* `use_user_1`@`localhost` |
| GRANT insert ON table *.* `use_user_1`@`localhost` |
| GRANT update ON table *.* `use_user_1`@`localhost` |
| GRANT connect ON account `use_user_1`@`localhost` |
| GRANT database all ON database * `use_user_1`@`localhost` |
+------------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.01 sec)
-- As you can see, the user use_user_1 has the default connection to MatrixOne permissions; it also has the select, insert and update permissions for all tables, and also has all permissions for the database.
```