ROUTINE_PRIVILEGES implementation

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’s my own implementation of the view.

I’ve followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although in 2nd 1st normal form.

I present it here as a query, using session variables, rather than a view definition:

SELECT STRAIGHT_JOIN
  CONCAT('\'', User, '\'@\'', Host, '\'') AS GRANTEE,
  NULL AS ROUTINE_CATALOG,
  Db AS ROUTINE_SCHEMA,
  Routine_name AS ROUTINE_NAME,
  Routine_type AS ROUTINE_TYPE,
  UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) AS PRIVILEGE_TYPE,
  IF(grantable_procs_priv.User IS NULL, 'NO', 'YES') AS IS_GRANTABLE
FROM
  mysql.procs_priv
  CROSS JOIN (SELECT @counter := -1) select_init
  CROSS JOIN (
    SELECT
      @counter := @counter+1 AS n
    FROM
      INFORMATION_SCHEMA.COLLATIONS
    LIMIT 5
  ) numbers
  LEFT JOIN (
      SELECT
        DISTINCT User, Host, Db, Routine_name
      FROM
        mysql.procs_priv
      WHERE
         find_in_set('Grant', Proc_priv) > 0
    ) grantable_procs_priv USING (User, Host, Db, Routine_name)
WHERE
  numbers.n BETWEEN 0 AND CHAR_LENGTH(Proc_priv) - CHAR_LENGTH(REPLACE(Proc_priv, ',', ''))
  AND UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) != 'GRANT'
ORDER BY
  GRANTEE, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, n
;

It takes 2 views and a table to make this a VIEW rather than a query.

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’m working on.

[UPDATE]

Guess I was in a rush to produce the query. Here’s a shorter, cleaner one:

SELECT
  CONCAT('\'', User, '\'@\'', Host, '\'') AS GRANTEE,
  NULL AS ROUTINE_CATALOG,
  Db AS ROUTINE_SCHEMA,
  Routine_name AS ROUTINE_NAME,
  Routine_type AS ROUTINE_TYPE,
  UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) AS PRIVILEGE_TYPE,
  IF(find_in_set('Grant', Proc_priv) > 0, 'YES', 'NO') AS IS_GRANTABLE
FROM
  mysql.procs_priv
  CROSS JOIN (
    SELECT
      @counter := @counter+1 AS n
    FROM
      INFORMATION_SCHEMA.COLLATIONS, (SELECT @counter := -1) select_init
    LIMIT 5
  ) numbers
WHERE
  numbers.n BETWEEN 0 AND CHAR_LENGTH(Proc_priv) - CHAR_LENGTH(REPLACE(Proc_priv, ',', ''))
  AND UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(Proc_priv, ',', n+1), ',', -1)) != 'GRANT'
ORDER BY
  GRANTEE, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, n
;

Example output:

+--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+
| GRANTEE                  | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME               | ROUTINE_TYPE | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+
| 'other_user'@'localhost' |            NULL | sakila         | film_in_stock              | PROCEDURE    | EXECUTE        | NO           |
| 'other_user'@'localhost' |            NULL | sakila         | film_in_stock              | PROCEDURE    | ALTER ROUTINE  | NO           |
| 'other_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | EXECUTE        | NO           |
| 'other_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | ALTER ROUTINE  | NO           |
| 'other_user'@'localhost' |            NULL | sakila         | inventory_held_by_customer | FUNCTION     | EXECUTE        | NO           |
| 'other_user'@'localhost' |            NULL | sakila         | inventory_held_by_customer | FUNCTION     | ALTER ROUTINE  | NO           |
| 'shlomi'@'127.0.0.1'     |            NULL | sakila         | film_in_stock              | PROCEDURE    | EXECUTE        | YES          |
| 'shlomi'@'127.0.0.1'     |            NULL | sakila         | get_customer_balance       | FUNCTION     | EXECUTE        | NO           |
| 'shlomi'@'127.0.0.1'     |            NULL | sakila         | get_customer_balance       | FUNCTION     | ALTER ROUTINE  | NO           |
| 'world_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | EXECUTE        | YES          |
| 'world_user'@'localhost' |            NULL | sakila         | get_customer_balance       | FUNCTION     | ALTER ROUTINE  | YES          |
+--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+