MySQL - SET DEFAULT ROLE Statement

A Role is a named set of privileges and 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.

And during the session if you execute SET ROLE DEFAULT statement the default role is set to the current user.

SET DEFAULT ROLE Statement

You can set any role as default role to a particular user using the SET DEFAULT ROLE statement. To execute this statement, the current account needs to have CREATE USER or, UPDATE USER privilege.

Syntax

Following is the syntax of the MySQL SET DEFAULT ROLE Statement.

SET DEFAULT ROLE
   {NONE | ALL | role [, role ] ...}
   TO user [, user ] ...

Example

Assume we have created a user and a role using CREATE statements as −

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

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 'newuser'@'localhost';

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

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

Setting default role to none

You can set the default role to none using the NONE clause −

SET DEFAULT ROLE NONE TO 'newuser'@'localhost';

Setting default role to ALL

You can set the default role to all roles granted to the account using the ALL clause −

SET DEFAULT ROLE ALL TO 'newuser'@'localhost';