Mariadb user roles
Last updated: May 17, 2022
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.
- We create roles.
- List of all roles.
- Assign a role to a user.
- 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;
- 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;
- 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.