Note
This view is available starting with V4.3.3 for V4.3x versions and V4.2.4 for V4.2.x versions.
Purpose
The role_routine_grants view displays the routine privileges granted to the roles activated in the current session and the routine privileges granted to other roles by these roles.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| GRANTOR | varchar(97) | YES | The username part of the account that granted the privilege to the role. |
| GRANTOR_HOST | varchar(256) | YES | The host part of the account that granted the privilege to the role. |
| GRANTEE | varchar(32) | NO | The username part of the account corresponding to the role. |
| GRANTEE_HOST | varchar(255) | NO | The host part of the account corresponding to the role. |
| SPECIFIC_CATALOG | varchar(3) | NO | The name of the catalog where the routine to which the role has been granted privileges is located. This value is always def. |
| SPECIFIC_SCHEMA | varchar(64) | NO | The name of the database where the routine to which the role has been granted privileges is located. |
| SPECIFIC_NAME | varchar(64) | NO | The name of the routine to which the role has been granted privileges. |
| ROUTINE_CATALOG | varchar(3) | NO | The name of the catalog where the routine to which the role has been granted privileges is located. |
| ROUTINE_SCHEMA | varchar(64) | NO | The name of the database where the routine to which the role has been granted privileges is located. |
| ROUTINE_NAME | varchar(64) | NO | The name of the routine to which the role has been granted privileges. |
| PRIVILEGE_TYPE | varchar(90) | NO | The privilege granted. This value can be any privilege granted at the routine level. |
| IS_GRANTABLE | varchar(3) | NO | Indicates whether the privileges of the role can be granted to other users. |
Sample query
Create a role and a function.
Create a role named
employee:obclient [test]> CREATE ROLE employee;Create a function named
add_numbers:obclient [test]> DELIMITER //obclient [test]> CREATE FUNCTION test.add_numbers(a INT, b INT) RETURNS INT BEGIN RETURN a + b; END;//obclient [test]> DELIMITER ;Grant routine privileges to the role.
obclient [test]> GRANT EXECUTE,ALTER ROUTINE ON test.add_numbers TO employee;Grant the role to a user.
obclient [test]> GRANT employee TO user1;Connect to the database as user
user1.Activate the
employeerole in the current session.obclient [test]> SET ROLE employee;Query the routine privileges granted to the activated roles in the current session.
obclient [test]> USE information_schema;obclient [information_schema]> SELECT * FROM information_schema.role_routine_grants;The query result is as follows:
+---------+--------------+-----------+--------------+------------------+-----------------+---------------+-----------------+----------------+--------------+-----------------------+--------------+ | GRANTOR | GRANTOR_HOST | GRANTEE | GRANTEE_HOST | SPECIFIC_CATALOG | SPECIFIC_SCHEMA | SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | +---------+--------------+-----------+--------------+------------------+-----------------+---------------+-----------------+----------------+--------------+-----------------------+--------------+ | root | % | employee | % | def | test | add_numbers | def | test | add_numbers | Execute,Alter Routine | NO | +---------+--------------+-----------+--------------+------------------+-----------------+---------------+-----------------+----------------+--------------+-----------------------+--------------+ 1 row in set