How to create ROLE in MariaDB

How to create ROLE in MariaDB

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

Syntax:
MariaDB > 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.

MariaDB > Create Role Role_Select; 

Grant Permissions to Role " Role_Select"
MariaDB > GRANT SHOW DATABASES ON *.* to Role_Select;

MariaDB > GRANT ALL ON TechBrothers.* to Role_Select; 

Assign  Role_Select  role to user TB.
MariaDB >  GRANT Role_Select To 'TB'@'localhost';

Grant Role_Select to user TB1.
MariaDB >  GRANT Role_Select To 'TB1'@'localhost'; 

Now once the user TB or TB1 will login to MairaDB, they can use this role. Once the user login , they can check which role they are using by using below statment.
MariaDB > 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.

MariaDB >  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 ont have to set the role everytime login.

MariaDB> set default role Role_Select;


2 comments: