{"id":3764,"date":"2011-06-22T14:14:45","date_gmt":"2011-06-22T12:14:45","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3764"},"modified":"2011-06-27T13:47:36","modified_gmt":"2011-06-27T11:47:36","slug":"routine_privileges-implementation","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/routine_privileges-implementation","title":{"rendered":"ROUTINE_PRIVILEGES implementation"},"content":{"rendered":"<p>Following up on <a title=\"Link to MySQL security: inconsistencies\" rel=\"bookmark\" href=\"http:\/\/code.openark.org\/blog\/mysql\/mysql-security-inconsistencies\">MySQL security: inconsistencies<\/a>, and on <a href=\"http:\/\/bugs.mysql.com\/bug.php?id=61596\">MySQL bug #61596<\/a>, I was thinking it may take a long time till the non-existent <strong>ROUTINE_PRIVILEGES<\/strong> view is implemented. Here&#8217;s my own implementation of the view.<\/p>\n<p>I&#8217;ve followed the somewhat strange conventions used in the <strong>*_PRIVILEGES<\/strong> tables in <strong>INFORMATION_SCHEMA<\/strong>, where the <strong>IS_GRANTABLE<\/strong> is a separate column, although in <em><del>2nd<\/del> 1st normal form<\/em>.<\/p>\n<p>I present it here as a query, using session variables, rather than a view definition:<!--more--><\/p>\n<blockquote>\n<pre>SELECT STRAIGHT_JOIN\r\n  CONCAT('\\'', User, '\\'@\\'', Host, '\\'') AS GRANTEE,\r\n  NULL AS ROUTINE_CATALOG,\r\n  Db AS ROUTINE_SCHEMA,\r\n  Routine_name AS ROUTINE_NAME,\r\n  Routine_type AS ROUTINE_TYPE,\r\n  UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) AS PRIVILEGE_TYPE,\r\n  IF(grantable_procs_priv.User IS NULL, 'NO', 'YES') AS IS_GRANTABLE\r\nFROM\r\n  mysql.procs_priv\r\n  CROSS JOIN (SELECT @counter := -1) select_init\r\n  CROSS JOIN (\r\n    SELECT\r\n      @counter := @counter+1 AS n\r\n    FROM\r\n      INFORMATION_SCHEMA.COLLATIONS\r\n    LIMIT 5\r\n  ) numbers\r\n  LEFT JOIN (\r\n      SELECT\r\n        DISTINCT User, Host, Db, Routine_name\r\n      FROM\r\n        mysql.procs_priv\r\n      WHERE\r\n         find_in_set('Grant', Proc_priv) &gt; 0\r\n    ) grantable_procs_priv USING (User, Host, Db, Routine_name)\r\nWHERE\r\n  numbers.n BETWEEN 0 AND CHAR_LENGTH(Proc_priv) - CHAR_LENGTH(REPLACE(Proc_priv, ',', ''))\r\n  AND UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) != 'GRANT'\r\nORDER BY\r\n  GRANTEE, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, n\r\n;<\/pre>\n<\/blockquote>\n<p>It takes <strong>2<\/strong> views and a table to make this a VIEW rather than a query.<\/p>\n<p>First teaser: the view which represents this query, along with many other interesting diagnostic views, is to take part in a new open source project I&#8217;m working on.<\/p>\n<h4>[UPDATE]<\/h4>\n<p>Guess I was in a rush to produce the query. Here&#8217;s a shorter, cleaner one:<\/p>\n<blockquote>\n<pre>SELECT\r\n  CONCAT('\\'', User, '\\'@\\'', Host, '\\'') AS GRANTEE,\r\n  NULL AS ROUTINE_CATALOG,\r\n  Db AS ROUTINE_SCHEMA,\r\n  Routine_name AS ROUTINE_NAME,\r\n  Routine_type AS ROUTINE_TYPE,\r\n  UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) AS PRIVILEGE_TYPE,\r\n  IF(find_in_set('Grant', Proc_priv) &gt; 0, 'YES', 'NO') AS IS_GRANTABLE\r\nFROM\r\n  mysql.procs_priv\r\n  CROSS JOIN (\r\n    SELECT\r\n      @counter := @counter+1 AS n\r\n    FROM\r\n      INFORMATION_SCHEMA.COLLATIONS, (SELECT @counter := -1) select_init\r\n    LIMIT 5\r\n  ) numbers\r\nWHERE\r\n  numbers.n BETWEEN 0 AND CHAR_LENGTH(Proc_priv) - CHAR_LENGTH(REPLACE(Proc_priv, ',', ''))\r\n  AND UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) != 'GRANT'\r\nORDER BY\r\n  GRANTEE, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, n\r\n;<\/pre>\n<\/blockquote>\n<p>Example output:<\/p>\n<blockquote>\n<pre>+--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+\r\n| GRANTEE                  | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME               | ROUTINE_TYPE | PRIVILEGE_TYPE | IS_GRANTABLE |\r\n+--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+\r\n| 'other_user'@'localhost' |            NULL | sakila         | film_in_stock              | PROCEDURE    | EXECUTE        | NO           |\r\n| 'other_user'@'localhost' |            NULL | sakila         | film_in_stock              | PROCEDURE    | ALTER ROUTINE  | NO           |\r\n| 'other_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | EXECUTE        | NO           |\r\n| 'other_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | ALTER ROUTINE  | NO           |\r\n| 'other_user'@'localhost' |            NULL | sakila         | inventory_held_by_customer | FUNCTION     | EXECUTE        | NO           |\r\n| 'other_user'@'localhost' |            NULL | sakila         | inventory_held_by_customer | FUNCTION     | ALTER ROUTINE  | NO           |\r\n| 'shlomi'@'127.0.0.1'     |            NULL | sakila         | film_in_stock              | PROCEDURE    | EXECUTE        | YES          |\r\n| 'shlomi'@'127.0.0.1'     |            NULL | sakila         | get_customer_balance       | FUNCTION     | EXECUTE        | NO           |\r\n| 'shlomi'@'127.0.0.1'     |            NULL | sakila         | get_customer_balance       | FUNCTION     | ALTER ROUTINE  | NO           |\r\n| 'world_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | EXECUTE        | YES          |\r\n| 'world_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | ALTER ROUTINE  | YES          |\r\n+--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here&#8217;s my own implementation of the view. I&#8217;ve followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although [&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":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[24,57,16,59,66],"class_list":["post-3764","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-information_schema","tag-open-source","tag-security","tag-stored-routines","tag-views"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-YI","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3764","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=3764"}],"version-history":[{"count":20,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3764\/revisions"}],"predecessor-version":[{"id":3781,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3764\/revisions\/3781"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}