Allow multiple users to see procedures and functions

Question

I have GRANT ALL PRIVILEGES ON `test1`.* TO 'admin'@'%', GRANT ALL PRIVILEGES ON `test1`.* TO 'devel'@'%' (from SHOW GRANTS FOR some_user) and I have created simple procedure as admin in test1.

And here is what I see:

as admin

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| admin@% |
+----------------+
1 row in set (0,03 sec)
mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='test' AND ROUTINE_SCHEMA='test1';
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| begin
end |
+--------------------+
1 row in set (0,04 sec)
mysql> call test();
Query OK, 0 rows affected (0,03 sec)

as devel:

mysql> select current_user(); +----------------+
| current_user() |
+----------------+
| devel@% |
+----------------+
1 row in set (0,03 sec)
mysql> SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='test' AND ROUTINE_SCHEMA='test1';
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| NULL |
+--------------------+
1 row in set (0,03 sec)
mysql> call test();
Query OK, 0 rows affected (0,03 sec)

How can I allow multiple users to see code of procedure or function?

• oNare: Try granting EXECUTE permissions on user devel. (GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'.
• oNare: Did you run those commands from above? ^
• ckorzhik: MySQL documentation says that EXECUTE is required to execute stored routines ( dev.mysql.com/doc/refman/5.6/en/… ). I tried to run commands from above, and I still get NULL as devel.
• oNare: I did the same test as I told you above. I created a SP with 'admin'@'%' then I did the SELECTs. Everything was like you have in your question, but I ran: GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%' and I was able to SELECT it on information_schema.
• ckorzhik: Oh, sorry, it's my fault, it works. Thanks! Can you post your answer as answer, not as comment?

Answer 1

Try granting EXECUTE permissions on user devel.

GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'

• ckorzhik: Is there way to grant execute to all procedures? mysql> GRANT EXECUTE ON PROCEDURE test1.`%` TO 'devel'@'%'; ERROR 1305 (42000): FUNCTION or PROCEDURE % does not exist
• oNare: SELECT CONCAT('GRANT EXECUTE ON PROCEDURE ',ROUTINE_SCHEMA,'.',ROUTINE_NAME," TO 'devel'@'%';") AS QUERIES FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='test'; Copy the rows and execute them.
• ckorzhik: Thanks! But is there way to do it just once? It seems to me that new procedures will not have appropriate privileges.