MariaDB database access using roles 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?
I can't reproduce the problem.

$ mysql -u root -p
Enter password: 


Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.0.23-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]&gt; DROP DATABASE IF EXISTS `database_name`;
Query OK, 4 rows affected (0.01 sec)

MariaDB [(none)]&gt; DROP ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; DROP USER 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; CREATE USER 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; CREATE ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; GRANT `client` TO 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; CREATE DATABASE `database_name`;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]&gt; CREATE TABLE `database_name`.`table1`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; CREATE TABLE `database_name`.`table2`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; CREATE TABLE `database_name`.`table3`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; CREATE TABLE `database_name`.`table4`(`col0` INT);
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; GRANT INSERT ON `database_name`.`table1` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; GRANT SELECT ON `database_name`.`table2` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; GRANT SELECT ON `database_name`.`table3` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; GRANT INSERT ON `database_name`.`table4` TO 'client';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; SHOW GRANTS FOR 'john'@'%';
+----------------------------------+
| Grants for john@%                |
+----------------------------------+
| GRANT client TO 'john'@'%'       |
| GRANT USAGE ON *.* TO 'john'@'%' |
+----------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]&gt; SHOW GRANTS FOR `client`;
+------------------------------------------------------+
| Grants for client                                    |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'client'                       |
| GRANT SELECT ON `database_name`.`table3` TO 'client' |
| GRANT SELECT ON `database_name`.`table2` TO 'client' |
| GRANT INSERT ON `database_name`.`table1` TO 'client' |
| GRANT INSERT ON `database_name`.`table4` TO 'client' |
+------------------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]&gt; exit
Bye


$ mysql -u john -p
Enter password: 


Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 10.0.23-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]&gt; SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]&gt; SET ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| database_name      |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]&gt; SELECT `col0` FROM `database_name`.`table1`;
ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table 'table1'

MariaDB [(none)]&gt; SELECT `col0` FROM `database_name`.`table2`;
Empty set (0.00 sec)


UPDATE

With a username longer than 6 characters, I can reproduce the problem, however, you can access the tables.

$ mysql -u root -p
Enter password:


MariaDB [(none)]&gt; CREATE USER 'usertestjohn'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; GRANT `client` TO 'usertestjohn'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; exit
Bye


$ mysql -u usertestjohn -p
Enter password:


MariaDB [(none)]&gt; SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]&gt; SET ROLE `client`;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]&gt; SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]&gt; SELECT `col0` FROM `database_name`.`table2`;
Empty set (0.00 sec)


Bug reported to MariaDB: Roles and Users longer than 6 characters.