34

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.

4b0
  • 21,981
  • 30
  • 95
  • 142
Olly
  • 587
  • 1
  • 5
  • 14

3 Answers3

54

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 :)

user3735178
  • 129
  • 10
sakhunzai
  • 13,900
  • 23
  • 98
  • 159
  • Im logged in as root and the user exists when running the code: select user from mysql.user where user like 'test%'; but i still appear to be getting the same errors. – Olly Apr 10 '12 at 18:53
  • try adding new user as I suggested and grant execute rights to that user – sakhunzai Apr 10 '12 at 19:33
  • 4
    one more try with password : `GRANT EXECUTE ON PROCEDURE myDB.spName TO 'TestUser'@'localhost' identified by 'password';` – sakhunzai Apr 10 '12 at 19:35
  • Adding the "Identified by 'password';" to the end worked! Many thanks! – Olly Apr 10 '12 at 19:44
5

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 :)

Shane E. Bryan
  • 109
  • 1
  • 7
1

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: learn.microsoft.com

Bettelbursche
  • 433
  • 6
  • 14