{"id":5792,"date":"2013-01-17T10:09:56","date_gmt":"2013-01-17T08:09:56","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5792"},"modified":"2013-01-17T10:19:35","modified_gmt":"2013-01-17T08:19:35","slug":"mysql-security-tasks-easily-solved-with-common_schema","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-security-tasks-easily-solved-with-common_schema","title":{"rendered":"MySQL security tasks easily solved with common_schema"},"content":{"rendered":"<p>Here are three security tasks I handled, which I&#8217;m happy to say were easily solved with <em>common_schema<\/em>&#8216;s views and routines (with no prior planning). Two are so easy, that I actually now integrated them into <a href=\"http:\/\/code.google.com\/p\/common-schema\">common_schema 1.3<\/a>:<\/p>\n<ul>\n<li>Duplicate a user (create new user with same privileges as another&#8217;s)<\/li>\n<li>Find users with identical set of grants (same roles)<\/li>\n<li>Finding redundant users (users who only have privileges on non-existing objects); I was approached on this by <a href=\"http:\/\/sheeri.com\/\">Sheeri K. Cabral<\/a> from Mozilla.<\/li>\n<\/ul>\n<h4>Duplicate user<\/h4>\n<p>How would you duplicate a grantee? That&#8217;s easy! Just get the SHOW GRANTS output, then do text search and replace: replace the existing account (e.g. <strong>&#8216;existing&#8217;@&#8217;localhost&#8217;<\/strong>) with the new account (e.g. <strong>&#8216;newcomer&#8217;@&#8217;localhost&#8217;<\/strong>).<\/p>\n<p>Ahem. And how would you get the output of <strong>SHOW GRANTS<\/strong>? That&#8217;s right: you can&#8217;t do this from within the server. You have to go outside the server, incoke mysql client, <em>sed<\/em> your way into it, then connect to MySQL again to invoke the GRANT query&#8230; Or you can do this by hand, of course, or you can use the new <a href=\"http:\/\/dev.mysql.com\/doc\/workbench\/en\/mysqluserclone.html\">mysqluserclone<\/a> tool from MySQL utilities. Bottom line: you have to go outside the server. You can&#8217;t directly do this with your favorite GUI tool unless it has this function integrated.<\/p>\n<p>But to have a truly automated, scriptable, server-side user-duplication you don&#8217;t need to go far, since the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_show_grants.html\">sql_show_grants<\/a> view simulates a <strong>SHOW GRANTS<\/strong> output, but using plain old SQL. It produces the <strong>GRANT<\/strong> statement as SQL output. Which means you can <strong>REPLACE()<\/strong> the grantee. It&#8217;s actually a one liner, but is such a common operation that I created the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/duplicate_grantee.html\">duplicate_grantee()<\/a> function for convenience. Just:<!--more--><\/p>\n<blockquote>\n<pre>call duplicate_grantee(<strong>'existing@localhost', <\/strong><strong>'newcomer@localhost'<\/strong>);<\/pre>\n<\/blockquote>\n<p>(and yes, using a more relaxed form of grantee, allowing dropping of all these gory quotes).<\/p>\n<h4>Finding similar grantees<\/h4>\n<p>If the grants of an account are available via SQL, I&#8217;m also able to compare it with the grants of another account. I just need to ignore the account&#8217;s name in itself. Since <em>sql_show_grants<\/em> normalizes the grants in expected order, this works well. Again, this is a simple query, but since it is of importance, I created a view for it: just <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/similar_grants.html\"><strong>SELECT * FROM similar_grants<\/strong><\/a> to find out about accounts sharing same set of privileges..<\/p>\n<h4>Drop redundant users<\/h4>\n<p>Sheeri approached me with the following problem (she authorized me to publicize): as she came to work for Mozilla, she inherited the databases to manage. When she reviewed them, she realized there were numerous accounts which were redundant. Redundant how? Well, obviously not being used, but moreover not having privileges on existing objects.<\/p>\n<p>She found out there were many accounts that only had privileges on non-existing schema, something like:<\/p>\n<blockquote>\n<pre>GRANT ALL PRIVILEGES ON <strong>penguin.*<\/strong> TO 'gromit'@'localhost'<\/pre>\n<\/blockquote>\n<p>Where the <strong>penguin<\/strong> schema does not exist anymore &#8211; it was some developer&#8217;s test database. The developer long since not working there, the database long since DROPped.<\/p>\n<p>How do you quickly find and drop such accounts? Should we do crazy parsing on <a href=\"http:\/\/www.percona.com\/doc\/percona-toolkit\/2.1\/pt-show-grants.html\">pt-show-grants<\/a> output? Any other method?<\/p>\n<p>Well, <em>common_schema<\/em> to the rescue: the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_grants.html\">sql_grants<\/a> view breaks the <strong>GRANT<\/strong> statement into domains (in fact, this is not a true explanation; it actually <em>builds<\/em> the <strong>GRANT<\/strong> statements per domain; but it&#8217;s more convenient to view it as <em>breaking<\/em>). You actually get the different <strong>GRANT<\/strong> statement per account per domain: the <strong>GRANT<\/strong> the account has on <strong>*.*<\/strong>, the <strong>GRANT<\/strong> it has on <strong>world.*<\/strong>, the <strong>GRANT<\/strong> it has on <strong>sakila.*<\/strong>, the <strong>GRANT<\/strong> it has on &#8230;.<\/p>\n<p>Now this one is not entirely simple. Let me introduce the solution, then explain it. The way to find &amp; drop all such users is:<\/p>\n<blockquote>\n<pre>call <strong>eval<\/strong>(<span style=\"color: #000080;\">\"SELECT <strong>sql_revoke<\/strong> FROM <strong>sql_grants<\/strong> LEFT JOIN INFORMATION_SCHEMA.SCHEMATA ON(object_name = SCHEMA_NAME) WHERE priv_level_name='schema' AND DEFAULT_CHARACTER_SET_NAME IS NULL\"<\/span>);\r\ncall <strong>eval<\/strong>(<span style=\"color: #000080;\">\"SELECT <strong>sql_drop_user<\/strong> FROM <strong>sql_grants<\/strong> GROUP BY GRANTEE HAVING COUNT(*)=1 AND SUM(current_privileges='USAGE')=1\"<\/span>);<\/pre>\n<\/blockquote>\n<p>Explanation:<\/p>\n<ol>\n<li>The <strong>LEFT JOIN<\/strong> query finds all <strong>GRANT<\/strong>s on non existing schemas.<\/li>\n<li>But we do more than that: we request the <strong>sql_revoke<\/strong> column which negates such <strong>GRANT<\/strong>s. <strong>sql_grants<\/strong> provides such info: the <strong>REVOKE<\/strong> statement for said <strong>GRANT<\/strong>s.<\/li>\n<li>We can actually evaluate the <strong>REVOKE<\/strong> statement via <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/eval.html\"><strong>eval()<\/strong><\/a>. There! These privileges are now gone.<\/li>\n<li>Now that we have revoked privileges on non-existing domains, we should be left with accounts whose only privilege is <strong>USAGE<\/strong>. Any such account is redundant by definition, and should be dropped. The second query looks up such accounts.<\/li>\n<li>And, it asks for the <strong>sql_drop_user<\/strong> statement column for those accounts<\/li>\n<li>Finally, it evaluates the <strong>DROP USER<\/strong> statement via <em>eval()<\/em>.<\/li>\n<\/ol>\n<p>Here is the statements output without executing the <strong>eval()<\/strong>:<\/p>\n<blockquote>\n<pre>root@mysql-5.1.51&gt; SELECT <strong>sql_revoke<\/strong> FROM <strong>sql_grants<\/strong> LEFT JOIN INFORMATION_SCHEMA.SCHEMATA ON(object_name = SCHEMA_NAME) WHERE\u00a0 priv_level_name='schema'\u00a0 AND DEFAULT_CHARACTER_SET_NAME IS NULL;\r\n+-----------------------------------------------------------------+\r\n| sql_revoke\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------------------------------------------------------+\r\n| REVOKE INSERT, SELECT ON `not_here`.* FROM 'gromit'@'localhost' |\r\n+-----------------------------------------------------------------+\r\n\r\nroot@mysql-5.1.51&gt; SELECT <strong>sql_drop_user<\/strong> FROM <strong>sql_grants<\/strong> GROUP BY GRANTEE HAVING COUNT(*)=1 AND SUM(current_privileges='USAGE')=1;\r\n+--------------------------------+\r\n| sql_drop_user\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+--------------------------------+\r\n| DROP USER 'gromit'@'localhost' |\r\n+--------------------------------+<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Here are three security tasks I handled, which I&#8217;m happy to say were easily solved with common_schema&#8216;s views and routines (with no prior planning). Two are so easy, that I actually now integrated them into common_schema 1.3: Duplicate a user (create new user with same privileges as another&#8217;s) Find users with identical set of grants [&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-5792","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-1vq","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5792","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=5792"}],"version-history":[{"count":31,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5792\/revisions"}],"predecessor-version":[{"id":6027,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5792\/revisions\/6027"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5792"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5792"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5792"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}