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

FLUSH PRIVILEGES can break hierarchy of roles

    XMLWordPrintable

    Details

    • Sprint:
      10.1.30

      Description

      FLUSH PRIVILEGES or server restart can break hierarchy of roles. IOW reading privileges from the disk is not reliable.

      See attached test case for details. In a nutshell:

      1. There is a role foo_all which is granted few other roles foo_xx, e .g. foo_sg.
      2. A user who is granted foo_all and has access to all databases available to foo_xx.
      3. FLUSH PRIVILEGES or server reboot makes the user lose access to some databases previously available through foo_xx. SHOW DATABASE does not list those databases, SELECT command is denied, etc.

      See how bob_live_sg and oms_live_sg are missed in SHOW DATABASES and SELECT is denied:

      Checking leftover processes...
      Removing old var directory...
      Creating var directory '/home/pashev/tmp/server/build/mysql-test/var'...
      Checking supported features...
      MariaDB Version 10.1.22-MariaDB
       - SSL connections supported
      Sphinx 'indexer' binary not found, sphinx suite will be skipped
      Collecting tests...
      Installing system database...
       
      ==============================================================================
       
      TEST                                      RESULT   TIME (ms) or COMMENT
      --------------------------------------------------------------------------
       
      worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
      CREATE USER u;
      CREATE ROLE operations_cluster;
      GRANT operations_cluster TO u;
      CREATE DATABASE bob_live_sg;
      CREATE TABLE bob_live_sg.a (i INT(10));
      CREATE TABLE bob_live_sg.b (i INT(10));
      CREATE TABLE bob_live_sg.c (i INT(10));
      CREATE TABLE bob_live_sg.d (i INT(10));
      CREATE DATABASE oms_live_sg;
      CREATE TABLE oms_live_sg.a (i INT(10));
      CREATE TABLE oms_live_sg.b (i INT(10));
      CREATE TABLE oms_live_sg.c (i INT(10));
      CREATE TABLE oms_live_sg.d (i INT(10));
      CREATE DATABASE bob_live_ph;
      CREATE TABLE bob_live_ph.a (i INT(10));
      CREATE TABLE bob_live_ph.b (i INT(10));
      CREATE TABLE bob_live_ph.c (i INT(10));
      CREATE TABLE bob_live_ph.d (i INT(10));
      CREATE DATABASE oms_live_ph;
      CREATE TABLE oms_live_ph.a (i INT(10));
      CREATE TABLE oms_live_ph.b (i INT(10));
      CREATE TABLE oms_live_ph.c (i INT(10));
      CREATE TABLE oms_live_ph.d (i INT(10));
      CREATE DATABASE bob_live_id;
      CREATE TABLE bob_live_id.a (i INT(10));
      CREATE TABLE bob_live_id.b (i INT(10));
      CREATE TABLE bob_live_id.c (i INT(10));
      CREATE TABLE bob_live_id.d (i INT(10));
      CREATE DATABASE oms_live_id;
      CREATE TABLE oms_live_id.a (i INT(10));
      CREATE TABLE oms_live_id.b (i INT(10));
      CREATE TABLE oms_live_id.c (i INT(10));
      CREATE TABLE oms_live_id.d (i INT(10));
      CREATE DATABASE bob_live_hk;
      CREATE TABLE bob_live_hk.a (i INT(10));
      CREATE TABLE bob_live_hk.b (i INT(10));
      CREATE TABLE bob_live_hk.c (i INT(10));
      CREATE TABLE bob_live_hk.d (i INT(10));
      CREATE DATABASE oms_live_hk;
      CREATE TABLE oms_live_hk.a (i INT(10));
      CREATE TABLE oms_live_hk.b (i INT(10));
      CREATE TABLE oms_live_hk.c (i INT(10));
      CREATE TABLE oms_live_hk.d (i INT(10));
      CREATE DATABASE bob_live_vn;
      CREATE TABLE bob_live_vn.a (i INT(10));
      CREATE TABLE bob_live_vn.b (i INT(10));
      CREATE TABLE bob_live_vn.c (i INT(10));
      CREATE TABLE bob_live_vn.d (i INT(10));
      CREATE DATABASE oms_live_vn;
      CREATE TABLE oms_live_vn.a (i INT(10));
      CREATE TABLE oms_live_vn.b (i INT(10));
      CREATE TABLE oms_live_vn.c (i INT(10));
      CREATE TABLE oms_live_vn.d (i INT(10));
      CREATE DATABASE bob_live_tw;
      CREATE TABLE bob_live_tw.a (i INT(10));
      CREATE TABLE bob_live_tw.b (i INT(10));
      CREATE TABLE bob_live_tw.c (i INT(10));
      CREATE TABLE bob_live_tw.d (i INT(10));
      CREATE DATABASE oms_live_tw;
      CREATE TABLE oms_live_tw.a (i INT(10));
      CREATE TABLE oms_live_tw.b (i INT(10));
      CREATE TABLE oms_live_tw.c (i INT(10));
      CREATE TABLE oms_live_tw.d (i INT(10));
      CREATE DATABASE bob_live_my;
      CREATE TABLE bob_live_my.a (i INT(10));
      CREATE TABLE bob_live_my.b (i INT(10));
      CREATE TABLE bob_live_my.c (i INT(10));
      CREATE TABLE bob_live_my.d (i INT(10));
      CREATE DATABASE oms_live_my;
      CREATE TABLE oms_live_my.a (i INT(10));
      CREATE TABLE oms_live_my.b (i INT(10));
      CREATE TABLE oms_live_my.c (i INT(10));
      CREATE TABLE oms_live_my.d (i INT(10));
      CREATE DATABASE bob_live_th;
      CREATE TABLE bob_live_th.a (i INT(10));
      CREATE TABLE bob_live_th.b (i INT(10));
      CREATE TABLE bob_live_th.c (i INT(10));
      CREATE TABLE bob_live_th.d (i INT(10));
      CREATE DATABASE oms_live_th;
      CREATE TABLE oms_live_th.a (i INT(10));
      CREATE TABLE oms_live_th.b (i INT(10));
      CREATE TABLE oms_live_th.c (i INT(10));
      CREATE TABLE oms_live_th.d (i INT(10));
      CREATE ROLE a_sg;
      CREATE ROLE b_sg;
      CREATE ROLE c_sg;
      CREATE ROLE d_sg;
      CREATE ROLE operations_sg;
      GRANT a_sg TO operations_sg;
      GRANT b_sg TO operations_sg;
      GRANT c_sg TO operations_sg;
      GRANT d_sg TO operations_sg;
      GRANT SELECT ON bob_live_sg.a TO a_sg;
      GRANT SELECT ON bob_live_sg.b TO b_sg;
      GRANT SELECT ON bob_live_sg.c TO c_sg;
      GRANT SELECT ON bob_live_sg.d TO d_sg;
      GRANT SELECT ON oms_live_sg.a TO a_sg;
      GRANT SELECT ON oms_live_sg.b TO b_sg;
      GRANT SELECT ON oms_live_sg.c TO c_sg;
      GRANT SELECT ON oms_live_sg.d TO d_sg;
      CREATE ROLE a_ph;
      CREATE ROLE b_ph;
      CREATE ROLE c_ph;
      CREATE ROLE d_ph;
      CREATE ROLE operations_ph;
      GRANT a_ph TO operations_ph;
      GRANT b_ph TO operations_ph;
      GRANT c_ph TO operations_ph;
      GRANT d_ph TO operations_ph;
      GRANT SELECT ON bob_live_ph.a TO a_ph;
      GRANT SELECT ON bob_live_ph.b TO b_ph;
      GRANT SELECT ON bob_live_ph.c TO c_ph;
      GRANT SELECT ON bob_live_ph.d TO d_ph;
      GRANT SELECT ON oms_live_ph.a TO a_ph;
      GRANT SELECT ON oms_live_ph.b TO b_ph;
      GRANT SELECT ON oms_live_ph.c TO c_ph;
      GRANT SELECT ON oms_live_ph.d TO d_ph;
      CREATE ROLE a_id;
      CREATE ROLE b_id;
      CREATE ROLE c_id;
      CREATE ROLE d_id;
      CREATE ROLE operations_id;
      GRANT a_id TO operations_id;
      GRANT b_id TO operations_id;
      GRANT c_id TO operations_id;
      GRANT d_id TO operations_id;
      GRANT SELECT ON bob_live_id.a TO a_id;
      GRANT SELECT ON bob_live_id.b TO b_id;
      GRANT SELECT ON bob_live_id.c TO c_id;
      GRANT SELECT ON bob_live_id.d TO d_id;
      GRANT SELECT ON oms_live_id.a TO a_id;
      GRANT SELECT ON oms_live_id.b TO b_id;
      GRANT SELECT ON oms_live_id.c TO c_id;
      GRANT SELECT ON oms_live_id.d TO d_id;
      CREATE ROLE a_hk;
      CREATE ROLE b_hk;
      CREATE ROLE c_hk;
      CREATE ROLE d_hk;
      CREATE ROLE operations_hk;
      GRANT a_hk TO operations_hk;
      GRANT b_hk TO operations_hk;
      GRANT c_hk TO operations_hk;
      GRANT d_hk TO operations_hk;
      GRANT SELECT ON bob_live_hk.a TO a_hk;
      GRANT SELECT ON bob_live_hk.b TO b_hk;
      GRANT SELECT ON bob_live_hk.c TO c_hk;
      GRANT SELECT ON bob_live_hk.d TO d_hk;
      GRANT SELECT ON oms_live_hk.a TO a_hk;
      GRANT SELECT ON oms_live_hk.b TO b_hk;
      GRANT SELECT ON oms_live_hk.c TO c_hk;
      GRANT SELECT ON oms_live_hk.d TO d_hk;
      CREATE ROLE a_vn;
      CREATE ROLE b_vn;
      CREATE ROLE c_vn;
      CREATE ROLE d_vn;
      CREATE ROLE operations_vn;
      GRANT a_vn TO operations_vn;
      GRANT b_vn TO operations_vn;
      GRANT c_vn TO operations_vn;
      GRANT d_vn TO operations_vn;
      GRANT SELECT ON bob_live_vn.a TO a_vn;
      GRANT SELECT ON bob_live_vn.b TO b_vn;
      GRANT SELECT ON bob_live_vn.c TO c_vn;
      GRANT SELECT ON bob_live_vn.d TO d_vn;
      GRANT SELECT ON oms_live_vn.a TO a_vn;
      GRANT SELECT ON oms_live_vn.b TO b_vn;
      GRANT SELECT ON oms_live_vn.c TO c_vn;
      GRANT SELECT ON oms_live_vn.d TO d_vn;
      CREATE ROLE a_tw;
      CREATE ROLE b_tw;
      CREATE ROLE c_tw;
      CREATE ROLE d_tw;
      CREATE ROLE operations_tw;
      GRANT a_tw TO operations_tw;
      GRANT b_tw TO operations_tw;
      GRANT c_tw TO operations_tw;
      GRANT d_tw TO operations_tw;
      GRANT SELECT ON bob_live_tw.a TO a_tw;
      GRANT SELECT ON bob_live_tw.b TO b_tw;
      GRANT SELECT ON bob_live_tw.c TO c_tw;
      GRANT SELECT ON bob_live_tw.d TO d_tw;
      GRANT SELECT ON oms_live_tw.a TO a_tw;
      GRANT SELECT ON oms_live_tw.b TO b_tw;
      GRANT SELECT ON oms_live_tw.c TO c_tw;
      GRANT SELECT ON oms_live_tw.d TO d_tw;
      CREATE ROLE a_my;
      CREATE ROLE b_my;
      CREATE ROLE c_my;
      CREATE ROLE d_my;
      CREATE ROLE operations_my;
      GRANT a_my TO operations_my;
      GRANT b_my TO operations_my;
      GRANT c_my TO operations_my;
      GRANT d_my TO operations_my;
      GRANT SELECT ON bob_live_my.a TO a_my;
      GRANT SELECT ON bob_live_my.b TO b_my;
      GRANT SELECT ON bob_live_my.c TO c_my;
      GRANT SELECT ON bob_live_my.d TO d_my;
      GRANT SELECT ON oms_live_my.a TO a_my;
      GRANT SELECT ON oms_live_my.b TO b_my;
      GRANT SELECT ON oms_live_my.c TO c_my;
      GRANT SELECT ON oms_live_my.d TO d_my;
      CREATE ROLE a_th;
      CREATE ROLE b_th;
      CREATE ROLE c_th;
      CREATE ROLE d_th;
      CREATE ROLE operations_th;
      GRANT a_th TO operations_th;
      GRANT b_th TO operations_th;
      GRANT c_th TO operations_th;
      GRANT d_th TO operations_th;
      GRANT SELECT ON bob_live_th.a TO a_th;
      GRANT SELECT ON bob_live_th.b TO b_th;
      GRANT SELECT ON bob_live_th.c TO c_th;
      GRANT SELECT ON bob_live_th.d TO d_th;
      GRANT SELECT ON oms_live_th.a TO a_th;
      GRANT SELECT ON oms_live_th.b TO b_th;
      GRANT SELECT ON oms_live_th.c TO c_th;
      GRANT SELECT ON oms_live_th.d TO d_th;
      GRANT operations_sg TO operations_cluster;
      GRANT operations_ph TO operations_cluster;
      GRANT operations_id TO operations_cluster;
      GRANT operations_hk TO operations_cluster;
      GRANT operations_vn TO operations_cluster;
      GRANT operations_tw TO operations_cluster;
      GRANT operations_my TO operations_cluster;
      GRANT operations_th TO operations_cluster;
      SHOW DATABASES;
      Database
      information_schema
      test
      SET ROLE operations_cluster;
      SHOW DATABASES;
      Database
      bob_live_hk
      bob_live_id
      bob_live_my
      bob_live_ph
      bob_live_sg
      bob_live_th
      bob_live_tw
      bob_live_vn
      information_schema
      oms_live_hk
      oms_live_id
      oms_live_my
      oms_live_ph
      oms_live_sg
      oms_live_th
      oms_live_tw
      oms_live_vn
      test
      SELECT COUNT(1) FROM oms_live_sg.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_sg.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_sg.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_sg.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_ph.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_ph.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_ph.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_ph.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_id.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_id.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_id.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_id.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_hk.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_hk.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_hk.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_hk.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_vn.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_vn.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_vn.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_vn.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_tw.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_tw.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_tw.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_tw.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_my.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_my.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_my.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_my.d;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_th.a;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_th.b;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_th.c;
      COUNT(1)
      0
      SELECT COUNT(1) FROM oms_live_th.d;
      COUNT(1)
      0
      FLUSH PRIVILEGES;
      SHOW DATABASES;
      Database
      information_schema
      test
      SET ROLE operations_cluster;
      SHOW DATABASES;
      Database
      bob_live_hk
      bob_live_id
      bob_live_my
      bob_live_ph
      bob_live_th
      bob_live_tw
      bob_live_vn
      information_schema
      oms_live_hk
      oms_live_id
      oms_live_my
      oms_live_ph
      oms_live_th
      oms_live_tw
      oms_live_vn
      test
      SELECT COUNT(1) FROM oms_live_sg.a;
      main.XXX 'innodb_plugin'                 [ fail ]
              Test ended at 2017-03-26 22:06:48
       
      CURRENT_TEST: main.XXX
      mysqltest: At line 275: query 'SELECT COUNT(1) FROM oms_live_sg.a' failed: 1142: SELECT command denied to user 'u'@'localhost' for table 'a'
       
       - saving '/home/pashev/tmp/server/build/mysql-test/var/log/main.XXX-innodb_plugin/' to '/home/pashev/tmp/server/build/mysql-test/var/log/main.XXX-innodb_plugin/'
       
      Only  1  of 2 completed.
      --------------------------------------------------------------------------
      The servers were restarted 0 times
      Spent 0.000 of 6 seconds executing testcases
       
      Failure: Failed 1/1 tests, 0.00% were successful.
      

        Attachments

          Activity

            People

            Assignee:
            cvicentiu Vicențiu Ciorbaru
            Reporter:
            ip1981 Igor Pashev
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: