Did you know common_schema supports a complete breakdown of all accounts on your database server? It can provide you with the GRANT statements required to set up an account, the REVOKE statements to undo the former, and this can be broken down on a per-object & per-object-type basis. Consider the sql_grants view:
Find who has privileges on a table called t:
select * from common_schema.sql_grants where priv_level_name='table' and object_name='t'\G GRANTEE: 'power1'@'localhost' user: power1 host: localhost priv_level: `mytst`.`t` priv_level_name: table object_schema: mytst object_name: t current_privileges: SELECT IS_GRANTABLE: NO sql_grant: GRANT SELECT ON `mytst`.`t` TO 'power1'@'localhost' sql_revoke: REVOKE SELECT ON `mytst`.`t` FROM 'power1'@'localhost' sql_drop_user: DROP USER 'power1'@'localhost'
or, who has privileges on the test schema:
select * from common_schema.sql_grants where priv_level_name='schema' and object_name='test' \G GRANTEE: 'power1'@'localhost' user: power1 host: localhost priv_level: `test`.* priv_level_name: schema object_schema: NULL object_name: test current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE IS_GRANTABLE: NO sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'power1'@'localhost' sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* FROM 'power1'@'localhost' sql_drop_user: DROP USER 'power1'@'localhost' GRANTEE: 'test'@'localhost' user: test host: localhost priv_level: `test`.* priv_level_name: schema object_schema: NULL object_name: test current_privileges: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE IS_GRANTABLE: NO sql_grant: GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* TO 'test'@'localhost' sql_revoke: REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `test`.* FROM 'test'@'localhost' sql_drop_user: DROP USER 'test'@'localhost'
In the same manner, you can easily SELECT for all grants that are defined per-table, per-schema, … priv_level_name: is any one of ‘user’, ‘schema’, ‘table’, ‘column’, ‘routine’.
The above is a view, which aggregates data from all relevant INFORMATION_SCHEMA tables, normalizing and de-normalizing as necessary.
You might also want to look at sql_show_grants which provides with a no-breakdown GRANT for all accounts. It’s like a SHOW ALL GRANTS which doesn’t exist, plus it’s SELECTable.
Also, thanks to eval(), the above make for executable code. Consider:
Revoke any and all specific grants on private_schema.private_table:
call common_schema.eval("select sql_revoke from sql_grants where object_schema='private_schema' and object_name='private_table'")
It’s been around for quite a while now. We’re using it in production extensively. Try it out!