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.

10
  • SECURITY DEFINER? or SECURITY INVOKER?
    – Rick James
    Mar 27, 2015 at 5:52
  • SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'proc_name';
    – Rick James
    Mar 27, 2015 at 5:54
  • @RickJames it is a SECURITY DEFINER.
    – svKris
    Mar 27, 2015 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, 2015 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, 2015 at 6:43

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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