Home / server / Questions / 411214
Accepted
Brad Mace
Asked: 2012-07-26 11:57:43 +0800 CST

Eliminate invalid privileges from MySQL

  • 772

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?

mysql

1 Answers

  1. Best Answer

    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;
    • 1