Homelab
People leave, apps get retired, and old accounts become a risk. Here is how to review every MySQL account, take rights away, lock an account and remove it for good.
Full guide: MySQL User & Permissions
Related guide: Full MySQL users and permissions guide
1. List every account
Review who exists and where they can connect from.
SELECT user, host, account_locked FROM mysql.user ORDER BY user;2. See what one account can do
SHOW GRANTS FOR 'olduser'@'localhost';3. Take away some rights
REVOKE removes rights but keeps the account.
REVOKE DELETE, DROP ON appdb.* FROM 'olduser'@'localhost';4. Lock instead of delete
A safe first step: the account stays but cannot log in.
ALTER USER 'olduser'@'localhost' ACCOUNT LOCK;
-- undo: ALTER USER 'olduser'@'localhost' ACCOUNT UNLOCK;5. Remove the account
When you are sure nothing uses it.
DROP USER 'olduser'@'localhost';6. Verify
The account should be gone, or show account_locked = Y.
SELECT user, host, account_locked FROM mysql.user WHERE user = 'olduser';FAQ
Will dropping a user delete its databases?
No. Databases and tables stay; only the account and its rights are removed.
How often should I review MySQL accounts?
At least quarterly, and every time someone leaves or an app is retired.
Further reading: DigitalOcean: create a new user and grant permissions in MySQL