Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

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

This question came from our site for professional and enthusiast programmers.

    
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

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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