Skip to content

# ROUTINE_PRIVILEGES implementation

MySQL INFORMATION_SCHEMA Open Source Security Stored routines Views June 22, 2011June 27, 2011

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          |
>     +--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+

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

###
2
Leave a Reply

2 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Shai

Hi Shlomi,

A nitpick: 2nd normal form requires that each attribute be dependent on “the whole key”; so actually, the fact that the attribute is_grantable depends only on part of the key makes the table _not_ 2NF (and consequently, of course, not any higher NF).

(according to Chris Date, the fact that these tables contain nulls makes them not even 1NF, but I believe most people disagree there).

Author
shlomi

Shai,

Thanks. Please continue nitpicking

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.