[MDEV-12366] FLUSH PRIVILEGES can break hierarchy of roles - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.21, 10.1.22, 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.0.34, 10.1.30, 10.2.12
Component/s: Authentication and Privilege System
Labels:
None
Environment:
Linux 4.9.0-2-amd64 #1 SMP Debian 4.9.13-1 (2017-02-27) x86_64 GNU/Linux

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)

SELECT COUNT(1) FROM oms_live_sg.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_sg.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_sg.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_ph.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_ph.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_ph.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_ph.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_id.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_id.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_id.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_id.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_hk.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_hk.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_hk.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_hk.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_vn.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_vn.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_vn.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_vn.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_tw.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_tw.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_tw.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_tw.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_my.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_my.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_my.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_my.d;

COUNT(1)

SELECT COUNT(1) FROM oms_live_th.a;

COUNT(1)

SELECT COUNT(1) FROM oms_live_th.b;

COUNT(1)

SELECT COUNT(1) FROM oms_live_th.c;

COUNT(1)

SELECT COUNT(1) FROM oms_live_th.d;

COUNT(1)

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

flush roles.test
11 kB
2017-03-26 19:09

Activity

Ascending order - Click to sort in descending order

Igor Pashev created issue - 2017-03-26 19:11

Igor Pashev made changes - 2017-03-26 19:19

Field	Original Value	New Value
Description	{{FLUSH PRIVILEGES}} or server restart can break hierarchy of roles. IOW reading (or writing?) 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: {noformat} 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. {noformat}	{{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: {noformat} 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. {noformat}

Igor Pashev made changes - 2017-03-28 15:52

Comment

[ From {{sql/sql_acl.cc}}:
{noformat}
/*
  this helper is used when building role_grants and parent_grantee arrays
  from scratch.

  this happens either on initial loading of data from tables, in acl_load().
  or in rebuild_role_grants after acl_role_reset_role_arrays().
*/
static bool add_role_user_mapping(const char *uname, const char *hname,
                                  const char *rname)
{noformat}

But {{rebuild_role_grants()}} is not called from {{acl_load()}}. So proposed patch:

{noformat}
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index cb4c3cb1049..742b3591774 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -1661,6 +1661,7 @@ static bool acl_load(THD *thd, TABLE_LIST *tables)
   }

   init_check_host();
+ rebuild_role_grants();

   initialized=1;
   return_val= FALSE;
{noformat}
]

Igor Pashev added a comment - 2017-03-30 08:53 - edited

It's kinda heisenbug depending on number/names of (sub)roles. When it happens, I observe merge_role_privileges() with 'x_sg' for 'operations_sg' called after 'operations_sg' for 'operations_cluster'.

Igor Pashev added a comment - 2017-03-30 08:53 - edited It's kinda heisenbug depending on number/names of (sub)roles. When it happens, I observe merge_role_privileges() with 'x_sg' for 'operations_sg' called after 'operations_sg' for 'operations_cluster'.

Igor Pashev added a comment - 2017-03-30 09:01 - edited

E. g. no issue:

...

T@7    : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@7    : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@7    : | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster'

T@9    : | | query: FLUSH PRIVILEGES

T@9    : | | | | | | | | | info: Query: FLUSH PRIVILEGES

T@9    : | | | | | | | | | info: Query: FLUSH PRIVILEGES

T@9    : | | | | | | wtf: merge_role_privileges: role 'a_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'b_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'c_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'd_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'a_vn', grantee 'operations_vn'

T@9    : | | | | | | wtf: merge_role_privileges: role 'b_vn', grantee 'operations_vn'

T@9    : | | | | | | wtf: merge_role_privileges: role 'c_vn', grantee 'operations_vn'

T@9    : | | | | | | wtf: merge_role_privileges: role 'd_vn', grantee 'operations_vn'

T@9    : | | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@9    : | | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster'

T@9    : | | | | | | | | info: Query: FLUSH PRIVILEGES

T@11   : | | | | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@11   : | | | | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster'

T@11   : | | | | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster'

...

Access denied (mysqltest: At line 102: query 'SELECT COUNT(1) FROM bob_live_tw.a' failed: 1142: SELECT command denied to user 'u'@'localhost' for table 'a'):

...

T@7    : | | | | | | wtf: merge_role_privileges: role 'd_tw', grantee 'operations_tw'

T@7    : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@7    : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@7    : | | | | | wtf: merge_role_privileges: role 'operations_tw', grantee 'operations_cluster'

T@9    : | | query: FLUSH PRIVILEGES

T@9    : | | | | | | | | | info: Query: FLUSH PRIVILEGES

T@9    : | | | | | | | | | info: Query: FLUSH PRIVILEGES

T@9    : | | | | | | wtf: merge_role_privileges: role 'a_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'b_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'c_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'd_sg', grantee 'operations_sg'

T@9    : | | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster'

T@9    : | | | | | | wtf: merge_role_privileges: role 'operations_tw', grantee 'operations_cluster'

T@9    : | | | | | | wtf: merge_role_privileges: role 'a_tw', grantee 'operations_tw'

T@9    : | | | | | | wtf: merge_role_privileges: role 'b_tw', grantee 'operations_tw'

T@9    : | | | | | | wtf: merge_role_privileges: role 'c_tw', grantee 'operations_tw'

T@9    : | | | | | | wtf: merge_role_privileges: role 'd_tw', grantee 'operations_tw'

T@9    : | | | | | | | | info: Query: FLUSH PRIVILEGES

...

Igor Pashev added a comment - 2017-03-30 09:01 - edited E. g. no issue: ... T@7 : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@7 : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@7 : | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster' T@9 : | | query: FLUSH PRIVILEGES T@9 : | | | | | | | | | info: Query: FLUSH PRIVILEGES T@9 : | | | | | | | | | info: Query: FLUSH PRIVILEGES T@9 : | | | | | | wtf: merge_role_privileges: role 'a_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'b_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'c_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'd_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'a_vn', grantee 'operations_vn' T@9 : | | | | | | wtf: merge_role_privileges: role 'b_vn', grantee 'operations_vn' T@9 : | | | | | | wtf: merge_role_privileges: role 'c_vn', grantee 'operations_vn' T@9 : | | | | | | wtf: merge_role_privileges: role 'd_vn', grantee 'operations_vn' T@9 : | | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@9 : | | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster' T@9 : | | | | | | | | info: Query: FLUSH PRIVILEGES T@11 : | | | | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@11 : | | | | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster' T@11 : | | | | | | | | wtf: merge_role_privileges: role 'operations_vn', grantee 'operations_cluster' ... Access denied ( mysqltest: At line 102: query 'SELECT COUNT(1) FROM bob_live_tw.a' failed: 1142: SELECT command denied to user 'u'@'localhost' for table 'a' ): ... T@7 : | | | | | | wtf: merge_role_privileges: role 'd_tw', grantee 'operations_tw' T@7 : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@7 : | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@7 : | | | | | wtf: merge_role_privileges: role 'operations_tw', grantee 'operations_cluster' T@9 : | | query: FLUSH PRIVILEGES T@9 : | | | | | | | | | info: Query: FLUSH PRIVILEGES T@9 : | | | | | | | | | info: Query: FLUSH PRIVILEGES T@9 : | | | | | | wtf: merge_role_privileges: role 'a_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'b_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'c_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'd_sg', grantee 'operations_sg' T@9 : | | | | | | wtf: merge_role_privileges: role 'operations_sg', grantee 'operations_cluster' T@9 : | | | | | | wtf: merge_role_privileges: role 'operations_tw', grantee 'operations_cluster' T@9 : | | | | | | wtf: merge_role_privileges: role 'a_tw', grantee 'operations_tw' T@9 : | | | | | | wtf: merge_role_privileges: role 'b_tw', grantee 'operations_tw' T@9 : | | | | | | wtf: merge_role_privileges: role 'c_tw', grantee 'operations_tw' T@9 : | | | | | | wtf: merge_role_privileges: role 'd_tw', grantee 'operations_tw' T@9 : | | | | | | | | info: Query: FLUSH PRIVILEGES ...

Igor Pashev added a comment - 2017-04-02 15:24 - edited

I think we need traverse_role_graph_up(role, &data, init_role_for_merging, count_subgraph_nodes) before traverse_role_graph_up(role, &data, NULL, merge_role_privileges);, and it would become propagate_role_grants:

diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc

index cb4c3cb1049..bf6a7b847f5 100644

--- a/sql/sql_acl.cc

+++ b/sql/sql_acl.cc

@@ -6754,9 +6754,7 @@ static my_bool role_propagate_grants_action(void *ptr,

   if (role->counter)

     return 0;

-  mysql_mutex_assert_owner(&acl_cache->lock);

-  PRIVS_TO_MERGE data= { PRIVS_TO_MERGE::ALL, 0, 0 };

-  traverse_role_graph_up(role, &data, NULL, merge_role_privileges);

+  propagate_role_grants(role, PRIVS_TO_MERGE::ALL, NULL, NULL);

   return 0;

This solves the problem, but looks dirty.

Igor Pashev added a comment - 2017-04-02 15:24 - edited I think we need traverse_role_graph_up(role, &data, init_role_for_merging, count_subgraph_nodes) before traverse_role_graph_up(role, &data, NULL, merge_role_privileges); , and it would become propagate_role_grants : diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index cb4c3cb1049..bf6a7b847f5 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -6754,9 +6754,7 @@ static my_bool role_propagate_grants_action(void *ptr, if (role->counter) return 0; - mysql_mutex_assert_owner(&acl_cache->lock); - PRIVS_TO_MERGE data= { PRIVS_TO_MERGE::ALL, 0, 0 }; - traverse_role_graph_up(role, &data, NULL, merge_role_privileges); + propagate_role_grants(role, PRIVS_TO_MERGE::ALL, NULL, NULL); return 0; } This solves the problem, but looks dirty.

Elena Stepanova made changes - 2017-04-03 22:04

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2017-04-03 22:05

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Assignee		Vicentiu Ciorbaru [ cvicentiu ]

Sergei Golubchik made changes - 2017-12-12 19:29

Sprint

10.1.30 [ 215 ]

Sergei Golubchik made changes - 2017-12-12 19:29

Rank

Ranked lower

Vicențiu Ciorbaru added a comment - 2017-12-17 16:22

I've investigated this issue. Thank you for the test case, it was very useful for figuring out the root cause.

Whenever we call merge_role_privileges on a role, we make use of the role->counter variable to check if all it's children have had their privileges merged. Only if all children have had their privileges merged, do we update the privileges on parent. This is done to prevent extra work. The same idea is employed during flush privileges. You only begin merging from "leaf" roles. The recursive calls will merge their parents at some point. A problem arises when we try to "re-merge" a parent. Take the following graph:

     A (0)  ----  C (2) ---- D (2)  ---- USER

                 /          /

     B (0)  ----/          /

     E (0) --------------/

In parentheses we have the "counter" value right before we start to iterate through the roles hash and propagate values. It represents the number of roles granted to the current role. The order in which we iterate through the roles hash is alphabetical.

First merge A, which leads to decreasing the counter for C to 1. Since C is not 0, we don't proceed with merging into C.
Second we merge B, which leads to decreasing the counter for C to 0. Now we proceed with merging into C. This leads to reducing the counter for D to 1 as part of C merge process.
Third as we iterate through the hash, we see that C has counter 0, thus we start the merge process again. This leads to reducing the counter for D to 0! We then attempt to merge D.
Fourth we start merging E. When E sees D as it's parent (according to the code) it attempts to reduce D's counter, which leads to overflow. Now D's counter is a very large number,
thus E's privileges are not forwarded to D yet.

To correct this behavior we must make sure to only start merging from initial leaf nodes.

Vicențiu Ciorbaru added a comment - 2017-12-17 16:22 I've investigated this issue. Thank you for the test case, it was very useful for figuring out the root cause. Whenever we call merge_role_privileges on a role, we make use of the role->counter variable to check if all it's children have had their privileges merged. Only if all children have had their privileges merged, do we update the privileges on parent. This is done to prevent extra work. The same idea is employed during flush privileges. You only begin merging from "leaf" roles. The recursive calls will merge their parents at some point. A problem arises when we try to "re-merge" a parent. Take the following graph: A (0) ---- C (2) ---- D (2) ---- USER / / B (0) ----/ / / E (0) --------------/ In parentheses we have the "counter" value right before we start to iterate through the roles hash and propagate values. It represents the number of roles granted to the current role. The order in which we iterate through the roles hash is alphabetical. First merge A, which leads to decreasing the counter for C to 1. Since C is not 0, we don't proceed with merging into C. Second we merge B, which leads to decreasing the counter for C to 0. Now we proceed with merging into C. This leads to reducing the counter for D to 1 as part of C merge process. Third as we iterate through the hash, we see that C has counter 0, thus we start the merge process again . This leads to reducing the counter for D to 0! We then attempt to merge D. Fourth we start merging E. When E sees D as it's parent (according to the code) it attempts to reduce D's counter, which leads to overflow. Now D's counter is a very large number, thus E's privileges are not forwarded to D yet. To correct this behavior we must make sure to only start merging from initial leaf nodes.

Vicențiu Ciorbaru made changes - 2017-12-17 16:40

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Vicențiu Ciorbaru added a comment - 2017-12-20 12:42

Fixed with:

https://github.com/mariadb/server/commit/be758322e299f1dc80898efcf2e71c4c8f4b3f4b

Vicențiu Ciorbaru added a comment - 2017-12-20 12:42 Fixed with: https://github.com/mariadb/server/commit/be758322e299f1dc80898efcf2e71c4c8f4b3f4b

Vicențiu Ciorbaru made changes - 2017-12-20 12:42

Fix Version/s		10.0.34 [ 22613 ]
Fix Version/s		10.1.30 [ 22637 ]
Fix Version/s		10.2.12 [ 22810 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80127 ]

MariaDB v4 [ 151864 ]

People

Assignee:: Vicențiu Ciorbaru

Reporter:: Igor Pashev

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-03-26 19:11

Updated:: 2017-12-21 16:10

Resolved:: 2017-12-20 12:42

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration