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

Your second approach is correct. Maybe try creating the user? — Michael, Jun 09 '18 at 20:52

score 5 · Accepted Answer · edited Jun 09 '18 at 21:10

5

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.

edited Jun 09 '18 at 21:10

Shaharyar

12,254
4
46
66

answered Jun 09 '18 at 20:46

revo

47,783
14
74
117

I already tried it, mentioned in **Approach 2**. Resulted with error `Error Code: 1133. Can't find any matching row in the user table` – Shaharyar Jun 09 '18 at 20:48
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 Jun 09 '18 at 20:51
Did you run that select query? – revo Jun 09 '18 at 20:55
Which query? Sorry didn't get your reference? – Shaharyar Jun 09 '18 at 20:56
No problem: `SELECT CONCAT(User, '@', Host) FROM mysql.user;` – revo Jun 09 '18 at 20:57
Yes, here are the results https://image.ibb.co/cAx4v8/Screenshot_from_2018_06_10_01_59_00.png – Shaharyar Jun 09 '18 at 21:01
1

You don't have `api_worker` user but `api_workers` (Watch `s`). Try with `'api_workers'@'%'`. – revo Jun 09 '18 at 21:02
Yes, I just realized that and I can't explain how embarrassed I am. It worked with `api_workers`.. – Shaharyar Jun 09 '18 at 21:03
No problem. You could remove your question if you want as it was a simple typo. – revo Jun 09 '18 at 21:05
I think we should improve your answer with this little detail, may be helpful for someone in the future. – Shaharyar Jun 09 '18 at 21:06

Grant Execute Function permission to another user

1 Answers1