Directly managing the accounts for a MariaDB server that has several users can be a pain. We may have 20 users that need permissions to perform the same actions. At some point in time, the structure of the database may change. We will need to update the permissions for 20 users, probably with 20 GRANT statements. This task is error prone and extremely frustrating.

For this reason, MariaDB 10.0 introduces roles following the SQL:2003 specification. If a set of permissions can be set for a role, instead of a single account, then the role itself can be associated to a set of accounts. Each of these accounts will then be allowed to enable one of the roles it is associated with. From this moment, MariaDB will check the role's permissions to determine whether the user has the right to perform the requested action. If something changes in the database, only the role's permissions will need to be updated.

Roles can be created and dropped with the CREATE ROLE and DROP ROLE statements. While creating a role, it is also possible to choose its administrator. Consider the following example:

MariaDB [(none)]> CREATE ROLE reviewer WITH ADMIN amanda;
Query OK, 0 rows affected (0.02 sec) 
MariaDB [(none)]> DROP ROLE reviewer;
Query OK, 0 rows affected (0.00 sec) 

If the WITH ADMIN clause is not present, the role administrator is the current user. The administrator can use the GRANT and REVOKE statements to associate roles to accounts, or to drop those associations. Note that a role can even be associated to another role. This is useful if we have many roles, some of which share a set of privileges. Instead of individually granting privileges to each role, common privileges can be assigned to another role. Here is a simple example of the usage of the GRANT and REVOKE statements:

MariaDB [(none)]> GRANT reviewer TO amanda, josh, lucy;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [(none)]> REVOKE reviewer FROM u3;
Query OK, 0 rows affected (0.00 sec) 

The GRANT statement can also be used to add permissions to a role. The permissions granted to the role are the ones that the associated user will be able to use. To drop the granted permissions, REVOKE can be used. In both cases, the syntax is exactly the same that is used to grant or revoke account permissions. A simple example is as follows:

MariaDB [(none)]> GRANT UPDATE ON TABLE 'news_db'.'article' TO reviewer; 
Query OK, 0 rows affected (0.03 sec)

Remember that users associated to a role will not automatically use the role when they connect to the server. Instead, they need to explicitly enable the role with SET ROLE. Only one role at a time can be enabled (though if a role is associated to another role, its permissions will be available for the user). The CURRENT_ROLE() function returns an active role. An example is shown as follows:

MariaDB [(none)]> SET ROLE r;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [(none)]> SELECT CURRENT_ROLE();
+----------------+ 
| CURRENT_ROLE() | 
+----------------+ 
| r              | 
+----------------+ 
1 row in set (0.00 sec) 
MariaDB [(none)]> SET ROLE NONE;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [(none)]> SELECT CURRENT_ROLE();
+----------------+ 
| CURRENT_ROLE() | 
+----------------+ 
| NULL           | 
+----------------+ 
1 row in set (0.00 sec) 

The information_schema database contains two tables that store information about roles. The APPLICABLE_ROLES table contains information about roles that can be selected by the current user with SET ROLE, such as to whom these roles are granted to and who can grant these roles. The ENABLED_ROLES table contains the name of the enabled roles and the names of the roles that are granted to that role. If one or more roles are assigned to the role that the user has explicitly selected, those roles will also be shown. Consider the following example:

MariaDB [mysql]> SELECT * FROM information_schema.APPLICABLE_ROLES; 
+----------------+-----------+--------------+ 
| GRANTEE        | ROLE_NAME | IS_GRANTABLE | 
+----------------+-----------+--------------+ 
| josh@localhost | writer    | YES          | 
| writer         | reviewer  | NO           | 
+----------------+-----------+--------------+ 
4 rows in set (0.00 sec) MariaDB [mysql]> SELECT * FROM information_schema.ENABLED_ROLES; 
+-----------+ 
| ROLE_NAME | 
+-----------+ 
| writer    | 
| reviewer  | 
+-----------+ 
2 rows in set (0.00 sec) 

In this case, the writer role can be applied to josh, the current user. Also, the reviewer role is automatically applied to whoever has the writer role enabled. In the ENABLED_ROLES table, josh can see both roles.