Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.1.33
-
None
-
debian
Description
Our system has the following user:
mysql:root@localhost [(none)]> show grants for 'maintainviews'@'localhost';
|
+----------------------------------------------------------------------------------------------------------------------+
|
| Grants for maintainviews@localhost |
|
+----------------------------------------------------------------------------------------------------------------------+
|
| GRANT SUPER ON *.* TO 'maintainviews'@'localhost' IDENTIFIED BY PASSWORD 'xx' |
|
| GRANT ALL PRIVILEGES ON `heartbeat\_p`.* TO 'maintainviews'@'localhost' |
|
| GRANT SELECT ON `heartbeat`.* TO 'maintainviews'@'localhost' |
|
| GRANT SELECT ON `centralauth`.* TO 'maintainviews'@'localhost' |
|
| GRANT ALL PRIVILEGES ON `meta\_p`.* TO 'maintainviews'@'localhost' |
|
| GRANT ALL PRIVILEGES ON `centralauth\_p`.* TO 'maintainviews'@'localhost' |
|
| GRANT ALL PRIVILEGES ON `meta_p`.* TO 'maintainviews'@'localhost' |
|
| GRANT SELECT, DROP, CREATE VIEW ON `%wik%`.* TO 'maintainviews'@'localhost' |
|
| GRANT ALL PRIVILEGES ON `%\_p`.* TO 'maintainviews'@'localhost' WITH GRANT OPTION |
|
| GRANT ALL PRIVILEGES ON `%wik%\_p`.* TO 'maintainviews'@'localhost' WITH GRANT OPTION |
|
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'maintainviews'@'localhost' |
|
+----------------------------------------------------------------------------------------------------------------------+
|
|
This user handles the GRANTS of a role that has the following GRANTS for each database on the system, there are around 900 grants for this role, these are how they look like:
| GRANT SELECT, SHOW VIEW ON `hsbwiktionary\_p`.* TO 'labsdbuser' |
|
| GRANT SELECT, SHOW VIEW ON `hrwikisource\_p`.* TO 'labsdbuser'
|
That role is assigned to around 3200 users.
The problem comes when trying to add a new GRANT for a new database to that labsdbuser.
mysql:maintainviews@localhost [(none)]> GRANT SELECT, SHOW VIEW ON `sahwikiquote\_p`.* TO 'labsdbuser';
|
ERROR 1044 (42000): Access denied for user 'maintainviews'@'localhost' to database 'sahwikiquote\_p'
|
However if we connect as root:
|
|
mysql:root@localhost [(none)]> GRANT SELECT, SHOW VIEW ON `sahwikiquote\_p`.* TO 'labsdbuser';
|
Query OK, 0 rows affected (0.00 sec)
|
|
Now we go back as our normal user and we can actually change the GRANT for a few seconds after it gets denied again:
root@labsdb1010:~# mysql -umaintainviews -p --skip-ssl
|
Enter password:
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 7266380
|
Server version: 10.1.33-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
mysql:maintainviews@localhost [(none)]> GRANT SELECT, SHOW VIEW ON `sahwikiquote\_p`.* TO 'labsdbuser';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql:maintainviews@localhost [(none)]> revoke SELECT, SHOW VIEW ON `sahwikiquote\_p`.* FROM 'labsdbuser';
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
mysql:maintainviews@localhost [(none)]> GRANT SELECT, SHOW VIEW ON `sahwikiquote\_p`.* TO 'labsdbuser';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql:maintainviews@localhost [(none)]> revoke SELECT, SHOW VIEW ON `sahwikiquote\_p`.* FROM 'labsdbuser';
|
ERROR 1044 (42000): Access denied for user 'maintainviews'@'localhost' to database 'sahwikiquote\_p'
|
Then if we go back as root, and play again with it:
root@labsdb1010:~# mysql -uroot --skip-ssl
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 7267895
|
Server version: 10.1.33-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
mysql:root@localhost [(none)]> GRANT SELECT, SHOW VIEW ON `sahwikiquote\_p`.* TO 'labsdbuser';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql:root@localhost [(none)]> revoke SELECT, SHOW VIEW ON `sahwikiquote\_p`.* FROM 'labsdbuser';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql:root@localhost [(none)]> Ctrl-C -- exit!
|
Aborted
|
Let's go back to our maintainviews user immediately after logging out from root
|
|
root@labsdb1010:~# mysql -umaintainviews -p --skip-ssl
|
Enter password:
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 7268063
|
Server version: 10.1.33-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
mysql:maintainviews@localhost [(none)]> GRANT SELECT, SHOW VIEW ON `sahwikiquote\_p`.* TO 'labsdbuser';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql:maintainviews@localhost [(none)]> revoke SELECT, SHOW VIEW ON `sahwikiquote\_p`.* FROM 'labsdbuser';
|
ERROR 1044 (42000): Access denied for user 'maintainviews'@'localhost' to database 'sahwikiquote\_p'
|
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
Activity
This is the same problem as MDEV-14732.
In your case, the matching records are
GRANT SELECT, DROP, CREATE VIEW ON `%wik%`.* TO 'maintainviews'@'localhost' |
GRANT ALL PRIVILEGES ON `%wik%\_p`.* TO 'maintainviews'@'localhost' WITH GRANT OPTION |
The order is undefined, when the one without GRANT OPTION is picked up (which apparently happens more often here), the GRANT query fails.
This also affects the database creation/dropping:
Trying to create a database with the maintainviews user fails
mysql:maintainviews@localhost [(none)]> create database sahwikiquote_p;ERROR 1044 (42000): Access denied for user 'maintainviews'@'localhost' to database 'sahwikiquote_p'mysql:maintainviews@localhost [(none)]> Ctrl-C -- exit!AbortedNow we try as root which obviously works
root@labsdb1010:~# mysql -uroot --skip-sslWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7280068Server version: 10.1.33-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql:root@localhost [(none)]> create database sahwikiquote_p;Query OK, 1 row affected (0.00 sec)mysql:root@localhost [(none)]> Ctrl-C -- exit!AbortedNow let's back to the maintainviews user and it works for a while:
root@labsdb1010:~# mysql -umaintainviews -p --skip-sslEnter password:Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 7280124Server version: 10.1.33-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql:maintainviews@localhost [(none)]> drop database sahwikiquote_p;Query OK, 0 rows affected (0.00 sec)mysql:maintainviews@localhost [(none)]> create database sahwikiquote_p;Query OK, 1 row affected (0.00 sec)