Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Stored Procedure Permissions

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.

like image 661
Olly Avatar asked Apr 10 '12 13:04

Olly


People also ask

How do I grant a stored procedure to run permissions in MySQL?

The syntax for granting EXECUTE privileges on a function/procedure in MySQL is: GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user; EXECUTE. The ability to execute the function or procedure.

How do I grant permission to a stored procedure?

To grant permissions on a stored procedureExpand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page.

How do I view stored procedure permissions?

Connect Server with Admin Session - Go to Database, Programmability, Stored Procedures, then select your Procedure. Right click on your procedure and select Properties. You'll get the following window. As shown inthe preceding image, go to Permissions tab and click on Search button.

Where are MySQL permissions stored?

Information about account privileges is stored in the grant tables in the mysql system database.


3 Answers

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

like image 172
sakhunzai Avatar answered Oct 18 '22 21:10

sakhunzai


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

like image 5
Shane E. Bryan Avatar answered Oct 18 '22 20:10

Shane E. Bryan


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

like image 1
Bettelbursche Avatar answered Oct 18 '22 21:10

Bettelbursche