mass create privileges for mysql stored procedures

Question

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?

Answer 1

grant execute on <dbname>.*

The word 'procedure' should be left out.

Answer 2

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'