Manage roles in MariaDB Server for streamlined user access control. This section explains how to create, assign, and manage roles to simplify privilege management and enhance security.
{% hint style="info" %} Important: The PUBLIC role is created implicitly by GRANT statements and its creation is not logged, distinguishing it from standard system principals. {% endhint %}
MariaDB automatically creates several users and roles for administrative and internal server functions.
These user accounts are created by the mariadb-install-db script during the initial server setup.
root@localhostmariadb-sys@localhostThese roles are built into the server and have special behaviors.
PUBLIC RoleThe PUBLIC role is a special, built-in concept that represents every user on the server.
The PUBLIC role is created implicitly by the server the first time a GRANT ... TO PUBLIC statement is executed. It is not created with CREATE ROLE.
It provides a convenient way to grant privileges server-wide without having to grant them to each user individually. Privileges granted to PUBLIC are inherited by all existing and future users.
Because the PUBLIC role is created implicitly, its creation is not written to the audit log or binary log as a standard DDL event. This is a critical detail for security auditing.
The PUBLIC role cannot be explicitly dropped with DROP ROLE.
To audit the privileges that apply to all users, you must check the grants for PUBLIC directly.
Creation
Created automatically by mariadb-install-db.
Purpose
Serves as the primary administrative account for initial server setup and management.
Management
It is highly recommended to secure this account immediately after installation. Standard security practices include setting a strong password, renaming the account, or removing it entirely in favor of other named administrative accounts.
Creation
Created automatically by mariadb-install-db.
Purpose
A mandatory system user required for internal server operations, such as executing scheduled events.
Management
This user account is essential for server functionality and is protected;
it cannot be dropped.
-- Grant a privilege to all users
GRANT SELECT ON my_app.reports TO PUBLIC;
-- View privileges granted to PUBLIC
SHOW GRANTS FOR PUBLIC;
-- Revoke a privilege from all users
REVOKE SELECT ON my_app.reports FROM PUBLIC;A role bundles a number of privileges together. It assists larger organizations where, typically, a number of users would have the same privileges, and, previously, the only way to change the privileges for a group of users was by changing each user's privileges individually.
Alternatively, multiple external users could have been assigned the same user, and there would have been no way to see which actual user was responsible for which action.
With roles, managing this is easy. For example, there could be a number of users assigned to a journalist role, with identical privileges. Changing the privileges for all the journalists is a matter of simply changing the role's privileges, while the individual user is still linked with any changes that take place.
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 statement, while privileges are assigned to a role in the regular way with . Similarly, the statement can be used to both revoke a role from a user, or revoke a privilege from a role.
Once a user has connected, he can obtain all privileges associated with a role by setting a role with the statement. The 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.
The statement allows one to set a default role for a user. A default role is automatically enabled when a user connects (an implicit SET ROLE statement is executed immediately after a connection is established).
Roles were implemented as a GSoC 2013 project by Vicentiu Ciorbaru.
Information about roles and who they've been granted to can be found in the as well as the .
The shows the enabled roles for the current session.
Creating a role and granting a privilege:
Note, that hulda has no SHOW DATABASES privilege, even though she was granted the journalist role. She needs to set the role first:
Roles can be granted to roles:
But one does not need to set a role granted to a role. For example, hulda will automatically get all writer privileges when she sets the journalist role:
When a user sets a role, he, in a sense, has two identities with two associated sets of privileges.
But a view (or a stored routine) can have only one definer. So, when a view (or a stored routine) is created with the SQL SECURITY DEFINER, one can specify whether the definer should be CURRENT_USER (and the view will have none of the privileges of the user's role) or CURRENT_ROLE (in this case, the view will use role's privileges, but none of the user's privileges). As a result, sometimes one can create a view that is impossible to use.
by Peter Gulutzan
This page is licensed: CC BY-SA / Gnu FDL
CREATE ROLE journalist;
GRANT SHOW DATABASES ON *.* TO journalist;
GRANT journalist TO hulda;SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SET ROLE journalist;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| journalist |
+--------------+
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| ... |
| information_schema |
| mysql |
| performance_schema |
| test |
| ... |
+--------------------+
SET ROLE NONE;CREATE ROLE writer;
GRANT SELECT ON data.* TO writer;
GRANT writer TO journalist;SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SHOW TABLES FROM data;
Empty set (0.01 sec)
SET ROLE journalist;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| journalist |
+--------------+
SHOW TABLES FROM data;
+------------------------------+
| Tables_in_data |
+------------------------------+
| set1 |
| ... |
+------------------------------+CREATE ROLE r1;
GRANT ALL ON db1.* TO r1;
GRANT r1 TO foo@localhost;
GRANT ALL ON db.* TO foo@localhost;
SELECT CURRENT_USER
+---------------+
| current_user |
+---------------+
| foo@localhost |
+---------------+
SET ROLE r1;
CREATE TABLE db1.t1 (i INT);
CREATE VIEW db.v1 AS SELECT * FROM db1.t1;
SHOW CREATE VIEW db.v1;
+------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `db`.`v1` AS SELECT `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8 | utf8_general_ci |
+------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
CREATE DEFINER=CURRENT_ROLE VIEW db.v2 AS SELECT * FROM db1.t1;
SHOW CREATE VIEW db.b2;
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v2 | CREATE ALGORITHM=UNDEFINED DEFINER=`r1` SQL SECURITY DEFINER VIEW `db`.`v2` AS select `db1`.`t1`.`a` AS `a` from `db1`.`t1` | utf8 | utf8_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+