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.
