0

I'm trying to use roles in MySQL 8, and failing. I have read the documentation and as far as I can tell, I'm doing it right. Since this is a 'hello world' level attempt, I assume I'm missing something very obvious, but I can't figure it out. Any help will be highly appreciated.

Here is what I'm trying to do.

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'reader_password';

CREATE ROLE 'read_all';

GRANT SELECT ON MySchema.* TO 'read_all';

GRANT 'read_all' TO 'reader'@'localhost';

The above script is executed with root user, and all statements succeed.

Then, I open a new connection using the 'reader' user credentials, and connect successfully. However, any attempt to SELECT from any table in MySchema is denied with the error message:

SELECT * FROM MySchema.SomeTable;

SQL Error [1142] [42000]: SELECT command denied to user 'reader'@'localhost' for table 'SomeTable'

I expect this SELECT to succeed as the role has SELECT permissions, and the reader user has been granted the role. What am I missing?

Have a wonderful weekend!


UPDATE: Following Rick James' comment, here are the relevant SHOWs:

On the root connection (with active schema context 'MySchema'):

SHOW CREATE USER 'reader'@'localhost';
--------------------------------------
CREATE USER 'reader'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*96B291A4F8FC2B6C453E4C54AD2080751B0D4712' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT

SHOW CREATE USER 'read_all';
----------------------------
CREATE USER 'read_all'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

SHOW GRANTS FOR 'reader'@'localhost';
-------------------------------------
GRANT USAGE ON *.* TO `reader`@`localhost`  
GRANT `read_all`@`%` TO `reader`@`localhost`

SHOW GRANTS FOR 'read_all';
----------------------------
GRANT USAGE ON *.* TO `read_all`@`%`         
GRANT SELECT ON `MySchema`.* TO `read_all`@`%

And on the reader connection:

SHOW GRANTS;
------------
GRANT USAGE ON *.* TO `reader`@`localhost`  
GRANT `read_all`@`%` TO `reader`@`localhost`
  • Please provide the corresponding SHOWs to see what was actually stored. – Rick James Jul 20 at 19:36
  • Thanks @RickJames. Added SHOW output to post. Let me know if there is any other SHOW you want to see. – SQLRaptor Jul 20 at 20:18
0

So after diving deeper into the documentation, and specifically the "Using Roles" page, I have found that: "By default, granting a role to an account or naming it in the mandatory_roles system variable value does not automatically cause the role to become active within account sessions... To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE."

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.