1

I've done what I consider to be sufficient research with no answers to justify posting this question here. I am attempting to grant users permission to execute certain stored procedures. And because I have to grant permission for several of these roles, but not all of them, I've chosen to do this using roles. But when I grant one of these roles to a test user and try to call a stored procedure, I see this error message:

1370 (42000): execute command denied to user 'me'@'localhost' for routine 'db.MyProc'

The SQL script to create a role looks like this:

CREATE ROLE 'MyRole';
GRANT USAGE ON db.* TO 'MyRole';
GRANT EXECUTE ON PROCEDURE db.MyProc TO 'MyRole';
-- grant execute on more procedures
FLUSH PRIVILEGES;

And then I grant the role to the test user and set it as default:

GRANT MyRole TO 'me'@'localhost';
SET DEFAULT ROLE MyRole TO 'me'@'localhost';
FLUSH PRIVILEGES;

Naturally, I do all of this as root. I think that the grants have worked because of the output of SHOW GRANTS.

 SHOW GRANTS FOR MyRole;
+--------------------------------------------------------------+
| Grants for MyRole@%                                          |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `MyRole`@`%`                           |
| GRANT EXECUTE ON PROCEDURE `db`.`MyProc` TO `Contributor`@`%`| 
|(grant execute on more procedures)                            |
+--------------------------------------------------------------+

SHOW GRANTS FOR 'me'@'localhost';
+---------------------------------------+
| Grants for contrib@localhost          |
+---------------------------------------+
| GRANT USAGE ON *.* TO `me`@`localhost`|
| GRANT `MyRole`@`%` TO `me`@`localhost`|
+---------------------------------------+

I have found no one else with this problem. And nothing I read from MySQL documentation suggested that this would not work. What is going wrong?

I'm using MySQL 8.0.11 on Windows 10.

0

The problem (it seems) was that MySQL didn't like the capital letters in my procedure names. I went though the entire script and renamed all of my procedures with lower case letters. I updated the roles accordingly, and then it worked properly.

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.