Why are not working my roles in my MySQL database?

user_14006368 0
Viewed 763

I am currently learning about user privileges and roles in MySQL. At this time I am learning how to create custom roles, grant privileges to them and finally create some users just for testing this new roles. The thing is that, I don't know why but, even though the roles are correctly assigned to the users, when I login using them I cannot see any schema just like if I didn't have any privilege. The script that I am using is the next one:

For creating a new role:

CREATE ROLE 'read_db';

For setting privileges in the "bdatosventas" database:

GRANT SELECT ON bdatosventas.* TO 'read_db';

Creating a new user:

CREATE USER 'test_read_user'@'localhost' IDENTIFIED BY 'somepassword';

Setting the role for this new user:

GRANT 'read_db' TO 'test_read_user'@'localhost';

Verifying the privileges with:

SHOW GRANTS FOR 'test_read_user'@'localhost';

And as output I get:

GRANT `read_db` TO `test_read_user`@`localhost`
GRANT USAGE ON *.* TO `test_read_user`@`localhost`...

Finally a good:

FLUSH PRIVILEGES;

So I guess the role is correctly assigned and should be working. But when I get into phpMyAdmin using that new user, it doesn't show me any schema, cannot see my database "bdatosventas", just one called 'information_schema'. What is the problem?, thank you.

phpmyadmin roles privileges mysql
2 Answers
user_122772150

ok, this is what solved it for me.

After creating the Roles, Granting privileges to the roles, creating users and then assigning User Membership to the roles, one important step is to ACTIVATE the roles:

SET ROLE ALL;

user_18807640
Related