Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.8, 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
Docker:
Version 17.06.0-ce-mac18 (18433)
Channel: stable
d9b66511e0
MacOS Sierra 10.12.6
MariaDB Docker Image ID 58730544b81b (mariadb:latest at time of filing).
-
10.1.30
Description
Running SET ROLE statement still yields permission denied when trying to create a database.
root@6d623b7b5bcd:/# mysql -u root -ppassword |
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 8 |
Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution |
|
|
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. |
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
MariaDB [(none)]> create role s;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> grant select, insert, update, delete, lock tables, execute on t.* to s;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> create role a;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> grant s to a;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> grant all on t.* to a;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> create user u;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> grant a to u;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> grant s to u;
|
Query OK, 0 rows affected (0.00 sec) |
Then,
root@6d623b7b5bcd:/# mysql -u u |
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 9 |
Server version: 10.2.8-MariaDB-10.2.8+maria~jessie mariadb.org binary distribution |
|
|
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. |
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
MariaDB [(none)]> show grants;
|
+-------------------------------+
|
| Grants for u@% | |
+-------------------------------+
|
| GRANT a TO 'u'@'%' | |
| GRANT s TO 'u'@'%' | |
| GRANT USAGE ON *.* TO 'u'@'%' | |
+-------------------------------+
|
3 rows in set (0.00 sec) |
|
|
MariaDB [(none)]> create database t;
|
ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't' |
MariaDB [(none)]> set role a;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [(none)]> create database t;
|
ERROR 1044 (42000): Access denied for user 'u'@'%' to database 't' |
MariaDB [(none)]> show grants;
|
+----------------------------------------------------------------------------+
|
| Grants for u@% | |
+----------------------------------------------------------------------------+
|
| GRANT a TO 'u'@'%' | |
| GRANT s TO 'u'@'%' | |
| GRANT USAGE ON *.* TO 'u'@'%' | |
| GRANT s TO 'a' | |
| GRANT USAGE ON *.* TO 'a' | |
| GRANT ALL PRIVILEGES ON `t`.* TO 'a' | |
| GRANT USAGE ON *.* TO 's' | |
| GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 's' | |
+----------------------------------------------------------------------------+
|
8 rows in set (0.00 sec) |
|
|
MariaDB [(none)]> select current_role;
|
+--------------+
|
| current_role |
|
+--------------+
|
| a |
|
+--------------+
|
1 row in set (0.00 sec) |
Granting all privileges directly to the user allows the user to create the database.
Attachments
Activity
Thanks for the report.
The culprit is the limited permissions to the database given to role s. Without it (or without giving this role to a), of if the limited permissions are granted to s after ALL is granted to a, it works all right.
We had a somewhat similar bug report MDEV-5771, apparently it wasn't fixed for all cases. I checked 10.0.10 to ensure that it's not a later regression.
In the example "s" is abbreviated for standard user, "a" for admin user and "u" for user. I wanted to make sure it is not a regression for a similar bug I saw in JIRA that was already closed.