Overview
The role_routine_grants view displays the routine privileges of activated roles granted to the user in the current session, as well as the routine privileges of other roles granted to these roles.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| GRANTOR | varchar(97) | YES | The username part of the account authorized with this role. |
| GRANTOR_HOST | varchar(256) | YES | The host part of the account being granted the role. |
| GRANTEE | varchar(32) | NO | The username part of the account corresponding to this role. |
| GRANTEE_HOST | varchar(255) | NO | The hostname part of the account corresponding to this role. |
| SPECIFIC_CATALOG | varchar(3) | NO | The name of the directory to which the routine belongs. This value is always 'def'. |
| SPECIFIC_SCHEMA | varchar(64) | NO | The name of the database to which the routine belongs. |
| SPECIFIC_NAME | varchar(64) | NO | The name of the routine to which this role has been granted access. |
| ROUTINE_CATALOG | varchar(3) | NO | The name of the directory to which the routine belongs. This value is always 'def'. |
| ROUTINE_SCHEMA | varchar(64) | NO | The name of the database to which the routine belongs. |
| ROUTINE_NAME | varchar(64) | NO | The name of the routine to which this role has been granted access. |
| PRIVILEGE_TYPE | varchar(90) | NO | The privilege granted. This value can be any privilege that can be granted at the routine level. |
| IS_GRANTABLE | varchar(3) | NO | Whether the privileges of this role can be granted to other users. |
Sample query
Create a role and a function.
Create the
employeerole:obclient [test]> CREATE ROLE employee;Create the function
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 on the procedure to the role.
obclient [test]> GRANT EXECUTE,ALTER ROUTINE ON test.add_numbers TO employee;Grant the role to the 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;View the routine privileges of activated roles granted to the user 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