MySQL Stored Procedure Permissions

Question

I am trying to give a user permission to run a stored procedure at the stored procedure level on a MySQL Database rather than allowing a user to execute any stored procedure in the database. I was trying to execute the following code:

GRANT EXECUTE ON myDB.spName TO 'TestUser'@'localhost';

But i keep getting the following error:

Illegal GRANT/REVOKE command, please consult the manual to see which privileges can be used.

I tried changing it to the following:

GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost';

And i get a different error stating:

Cant find any matching rows in the user table.

I am confused as to where I am going wrong?

Also on the MySQL Workbench I can not seem to see any way to grant permissions at the stored procedure level via the GUI. Is this correct or am I missing something?

Thanks in advance.

Solution 1

Your second attempt is the right approach:

GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost';

but if that is not working, verify ...

a) you (the user from which you are running all these command) have grant rights [i.e WITH GRANT OPTION]. If you are root, then you have grant rights.

b) the user exists to which you are granting execute permission e.g.

 select user from mysql.user where user  like  'test%';

If not, then create the user e.g.

CREATE USER 'TestUser'@'localhost' IDENTIFIED BY 'passwordxxxx';
#depending on your needs
GRANT SELECT,DELETE,UPDATE PRIVILEGES ON myDb.* TO 'TestUser'@'localhost'; 

Hope this helps :)

Solution 2

To answer the other part of your question regarding MySQL Workbench, I was having the same issue. But after experimenting I discovered that if you create a role and open the privileges tab at the bottom you can then drag the routine from the Model Overview into the objects box. From there just click on the newly added object and add the permissions you want for that role.

Hope that helps :)

Solution 3

I found this code: grant permissions on a stored procedure

USE [Database];   

GRANT EXECUTE ON OBJECT::[dbo].[your stored procedure]
    TO databaseUser; 

from this page: docs.microsoft.com