MySQL 8.0 vs. MariaDB: Comparison of Database Roles
MySQL 8.0 vs. MariaDB: Comparison of Database Roles
With the release of v8, MySQL has made great strides in closing the gap in user roles support as specified in SQL Standard 2003 and implemented in DBMSes such as MariaDB.
Join the DZone community and get the full member experience.Join For Free
Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.
In our last article, we had a look at MySQL 8 and mentioned new features available with it. 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 simply a matter of 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 that 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 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 had supported flexible user roles since version 10.0.5. They were implemented almost exactly as specified in SQL Standard 2003 with T331 basic roles and T332 extended roles, so role management follows a similar process as with many other popular DBMSes:
- Roles are created with the
CREATE ROLEstatement and dropped with the
- Roles are then assigned to a user with an extension to the
GRANTstatement, while privileges are assigned to a role in a regular way with
GRANT. Similarly, the
REVOKEstatement can be used to both revoke a role from a user or to revoke a privilege from a role.
- Once a user has connected, they can obtain all privileges associated with a role by setting a role with the
SET ROLEstatement. 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.
Creating a role and granting a privilege:
CREATE ROLE interviewer; GRANT SHOW DATABASES ON *.* TO interviewer; GRANT interviewer to tom_m;
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 | | ... | +------------------------------+
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 a scenario in which an application uses a database named app_db. Associated with the application, there can be accounts:
- For developers who create and maintain the application.
- 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.
- Other users 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
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 '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';
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_rolessystem 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_rolesin your server my.cnf file:
- To set and persist
mandatory_rolesat 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:
- Add roles not to mysql.user but to a new table named mysql.role.
- Add a new column to mysql.user named
ìs_role(MariaDB does exactly this, BTW).
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 articles, we’ll perform similar comparisons with respect to window functions and common table expressions.
Published at DZone with permission of Shree Nair . See the original article here.
Opinions expressed by DZone contributors are their own.