routine_privileges
NAME
routine_privileges: INFORMATION_SCHEMA-like view on routines privilegesTYPE
ViewDESCRIPTION
INFORMATION_SCHEMA maps the mysql privileges tables into *_PRIVILEGES views. However, it only maps users, db, tables_priv, columns_priv, and it fails mapping the procs_priv table. This is an inconsistency within INFORMATION_SCHEMA (see bug #61596).
routine_privileges implements what the author believes to be the definition of ROUTINE_PRIVILEGES within INFORMATION_SCHEMA should be. It follows up on the *_PRIVILEGES tables conventions.
The view presents with grantees, and their set of privileges on specific routines (functions & procedures).
STRUCTURE
mysql> DESC routine_privileges;
+-----------------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | YES | | NULL | |
| ROUTINE_CATALOG | binary(0) | YES | | NULL | |
| ROUTINE_SCHEMA | char(64) | NO | | | |
| ROUTINE_NAME | char(64) | NO | | | |
| ROUTINE_TYPE | enum('FUNCTION','PROCEDURE') | NO | | NULL | |
| PRIVILEGE_TYPE | varchar(27) | YES | | NULL | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+-----------------+------------------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- GRANTEE: grantee's account
- ROUTINE_CATALOG: unused; NULL
- ROUTINE_SCHEMA: schema in which routines is located
- ROUTINE_NAME: name of routine
- ROUTINE_TYPE: 'FUNCTION' or 'PROECEDURE'
- PRIVILEGE_TYPE: single privilege (e.g. 'EXECUTE' or 'ALTER ROUTINE')
- IS_GRANTABLE: whether the grantee is grantable on this routine. This is a de-normalized column, following the convention of the *_PRIVILEGES tables in INFORMATION_SCHEMA
The view is denormalized. While the mysql.procs_privs table lists the set of privileges per account in one row, this view breaks the privileges to distinct rows. Also, the 'Grant' privilege is not listed on its own, but rather as an extra column.
EXAMPLES
mysql> SELECT * FROM common_schema.routine_privileges ORDER BY GRANTEE, ROUTINE_SCHEMA, ROUTINE_NAME; +--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+ | GRANTEE | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | PRIVILEGE_TYPE | IS_GRANTABLE | +--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+ | 'apps'@'%' | NULL | sakila | get_customer_balance | FUNCTION | EXECUTE | YES | | 'other_user'@'localhost' | NULL | sakila | film_in_stock | PROCEDURE | ALTER ROUTINE | NO | | 'other_user'@'localhost' | NULL | sakila | film_in_stock | PROCEDURE | EXECUTE | NO | | 'other_user'@'localhost' | NULL | sakila | get_customer_balance | FUNCTION | ALTER ROUTINE | NO | | 'other_user'@'localhost' | NULL | sakila | get_customer_balance | FUNCTION | EXECUTE | NO | | 'other_user'@'localhost' | NULL | sakila | inventory_held_by_customer | FUNCTION | ALTER ROUTINE | NO | | 'other_user'@'localhost' | NULL | sakila | inventory_held_by_customer | FUNCTION | EXECUTE | NO | | 'world_user'@'localhost' | NULL | sakila | get_customer_balance | FUNCTION | EXECUTE | YES | | 'world_user'@'localhost' | NULL | sakila | get_customer_balance | FUNCTION | ALTER ROUTINE | YES | +--------------------------+-----------------+----------------+----------------------------+--------------+----------------+--------------+
Compare with:
mysql> SELECT * FROM mysql.procs_priv; +-----------+--------+------------+----------------------------+--------------+----------------+-----------------------------+---------------------+ | Host | Db | User | Routine_name | Routine_type | Grantor | Proc_priv | Timestamp | +-----------+--------+------------+----------------------------+--------------+----------------+-----------------------------+---------------------+ | % | sakila | apps | get_customer_balance | FUNCTION | root@localhost | Execute,Grant | 2011-06-22 14:29:01 | | localhost | sakila | world_user | get_customer_balance | FUNCTION | root@localhost | Execute,Alter Routine,Grant | 2011-06-22 14:29:18 | | localhost | sakila | other_user | get_customer_balance | FUNCTION | root@localhost | Execute,Alter Routine | 2011-06-22 14:29:25 | | localhost | sakila | other_user | inventory_held_by_customer | FUNCTION | root@localhost | Execute,Alter Routine | 2011-06-22 14:30:12 | | localhost | sakila | other_user | film_in_stock | PROCEDURE | root@localhost | Execute,Alter Routine | 2011-06-22 14:30:46 | +-----------+--------+------------+----------------------------+--------------+----------------+-----------------------------+---------------------+