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?
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';
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 (
external) and two functions (
real function that periodically removed and the
mock function that does not eliminate. The
external user only has the privilege to
mock function and internally
mock function call
real function and will execute with
internal user permissions.