MySQL Stored Procedure Permissions

grantMySQLpermissionsstored-procedures

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.

Best Answer

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

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Best Answer

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Sql – Insert results of a stored procedure into a temporary table

Related Topic