Mysql – Grant Execute Function permission to another user

database-permissionsgrantMySQLstored-procedures

I have a database function fn_relation_isModerator, only user api has access to this function. Now I want another user to have this permission (while keeping the previous permission as well).

I checked routine name and user by following query:

select routine_name, routine_type, definer from information_schema.ROUTINES where ROUTINE_SCHEMA = 'db_name';

Which resulted:

+-------------------------+---------------+----------+
|      ROUTINE_NAME       |  ROUTINE_TYPE |  DEFINER |
+-------------------------+---------------+----------+
|                         |               |          |
| fn_relation_isModerator |  FUNCTION     |  api@%   |
+-------------------------+---------------+----------+

Approach 1:

So I ran the following query to grant this permission:

GRANT EXECUTE ON PROCEDURE db_name.fn_relation_isModerator TO 'api_worker'@'%';

But it resulted in following error:

Error Code: 1305. PROCEDURE fn_relation_isModerator does not exist

Approach 2:

Query:

GRANT EXECUTE ON FUNCTION `db_name`.`fn_relation_isModerator` TO 'api_worker'@'%';

Error

Error Code: 1133. Can't find any matching row in the user table

Approach 3:

Query:

GRANT EXECUTE ON `db_name`.`fn_relation_isModerator` TO 'api_worker'@'%';

Error:

Error Code: 1144. Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used

Best Answer

You have to use grant execute on the function (Approach 2):

GRANT EXECUTE ON FUNCTION `db_name`.`fn_relation_isModerator` TO 'api_workers'@'%';

As explained in the comments by OP, there was a typo in user, it should be api_workers instead of api_worker.

All approaches failed due to the use of non-existent user.

Related Topic