Tips and Tricks for Implementing Database Role-Based Access Controls for MariaDB

In a database management system (DBMS), role-based access controls (RBAC), is a restriction on database resources based on a set of pre-defined groups of privileges and has become one of the main methods for advanced access control. Database roles can be created and dropped, as well as have privileges granted to and revoked from them. Roles can be granted to and revoked from individual user accounts. The applicable active roles for an account can be selected from those granted to the account and changed during sessions for that account.

In this blog post, we will cover some tips and tricks on using the database role to manage user privileges and as an advanced access control mechanism for our database access. If you would like to learn about the basics of roles in MySQL and MariaDB, check out this blog post, Database User Management: Managing Roles for MariaDB.

MySQL vs MariaDB Roles

MySQL and MariaDB use two different role mechanisms. In MySQL 8.0 and later, the role is similar to another user, with username and host (‘role1’@’localhost’). Yes, that is the role name, which is practically similar to the standard user-host definition. MySQL stores the role definition just like storing user privileges in the mysql.user system table.

MariaDB had introduced role and access privileges in MariaDB version 10.0.5 (Nov 2013), a good 8 years before MySQL included this feature in MySQL8.0. It follows similar role management in a SQL-compliant database system, more robust and much easier to understand. MariaDB stores the definition in the mysql.user system table flagged with a newly added column called is_role. MySQL stores the role differently, using a user-host combination similar to the standard MySQL user management.

Having said that, role migration between these two DBMSs is now incompatible with each other.

MariaDB Administrative and Backup Roles

MySQL has dynamic privileges, which provide a set of privileges for common administration tasks. For MariaDB, we can set similar things using roles, especially for backup and restore privileges. For MariaDB Backup, since it is a physical backup and requires a different set of privileges, we can create a specific role for it to be assigned to another database user.

Firstly, create a role and assign it with the right privileges:

MariaDB> CREATE ROLE mariadb_backup;
MariaDB> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO mariadb_backup;

We can then create the backup user, grant it with mariadb_backup role and assign the default role:

MariaDB> CREATE USER mariabackup_user1@localhost IDENTIFIED BY 'passw0rdMMM';
MariaDB> GRANT mariadb_backup TO mariabackup_user1@localhost;
MariaDB> SET DEFAULT ROLE mariadb_backup FOR mariabackup_user1@localhost;

For mysqldump or mariadb-dump, the minimal privileges to create a backup can be set as below:

MariaDB> CREATE ROLE mysqldump_backup;
MariaDB> GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO mysqldump_backup;

We can then create the backup user, grant it with the mysqldump_backup role and assign the default role:

MariaDB> CREATE USER dump_user1@localhost IDENTIFIED BY 'p4ss182MMM';
MariaDB> GRANT mysqldump_backup TO dump_user1@localhost;
MariaDB> SET DEFAULT ROLE mysqldump_backup FOR dump_user1@localhost;

For restoration, it commonly requires a different set of privileges, which is a bit:

MariaDB> CREATE ROLE mysqldump_restore;
MariaDB> GRANT SUPER, ALTER, INSERT, CREATE, DROP, LOCK TABLES, REFERENCES, SELECT, CREATE ROUTINE, TRIGGER ON *.* TO mysqldump_restore;

We can then create the restore user, grant it with the mysqldump_restore role, and assign the default role:

MariaDB> CREATE USER restore_user1@localhost IDENTIFIED BY 'p4ss182MMM';
MariaDB> GRANT mysqldump_restore TO restore_user1@localhost;
MariaDB> SET DEFAULT ROLE mysqldump_restore FOR restore_user1@localhost;

By using this trick we can simplify the administrative user creation process by assigning a role with pre-defined privileges. Thus, our GRANT statement can be shortened and easy to understand.

Creating Role Over Role In MariaDB 

We can create another role over an existing role similar to a nested group membership with more fine-grained control over privileges. For example, we could create the following 4 roles:

MariaDB> CREATE ROLE app_developer, app_reader, app_writer, app_structure;

Grant the privileges to manage the schema structure to the app_structure role:

MariaDB> GRANT CREATE, ALTER, DROP, CREATE VIEW, CREATE ROUTINE, INDEX, TRIGGER, REFERENCES ON app.* to app_structure;

Grant the privileges for Data Manipulation Language (DML) to the app_writer role:

MariaDB> GRANT INSERT, DELETE, UPDATE, CREATE TEMPORARY TABLES app.* to app_writer;

Grant the privileges for Data Query Language (DQL) to the app_reader role:

MariaDB> GRANT SELECT, LOCK TABLES, SHOW VIEW app.* to app_reader;

And finally, we can assign all of the above roles to app_developer which should have full control over the schema:

MariaDB> GRANT app_structure TO app_developer;
MariaDB> GRANT app_reader TO app_developer;
MariaDB> GRANT app_writer TO app_developer;

The roles are ready and now we can create a database user with app_developer role:

MariaDB> CREATE USER 'michael'@'192.168.0.%' IDENTIFIED BY 'passw0rdMMMM';
MariaDB> GRANT app_developer TO 'michael'@'192.168.0.%';
MariaDB> GRANT app_reader TO 'michael'@'192.168.0.%';

Since Michael now belongs to the app_deleloper and app_reader roles, we can also assign the lowest privileges as the default role to protect him against unwanted human mistake:

MariaDB> SET DEFAULT ROLE app_reader FOR 'michael'@'192.168.0.%';

The good thing about using a role is you can hide the actual privileges from the database user. Consider the following database user just logged in:

MariaDB> SELECT user();
+----------------------+
| user()               |
+----------------------+
| [email protected] |
+----------------------+

When trying to retrieve the privileges using SHOW GRANTS, Michael would see:

MariaDB> SHOW GRANTS FOR 'michael'@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------+
| Grants for michael@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT `app_developer` TO `michael`@`localhost`                                                                 |
| GRANT USAGE ON *.* TO `michael`@`localhost` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+----------------------------------------------------------------------------------------------------------------+

And when Michael is trying to look up for the app_developer’s privileges, he would see this error:

MariaDB> SHOW GRANTS FOR FOR app_developer;
ERROR 1044 (42000): Access denied for user 'michael'@'localhost' to database 'mysql'

This trick allows the DBAs to exhibit only the logical grouping where the user belongs and nothing more. We can reduce the attack vector from this aspect since the users will have no idea of the actual privileges being assigned to them.

Enforcing Default Role In MariaDB

By enforcing a default role, a database user can be protected at the first layer against accidental human mistakes. For example, consider user Michael which has been granted the app_developer role, where the app_developer role is a superset of app_strucutre, app_writer and app_reader roles, as illustrated below:

Since Michael belongs to the app_deleloper role, we can also set the lowest privilege as the default role to protect him against accidental data modification:

MariaDB> GRANT app_reader TO 'michael'@'192.168.0.%';
MariaDB> SET DEFAULT ROLE app_reader FOR 'michael'@'192.168.0.%';

As for user “michael”, he would see the following once logged in:

MariaDB> SELECT user(),current_role();
+-------------------+----------------+
| user()            | current_role() |
+-------------------+----------------+
| michael@localhost | app_reader     |
+-------------------+----------------+

Its default role is app_reader, which is a read_only privilege for a database called “app”. The current user has the ability to switch between any applicable roles using the SET ROLE feature. As for Michael, he can switch to another role by using the following statement:

MariaDB> SET ROLE app_developer;

At this point, Michael should be able to write to the database ‘app’ since app_developer is a superset of app_writer and app_structure. To check the available roles for the current user, we can query the information_schema.applicable_roles table:

MariaDB> SELECT * FROM information_schema.applicable_roles;
+-------------------+---------------+--------------+------------+
| GRANTEE           | ROLE_NAME     | IS_GRANTABLE | IS_DEFAULT |
+-------------------+---------------+--------------+------------+
| michael@localhost | app_developer | NO           | NO         |
| app_developer     | app_writer    | NO           | NULL       |
| app_developer     | app_reader    | NO           | NULL       |
| app_developer     | app_structure | NO           | NULL       |
| michael@localhost | app_reader    | NO           | YES        |
+-------------------+---------------+--------------+------------+

This way, we are kind of setting a primary role for the user, and the primary role can be the lowest privilege possible for a specific user. The user has to consent about its active role, by switching to another privileged role before executing any risky activity to the database server.

Role Mapping in MariaDB

MariaDB provides a role mapping table called mysql.roles_mapping. The mapping allows us to easily understand the correlation between a user and its roles, and how a role is mapped to another role:

MariaDB> SELECT * FROM mysql.roles_mapping;
+-------------+-------------------+------------------+--------------+
| Host        | User              | Role             | Admin_option |
+-------------+-------------------+------------------+--------------+
| localhost   | root              | app_developer    | Y            |
| localhost   | root              | app_writer       | Y            |
| localhost   | root              | app_reader       | Y            |
| localhost   | root              | app_structure    | Y            |
|             | app_developer     | app_structure    | N            |
|             | app_developer     | app_reader       | N            |
|             | app_developer     | app_writer       | N            |
| 192.168.0.% | michael           | app_developer    | N            |
| localhost   | michael           | app_developer    | N            |
| localhost   | root              | mysqldump_backup | Y            |
| localhost   | dump_user1        | mysqldump_backup | N            |
| localhost   | root              | mariadb_backup   | Y            |
| localhost   | mariabackup_user1 | mariadb_backup   | N            |
+-------------+-------------------+------------------+--------------+

From the above output, we can tell that a User without a Host is basically a role over a role and administrative users (Admin_option = Y) are also being assigned to the created roles automatically. To get the list of created roles, we can query the MySQL user table:

MariaDB> SELECT user FROM mysql.user WHERE is_role = 'Y';
+------------------+
| User             |
+------------------+
| app_developer    |
| app_writer       |
| app_reader       |
| app_structure    |
| mysqldump_backup |
| mariadb_backup   |
+------------------+

Final Thoughts

Using roles can improve database security by providing an additional layer of protection against accidental data modification by the database users. Furthermore, it simplifies the privilege management and maintenance operations for organizations that have many database users.