I run MariaDB 10.0.23, and I use roles to grant privileges to users. I have a user john
:
+-------------------------------------------------------------------+
| Grants for john@% |
+-------------------------------------------------------------------+
| GRANT client TO 'john'@'%' |
| GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*C...DF' |
+-------------------------------------------------------------------+
The client
role is defined like this:
+--------------------------------------------------------+
| Grants for client |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'client' |
| GRANT INSERT ON `database_name`.`table1` TO 'client' |
| GRANT SELECT ON `database_name`.`table2` TO 'client' |
| GRANT SELECT ON `database_name`.`table3` TO 'client' |
| GRANT INSERT ON `database_name`.`table4` TO 'client' |
+--------------------------------------------------------+
When I connect using mysql -u john -p database_name
, I get
ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'database_name'
which kind of makes sense because when users log in, they don't have a role assigned by default.
However, I get the same error when I do mysql -u john -p
and then
SET ROLE client;
SHOW GRANTS;
+-------------------------------------------------------------------+
| Grants for john@% |
+-------------------------------------------------------------------+
| GRANT client TO 'john'@'%' |
| GRANT USAGE ON *.* TO 'john'@'%' IDENTIFIED BY PASSWORD '*C...DF' |
| GRANT USAGE ON *.* TO 'client' |
| GRANT INSERT ON `database_name`.`table1` TO 'client' |
| GRANT SELECT ON `database_name`.`table2` TO 'client' |
| GRANT SELECT ON `database_name`.`table3` TO 'client' |
| GRANT INSERT ON `database_name`.`table4` TO 'client' |
+-------------------------------------------------------------------+
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
USE database_name;
ERROR 1044 (42000): Access denied for user 'john'@'%' to database 'database_name'
The workaround that I use is to add one of the role's permissions to each user directly. This way users get access to the database, but it doesn't seem quite logical to me that using roles users still have to have permissions granted to them explicitly. MariaDB documentation doesn't say anything about this issue.
Any thoughts on why it happens and how to fix this behavior?
Best Answer
I can't reproduce the problem.
UPDATE
With a username longer than 6 characters, I can reproduce the problem, however, you can access the tables.
Bug reported to MariaDB: Roles and Users longer than 6 characters.