Grant Execute Function permission to another user

Question

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

Solution

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.

Comments

[Michael]

Are you trying to do it as root?

[Shaharyar]

@MichaelO. Yes!

[Michael]

Your second approach is correct. Maybe try creating the user?

[Shaharyar]

It already exists

[Shaharyar]

I already tried it, mentioned in Approach 2. Resulted with error Error Code: 1133. Can't find any matching row in the user table

[revo]

Sorry I think I missed it. Then it means api_worker user on % host doesn't exist yet. Do a SELECT CONCAT(User, '@', Host) FROM mysql.user; to confirm.

[revo]

Did you run that select query?

[Shaharyar]

Which query? Sorry didn't get your reference?

[revo]

No problem: SELECT CONCAT(User, '@', Host) FROM mysql.user;

[Shaharyar]

Yes, here are the results image.ibb.co/cAx4v8/Screenshot_from_2018_06_10_01_59_00.png

[revo]

You don't have api_worker user but api_workers (Watch s). Try with 'api_workers'@'%'.

[Shaharyar]

Yes, I just realized that and I can't explain how embarrassed I am. It worked with api_workers..

[revo]

No problem. You could remove your question if you want as it was a simple typo.

[Shaharyar]

I think we should improve your answer with this little detail, may be helpful for someone in the future.