Debian Codegraph @

mysql-test/suite/group_replication/t/gr_roles.test

raw
1 ###############################################################################
2 # Validate if granting privileges with 'Roles' works with group_replication
3
4 # 0.Test uses 2 servers: server1, server2
5 # 1.Verify that ROLES are replicated successfully.
6 # 1.1 Create role, user on server1.
7 # 1.2 Grant privileges to roles and grant roles to users.
8 # 2.Verify execution of GR commands is successful by the user when
9 # different privileges are granted by roles.
10 # 2.1 Error as user does not have privilege to execute command.
11 # 2.2 Assign SYSTEM_VARIABLES_ADMIN privilege to user by enabling the role
12 # and test that command is executed successfully.
13 # 3.Verify that ROLES on replication users used in 'CHANGE MASTER TO' work
14 # as expected for GR.
15 # 3.1 Error in START GR as master_user used in CHM does not have privilege.
16 # 3.2 Assign REPLICATION_SLAVE privilege to user by enabling the role and
17 # test that start group_replication succeeds.
18 # 4.Verify that Activating the role by 'ALTER USER' is successful on
19 # all GR members.
20 # 5.Clean Up.
21 ###############################################################################
22 --source include/have_group_replication_xcom_communication_stack.inc
23 --source include/not_have_privilege_checks_user.inc
24 --source include/have_group_replication_plugin.inc
25 --let $rpl_server_count= 2
26 --let $rpl_skip_group_replication_start= 1
27 --source include/group_replication.inc
28
29 # Test 1 : ROLES are replicated successfully
30
31 # Create role, user on server1
32 CREATE ROLE 'gr_user','gr_recovery_user';
33 # Grant privileges to roles and grant roles to users.
34 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'gr_user';
35 GRANT REPLICATION SLAVE ON *.* TO 'gr_recovery_user';
36 CREATE USER 'alpha' IDENTIFIED BY 'alpha';
37 GRANT gr_user TO alpha;
38
39 --echo # Check that 'gr_user' role is assigned to 'alpha' user
40 --let $wait_condition=SELECT FROM_USER='gr_user' FROM mysql.role_edges WHERE TO_USER='alpha'
41 --source include/wait_condition_or_abort.inc
42
43 # Test 2 : Verify execution of GR commands is successful by the user when
44 # different privileges are granted by roles.
45
46 # 'alpha' user is not granted any privileges, it will be denied to set GR related
47 # variables or START GROUP_REPLICATION
48 --echo [connection alpha]
49 connect(alpha_con, localhost, alpha, alpha);
50 --disable_query_log
51 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
52 --eval SET GLOBAL group_replication_group_name= "$group_replication_group_name"
53 --enable_query_log
54
55 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
56 SET GLOBAL group_replication_bootstrap_group= 1;
57
58 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
59 START GROUP_REPLICATION;
60
61 --disconnect alpha_con
62 --let $rpl_connection_name= server1
63 --source include/rpl_connection.inc
64
65 # Enable 'gr_user' ROLE for USER 'alpha'
66 ALTER USER alpha DEFAULT ROLE 'gr_user';
67
68 --echo # Check that 'gr_user' role is enabled for 'alpha' user
69 --let $wait_condition= SELECT DEFAULT_ROLE_USER='gr_user' FROM mysql.default_roles WHERE USER='alpha'
70 --source include/wait_condition_or_abort.inc
71
72 --let $wait_condition= SELECT FROM_USER='gr_user' FROM mysql.role_edges WHERE TO_USER='alpha';
73 --source include/wait_condition_or_abort.inc
74
75 --echo [connection alpha]
76 connect(alpha_con, localhost, alpha, alpha,*NO-ONE*);
77 SELECT CURRENT_ROLE();
78
79 # Execution of commands should be successful.
80 --replace_result $group_replication_group_name GROUP_REPLICATION_GROUP_NAME
81 --eval SET GLOBAL group_replication_group_name= "$group_replication_group_name"
82 SET GLOBAL group_replication_bootstrap_group= 1;
83
84 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
85 START GROUP_REPLICATION;
86
87 --disconnect alpha_con
88
89 --let $rpl_connection_name= server1
90 --source include/rpl_connection.inc
91
92 GRANT GROUP_REPLICATION_ADMIN ON *.* TO 'gr_user';
93
94 --echo [connection alpha]
95 connect(alpha_con, localhost, alpha, alpha,*NO-ONE*);
96 START GROUP_REPLICATION;
97 SET GLOBAL group_replication_bootstrap_group= 0;
98
99 --disconnect alpha_con
100
101 --let $rpl_connection_name= server1
102 --source include/rpl_connection.inc
103 --echo # Wait until server1 is ONLINE
104 --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_group_members WHERE member_state='ONLINE'
105 --source include/wait_condition_or_abort.inc
106
107 # Test 3 : Verify that ROLES on replication users used in 'CHANGE MASTER TO' work
108 # as expected for GR.
109 --let $rpl_connection_name= server2
110 --source include/rpl_connection.inc
111
112 SET @group_replication_recovery_retry_count_save= @@GLOBAL.group_replication_recovery_retry_count;
113 SET @group_replication_recovery_reconnect_interval_save= @@GLOBAL.group_replication_recovery_reconnect_interval;
114
115 set sql_log_bin=0;
116 call mtr.add_suppression(".*Replica I/O for channel 'group_replication_recovery': Source command COM_REGISTER_REPLICA failed.*");
117 call mtr.add_suppression(".*Replica I/O thread couldn't register on source");
118 call mtr.add_suppression("Maximum number of retries when*");
119 call mtr.add_suppression("Fatal error during the incremental recovery process of Group Replication. The server will leave the group.");
120 call mtr.add_suppression("Skipping leave operation: concurrent attempt to leave the group is on-going.");
121 call mtr.add_suppression("There was an error when connecting to the donor*");
122 call mtr.add_suppression("For details please check performance_schema.replication_connection_status table and error log messages of Replica I/O for channel group_replication_recovery.");
123 call mtr.add_suppression("The server was automatically set into read only mode after an error was detected.");
124 set sql_log_bin=1;
125
126 # Execute CHM where master_user='alpha'
127 --replace_result $group_replication_group_name GROUP_REPLICATION_GROUP_NAME
128 --eval SET GLOBAL group_replication_group_name= "$group_replication_group_name"
129
130 SET GLOBAL group_replication_recovery_retry_count= 1;
131 --echo # Set the reconnect interval to one. To prevent timeout on recovery.
132 SET GLOBAL group_replication_recovery_reconnect_interval= 1;
133
134 CHANGE REPLICATION SOURCE TO SOURCE_USER='alpha', SOURCE_PASSWORD='alpha' FOR CHANNEL 'group_replication_recovery';
135
136 # START GR on server2 will fail
137 --let $group_replication_start_member_state= ERROR
138 --source include/start_group_replication.inc
139
140 # Error ER_REPLICA_SOURCE_COM_FAILURE : as USER 'alpha' doesn't have
141 # REPLICATION_SLAVE_ADMIN privilege
142 --let $grep_pattern= Source command COM_REGISTER_REPLICA failed
143 --let $grep_file= $MYSQLTEST_VARDIR/log/mysqld.2.err
144 --let $grep_output= boolean
145 --source include/grep_pattern.inc
146
147 --let $grep_pattern= Replica I/O thread couldn't register on source
148 --let $grep_file= $MYSQLTEST_VARDIR/log/mysqld.2.err
149 --let $grep_output= boolean
150 --source include/grep_pattern.inc
151
152 --source include/stop_group_replication.inc
153
154 # Test 4 : Verify that Activating the role by 'ALTER USER' is successful on
155 # all GR members.
156 --let $rpl_connection_name= server1
157 --source include/rpl_connection.inc
158 # Enable 'REPLICATION_SLAVE_ADMIN' for 'alpha'
159 GRANT gr_recovery_user TO alpha;
160 ALTER USER alpha DEFAULT ROLE ALL;
161
162 --echo # Check that all roles assigned to 'alpha' user are enabled
163 SELECT DEFAULT_ROLE_USER FROM mysql.default_roles WHERE USER='alpha';
164 SELECT FROM_USER FROM mysql.role_edges WHERE TO_USER='alpha';
165
166 --echo [connection alpha]
167 connect(alpha_con, localhost, alpha, alpha,*NO-ONE*);
168 SELECT CURRENT_ROLE();
169 SHOW GRANTS;
170
171 # Creation of new database fails
172 --error ER_DBACCESS_DENIED_ERROR
173 CREATE DATABASE newtest;
174
175 --disconnect alpha_con
176
177 --let $rpl_connection_name= server2
178 --source include/rpl_connection.inc
179 --let $group_replication_start_member_state= ONLINE
180 --source include/start_group_replication.inc
181
182 GRANT ALL ON newtest.* TO gr_recovery_user;
183
184 --echo [connection alpha]
185 connect(alpha_con, localhost, alpha, alpha,*NO-ONE*,$SLAVE_MYPORT,$SLAVE_MYSOCK);
186
187 # Replicate data
188 CREATE DATABASE newtest;
189 use newtest;
190 CREATE TABLE t1(a INT PRIMARY KEY);
191 INSERT INTO t1 VALUES (1),(2);
192 UPDATE t1 SET a=4 WHERE a=1;
193 DELETE FROM t1;
194
195 # Cleanup
196 --let $rpl_connection_name= server2
197 --source include/rpl_connection.inc
198
199 SET GLOBAL group_replication_recovery_retry_count= @group_replication_recovery_retry_count_save;
200 SET GLOBAL group_replication_recovery_reconnect_interval= @group_replication_recovery_reconnect_interval_save;
201
202 DROP TABLE newtest.t1;
203 DROP DATABASE newtest;
204 DROP ROLE gr_user, gr_recovery_user;
205 DROP USER alpha;
206
207 --source include/group_replication_end.inc