Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is it possible to grant a privilege to a user such that they can execute all stored procs on the server (or even within a single db on that server). Looking at the grant syntax docs (5.5), it says that I have to grant execute on procedure <dbname>.<procname> to .... for each and every stored proc. Is there a way to do this less clusily by manipulating the mysql db itself?

share|improve this question
add comment

2 Answers

grant execute on <dbname>.*

The word 'procedure' should be left out.

share|improve this answer
add comment

For any user privileges defined against database mydb run this:

UPDATE mysql.db SET execute_priv='Y' WHERE db = 'mydb';
FLUSH PRIVILEGES;

First, see if any users are defined in mysql.db with

SELECT COUNT(1) FROM mysql.db;

If none of the users already have database-level privileges, then give it to them. If you want to grant every user the ability to run stored procedures against database mydb, run this

INSERT INTO mysql.db (Host,db,user,Select_priv,Insert_priv,Update_priv
Delete_priv,Create_priv,Drop_priv,Grant_priv,
References_priv,Index_priv,Alter_priv,Create_tmp_table_priv
Lock_tables_priv,Create_view_priv,Show_view_priv,Create_routine_priv
Alter_routine_priv,Execute_priv,Event_priv,Trigger_priv
SELECT Host,'mydb',user,Select_priv,Insert_priv,Update_priv
Delete_priv,Create_priv,Drop_priv,Grant_priv,
References_priv,Index_priv,Alter_priv,Create_tmp_table_priv
Lock_tables_priv,Create_view_priv,Show_view_priv,Create_routine_priv
Alter_routine_priv,'Y',Event_priv,Trigger_priv
FROM mysql.user;
FLUSH PRIVILEGES;

Notice I set :

  • Execute_priv to 'Y'
  • db to 'mydb'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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