Remove Obsolete Privileges

by jeremyjones on March 15, 2011

  • Share
  • CevherShare
  • Share

Security is an important consideration for any computer systems 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 just waiting to be found.

The user table stores user accounts and it is good practice to check this table on a regular basis and disable or delete any accounts that are no longer required. The DROP USER statement deletes user accounts, but you will 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:

mysql> 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 HULL;

Good security practice would be to check user accounts and the INFORMATION_SCHEMA tables on a regular basis, and remove any unwanted user accounts and obsolete objects and privileges.

Leave a Comment

Previous post:

Next post: