Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16466

Inconsistent privileges when changing GRANTs on a role

    Details

      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

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                marostegui Manuel Arostegui
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: