Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_stored_grants.test

raw
1 # This test case demonstrates the relationship between NDB distributed users
2 # and MySQL replication.
3 # Some rules:
4 # - Any ACL change executed on a binlogging server will be written to the
5 # binlog there. This includes changes to a local users on the binlogging
6 # server, as well as changes to NDB_STORED_USER users on that cluster.
7 # - Setting SQL_LOG_BIN=0 before a change should prevent it from being
8 # written to the binlog. This includes a change to an NDB stored user
9 # issued on a non-binlogging server.
10 # - Setting log_replica_updates=0 should prevent ACL changes from being
11 # propagated.
12 # - Every ACL change should be correctly tagged in binlogs with the
13 # server_id of its origin server.
14
15 #
16 # Cluster 1 Cluster 2
17 # --------- ---------
18 # A --> binlog --> C --> binlog ( log_replica_updates=0 )
19 # B D --> binlog ( log_replica_updates=1 )
20 #
21
22 --source include/have_ndb.inc
23 --let $rpl_topology= 1->3
24 --source include/rpl_init.inc
25
26 --connect(C, 127.0.0.1,root,,test,$MYSQL_C_PORT)
27
28 # Test (1): a user with NDB_STORED_USER is replicated to cluster 2.
29 # The user is created on server (A) and tested on servers (C) and (D).
30 # Tests 1C and 1D test server_id propogation and log_replica_updates.
31 # 1C: Check that the user is not written to (C)'s binlog.
32 # 1D: Check that the user is written to (D)'s binlog with an origin server_id
33 # matching (B).
34
35 # Test (2): a user with NDB_STORED_USER is replicated to cluster 2.
36 # The user is created on server (B) and tested on servers (A), (C) and (D).
37
38 # Test (3): a local user (i.e. without NDB_STORED_USER) created on (A)
39 # is replicated to (C) but not to (D)
40
41 # Test (4): a local user created on (B) is not replicated to cluster 2.
42
43 # Test (5): test sql_log_bin=0 on a binlogging server.
44 # On server (A), sql_log_bin is set to 0, and then a user with
45 # NDB_STORED_USER is created. Test to verify that the user has not been
46 # replicated to server (C), but exists on (A) and (B).
47
48 # Test (6): test sql_log_bin=0 on a non-binlogging server.
49 # On server (B), sql_log_bin is set to 0, and then a user with
50 # NDB_STORED_USER is created. Test to verify that the user has not been
51 # replicated to server (C), but exists on (A) and (B).
52
53 # Test (7) - Bug#33542052: Create user on (A) and test login from (C).
54 # Then grant ndb_stored_user, and test login from (B). The user has a
55 # randomly generated password, and the bug revolves around the possibility
56 # of the IDENTIFIED WITH hashed password being corrupted in transit.
57
58 connection default;
59 echo;
60 echo [Server A];
61 CREATE DATABASE auth_test_db;
62 USE auth_test_db;
63 CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb;
64
65 #
66 # Test (1): a user with NDB_STORED_USER is replicated to cluster 2.
67 # The user is created on server (A) and tested on servers (C) and (D).
68 # Tests 1C and 1D test server_id propogation and log_replica_updates.
69 # 1C: Check that the user is not written to (C)'s binlog.
70 # 1D: Check that the user is written to (D)'s binlog with an origin server_id
71 # matching (B).
72 #
73 connection default;
74 echo;
75 echo Test 1:;
76 echo [Server A];
77 CREATE USER 'ndb_u1'@'localhost' IDENTIFIED by 'pass1';
78 GRANT SELECT ON t1 TO 'ndb_u1'@'localhost';
79 GRANT NDB_STORED_USER ON *.* to 'ndb_u1'@'localhost';
80 INSERT INTO t1 VALUES(1,1);
81 # Wait for replication. Note that the final statement before
82 # sync_slave_with_master was just a normal DDL statement, not a schema change.
83 # This is done as a workaround for a known race condition.
84 sync_slave_with_master C;
85
86 connect(C_test_user,127.0.0.1,ndb_u1,pass1,auth_test_db,$MYSQL_C_PORT,);
87 connection C_test_user;
88 echo [Connected to server C as ndb_u1];
89 SELECT * FROM t1 ORDER BY pk;
90 disconnect C_test_user;
91
92 connect(D_test_user,127.0.0.1,ndb_u1,pass1,auth_test_db,$MYSQL_D_PORT,);
93 connection D_test_user;
94 echo [Connected to server D as ndb_u1];
95 SELECT * FROM t1 ORDER BY pk;
96 disconnect D_test_user;
97
98 connection C;
99 echo;
100 echo [Test 1C: Server C binlog events];
101 --echo ** Expect log_replica_updates=0, and no binlog events
102 SHOW VARIABLES LIKE 'log_replica_updates';
103 --source include/show_binlog_events.inc
104
105 connect(D_root,127.0.0.1,root,,,$MYSQL_D_PORT,);
106 connection D_root;
107 echo;
108 echo [Test 1D: Server D binlog events];
109 --echo ** Expect 4 Query events: 1 CREATE USER + 3 GRANTs.
110 --echo ** These must have server_id = 1.
111 --let $show_binlog_events_mask_columns= 1,2,5
112 --let $mask_user_password_events= 1
113 --let binlog_file= LAST
114 # The LIMIT clause reflects six header events plus one row that
115 # show_binlog_events.inc filters out for each of the 4 rows we
116 # are interested in.
117 --let $binlog_limit= 6,8
118 --source include/show_binlog_events.inc
119
120
121 #
122 # Test (2): a user with NDB_STORED_USER is replicated to cluster 2.
123 # The user is created on server (B) and tested on servers (A), (C) and (D).
124 #
125 connect(B_root,127.0.0.1,root,,auth_test_db,$MYSQL_B_PORT,);
126 connection B_root;
127 echo;
128 echo Test 2:;
129 echo [Server B];
130 CREATE USER 'ndb_u2'@'localhost' IDENTIFIED by 'pass2';
131 GRANT NDB_STORED_USER ON *.* to 'ndb_u2'@'localhost';
132 GRANT SELECT ON t1 TO 'ndb_u2'@'localhost';
133 INSERT INTO t1 VALUES(2,2);
134 let $wait_timeout= 1;
135 --source include/wait_for_ndb_committed_to_binlog.inc
136
137 connect(A_test_user,127.0.0.1,ndb_u2,pass2,auth_test_db,$MYSQL_A_PORT,);
138 connection A_test_user;
139 echo [Connected to Server A as ndb_u2];
140 SELECT * FROM t1 ORDER BY pk;
141 disconnect A_test_user;
142
143 connection default;
144 sync_slave_with_master C;
145
146 connect(C_test_user,127.0.0.1,ndb_u2,pass2,auth_test_db,$MYSQL_C_PORT,);
147 connection C_test_user;
148 echo [Connected to Server C as ndb_u2];
149 SELECT * FROM t1 ORDER BY pk;
150 disconnect C_test_user;
151
152 connect(D_test_user,127.0.0.1,ndb_u2,pass2,auth_test_db,$MYSQL_D_PORT,);
153 connection D_test_user;
154 echo [Connected to Server D as ndb_u2];
155 SELECT * FROM t1 ORDER BY pk;
156 disconnect D_test_user;
157
158
159 #
160 # Test (3): a local user (i.e. without NDB_STORED_USER) created on (A)
161 # is replicated to (C) but not to (D)
162 #
163 connection default;
164 echo;
165 echo Test 3:;
166 echo [Server A];
167 CREATE USER 'local_user_1'@'localhost' IDENTIFIED BY 'local1';
168 sync_slave_with_master C;
169
170 connect(C_test_user,127.0.0.1,local_user_1,local1,test,$MYSQL_C_PORT,);
171 connection C_test_user;
172 echo [Connected to Server C as local_user_1];
173 disconnect C_test_user;
174
175 connection D_root;
176 echo [Server D];
177 error ER_CANNOT_USER;
178 SHOW CREATE USER local_user_1@localhost;
179
180
181 #
182 # Test (4): a local user created on (B) is not replicated to the slave cluster.
183 #
184 connection B_root;
185 echo;
186 echo Test 4:;
187 echo [Server B];
188 CREATE USER local_user_2@b;
189
190 connection default;
191 echo [Server A];
192 sync_slave_with_master C;
193
194 connect(C_root,127.0.0.1,root,,test,$MYSQL_C_PORT,);
195 connection C_root;
196 echo [Server C];
197 error ER_CANNOT_USER;
198 SHOW CREATE USER local_user_2@b;
199
200
201 # Test (5): On server (A), sql_log_bin is set to 0, and then a user with
202 # NDB_STORED_USER is created. Test to verify that the user has not been
203 # replicated to server (C), but exists on (A) and (B).
204 #
205 connection default;
206 echo;
207 echo Test 5:;
208 echo [Server A];
209 SET sql_log_bin=0;
210 CREATE USER 'ndb_u5'@'localhost' IDENTIFIED by 'pass5';
211 GRANT NDB_STORED_USER ON *.* to 'ndb_u5'@'localhost';
212 GRANT SELECT ON t1 TO 'ndb_u5'@'localhost';
213 INSERT INTO t1 VALUES(3,3);
214 SET sql_log_bin=1;
215 sync_slave_with_master C;
216 # Connection to (A) succeeds:
217 connect(A_u5,127.0.0.1,ndb_u5,pass5,auth_test_db,$MYSQL_A_PORT,);
218 disconnect A_u5;
219 # Connection to (B) succeeds:
220 connect(B_u5,127.0.0.1,ndb_u5,pass5,auth_test_db,$MYSQL_B_PORT,);
221 disconnect B_u5;
222 # Connection to (C) fails:
223 disable_query_log;
224 error ER_ACCESS_DENIED_ERROR;
225 connect(should_fail,127.0.0.1,ndb_u5,pass5,auth_test_db,$MYSQL_C_PORT,);
226 enable_query_log;
227
228 # Test (6): On server (B), sql_log_bin is set to 0, and then a user with
229 # NDB_STORED_USER is created. Test to verify that the user has not been
230 # replicated to server (C), but exists on (A) and (B).
231 connection B_root;
232 echo;
233 echo Test 6:;
234 echo [Server B];
235 SET sql_log_bin=0;
236 CREATE USER 'ndb_u6'@'localhost' IDENTIFIED by 'pass6';
237 GRANT NDB_STORED_USER ON *.* to 'ndb_u6'@'localhost';
238 GRANT SELECT ON t1 TO 'ndb_u6'@'localhost';
239 INSERT INTO t1 VALUES(4,4);
240 SET sql_log_bin=1;
241
242 connection default;
243 sync_slave_with_master C;
244 # Connection to (A) succeeds:
245 connect(A_u6,127.0.0.1,ndb_u6,pass6,auth_test_db,$MYSQL_A_PORT,);
246 disconnect A_u6;
247 # Connection to (B) succeeds:
248 connect(B_u6,127.0.0.1,ndb_u6,pass6,auth_test_db,$MYSQL_B_PORT,);
249 disconnect B_u6;
250 # Connection to (C) fails:
251 disable_query_log;
252 error ER_ACCESS_DENIED_ERROR;
253 connect(should_fail,127.0.0.1,ndb_u6,pass6,auth_test_db,$MYSQL_C_PORT,);
254 enable_query_log;
255
256 # Test (7)
257 # Bug#33542052 grant ndb_stored_user alters user with wrong hash
258 # Create user on (A), test login from (C),
259 # then grant ndb_stored_user, then test login from (B)
260
261 connection default;
262 echo;
263 echo Test 7:;
264 echo [Server A];
265 echo Creating user u7 with random password;
266 let $u7pass=`SELECT concat(char(floor(rand() * 56) + 35 USING latin1),
267 char(floor(rand() * 56) + 35 USING latin1),
268 char(floor(rand() * 56) + 35 USING latin1),
269 char(floor(rand() * 56) + 35 USING latin1),
270 char(floor(rand() * 56) + 35 USING latin1),
271 char(floor(rand() * 56) + 35 USING latin1),
272 char(floor(rand() * 56) + 35 USING latin1),
273 char(floor(rand() * 56) + 35 USING latin1),
274 char(floor(rand() * 56) + 35 USING latin1)) as p`;
275 disable_query_log ONCE;
276 eval create user 'u7'@'127.%'
277 identified with caching_sha2_password by "$u7pass"
278 with max_user_connections 10;
279 GRANT SELECT ON t1 TO 'u7'@'127.%';
280 sync_slave_with_master C;
281
282 connect(C_u7,127.0.0.1,u7,$u7pass,auth_test_db,$MYSQL_C_PORT,);
283 connection C_u7;
284 echo [Server C];
285 select current_user(), @@server_id;
286 disconnect C_u7;
287
288 connection default;
289 echo [Server A];
290 GRANT ndb_stored_user on *.* to u7@'127.%';
291 select current_user(), @@server_id;
292
293 connect(B_u7,127.0.0.1,u7,$u7pass,auth_test_db,$MYSQL_B_PORT,);
294 connection B_u7;
295 echo [Server B];
296 select current_user(), @@server_id;
297 disconnect B_u7;
298
299 # Cleanup
300
301 connection default;
302 echo;
303 echo Cleanup:;
304 SET sql_log_bin=1;
305 drop user local_user_1@localhost;
306 drop user ndb_u1@localhost;
307 drop user ndb_u2@localhost;
308 drop user if exists ndb_u5@localhost;
309 drop user if exists ndb_u6@localhost;
310 drop user if exists u7@'127.%';
311 drop table t1;
312 drop database auth_test_db;
313 sync_slave_with_master C;
314
315 connection B_root;
316 drop user local_user_2@b;
317
318 connection D_root;
319 FLUSH BINARY LOGS; # so that the test can be run with --repeat
320
321 --source include/rpl_end.inc