How to Simplify Permission Management with MariaDB Roles

Grant Role?

Creating roles has been supported by Oracle for a long time, but MySQL 5.7 still does not support it. Only MariaDB 10.0/10.1 and later provide role creation.

Why We Need Roles

In practice many users share the same permissions, e.g., read‑only. Granting each user individually is cumbersome.

Roles let DBA define permission groups once and assign them to users, avoiding per‑user management.

When role permissions change, administrators do not need to modify each user’s privileges.

Roles Overview

Using Roles in MariaDB

Create a role named dbuser.

Grant SELECT, INSERT, UPDATE, DELETE privileges to the dbuser role.

Assign the role to user helei@'%' and set password MANAGER (see image).

Set dbuser as the default role for helei and enable the role.

Because only the helei user has the dbuser role and the role does not have CREATE privilege, attempts to create tables result in a “create command denied” error.

Conclusion

Role creation greatly reduces the complexity of maintaining user permissions, and we look forward to MySQL adding this feature.