Cannot grant execute on procedure

schmintan · #1 (**permalink**) 10-27-10, 18:39

I have mysql installed on my mac.

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:

Code:

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

Code:

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:

Code:

grant all privileges on *.* to user1@localhost;

it-iss.com · #2 (**permalink**) 10-28-10, 04:57

Hi,

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:

Code:

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

schmintan · #3 (**permalink**) 10-28-10, 08:10

Thanks for the response. no, im not placing the sproc name inside quotes. I have tried the command with the username and domain both inside and outside comments but to the same result.

Internet Services

Web Development

Digital Marketing

Consumer Tech