MariaDB Roles and SHOW DATABASES

mariadbrole

I'm not sure if this is a feature, or a bug, so I figured I'd ask here. I'm using MariaDB 10.1.14 on Amazon RDS, and I found that a user with an assigned role that has SELECT privileges on database(s) can't actually see those databases when they run SHOW DATABASES on the command line, or when they use a GUI client.

I tested this on a MariaDB instance on RDS, and with Docker locally.

The Docker command:

docker run \
--name mariadb-10114-test \
--publish 3306:3306 \
--env MYSQL_ROOT_PASSWORD=root \
--detach \
mariadb:10.1.14

Creating the database, table, role, user, and assigning the default role to the user as root:

mysql> DROP DATABASE IF EXISTS `testdb`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP ROLE IF EXISTS `developer`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP USER IF EXISTS 'john'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE `testdb`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `testdb`.`t0`(`c0` INT);
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE OR REPLACE ROLE `developer`;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `testdb`.* TO `developer`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'john'@'%' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT 'developer' TO 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET DEFAULT ROLE 'developer' FOR 'john'@'%';
Query OK, 0 rows affected (0.00 sec)

And then logging in as the newly created john user in another window:

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| developer      |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> USE `testdb`;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| t0               |
+------------------+
1 row in set (0.00 sec)

I can use the database, but that's only because I have explicit knowledge that I can use it. Is this normal for MariaDB roles? In MySQL / MariaDB with non-role based permissions, the user can see which databases they have access to. The fix for this is obviously to do GRANT SHOW DATABASES ON *.* TO 'developer' but this wouldn't be preferred.

Best Answer

It looks like this bug has already been reported, and is listed as "Stalled" at the moment.

https://jira.mariadb.org/browse/MDEV-10463