0

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.

1 Answer 1

0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.