0

As a root user I did this:

GRANT DELETE, SELECT, INSERT, UPDATE, EXECUTE ON dbName.* TO
'user'@'localhost' IDENTIFIED BY 'apassword';

In 'dbName' I created a procedure 'proc_name' with definer set to 'user'@'localhost'.

Similarly I create a function 'func_name' with definer set to 'user'@'localhost'.

Now the issue is, I'm able to do select func_name(); but I'm not able to call the procedure proc_name()

When i did :

call proc_name(), I get the below error:

ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'dbName'

Any pointers to the solution would be appreciated.

Improve this question
10
  • SECURITY DEFINER? or SECURITY INVOKER?
    – Rick James
    Mar 27 '15 at 5:52
  • SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'proc_name';
    – Rick James
    Mar 27 '15 at 5:54
  • @RickJames it is a SECURITY DEFINER.
    – svKris
    Mar 27 '15 at 6:29
  • @RickJames i am able to see the procedure code when i did select * from information_schema.ROUTINES WHERE ROUTINE_NAME = 'proc_name'
    – svKris
    Mar 27 '15 at 6:30
  • 1
    What kind of access to dbName does the Proc need? Perhaps more than DELETE, SELECT, INSERT, UPDATE?
    – Rick James
    Mar 27 '15 at 6:43
0

As @Rick suggested in the question comments. We need to grant all the permissions, procName expects. In my case, procName Creates and Deletes temporary tables. So, granting CREATE TEMPORARY TABLES permissions on dbName.* did the trick

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.