Clean up MySQL privileges

Question

Is it possible to delete users/privileges who exist but don't have access to a database (excluding users with global privileges)

Also I notice a number of "database-specific privileges" that no longer exist are still assigned to the user even after delete the database?

You can use the data in mysql.* and information_schema.* to find/delete users. But be very careful doing so - there's lots of non-user-related data in there and deleting the wrong record(s) can kill your database. Try mysql.users and information_schema.USER_PRIVILEGES as a starting point.
I have thousand, I thought there might be some clean up script that can automatically delete these.
No. but it shouldn't be too hard to have a query find all the "dangling" records and delete them. This is all due to the info_schem and mysql databases using MyISAM, so there's no referential integrity checks being done - not everyone has InnoDB enabled, so the internal system tables/databases have to be in an engine that's guaranteed to be available, which is myisam.

ThinkCode · Accepted Answer · 2011-08-16 16:41:12Z

Table level privileges :

select * from mysql.tables_priv;

DB level privileges :

select * from mysql.db;

I am sure you know that you have to flush privileges every time you change permissions.

Try this for more info on privileges.

Having said that, as far as my experience goes, you have to cleanup the user privileges as you see fit.

ajreal · Answer 2 · 2011-08-16 16:44:05Z

up vote 1 down vote

you should refer to drop user

select * from mysql.user;

then with the drop user command it will delete privileges
and the entries in db (table) as well

answered Aug 16 '11 at 16:44

ajreal
17510

migrated from stackoverflow.com Aug 16 '11 at 20:41