MDEV-5771 Privileges acquired via roles depend on the order of granting

GRANT ROLE: don't forget to propagate privileges from granted role to a grantee

3 files changed, 87 insertions, 23 deletions

diff --git a/mysql-test/suite/roles/grant-5771.result b/mysql-test/suite/roles/grant-5771.result
new file mode 100644
index 00000000000..4921641f3a5
--- /dev/null
+++ b/mysql-test/suite/roles/grant-5771.result
@@ -0,0 +1,33 @@
+create database mysqltest1;
+create database mysqltest2;
+create role r1, r2;
+grant all on mysqltest1.* to r1;
+grant all on mysqltest2.* to r2;
+grant r1 to r2;
+grant r2 to foo@localhost;
+select current_user;
+current_user
+foo@localhost
+show tables in mysqltest1;
+ERROR 42000: Access denied for user 'foo'@'localhost' to database 'mysqltest1'
+show tables in mysqltest2;
+ERROR 42000: Access denied for user 'foo'@'localhost' to database 'mysqltest2'
+set role r2;
+show tables in mysqltest1;
+Tables_in_mysqltest1
+show tables in mysqltest2;
+Tables_in_mysqltest2
+show grants;
+Grants for foo@localhost
+GRANT r2 TO 'foo'@'localhost'
+GRANT USAGE ON *.* TO 'foo'@'localhost'
+GRANT r1 TO 'r2'
+GRANT USAGE ON *.* TO 'r2'
+GRANT ALL PRIVILEGES ON `mysqltest2`.* TO 'r2'
+GRANT USAGE ON *.* TO 'r1'
+GRANT ALL PRIVILEGES ON `mysqltest1`.* TO 'r1'
+drop user foo@localhost;
+drop role r1;
+drop role r2;
+drop database mysqltest1;
+drop database mysqltest2;
diff --git a/mysql-test/suite/roles/grant-5771.test b/mysql-test/suite/roles/grant-5771.test
new file mode 100644
index 00000000000..84c625a6067
--- /dev/null
+++ b/mysql-test/suite/roles/grant-5771.test
@@ -0,0 +1,31 @@
+#
+# MDEV-5771 Privileges acquired via roles depend on the order of granting
+#
+--source include/not_embedded.inc
+
+create database mysqltest1;
+create database mysqltest2;
+
+create role r1, r2;
+grant all on mysqltest1.* to r1;
+grant all on mysqltest2.* to r2;
+grant r1 to r2;
+grant r2 to foo@localhost;
+
+--connect (foo,localhost,foo,,)
+select current_user;
+--error ER_DBACCESS_DENIED_ERROR
+show tables in mysqltest1;
+--error ER_DBACCESS_DENIED_ERROR
+show tables in mysqltest2;
+set role r2;
+show tables in mysqltest1;
+show tables in mysqltest2;
+show grants;
+
+connection default;
+drop user foo@localhost;
+drop role r1;
+drop role r2;
+drop database mysqltest1;
+drop database mysqltest2;
diff --git a/mysql-test/suite/roles/recursive_dbug.result b/mysql-test/suite/roles/recursive_dbug.result
index 268ef47232e..1d857a5c888 100644
--- a/mysql-test/suite/roles/recursive_dbug.result
+++ b/mysql-test/suite/roles/recursive_dbug.result
@@ -46,7 +46,7 @@ role9	role6	NO
 role9	role7	NO
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	0
+Debug_role_merges_global	11
 Debug_role_merges_db	0
 Debug_role_merges_table	0
 Debug_role_merges_column	0
@@ -54,7 +54,7 @@ Debug_role_merges_routine	0
 grant select on *.* to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	8
+Debug_role_merges_global	19
 Debug_role_merges_db	0
 Debug_role_merges_table	0
 Debug_role_merges_column	0
@@ -102,7 +102,7 @@ role9
 revoke select on *.* from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	0
 Debug_role_merges_table	0
 Debug_role_merges_column	0
@@ -118,7 +118,7 @@ set role none;
 grant select on mysql.* to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	8
 Debug_role_merges_table	0
 Debug_role_merges_column	0
@@ -156,7 +156,7 @@ GRANT role9 TO 'role10'
 revoke select on mysql.* from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	0
 Debug_role_merges_column	0
@@ -167,7 +167,7 @@ set role none;
 grant select on mysql.roles_mapping to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	8
 Debug_role_merges_column	0
@@ -205,7 +205,7 @@ GRANT role9 TO 'role10'
 revoke select on mysql.roles_mapping from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	16
 Debug_role_merges_column	0
@@ -216,7 +216,7 @@ set role none;
 grant select(User) on mysql.roles_mapping to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	24
 Debug_role_merges_column	8
@@ -256,7 +256,7 @@ GRANT role9 TO 'role10'
 grant select(Host) on mysql.roles_mapping to role3;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	30
 Debug_role_merges_column	14
@@ -294,7 +294,7 @@ GRANT role9 TO 'role10'
 revoke select(User) on mysql.roles_mapping from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	38
 Debug_role_merges_column	22
@@ -307,7 +307,7 @@ count(concat(Host))
 revoke select(Host) on mysql.roles_mapping from role3;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	44
 Debug_role_merges_column	28
@@ -320,7 +320,7 @@ create function fn1() returns char(10) return "fn1";
 grant execute on procedure test.pr1 to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	44
 Debug_role_merges_column	28
@@ -336,7 +336,7 @@ ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.
 grant execute on function test.fn1 to role5;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	44
 Debug_role_merges_column	28
@@ -347,7 +347,7 @@ fn1
 revoke execute on procedure test.pr1 from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	44
 Debug_role_merges_column	28
@@ -360,7 +360,7 @@ fn1
 revoke execute on function test.fn1 from role5;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	44
 Debug_role_merges_column	28
@@ -373,7 +373,7 @@ drop function fn1;
 grant select on mysql.roles_mapping to role3;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	50
 Debug_role_merges_column	28
@@ -381,7 +381,7 @@ Debug_role_merges_routine	26
 grant select on mysql.roles_mapping to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	53
 Debug_role_merges_column	28
@@ -389,7 +389,7 @@ Debug_role_merges_routine	26
 revoke select on mysql.roles_mapping from role3;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	54
 Debug_role_merges_column	28
@@ -397,7 +397,7 @@ Debug_role_merges_routine	26
 revoke select on mysql.roles_mapping from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	16
 Debug_role_merges_table	62
 Debug_role_merges_column	28
@@ -405,7 +405,7 @@ Debug_role_merges_routine	26
 grant select on mysql.* to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	24
 Debug_role_merges_table	62
 Debug_role_merges_column	28
@@ -413,7 +413,7 @@ Debug_role_merges_routine	26
 grant select on test.* to role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	32
 Debug_role_merges_table	62
 Debug_role_merges_column	28
@@ -421,7 +421,7 @@ Debug_role_merges_routine	26
 revoke select on mysql.* from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	40
 Debug_role_merges_table	62
 Debug_role_merges_column	28
@@ -429,7 +429,7 @@ Debug_role_merges_routine	26
 revoke select on test.* from role1;
 show status like 'debug%';
 Variable_name	Value
-Debug_role_merges_global	16
+Debug_role_merges_global	27
 Debug_role_merges_db	48
 Debug_role_merges_table	62
 Debug_role_merges_column	28