Home / server / Questions / 411502
In Process
dotplus
Asked: 2012-07-27 08:01:51 +0800 CST

mass create privileges for mysql stored procedures

  • 772

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?

mysql

2 Answers

  1. grant execute on <dbname>.*

    The word 'procedure' should be left out.

    • 1

  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'
    • 1