MySQL - SET ROLE Statement

---

---

MySQL SET ROLE Statement

A Role is a named set of privileges, you can specify the active ones among the granted roles of the current MySQL user using the SET ROLE Statement.

Syntax

Following is the syntax of the MySQL SET ROLE Statement −

SET ROLE {
   DEFAULT
   | NONE
   | ALL
   | ALL EXCEPT role_name1, role_name2, role_name3 . . . . .
   | role_name1, role_name2, role_name3 . . . . .
}

you can just specify the role(s) to be activated. If you want to activate multiple roles separate them with commas.

Example

Assume we have created a role using CREATE statements as −

CREATE ROLE 'TestRole';

Now, let’s grant read only privilege to the created role using the GRANT statement as −

GRANT SELECT ON * . * TO 'TestRole';

Then, you can GRANT the created role to the user as follows −

GRANT 'TestRole' TO CURRENT_USER;

Following query activates the 'TestRole' role −

SET ROLE 'TestRole';

You can use different role specifiers with this statement they are discussed below −

SET ROLE DEFAULT statement

You can set a role as default using the SET DEFAULT ROLE. Statement. If you set any role as default to a particular MYSQL user, soon you connect to server with as this user, the role that is set as default is assigned to the user. During the session if you execute SET ROLE DEFAULT statement the default role is set to the current user.

You can activate all the roles that are marked as default, using the SET ROLE DEFAULT statement.

Example

Assume we have created a role using CREATE statements as −

CREATE ROLE 'TestRole_ReadOnly';

Now, let’s grant read only privilege to the created role using the GRANT statement as −

GRANT SELECT ON * . * TO 'TestRole_ReadOnly';

Then, you can GRANT the created role to the user as follows −

GRANT 'TestRole_ReadOnly' TO CURRENT_USER;

Following query sets the above created role 'TestRole_ReadOnly' as the default role to the above created user −

SET DEFAULT ROLE 'TestRole_ReadOnly' TO 'root'@'localhost';

Now, you can activate the default role as −

SET ROLE DEFAULT;

The NONE and ALL role specifiers

If you set active roles to none, all the roles granted to the current user are deactivated. Following statement sets the active roles to none −

SET ROLE NONE;

If you set active roles to ALL, all the roles granted to the current user are activated. Following statement sets activates all the roles granted to the current user −

SET ROLE ALL;

The ALL Except specifier

You can activate only selected roles using this specifier. You need to specify the roles that should be deactivated separated by commas.

Example

Assume we have created multiple roles as shown below −

CREATE ROLE 'MyAdmin', 'MyDeveloper', 'demouser';

Let us grant privileges to these users −

GRANT ALL ON * . * TO 'MyAdmin';
GRANT INSERT, UPDATE ON * . * TO 'MyDeveloper';
GRANT SELECT ON * . * TO 'demouser';

Following query activates all roles except 'MyAdmin −

SET ROLE ALL EXCEPT 'MyAdmin';