Database User Management: Managing Roles for MariaDB

It’s always a headache… you need to add a new user role or change some privileges, and you need to assign it one… by… one. This is a regular duty, especially in large organizations, or in a company where you have a complex privilege structure, or even if you have to manage a high number of database users. 

For example, let’s say you need to add the UPDATE privilege to a specific database for all the QA team, if they’re a team of five there’s no problem, but if they’re 50… or 100… that can get hard. Of course, you can always write a script for it, but in this way there is always risk.

In this blog, we’ll see how we can solve this database user management issue by using roles and with specific tips on how to use them with MariaDB.

What is a Role?

In the database world, a role is a group of privileges that can be assigned to one or more users, and a user can have one or more roles assigned to him. To make a comparison, it’s like a group on Linux OS.

If we see the previous example about the UPDATE privilege on the QA team, if we have the QA role created, and all the QA members have this role assigned, it doesn’t matter the number of members, you only need to change the privilege on this QA role and it’ll be propagated for all the QA users.

Roles on MariaDB

To manage roles on MariaDB you must create the role with the CREATE ROLE statement, assign the privilege to that role with a GRANT statement, and then assign the privilege to the user to be able to use this role. You can also set a default role, so the user will take it when connecting.

As a database user, you must set the role when you access the database (if there is not a default role), and you can change the role if needed with a SET ROLE statement.

From the application side, you should be able to set the role (or use the default) before querying to make this work, so in old applications, it could be complex to implement.

Let’s see some specification for Roles on MariaDB.

• Only one role can be active at the same time for the current user.
• Since MariaDB 10.1 we have a Default Role. This role is automatically enabled when the user connects.
• Roles are stored in memory.

How to Check Roles

On MariaDB there are multiple ways to check it:

• SHOW GRANTS [ FOR (user | role) ]: List the grants for the current user or for a specific one.

  MariaDB [testing]> SHOW GRANTS for testuser@'%';
  +----------------------------------------------------------------------------------------------------------+
  | Grants for testuser@%                                                                                   |
  +----------------------------------------------------------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2' |
  +----------------------------------------------------------------------------------------------------------+
  1 row in set (0.000 sec)

• SELECT user FROM mysql.user WHERE is_role=’Y’: List the roles created in the database.

  MariaDB [testing]> SELECT user FROM mysql.user WHERE is_role='Y';
  +--------+
  | user   |
  +--------+
  | qateam |
  +--------+
  1 row in set (0.000 sec)

• SELECT * FROM information_schema.applicable_roles: It’s a list of available roles for the current user.

  MariaDB [testing]> SELECT * FROM information_schema.applicable_roles;
  +-------------+-----------+--------------+------------+
  | GRANTEE     | ROLE_NAME | IS_GRANTABLE | IS_DEFAULT |
  +-------------+-----------+--------------+------------+
  | testuser@%  | qateam    | NO           | NO         |
  +-------------+-----------+--------------+------------+
  1 row in set (0.000 sec)

• SELECT * FROM information_schema.enabled_roles: List the current active roles.

  MariaDB [testing]> SELECT * FROM information_schema.enabled_roles;
  +-----------+
  | ROLE_NAME |
  +-----------+
  | qateam    |
  +-----------+
  1 row in set (0.000 sec)

• SELECT * FROM mysql.roles_mapping: List the relations between roles and user grants.

  MariaDB [testing]> SELECT * FROM mysql.roles_mapping;
  +-----------+-----------+--------+--------------+
  | Host      | User      | Role   | Admin_option |
  +-----------+-----------+--------+--------------+
  | localhost | root      | qateam | Y            |
  | %         | testuser  | qateam | N            |
  +-----------+-----------+--------+--------------+
  2 rows in set (0.000 sec)

How to manage roles on MariaDB

Let’s see an example of how to manage it on MariaDB. In this case, we’ll use MariaDB 10.3 version running on CentOS 7.

First, let’s create a new database user:

MariaDB [testing]> CREATE USER testuser@'%' IDENTIFIED BY 'PASSWORD';

If we check the grants for this new user, we’ll see something like this:

MariaDB [testing]> SHOW GRANTS for testuser@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                   |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2' |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

Now, let’s try to login with this user and connect to the testing database:

$ mysql -utestuser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 54
Server version: 10.3.16-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> use testing
ERROR 1044 (42000): Access denied for user 'testuser'@'%' to database 'testing'

As we could see, we can’t connect to the testing database with this user, so, now, we’ll create a “qateam” role with the privileges and we’ll assign this role to this new user.

MariaDB [testing]> CREATE ROLE qateam;
Query OK, 0 rows affected (0.001 sec)

MariaDB [testing]> GRANT SELECT,INSERT,UPDATE,DELETE ON testing.* TO qateam;
Query OK, 0 rows affected (0.000 sec)

If we try to use this role without the GRANT, we’ll see the following error:

MariaDB [(none)]> SET ROLE qateam;
ERROR 1959 (OP000): Invalid role specification `qateam`

So, now we’ll run the GRANT to allow the user use it:

MariaDB [(none)]> GRANT qateam TO testuser@'%';
Query OK, 0 rows affected (0.000 sec)

Set the role to the current user:

MariaDB [(none)]> SET ROLE qateam;
Query OK, 0 rows affected (0.000 sec)

And try to access the database:

MariaDB [(none)]> use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testing]>

We can check the grants for the current user:

MariaDB [(none)]> SHOW GRANTS for testuser@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                   |
+----------------------------------------------------------------------------------------------------------+
| GRANT qateam TO 'testuser'@'%'                                                                          |
| GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2' |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

And the current role:

MariaDB [testing]> SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| qateam       |
+--------------+
1 row in set (0.000 sec)

Here we can see the grant for the qateam role, and that’s it, we don’t have the privilege assigned directly to the user, we have the privileges for the role, and the user takes the privileges from there.

Conclusion

Managing roles can make our life easier in large companies or databases with a high number of user that access it. If we want to use it from our application, we must take into account the application must be able to manage it too.