1

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?

  • Try granting EXECUTE permissions on user devel. (GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'. – oNare Jul 17 '15 at 13:45
  • Did you run those commands from above? ^ – oNare Jul 17 '15 at 19:00
  • 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. – ckorzhik Jul 17 '15 at 19:22
  • 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. – oNare Jul 17 '15 at 19:26
  • Oh, sorry, it's my fault, it works. Thanks! Can you post your answer as answer, not as comment? – ckorzhik Jul 17 '15 at 21:24
1

Try granting EXECUTE permissions on user devel.

GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'
  • 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 – ckorzhik Jul 17 '15 at 21:31
  • 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. – oNare Jul 17 '15 at 21:36
  • 1
    Thanks! But is there way to do it just once? It seems to me that new procedures will not have appropriate privileges. – ckorzhik Jul 17 '15 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.