Mariadb user roles

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;

mariadb_roles


 

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;

mariadb_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;

mariadb_roles

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';

mariadb_roles

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;

mariadb_roles

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.

SIMILAR ARTICLES

MariaDB - ERROR Incorrect definition of table
MariaDB - ERROR Incorrect definition of table
MARIADB
Read more
Mariabackup backup - gzip compression
Mariabackup backup - gzip compression
MARIADB
Read more
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
Mariabackup error - Error writing file UNKNOWN errno 32 Broken pipe
MARIADB
Read more