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. – Marc B Aug 16 '11 at 16:31
  • I have thousand, I thought there might be some clean up script that can automatically delete these. – John Magnolia Aug 16 '11 at 16:36
  • 1
    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. – Marc B Aug 16 '11 at 16:39

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.