CODEWITHSUNDEEP
mysqlpermissionsstored-functions
Bimal Poudel
Bimal Poudel

Reputation: 1234

Grant/Permit all MySQL User Defined Functions under a database to a user

How can I grant execute permission for all of the user defined functions under a database to a particular user? A new user may execute/run all functions defined by some other users.

1. GRANT ALL ON DATABASE.* TO 'USERNAME'@'HOSTNAME';
2. GRANT EXECUTE ON DATABASE.* TO 'USERNAME'@'HOSTNAME';

Both of these attempts failed to permit a new user. And the following call ends with an error.

SELECT f_custom_function();

Corresponding Error Message:

Query : ____
Error Code : 1370
execute command denied to user 'USERNAME'@'HOSTNAME' for routine 'DATABASE.f_custom_function'

Upvotes: 2

Views: 4571

Answers (1)

WEBjuju
WEBjuju

Reputation: 6591

The following will give execute privilege to one user for all objects within one database: 

GRANT EXECUTE
  ON *.*
  TO 'myuser'@'localhost';

This alternative allows access to a single MySQL user defined function for a single user:

GRANT EXECUTE
  ON FUNCTION `mydatabase`.`myuserdefunc`
  TO 'myuser'@'localhost';

Note: the mydatabase, myuser, and myuserdefunc; are for example only and are to be replaced with the values appropriate to your project.

Upvotes: 2

Related Questions