information_schema.role_routine_grants

Purpose

The role_routine_grants view displays the stored procedure privileges granted to activated roles for the current session and the stored procedure privileges granted to other roles by these activated roles.

Columns

Sample query

1. Create roles and functions.

   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 stored procedure 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 the user user1.

5. Activate the employee role in the current session.

   obclient [test]> SET ROLE employee;
   

6. View the stored procedure privileges granted to the activated role for 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