information_schema.role_routine_grants

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

Sample query

1. 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 ;
   

2. Grant routine privileges to the role.

   obclient [test]> GRANT EXECUTE,ALTER ROUTINE ON test.add_numbers TO employee;
   

3. Grant the role to a user.

   obclient [test]> GRANT employee TO user1;
   

4. Connect to the database as user user1.

5. Activate the employee role in the current session.

   obclient [test]> SET ROLE employee;
   

6. 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
   

References

View roles