information_schema.role_routine_grants

Purpose

The information_schema.role_routine_grants view displays the routine privileges of activated roles granted to the user in the current session, and the routine privileges of other roles granted to 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. Assign the role to the user1 user.

   obclient [test]> GRANT employee TO user1;
   

4. Connect to the database as the user1 user.

5. Activate the employee role in the current session.

   obclient [test]> SET ROLE employee;
   

6. Query the routine privileges of the activated role assigned 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