Fixed: "ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES" in MySQL
GRANT PROCESS ON inventory.* TO ... failed with ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES. The syntax is fine. Some privileges apply to the whole server, and can't be limited to one database.
What I Was Doing
I was setting up an account for looking at locks, on MySQL 9.7.4 in Docker, and wanted to keep its rights as small as possible by naming one database:
CREATE USER IF NOT EXISTS 'lock_viewer'@'%' IDENTIFIED BY 'choose-a-strong-password';
GRANT PROCESS ON inventory.* TO 'lock_viewer'@'%';
What I Expected
Query OK.
What I Got
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
What I Checked
The table of privileges. MySQL's privileges page gives each one a context, and PROCESS has "Server administration". It also describes it as controlling "access to information about threads executing within the server".
Why It Happens
PROCESS shows what every session on the server is doing, whatever database they're using. Limiting it to one database wouldn't mean anything, so MySQL refuses. The message's "global privileges" are rights that only exist for the whole server.
The Fix
Grant it on *.*, and keep the rest of the account narrow:
GRANT PROCESS ON *.* TO 'lock_viewer'@'%';
*.* here doesn't mean access to every table. PROCESS is the only right it gives, and this account still can't read your data. How to Let a MySQL User See Locks and Transactions has the full set of grants.
After the Fix
Query OK, 0 rows affected (0.001 sec)
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
Reading ON *.* as "all data" | Only the named privilege applies | Check with SHOW GRANTS |
Using GRANT ALL ON *.* to get past it | The account can do anything | Grant only the one you need |
| Trying other server-wide rights on a database | The same error | Use *.* for them |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| The same error for another privilege | It's server-wide too | Check its context on the privileges page |
Access denied; you are not allowed to grant | The granting account lacks it | Use an administrator |
Conclusion
ERROR 1221 means the privilege belongs to the whole server. Grant it on *.*, which gives that one right and no others.
Additional Resources
- Privileges Provided by MySQL — each privilege's context.
- GRANT Statement — the levels.
