How to create and Drop Role in MySQL Server - MySQL DBA Tutorial

What is Role in MySQL, How to create Role and How to drop Role in MySQL Server

Roles in MySQL Server really helps to group the permissions to single object ( Role) and then you can assign role to user instead of you create each user individually and then grant permissions.

Syntax:

MySQL> Create ROLE ROLE_Name;

Example : 
Let's say we want to create Select role with  name "Role_Select" and then Grant Show databases and Select permission on all the tables in TechBrothers Database. Once the role is created we would like to assign user TB and TB1 to it.

MySQL> Create Role Role_Select; 

Grant Permissions to Role " Role_Select"

MySQL> GRANT SHOW DATABASES ON *.* to Role_Select;

MariaDB > GRANT ALL ON TechBrothers.* to Role_Select; 

Assign  Role_Select  role to user TB.

MySQL>  GRANT Role_Select To 'TB'@'localhost';

Grant Role_Select to user TB1.

MySQL>  GRANT Role_Select To 'TB1'@'localhost'; 

Now once the user TB or TB1 will login to MySQL Server, they can use this role. Once the user login , they can check which role they are using by using below statement.

MySQL> Select Current_Role; 

First time you are going to get below output.

+--------------+
| Current_Role |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

If user TB or TB1 will try to select the data from TechBrothers database or run show databases statement, they will get permission denied error. They have to set the role first. Below statement can be used to set the role.

MySQL>  set Role Role_Select()

Now they should be able to use all the object on wich Role_Select has permission.
User can also set the default Role by using below statement so he/she does not have to set the role every time login. Root or DBA will be setting the default role for user.

MySQL> set default role Role_Select;

How to Drop Role in MySQL Server :

In MySQL server, you can use below statement to drop the role.

Syntax:

MySQL> Drop Role RoleName;

Example: 
Let's say if we have a role with name "Role_Select" and we want to drop, we can use below statement.

MySQL > Drop Role Role_Select;