[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}
]

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 made changes - 2017-12-17 16:40

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

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