Remove Obsolete Privileges in MySQL
Last Updated on: October 11, 2022
Security is an essential consideration for any computer system and MySQL is no exception to this rule.
One big security problem that administrators can often overlook is obsolete accounts. These accounts were created for people who no longer use them and are big security holes waiting to be found.
The user table stores user accounts, and it is good practice to check this table regularly and disable or delete any accounts that are no longer required.
The DROP USER statement deletes user accounts, but you need to have global CREATE USER privilege or DELETE privilege for the MySQL database. Since version 5.0.2, DROP USER will remove any privileges associated with the user and delete the account.
This example will remove an account:
DROP USER ‘dave’@’localhost’;
Not only do you have to worry about users, but if you delete any other object, then any privileges assigned to it with a GRANT statement are not automatically deleted.
One way around this is the INFORMATION_SCHEMA tables that can help you to identify obsolete privileges.
This query is a great example that finds privileges that reference databases that no longer exist:
SELECT d.Host, d.Db, d.User FROM mysql.db as d LEFT OUT JOIN INFORMATION_SCHEMA.SCHEMATA AS s ON s.SCHEMA_NAME LIKE d.Db WHERE s.SCHEME_NAME IS NULL;
Good security practice would be to check user accounts and the INFORMATION_SCHEMA tables regularly, and remove any unwanted user accounts and obsolete objects and privileges.
Get notified of new posts: