MySQL Stored Procedure Permissions
mysql, permissions, sql-grant, stored-procedures
Solution
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 :)
Problem
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.