{"id":7007,"date":"2014-09-29T13:30:09","date_gmt":"2014-09-29T11:30:09","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7007"},"modified":"2014-09-29T13:30:09","modified_gmt":"2014-09-29T11:30:09","slug":"get-per-object-grants-via-common_schema","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/get-per-object-grants-via-common_schema","title":{"rendered":"Get per-object grants via common_schema"},"content":{"rendered":"<p>Did you know <a href=\"http:\/\/code.google.com\/p\/common-schema\">common_schema<\/a> supports a complete breakdown of all accounts on your database server? It can provide you with the <strong>GRANT<\/strong> statements required to set up an account, the <strong>REVOKE<\/strong> statements to undo the former, and this can be broken down on a per-object &amp; per-object-type basis. Consider the <a href=\"https:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_grants.html\">sql_grants<\/a> view:<\/p>\n<p><em>Find who has privileges on a table called <strong>t<\/strong><\/em>:<\/p>\n<blockquote>\n<pre>select * from <strong>common_schema.sql_grants<\/strong> where <strong>priv_level_name<\/strong>='table' and <strong>object_name<\/strong>='t'\\G\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANTEE: 'power1'@'localhost'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 user: power1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 host: localhost\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 priv_level: `mytst`.`t`\r\n\u00a0\u00a0 priv_level_name: table\r\n\u00a0\u00a0\u00a0\u00a0 object_schema: mytst\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name: t\r\ncurrent_privileges: SELECT\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IS_GRANTABLE: NO\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sql_grant: GRANT SELECT ON `mytst`.`t` TO 'power1'@'localhost'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sql_revoke: REVOKE SELECT ON `mytst`.`t` FROM 'power1'@'localhost'\r\n\u00a0\u00a0\u00a0\u00a0 sql_drop_user: DROP USER 'power1'@'localhost'<\/pre>\n<\/blockquote>\n<p>or, <em>who has privileges on the <strong>test<\/strong> schema<\/em>:<\/p>\n<blockquote>\n<pre>select * from common_schema.sql_grants where priv_level_name='schema' and object_name='test' \\G\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANTEE: 'power1'@'localhost'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 user: power1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 host: localhost\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 priv_level: `test`.*\r\n\u00a0\u00a0 priv_level_name: schema\r\n\u00a0\u00a0\u00a0\u00a0 object_schema: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name: test\r\ncurrent_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\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IS_GRANTABLE: NO\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 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'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 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'\r\n\u00a0\u00a0\u00a0\u00a0 sql_drop_user: DROP USER 'power1'@'localhost'\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GRANTEE: 'test'@'localhost'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 user: test\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 host: localhost\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 priv_level: `test`.*\r\n\u00a0\u00a0 priv_level_name: schema\r\n\u00a0\u00a0\u00a0\u00a0 object_schema: NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 object_name: test\r\ncurrent_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\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IS_GRANTABLE: NO\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 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'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 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'\r\n\u00a0\u00a0\u00a0\u00a0 sql_drop_user: DROP USER 'test'@'localhost'\r\n\r\n<\/pre>\n<\/blockquote>\n<p>In the same manner, you can easily SELECT for all grants that are defined per-table, per-schema, &#8230; <strong>priv_level_name<\/strong>: is any one of <strong>&#8216;user&#8217;<\/strong>, <strong>&#8216;schema&#8217;<\/strong>, <strong>&#8216;table&#8217;<\/strong>, <strong>&#8216;column&#8217;<\/strong>, <strong>&#8216;routine&#8217;<\/strong>.<\/p>\n<p>The above is a view, which aggregates data from all relevant <strong>INFORMATION_SCHEMA<\/strong> tables, normalizing and de-normalizing as necessary.<\/p>\n<p>You might also want to look at <a href=\"https:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_show_grants.html\">sql_show_grants<\/a> which provides with a no-breakdown GRANT for all accounts. It&#8217;s like a SHOW ALL GRANTS which doesn&#8217;t exist, plus it&#8217;s SELECTable.<\/p>\n<p>Also, thanks to <a href=\"https:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/eval.html\">eval()<\/a>, the above make for executable code. Consider:<!--more--><\/p>\n<p><em>Revoke any and all specific grants on private_schema.private_table:<\/em><\/p>\n<blockquote>\n<pre>call <strong>common_schema.eval<\/strong>(\"<strong><span style=\"color: #808000;\">select sql_revoke from sql_grants where object_schema='private_schema' and object_name='private_table'<\/span><\/strong>\")<\/pre>\n<p>&nbsp;<\/p><\/blockquote>\n<p>It&#8217;s been around for quite a while now. We&#8217;re using it in production extensively. Try it out!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &amp; per-object-type basis. Consider the sql_grants view: Find who has [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,16],"class_list":["post-7007","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1P1","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7007","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=7007"}],"version-history":[{"count":6,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7007\/revisions"}],"predecessor-version":[{"id":7013,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7007\/revisions\/7013"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}