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

I have privileges floating around in the mysql.* tables pertaining to users, databases and tables which no longer exist. Is there a simple way to find and remove these orphaned entries?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

This really isn't the simple way. But you could get some of what you want by composing a set of outer joins that would compare the privilege tables against information_schema tables. After comparing any privilege table to the schema you could then compare the user table against the privilege tables to find users without any privileges.

The challenging part is that most privilege tables allow you to use the % character as a wildcard. So you could be permitting database_foo_% which would map to database_foo_1, database_foo_blah and so on. Given the the wildcard feature, I doubt there is any completely automated setup. Or at least if there is one it would have to be pretty complex.

For example this will look for entries where There is a database(schema) without privileges, or a set of privileges that don't directly match a database.

select SCHEMA_NAME,Db 
from 
     information_schema.SCHEMATA left join 
     mysql.db on 
     SCHEMATA.SCHEMA_NAME=db.Db
where Db is null
union 
select SCHEMA_NAME,Db 
from 
     information_schema.SCHEMATA right join 
     mysql.db on 
     SCHEMATA.SCHEMA_NAME=db.Db
where SCHEMA_NAME is null;
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.