Troubleshooting·MySQL
MySQL Roles and Default Roles
A role is granted, the account still cannot read the schema, and SET ROLE in the same command does not help - because the database is selected before the role is activated. SET DEFAULT ROLE is the fix, and nothing in the error says so.
Users and Privileges Guide 19 of 45 Intermediate
- OSUbuntu 26.04 LTS
- MySQL8.4.10-0ubuntu0.26.04.1
- Authcaching_sha2_password
- TimeAbout 16 min
Roles are MySQL 8.0+. On 5.7 the equivalent is granting the same privileges to every account individually, which is what roles exist to stop.
| Server Name | IP Address | OS | Roles | CPU | RAM | HDD |
|---|---|---|---|---|---|---|
| db-a01 | 192.168.0.81 | Ubuntu 26.04 LTS | Primary / Source / Replica Set Member 1 | 2 Core | 4 GB | 50 GB |
This guide includes
Use this when privileges should be managed once and reused. This matters because granting a role is not enough on its own - the account still cannot read anything until a role is activated, and it fails twice before that lands.
- creating a role, which is an account that cannot log in
- granting it to an account, and reading the grants back
- trying to use it and failing twice, which is the step to slow down on
- making the role activate on connection with
SET DEFAULT ROLE
Before you start
- The
appuseraccount from the foundations track.
-
Create a role and put privileges on it
-
Grant the role to an account and read the grants
-
Try to use it, and fail twice
-
Make the role activate on connection