In our last blog, we had a look at MySQL 8 and mentioned new features available here. However, some of those features are already in MariaDB and have been for a while. We will here compare database roles in both server dialects.
The Purpose of Database Roles
It’s common for numerous users within an organization to share the same database privileges. A role bundles a number of privileges together so that the DBA can set the privileges for a group of users all at once, rather than having to set each user’s privileges individually.
With shared applications, it is not uncommon for multiple users to share the same user account. The drawback to this arrangement is that there is no way to see which actual user was responsible for which action.
Roles make managing this much easier. For example, there could be a number of users assigned to an Interviewer role, with identical privileges. Changing the privileges for all the Interviewers is a matter of simply changing the role’s privileges. Meanwhile, the individual user is still linked with any actions that they perform within the database.
Fixed vs. Flexible Roles
There are two kinds of database roles: fixed and flexible.
Fixed roles are those which automatically exist in a database. Adding a user to one of these roles will not change that user’s permissions in any other database.
Any user or role can be added to a database role. Once a user has been added to a role, they may also be able to add other users or roles to that role, depending on the vendor.
Flexible database roles are those that you create yourself in the database. When you start with a new database, there are no flexible roles, just any defined fixed roles. The upside of this is that you are free to create all of the roles that you need and grant all of the permissions that you desire to these roles.
It’s very important to be careful when adding flexible roles to a fixed role as you could very easily elevate privileges for a large number of users in one fell swoop!
Roles in MariaDB
MariaDB does not come with fixed roles, but has supported flexible user roles since version 10.0.5. They were implemented almost exactly as specified in the SQL Standard 2003, features T331 “Basic roles” and T332 “Extended Roles”, so that role management follows a similar process as with many other popular DBMSes:
- Roles are created with the CREATE ROLE statement and dropped with the DROP ROLE statement.
- Roles are then assigned to a user with an extension to the GRANT statement, while privileges are assigned to a role in the regular way with GRANT. Similarly, the REVOKE statement can be used to both revoke a role from a user or to revoke a privilege from a role.
- Once a user has connected, he/she can obtain all privileges associated with a role by setting a role with the SET ROLE statement. The CURRENT_ROLE() function returns the currently set role for the session if any.
- Only roles granted directly to a user can be set, roles granted to other roles cannot. Instead, the privileges granted to a role, which is, in turn, granted to another role (grantee), will be immediately available to any user who sets this second grantee role.
Examples
Creating a role and granting a privilege:
CREATE ROLE interviewer; GRANT SHOW DATABASES ON *.* TO interviewer; GRANT interviewer to tom_m;
Note, that tom_m has no SHOW DATABASES privilege, even though he was granted the interviewer role. He needs to set the role first:
SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | NULL | +--------------+ SET ROLE interviewer; SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | interviewer | +--------------+ SHOW DATABASES; +--------------------+ | Database | +--------------------+ | ... | | information_schema | | mysql | | performance_schema | | test | | ... | +--------------------+ -- removes all roles SET ROLE NONE;
Roles can also be granted to roles:
CREATE ROLE trainer; GRANT SELECT ON data.* TO trainer; GRANT trainer TO interviewer;
But one does not need to set a role granted to a role. For example, tom_m will automatically inherit all trainer privileges when he sets the interviewer role:
SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | NULL | +--------------+ SHOW TABLES FROM data; Empty set (0.01 sec) SET ROLE interviewer; SELECT CURRENT_ROLE; +--------------+ | CURRENT_ROLE | +--------------+ | interviewer | +--------------+ SHOW TABLES FROM data; +------------------------------+ | Tables_in_data | +------------------------------+ | set1 | | ... | +------------------------------+
Default Roles
The original MariaDB roles implementation specified that one had to explicitly set a role using the SET ROLE statement. This was not always convenient and sometimes not even possible, such as in the case of accounts used by applications.
To solve this issue MariaDB introduced the concept of a default role. A default role for given user is automatically enabled when a user connects.
To set a default role you use the SET DEFAULT ROLE command:
SET DEFAULT ROLE interviewer;
This stores your default role in the mysq.user table, and next time you connect the interviewer role will be enabled for you automatically.
You can also set a default role for another user:
SET DEFAULT ROLE interviewer FOR user@host;
Similar to the standard SET ROLE statement, you can remove a default role at any time using:
SET DEFAULT ROLE NONE;
Roles in MySQL
MySQL before 8.0 did not support roles. However, Oracle has for some years implemented “sort-of roles support” in their GUI client MySQL Workbench. It defines a number of Fixed roles available from within Workbench (and no other clients obviously, as it is not implemented server-side).
MySQL now supports flexible roles, which can be created and dropped, as well as have privileges granted to and revoked from them. Moreover, roles can be granted to and revoked from individual user accounts. The active applicable roles for an account can be selected from among those granted to the account, and can be changed during sessions for that account.
Creating Roles and Granting Privileges to Them
Consider the scenario where an application uses a database named app_db. Associated with the application, there can be accounts:
- for developers who create and maintain the application, and
- for users who interact with it.
There are usually three types of access needed:
- Developers need full access to the database.
- Some users need only read access,
- while others need read/write access.
To avoid having to grant privileges individually to many user accounts, we can create roles as names for the required sets of privileges. This makes it easy to grant the required privileges to different user accounts, by granting the appropriate roles.
Roles can be created using the CREATE ROLE command:
CREATE ROLE 'app_developer', 'app_read', 'app_write';
Role names are much like user account names and consist of a user part and host part in 'user_name'@'host_name' format. The host part, if omitted, defaults to “%”. The user and host parts can be unquoted unless they contain special characters such as “–” or “%”.
To assign privileges to the roles, we would execute the GRANT statement using the same syntax as for assigning privileges to user accounts:
GRANT ALL ON app_db.* TO 'app_developer'; GRANT SELECT ON app_db.* TO 'app_read'; GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
Here is how we would create one developer account, two read-only accounts, and one read/write account using CREATE USER:
CREATE USER 'dev_acc_1'@'localhost' IDENTIFIED BY 'dev_acc_1_pw'; CREATE USER 'read_acc_1'@'localhost' IDENTIFIED BY 'read_acc_1_pw'; CREATE USER 'read_acc_2'@'localhost' IDENTIFIED BY 'read_acc_2_pw'; CREATE USER 'rw_acc_1'@'localhost' IDENTIFIED BY 'rw_acc_1_pw';
We could assign each user account its required privileges using GRANT statements as we did earlier, but that would require enumerating individual privileges for each user. It’s more efficient to use an alternative GRANT syntax that permits granting roles rather than privileges:
GRANT 'app_developer' TO 'dev_acc_1'@'localhost'; GRANT 'app_read' TO 'read_acc_1'@'localhost', 'read_acc_2'@'localhost'; GRANT 'app_read', 'app_write' TO 'rw_acc_1'@'localhost';
Mandatory Roles
In the 8.0.2 dev release, the MySQL team introduced the mandatory roles extension, making it possible to specify roles as mandatory by naming them in the value of the mandatory_roles system variable. A mandatory role is granted to all users, so that it need not be granted explicitly to any account.
Mandatory roles may be set either at server startup or at runtime. This variable can be set with a list of roles:
- To specify mandatory roles at server startup, define mandatory_roles in your server my.cnf file:
[mysqld] mandatory_roles='role1,role2@localhost,r3@%.acme.com'
- To set and persist mandatory_roles at runtime, use a statement such as this:
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.acme.com'
Once set, the roles in the list will be added to the privileges of all users, including future ones.
Here’s an example of how this feature could be utilized:
CREATE ROLE post_reader; GRANT SELECT ON company.* TO post_reader; SET PERSIST mandatory_roles = post_reader;
The above statements set the post_reader role as a mandatory role at runtime.
Distinguishing between Roles and Regular Users in the mysql.user Table
As reported by Peter Laursen on the MySQL site:
When a role is created, a row is added to the mysql.user table. But the row is not clearly distinguishable from a row describing a plain user.
The only difference I see is the value of the account_locked column. It is “Y” for the View and “N” for the Table. I don’t know if this can reliably be used for distinguishing roles and plain users. I don’t find any help in documentation.
Being unable to distinguish between roles and plain users does not just affect DBAs; it also confuses the database server!
CREATE role someone; CREATE USER someone; -- Error Code: 1396: Operation CREATE USER failed for 'someone'@'%' DROP USER someone; -- success. Erhh??? The role was dropped as shown by SELECT COUNT(*) FROM mysql.user WHERE `host` = '%'; -- returns "0"
Laursen suggests the following two fixes:
Either
- don’t add roles to mysql.user but to a new table named mysql.role,
- add a new column to mysql.user named `ìs_role`. (Also possible with MariaDB)
Conclusion
With the release of version 8, MySQL has made great strides in closing the gap in user roles support as specified in the SQL Standard 2003 and implemented in popular DBMSes such as MariaDB. In future blogs, we’ll perform similar comparisons with respect to Window Functions and Common Table Expressions.
