Default privileges to execute function

Question

I have a user that has select only access but was granted execute on a single function in our mysql db. The issues I'm running into is other more privileged users will drop and create this function periodically which causes my less privileged user to lose the execute privilege they were granted. Is there a way to grant a default privilege just to the one function the user needs so that when it's dropped and created I don't have to re-grant their permissions?

Answer 1

I find no MySQL functionality to achieve what you need, you have to create what you need.

If you use a statement like:

GRANT EXECUTE
ON FUNCTION `yourdatabase`.`yourfunction`
TO 'youruser'@'localhost';

will apply:

14.7.1.4 GRANT Syntax

...

Important

MySQL does not automatically revoke any privileges when you drop a database or table. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

...

One option is to use a statement such as:

GRANT EXECUTE
ON `yourdatabase`.*
TO 'youruser'@'localhost';

of course, considering that possibly will be giving more privileges to the user really wants to give.

Another option is to have two user (internal and external) and two functions (real and mock), the real function that periodically removed and the mock function that does not eliminate. The external user only has the privilege to EXECUTE mock function and internally mock function call real function and will execute with internal user permissions.