Skip to content

# Get per-object grants via common_schema

MySQL common_schema Security September 29, 2014

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!

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

### Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.