MySQL 8 roles – I’m probably missing something obvious

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`

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.“