Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: Cannot grant execute on procedure

    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;
    Last edited by schmintan; 10-27-10 at 19:54.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jul 2004
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •