MySQL 8 Roles: Granular Privilege Management Instead of Per User GRANTs

1. Why per user GRANTs become problematic over time

In a classic, role free privilege model, every individual privilege gets granted directly to a specific user account, so five equally privileged analysts require the same list of GRANT statements to be run five separate times. As soon as the required privilege set changes, for example because an additional table needs to be exposed for reporting, all five accounts need to be updated individually, which in practice regularly leads to accounts being overlooked and actual privileges drifting apart over time.

That drift is not just a maintenance problem, it is a genuine security risk: a former employee's account that accidentally never got fully disabled, or an account with historically accumulated extra privileges that were never revoked, becomes immediately visible in a role based model through a single central look at role assignments, whereas in a per user GRANT model it only surfaces through laboriously comparing every single account.

2. CREATE ROLE and GRANT TO ROLE: the basic syntax

A role is created with syntax nearly identical to a user account, but differs in that it can never log in directly, it only serves as a container for bundled privileges. Privileges are assigned to the role through the same GRANT syntax used for regular users, with the only difference being that the role name is given as the target instead of a username.

The actual connection between role and user is then established through a further GRANT statement that assigns the role to one or more concrete user accounts. This separation between defining privileges on the role and assigning it to users is the real structural benefit: if the privilege set changes, a single adjustment to the role is enough, and every assigned user automatically inherits the change.

-- Create the role
CREATE ROLE 'analyst_readonly';

-- Grant privileges to the role once
GRANT SELECT ON magento2db.* TO 'analyst_readonly';

-- Assign the role to a concrete user
CREATE USER 'jsmith'@'%' IDENTIFIED BY 'a_secure_password';
GRANT 'analyst_readonly' TO 'jsmith'@'%';

3. The difference between mandatory and optionally activated roles

A role assigned to a user is not automatically active at login by default, it first has to be explicitly activated through SET ROLE, which is convenient for security audits but adds friction in day to day work. For roles that should practically always be active, for example an app user's base privilege on its own database, automatic activation at login can be configured through the activate_all_roles_on_login system variable or through SET DEFAULT ROLE.

Alternatively, a role can be marked mandatory server wide through mandatory_roles, making it implicitly available to every user regardless of an explicit GRANT assignment. This mechanism is particularly suited to organization wide base privileges, for example read access to a central reference table with country codes that genuinely every account needs, regardless of its otherwise specific function.

-- Automatically activate the role at login
ALTER USER 'jsmith'@'%' DEFAULT ROLE 'analyst_readonly';

-- Set a server wide mandatory role (my.cnf or global)
SET PERSIST mandatory_roles = 'basic_reference_reader';

4. Combining roles and building a hierarchy

Roles can be assigned not only to users but also to other roles, allowing hierarchical privilege structures without defining privileges more than once. A role 'app_write' can, for example, inherit all privileges of an already existing role 'app_read' and add write privileges on top, instead of redundantly listing the read privileges again.

This ability to compose roles not only reduces setup effort, it also makes later audits considerably easier, since a user's actually effective privileges can be fully traced through SHOW GRANTS with the USING option, including every individual privilege inherited through chains of roles.

CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON magento2db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON magento2db.* TO 'app_write';
GRANT 'app_read' TO 'app_write';

-- Show a user's effective privileges including roles
SHOW GRANTS FOR 'shopapp'@'%' USING 'app_write';

5. A practical privilege model for a team with three access levels

For a typical team working around a Magento database, splitting into at least three clearly separated roles works well: a read only analyst role with pure SELECT access for reporting and ad hoc analysis, an app user role with the DML privileges actually needed for day to day store operation without any DDL privileges, and a DBA role with full access including schema changes, backup privileges, and user management.

What matters most for practical security is that the app user role explicitly does not include DDL privileges such as CREATE, ALTER, or DROP, even if the application never changes the schema during normal operation. A compromised application account without DDL privileges structurally limits the possible damage to data manipulation, while an account with full access could, in the worst case, endanger the entire database structure.

CREATE ROLE 'readonly_analyst', 'shop_app_user', 'db_admin';

GRANT SELECT ON magento2db.* TO 'readonly_analyst';

GRANT SELECT, INSERT, UPDATE, DELETE
    ON magento2db.* TO 'shop_app_user';

GRANT ALL PRIVILEGES ON magento2db.* TO 'db_admin' WITH GRANT OPTION;
GRANT 'readonly_analyst' TO 'db_admin';

6. Temporary privilege extension without permanent GRANTs

A common use case is a briefly needed privilege extension, for example when an analyst needs additional write access to a specific staging table for a one time investigation. Instead of granting permanent privileges for that, which would then need to be manually removed again afterward, a tightly scoped, additional role can be created that the user only activates for the current session through SET ROLE when actually needed.

Once the session ends, this additional role is automatically inactive again without any administrator having to step in manually, and the user's base privileges stay unchanged at the regular level. This technique limits the window of extended privileges to the actually needed minimum and avoids the typical accumulation of forgotten, never revoked special privileges.

CREATE ROLE 'staging_writer_temp';
GRANT INSERT, UPDATE ON magento2db.staging_import TO 'staging_writer_temp';
GRANT 'staging_writer_temp' TO 'jsmith'@'%';

-- The user activates the role only when needed, for the current session
SET ROLE 'staging_writer_temp';

7. Revoking roles and controlling privilege changes centrally

When a team member leaves the project or moves into a different function, a single REVOKE command is enough to remove that user's role assignment, while the role itself, with all its associated privileges, remains unchanged for every other user. In a per user GRANT model, that would require a full list of individual REVOKE statements, with the corresponding risk of simply forgetting one.

A role can also be removed entirely with DROP ROLE, which immediately strips the corresponding privilege set from every user it was assigned to, without needing to edit each individual user account separately. This central controllability is a substantial operational benefit, especially with frequent team changes or during regular access recertification.

-- Remove only one user's role assignment
REVOKE 'shop_app_user' FROM 'former_employee'@'%';

-- Remove a role including all its privileges for every user entirely
DROP ROLE 'staging_writer_temp';

8. Auditing role assignments and spotting deviations

For regular security audits, the information_schema.applicable_roles view gives a complete overview of which roles have been assigned to which user and whether that assignment is active as a default role at login. This query is straightforward to automate and can be regularly compared against a documented target list to catch deviations early, instead of only noticing them during an actual security incident.

In addition, mysql.role_edges shows the concrete relationships between roles and users, as well as between roles themselves, at the table level, which is particularly useful for more complex, multi level role hierarchies where a plain SHOW GRANTS query per user quickly becomes hard to follow.

SELECT grantee, role_name, is_default, is_mandatory
FROM information_schema.applicable_roles
ORDER BY grantee, role_name;

9. Migrating existing per user GRANTs to roles step by step

An existing, historically grown privilege assignment cannot be safely switched over to roles in a single step without risking accidentally losing or duplicating privileges. The safer path starts by documenting each existing user's current privileges through SHOW GRANTS, identifying common patterns from that, and creating a dedicated role for every recurring pattern whose privileges exactly match the documented state.

Only once these new roles have been additionally assigned to the corresponding users, and a comparison through information_schema.applicable_roles confirms that effective privileges remain unchanged, are the original per user GRANTs removed step by step. This two stage approach with a parallel transition phase minimizes the risk of accidentally losing access that is genuinely needed for ongoing operation during the migration.

10. Summary

11. FAQ: MySQL Roles: Key Takeaways