How to create and manage roles in MySQL or MariaDB

Roles reduce privilege sprawl by grouping database permissions into reusable bundles that can be granted to many accounts at once. Centralizing access rules makes onboarding, offboarding, and audit reviews faster, especially when multiple applications share the same schemas.

In MySQL and MariaDB, a role is a named authorization entity that holds privileges granted with GRANT. Roles are assigned to users or other roles, and privileges become effective only when the role is active for the session.

Role changes take effect immediately for new connections, while existing sessions might require a role activation change to see different effective privileges. Keep role privilege scope tight, prefer schema-level grants over global grants, and treat role delegation (WITH ADMIN OPTION) as an administrative capability.

1. Open a mysql client session using an account permitted to manage users, roles, grants.

   $ mysql -u root -p
   Enter password:
   mysql>

   

2. Create role accounts for shared privilege bundles.

   mysql> CREATE ROLE 'app_read'@'%', 'app_write'@'%';
   Query OK, 0 rows affected (0.01 sec)

3. Grant read-only access on the target schema to the read role.

   mysql> GRANT SELECT, SHOW VIEW ON `appdb`.* TO 'app_read'@'%';
   Query OK, 0 rows affected (0.01 sec)

   Schema-level grants keep privilege scope narrow compared to *.*.

4. Grant write access on the target schema to the write role.

   mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.* TO 'app_write'@'%';
   Query OK, 0 rows affected (0.01 sec)

   Grants on *.* apply to every schema on the server.

5. Create the user account that will receive the roles.

   mysql> CREATE USER 'appuser'@'%' IDENTIFIED BY 'REPLACE_WITH_STRONG_PASSWORD';
   Query OK, 0 rows affected (0.01 sec)

   Use a strong password and restrict the host (avoid '%') for non-test accounts.

6. Grant the roles to the user account.

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

7. Set the default role for the user to activate permissions at login.

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

   Some MariaDB releases use SET DEFAULT ROLE ... FOR instead of TO.

8. Show the effective grants attached to the read role.

   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)

9. Show the roles granted 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)

10. Open a separate mysql session as the user to validate role activation.

    $ mysql -u appuser -p
    Enter password:
    mysql>

    

11. Display the roles active in the current session.

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

12. Activate the write role for the current session.

    mysql> SET ROLE 'app_write'@'%';
    Query OK, 0 rows affected (0.00 sec)

    Use SET ROLE DEFAULT; to return to default roles.

13. Confirm the active roles for the current session.

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

14. Grant role delegation using WITH ADMIN OPTION when role assignment must be delegated.

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

    WITH ADMIN OPTION permits granting the role to other accounts, so treat it like an admin permission.

15. 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.

16. 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.