Added a more complicated test for recursive role grants.

1 files changed, 132 insertions, 0 deletions

diff --git a/mysql-test/r/acl_roles_set_role-multiple-role.result b/mysql-test/r/acl_roles_set_role-multiple-role.result
new file mode 100644
index 00000000000..9aeb5288ce8
--- /dev/null
+++ b/mysql-test/r/acl_roles_set_role-multiple-role.result
@@ -0,0 +1,132 @@
+create user 'test_user'@'localhost';
+create user 'r_sel'@'';
+create user 'r_ins'@'';
+create user 'r_upd'@'';
+create user 'r_del'@'';
+create user 'r_crt'@'';
+create user 'r_drp'@'';
+create user 'r_rld'@'';
+update mysql.user set Select_priv='Y' where user like 'r_sel';
+update mysql.user set Insert_priv='Y' where user like 'r_ins';
+update mysql.user set Update_priv='Y' where user like 'r_upd';
+update mysql.user set Delete_priv='Y' where user like 'r_del';
+update mysql.user set Create_priv='Y' where user like 'r_crt';
+update mysql.user set Drop_priv  ='Y' where user like 'r_drp';
+update mysql.user set Reload_priv='Y' where user like 'r_rld';
+update mysql.user set is_role='Y' where user like 'r\_%';
+select * from mysql.user where user='r_sel';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_sel		Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+select * from mysql.user where user='r_ins';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_ins		N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+select * from mysql.user where user='r_upd';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_upd		N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+select * from mysql.user where user='r_del';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_del		N	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+select * from mysql.user where user='r_crt';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_crt		N	N	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+select * from mysql.user where user='r_drp';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_drp		N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+select * from mysql.user where user='r_rld';
+Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	is_role
+	r_rld		N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					0	0	0	0			Y
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_sel');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_ins');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_upd');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_del');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_crt');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_drp');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('localhost',
+'test_user',
+'r_rld');
+flush privileges;
+select * from mysql.roles_mapping;
+ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping'
+show grants;
+Grants for test_user@localhost
+GRANT USAGE ON *.* TO 'test_user'@'localhost'
+set role r_sel;
+show grants;
+Grants for test_user@localhost
+GRANT USAGE ON *.* TO 'test_user'@'localhost'
+select * from mysql.roles_mapping;
+HostFk	UserFk	RoleFk
+localhost	test_user	r_sel
+localhost	test_user	r_ins
+localhost	test_user	r_upd
+localhost	test_user	r_del
+localhost	test_user	r_crt
+localhost	test_user	r_drp
+localhost	test_user	r_rld
+set role r_ins;
+show grants;
+Grants for test_user@localhost
+GRANT USAGE ON *.* TO 'test_user'@'localhost'
+select * from mysql.roles_mapping;
+ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping'
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
+'r_sel',
+'r_rld');
+flush privileges;
+ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
+set role r_rld;
+flush privileges;
+set role r_sel;
+flush privileges;
+set role none;
+flush privileges;
+ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
+set role r_ins;
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
+'r_sel',
+'r_upd');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
+'r_sel',
+'r_del');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
+'r_sel',
+'r_crt');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
+'r_sel',
+'r_drp');
+insert into mysql.roles_mapping (HostFk, UserFk, RoleFk) values ('',
+'r_del',
+'r_ins');
+set role r_rld;
+flush privileges;
+set role r_sel;
+update mysql.roles_mapping set RoleFk='r_ins' where RoleFk='r_ins_wrong';
+flush privileges;
+set role r_sel;
+create table mysql.random_test_table (id INT);
+insert into mysql.random_test_table values (1);
+select * from mysql.random_test_table;
+id
+1
+delete from mysql.roles_mapping where RoleFk='r_ins';
+flush privileges;
+set role r_sel;
+insert into mysql.random_test_table values (1);
+ERROR 42000: INSERT command denied to user 'test_user'@'localhost' for table 'random_test_table'
+drop table mysql.random_test_table;
+delete from mysql.user where user like 'r\_%';
+delete from mysql.roles_mapping where RoleFk like 'r\_%';
+flush privileges;
+drop user 'test_user'@'localhost';