Mariadb user roles

Hello colleagues.

In today's article, I want to tell you about such an opportunity as creating roles in the MariaDB database. The usefulness of roles is that you can combine a number of privileges. Roles help larger organizations where typically multiple users have the same privileges, and previously the only way to change privileges for a group of users was to change each user's privileges individually.

Article content:

1. We create roles.
2. List of all roles.
3. Assign a role to a user.
4. Delete roles.

1. We create roles.

The process of creating roles is very simple. Let's create two roles: manager and moderator.

mariadb> CREATE ROLE manager;

mariadb> CREATE ROLE moderator;

2. List of all roles.

After we have created two roles, we can now look at them. All roles are in information_schema.APPLICABLE_ROLES and mysql.roles_mapping tables

mariadb> SELECT * FROM information_schema.APPLICABLE_ROLES;

Field descriptions:

• GRANTEE - Who created the role.
• ROLE_NAME - The name of the role.
• IS_GRANTABLE - The ability to assign a role to someone.
• IS_DEFAULT - Whether is the default role.

mariadb> SELECT * FROM mysql.roles_mapping;

Field descriptions:

• Host - The host of the account that created the role.
• User - The name of the account that created the role.
• Role - The name of the role itself.
• Admin_option - Inheritance.

3. Assign a role to a user.

Once we have created all the roles, we can now assign a role to a user.

mariadb> GRANT manager TO 'ruslan'@'localhost';

As you can see in the screenshot, the ruslan@localhost account now has the manager role.

4. Delete roles.

Removing roles is also very easy.

mariadb> DROP ROLE manager;

After we have deleted the role, this role is automatically deleted from the user.

Thank you all, I hope my article was of some help to you.