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

> 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

> 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.