How to give privileges to a user to execute all the procedures?

Question

Navigation

#1 by (1 votes)

0

In Mysql, as I can give permissions for a user to execute all the procedures, I found:

GRANT EXECUTE ON PROCEDURE miBD.miProcedure TO 'USER'@'localhost';

As you can see you can only give permission to one procedure but not all, how to give permission to all without doing it one by one. I've tried:

GRANT EXECUTE ON PROCEDURE miBD.* TO 'USER'@'localhost';

This query marks error. Y:

GRANT ALL PRIVILEGES ON miBD.* TO 'USER'@'localhost';

This does not grant them.

mysql sql

asked by Osiel Candido Onofre 18.10.2016 в 19:32

source

1 answer

score 1 · Answer 1

In a project we find this same casuistry. We did not get a direct way to do it based on the MySQL documentation.

We opted to create a query that would "generate" a list of grant commands for all procedures in this way:

select concat('grant execute on procedure miBD.',name,' to user \'USER\'@\'localhost\';') from mysql.proc where db = 'miBD'

It is not a direct or very clean way, but at least it solved us having to generate all the GRANTs by hand.