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?

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

1 Answer 1

1

Try granting EXECUTE permissions on user devel.

GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'
3
  • 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 Commented Jul 17, 2015 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. Commented Jul 17, 2015 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. Commented Jul 17, 2015 at 21:44

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.