I am logged in as root and have created user1 and granted the following access to the database, sproc, and table in question (gotten by executing show grants for current_user:
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*F29D00D619BC53E42CD9872D41B9A05F2FD32540' |
| GRANT EXECUTE ON `test`.* TO 'user1'@'localhost' |
| GRANT SELECT ON `test`.`testtable` TO 'user1'@'localhost' |
| GRANT EXECUTE ON PROCEDURE `test`.`firstprocget` TO 'user1'@'localhost'
When i log on as user 1, change the database context to "test" database, and try to exec the sproc i get the following error:
mysql> call firstProcGet;
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.firstProcGet'
ive removed and recreated the user a number of times but cant get this issue resolved. any ideas?
edit: the sproc was created by root, in case that matters.
also, if i run the below command to grant all privileges, it works, so i dont know where the happy medim is:
when issuing the grant you are placing the procedure name inside `which indicates that the procedure is case sensitive. I have done the same thing as follows:
mysql> GRANT USAGE ON seminar.* TO 'user1'@'localhost' IDENTIFIED BY 'user1';
Query OK, 0 rows affected (0.01 sec)
mysql> grant execute on procedure seminar.myproc to user1@localhost;
Query OK, 0 rows affected (0.05 sec)
rcashell@linux:~> mysql -u user1 -p -D seminar
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 207
Server version: 5.0.45 SUSE MySQL RPM
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> call myproc(@x);
Query OK, 0 rows affected (0.02 sec)
mysql> select @x;
| @x |
| 3 |
1 row in set (0.00 sec)