Roles let you define one reusable privilege set and apply it to multiple database accounts without repeating the same GRANT statements everywhere. That makes application onboarding, access reviews, and privilege cleanup much easier when several users need the same level of schema access.

In MySQL and MariaDB, a role holds privileges while each login still belongs to a specific 'user'@'host' account. You grant privileges to the role, grant the role to users, and activate the role for a session or as the user's default role at login. The examples below use simple role names such as app_read so the same workflow works on both servers.

The role-management syntax is mostly shared, but current MySQL and MariaDB releases still differ in a few places. MySQL treats hostless role names as @'%' internally and uses SET DEFAULT ROLE ... TO, while MariaDB uses hostless role names in output and SET DEFAULT ROLE ... FOR. Assigning one role per GRANT role TO user statement keeps the steps portable across both engines.

Related: How to grant privileges to a MySQL or MariaDB user
Related: How to view permissions for a MySQL or MariaDB user

Example identifiers in steps: schema appdb, roles app_read and app_write, user appuser@'%'.

Steps to create and manage roles in MySQL or MariaDB:

  1. Connect with an administrative account using the mysql or mariadb client. The examples use mysql because it can connect to both servers. 
    $ mysql -u root -p
Enter password:
mysql>

Some Linux installs authenticate the local administrator through the Unix socket, so sudo mysql or sudo mariadb may replace mysql -u root -p.

  1. Create the roles that will hold the shared privileges. 
    mysql> CREATE ROLE app_read, app_write;
Query OK, 0 rows affected (0.01 sec)

Using simple role names keeps the syntax portable. MySQL shows these roles later as app_read@'%' and app_write@'%' because an omitted role host defaults to '%'.

  1. Grant read access to the read-only role. 
    mysql> GRANT SELECT, SHOW VIEW ON `appdb`.* TO app_read;
Query OK, 0 rows affected (0.00 sec)

Grant schema-level privileges such as `appdb`.* instead of *.* whenever the role does not need server-wide access.

  1. Grant write privileges to the write role. 
    mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO app_write;
Query OK, 0 rows affected (0.00 sec)

Adding global privileges or WITH GRANT OPTION to an application role expands the blast radius if that account is compromised.

  1. Create the user account that will receive the roles. 
    mysql> CREATE USER 'appuser'@'%' IDENTIFIED BY 'RolePass123!';
Query OK, 0 rows affected (0.00 sec)

Replace the example password and narrow the host component to a specific host or subnet whenever possible instead of leaving '%' in production.

  1. Grant each role to the user account. 
    mysql> GRANT app_read TO 'appuser'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT app_write TO 'appuser'@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL also accepts a comma-separated role list here, but current MariaDB is safest with one role grant per statement.

  1. Set the default role that should activate when the user signs in. 
    MySQL:
mysql> SET DEFAULT ROLE app_read TO 'appuser'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB:
mysql> SET DEFAULT ROLE app_read FOR 'appuser'@'%';
Query OK, 0 rows affected (0.00 sec)

Use SET DEFAULT ROLE NONE to stop automatic role activation, or SET ROLE DEFAULT inside a session to return to the account's default roles.

  1. Inspect the privileges stored on the role itself. 
    mysql> SHOW GRANTS FOR app_read;
+--------------------------------------------------------+
| Grants for app_read@%                                  |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_read`@`%`                   |
| GRANT SELECT, SHOW VIEW ON `appdb`.* TO `app_read`@`%` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB shows the same role without the host suffix, for example Grants for app_read, but the schema privileges are the same.

  1. Inspect the role grants attached to the user account. 
    mysql> SHOW GRANTS FOR 'appuser'@'%';
+-------------------------------------------------------+
| Grants for appuser@%                                  |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`%`                   |
| GRANT `app_read`@`%`,`app_write`@`%` TO `appuser`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB usually shows one role grant per line and also records the default role as SET DEFAULT ROLE `app_read` FOR `appuser`@`%`.

  1. Sign in as the user and confirm that the default role is active. 
    $ mysql -u appuser -p
Enter password:
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
1 row in set (0.00 sec)

MariaDB returns the same active role as app_read without the host component.

  1. Switch to a different granted role for the current session when you need another privilege set. 
    mysql> SET ROLE app_write;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURRENT_ROLE();
+-----------------+
| CURRENT_ROLE()  |
+-----------------+
| `app_write`@`%` |
+-----------------+
1 row in set (0.00 sec)

Use SET ROLE NONE to disable all granted roles for the current session, or SET ROLE DEFAULT to re-apply the account's default role set.

  1. Delegate role assignment only when another account needs to grant that role to others. 
    mysql> CREATE USER 'delegator'@'%' IDENTIFIED BY 'RolePass123!';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT app_read TO 'delegator'@'%' WITH ADMIN OPTION;
Query OK, 0 rows affected (0.00 sec)

WITH ADMIN OPTION lets that account grant or revoke the role for other users, so treat it like an administrative permission rather than normal application access.

  1. Revoke a role from the user when access is no longer required. 
    mysql> REVOKE app_write FROM 'appuser'@'%';
Query OK, 0 rows affected (0.00 sec)

Role revocation can break application logins if the role provides required privileges.

  1. Drop a role that is no longer needed. 
    mysql> DROP ROLE app_write;
Query OK, 0 rows affected (0.00 sec)

Dropping a role removes it from every account that had it granted.