In MySQL 8.4, create a role per access level, grant privileges to the role, then grant the role to accounts. Granting a role does not activate it, so run SET DEFAULT ROLE as well. Roles are locked by default and cannot log in, which suits them as permission bundles for application accounts.
Create and grant
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
GRANT 'app_read' TO 'report_user'@'10.0.0.%';
GRANT 'app_read', 'app_write' TO 'api_user'@'10.0.0.%';
Privilege grants and role grants cannot be mixed in one statement. Role names default to host % if you omit the host, and the manual says roles cannot be granted to anonymous users.
Activate the roles
A granted role is not active until you say so:
SET DEFAULT ROLE ALL TO 'report_user'@'10.0.0.%', 'api_user'@'10.0.0.%';
Before defaults are set, SELECT CURRENT_ROLE(); returns NONE. Within a session, SET ROLE can narrow the active set, for example SET ROLE NONE;. The variable activate_all_roles_on_login is off by default. When on, all granted roles activate at login whatever SET DEFAULT ROLE says. Keep it off if you want explicit control.
Locked by default
A new role is locked, has no password and uses the default authentication plugin. A locked role cannot authenticate. CREATE USER creates an unlocked account, which is the main difference. That means a stray role cannot become a back door, unless someone unlocks it with ALTER USER.
One change fixes many accounts
Privileges on a role affect every account holding it. Revoking write privileges from app_write makes all of its holders read-only at once. This beats auditing dozens of individual grants.
Related guidance
The MySQL security guidelines tell you to grant no more privileges than necessary and never to grant privileges to all hosts. Audit with SHOW GRANTS and remove excess with REVOKE. See the MySQL hardening checklist.
Quick check
I granted a role but the user sees no privileges. Why?
The role is probably not active. Use SET DEFAULT ROLE for the account, or enable activate_all_roles_on_login if you accept auto activation of all granted roles.
Sources
Last reviewed: 19 October 2026.
