|
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'
|
|
|