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.
