Debian Codegraph @

mysql-test/suite/auth_sec/include/acl_tables_locking_tests_big_debug_1.inc

raw
1 #
2 # Validate that ACL DDLs will block concurrent FLUSH PRIVILEGES
3 # for given isolation level
4 #
5 # Usage:
6 # --let $ISOLATION_LEVEL=<isolation level>
7 # --source ../include/acl_tables_locking_tests_big_debug_1.inc
8
9 --echo # -----------------------------------------------------------------------
10 --echo # Setup
11 --echo # Saving initial values
12 SET @global_start_value = @@global.transaction_isolation;
13 SELECT @global_start_value;
14 SET @session_start_value = @@session.transaction_isolation;
15 SELECT @session_start_value;
16
17 --echo # Setting transaction isolation to '$ISOLATION_LEVEL'
18 --eval SET GLOBAL transaction_isolation = '$ISOLATION_LEVEL'
19
20 --echo # -----------------------------------------------------------------------
21 --echo # Case 1: Successful ACL DDLs + FLUSH PRIVILEGES
22
23 --echo # -------------------------------
24 --echo # Case 1.1: CREATE USER + FLUSH PRIVILEGES
25
26 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
27 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
28 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
29 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
30 --let $EXECUTE_SQL=CREATE USER u1
31 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
32 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
33 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
34 --let $EXECUTE_EXPECT_ERROR=0
35 --source acl_tables_locking_tests_big_debug_helper.inc
36 --connection default
37 DROP USER u1;
38
39 --echo # -------------------------------
40 --echo # Case 1.2: ALTER USER + FLUSH PRIVILEGES
41
42 CREATE USER u1;
43 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
44 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
45 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
46 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
47 --let $EXECUTE_SQL=ALTER USER u1 IDENTIFIED BY 'abcd'
48 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
49 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
50 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
51 --let $EXECUTE_EXPECT_ERROR=0
52 --source acl_tables_locking_tests_big_debug_helper.inc
53 --connection default
54 DROP USER u1;
55
56 --echo # -------------------------------
57 --echo # Case 1.3: RENAME USER + FLUSH PRIVILEGES
58
59 CREATE USER u1;
60 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
61 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
62 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
63 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
64 --let $EXECUTE_SQL=RENAME USER u1 TO u2
65 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
66 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
67 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
68 --let $EXECUTE_EXPECT_ERROR=0
69 --source acl_tables_locking_tests_big_debug_helper.inc
70 --connection default
71 DROP USER u2;
72
73 --echo # -------------------------------
74 --echo # Case 1.4: CREATE ROLE + FLUSH PRIVILEGES
75
76 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
77 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
78 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
79 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
80 --let $EXECUTE_SQL=CREATE ROLE r1
81 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
82 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
83 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
84 --let $EXECUTE_EXPECT_ERROR=0
85 --source acl_tables_locking_tests_big_debug_helper.inc
86 --connection default
87 DROP ROLE r1;
88
89 --echo # -------------------------------
90 --echo # Case 1.5: GRANT privilege + FLUSH PRIVILEGES
91
92 CREATE USER u1;
93 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
94 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
95 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
96 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
97 --let $EXECUTE_SQL=GRANT SELECT ON *.* TO u1
98 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
99 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
100 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
101 --let $EXECUTE_EXPECT_ERROR=0
102 --source acl_tables_locking_tests_big_debug_helper.inc
103 --connection default
104 DROP USER u1;
105
106 --echo # -------------------------------
107 --echo # Case 1.6: GRANT role + FLUSH PRIVILEGES
108
109 CREATE USER u1;
110 CREATE ROLE r1;
111 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
112 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
113 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
114 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
115 --let $EXECUTE_SQL=GRANT r1 TO u1
116 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
117 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
118 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
119 --let $EXECUTE_EXPECT_ERROR=0
120 --source acl_tables_locking_tests_big_debug_helper.inc
121 --connection default
122 DROP USER r1;
123 DROP USER u1;
124
125 --echo # -------------------------------
126 --echo # Case 1.7: SET DEFAULT ROLE + FLUSH PRIVILEGES
127
128 CREATE USER u1;
129 CREATE ROLE r1;
130 GRANT r1 TO u1;
131 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
132 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
133 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
134 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
135 --let $EXECUTE_SQL=SET DEFAULT ROLE r1 TO u1
136 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
137 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
138 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
139 --let $EXECUTE_EXPECT_ERROR=0
140 --source acl_tables_locking_tests_big_debug_helper.inc
141 --connection default
142 DROP USER r1;
143 DROP USER u1;
144
145 --echo # -------------------------------
146 --echo # Case 1.8: SET PASSWORD + FLUSH PRIVILEGES
147
148 CREATE USER u1;
149 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
150 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
151 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
152 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
153 --let $EXECUTE_SQL=SET PASSWORD FOR u1 = 'efgh'
154 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
155 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
156 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
157 --let $EXECUTE_EXPECT_ERROR=0
158 --source acl_tables_locking_tests_big_debug_helper.inc
159 --connection default
160 DROP USER u1;
161
162 --echo # -------------------------------
163 --echo # Case 1.9: REVOKE role + FLUSH PRIVILEGES
164
165 CREATE USER u1;
166 CREATE ROLE r1;
167 GRANT r1 TO u1;
168 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
169 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
170 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
171 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
172 --let $EXECUTE_SQL=REVOKE r1 FROM u1
173 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
174 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
175 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
176 --let $EXECUTE_EXPECT_ERROR=0
177 --source acl_tables_locking_tests_big_debug_helper.inc
178 --connection default
179 DROP ROLE r1;
180 DROP USER u1;
181
182 --echo # -------------------------------
183 --echo # Case 1.10: REVOKE privilege + FLUSH PRIVILEGES
184
185 CREATE USER u1;
186 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
187 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
188 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
189 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
190 --let $EXECUTE_SQL=REVOKE SELECT ON *.* FROM u1
191 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
192 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
193 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
194 --let $EXECUTE_EXPECT_ERROR=0
195 --source acl_tables_locking_tests_big_debug_helper.inc
196 --connection default
197 DROP USER u1;
198
199 --echo # -------------------------------
200 --echo # Case 1.11: DROP ROLE + FLUSH PRIVILEGES
201
202 CREATE ROLE r1;
203 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
204 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
205 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
206 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
207 --let $EXECUTE_SQL=DROP ROLE r1
208 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
209 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
210 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
211 --let $EXECUTE_EXPECT_ERROR=0
212 --source acl_tables_locking_tests_big_debug_helper.inc
213 --connection default
214
215 --echo # -------------------------------
216 --echo # Case 1.12: DROP USER + FLUSH PRIVILEGES
217
218 CREATE USER u1;
219 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
220 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
221 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
222 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
223 --let $EXECUTE_SQL=DROP USER u1
224 --let $EXECUTE_DEBUG=wl14084_acl_ddl_after_table_lock_trigger_timeout
225 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
226 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
227 --let $EXECUTE_EXPECT_ERROR=0
228 --source acl_tables_locking_tests_big_debug_helper.inc
229 --connection default
230
231 --echo # -----------------------------------------------------------------------
232 --echo # Case 2: Unsuccessful ACL DDLs + FLUSH PRIVILEGES
233
234 --connect(conn_attempt_flush_privileges, localhost, root,,,,)
235 SET @@lock_wait_timeout=1;
236 --connect(conn_execute_acl_ddl, localhost, root,,,,)
237
238 --echo # -------------------------------
239 --echo # Case 2.1: CREATE USER + FLUSH PRIVILEGES
240
241 CREATE USER u1;
242 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
243 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
244 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
245 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
246 --let $EXECUTE_SQL=CREATE USER u3, u2, u1
247 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
248 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
249 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
250 --let $EXECUTE_EXPECT_ERROR=1
251 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANNOT_USER
252 --source acl_tables_locking_tests_big_debug_helper.inc
253 --connection default
254 DROP USER u1;
255
256 --echo # -------------------------------
257 --echo # Case 2.2: ALTER USER + FLUSH PRIVILEGES
258
259 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
260 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
261 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
262 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
263 --let $EXECUTE_SQL=ALTER USER u2 IDENTIFIED BY 'abcd'
264 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
265 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
266 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
267 --let $EXECUTE_EXPECT_ERROR=1
268 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANNOT_USER
269 --source acl_tables_locking_tests_big_debug_helper.inc
270 --connection default
271
272 --echo # -------------------------------
273 --echo # Case 2.3: RENAME USER + FLUSH PRIVILEGES
274
275 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
276 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
277 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
278 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
279 --let $EXECUTE_SQL=RENAME USER u2 TO u3
280 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
281 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
282 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
283 --let $EXECUTE_EXPECT_ERROR=1
284 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANNOT_USER
285 --source acl_tables_locking_tests_big_debug_helper.inc
286 --connection default
287
288 --echo # -------------------------------
289 --echo # Case 2.4: DROP USER + FLUSH PRIVILEGES
290
291 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
292 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
293 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
294 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
295 --let $EXECUTE_SQL=DROP USER u2
296 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
297 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
298 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
299 --let $EXECUTE_EXPECT_ERROR=1
300 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANNOT_USER
301 --source acl_tables_locking_tests_big_debug_helper.inc
302 --connection default
303
304 --echo # -------------------------------
305 --echo # Case 2.5: SET PASSWORD + FLUSH PRIVILEGES
306
307 CREATE USER u1;
308 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
309 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
310 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
311 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
312 --let $EXECUTE_SQL=SET PASSWORD FOR u1 = 'abcd'
313 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure
314 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
315 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
316 --let $EXECUTE_EXPECT_ERROR=1
317 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_PASSWORD_NO_MATCH
318 --source acl_tables_locking_tests_big_debug_helper.inc
319 --connection default
320 DROP USER u1;
321
322 --echo # -------------------------------
323 --echo # Case 2.6: CREATE ROLE + FLUSH PRIVILEGES
324
325 CREATE ROLE r1;
326 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
327 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
328 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
329 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
330 --let $EXECUTE_SQL=CREATE ROLE r3, r2, r1
331 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
332 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
333 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
334 --let $EXECUTE_EXPECT_ERROR=1
335 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANNOT_USER
336 --source acl_tables_locking_tests_big_debug_helper.inc
337 --connection default
338 DROP ROLE r1;
339
340 --echo # -------------------------------
341 --echo # Case 2.7: DROP ROLE + FLUSH PRIVILEGES
342
343 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
344 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
345 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
346 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
347 --let $EXECUTE_SQL=DROP ROLE r2
348 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
349 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
350 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
351 --let $EXECUTE_EXPECT_ERROR=1
352 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANNOT_USER
353 --source acl_tables_locking_tests_big_debug_helper.inc
354 --connection default
355
356 --echo # -------------------------------
357 --echo # Case 2.8: GRANT privilege + FLUSH PRIVILEGES
358
359 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
360 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
361 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
362 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
363 --let $EXECUTE_SQL=GRANT SELECT ON *.* TO u2
364 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
365 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
366 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
367 --let $EXECUTE_EXPECT_ERROR=1
368 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_CANT_CREATE_USER_WITH_GRANT
369 --source acl_tables_locking_tests_big_debug_helper.inc
370 --connection default
371
372 --echo # -------------------------------
373 --echo # Case 2.9: GRANT role + FLUSH PRIVILEGES
374
375 CREATE ROLE r1;
376 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
377 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
378 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
379 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
380 --let $EXECUTE_SQL=GRANT r1 TO u2
381 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
382 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
383 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
384 --let $EXECUTE_EXPECT_ERROR=1
385 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_UNKNOWN_AUTHID
386 --source acl_tables_locking_tests_big_debug_helper.inc
387 --connection default
388 DROP ROLE r1;
389
390 --echo # -------------------------------
391 --echo # Case 2.10: REVOKE role + FLUSH PRIVILEGES
392
393 CREATE ROLE r1;
394 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
395 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
396 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
397 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
398 --let $EXECUTE_SQL=REVOKE r1 FROM u2
399 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
400 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
401 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
402 --let $EXECUTE_EXPECT_ERROR=1
403 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_UNKNOWN_AUTHID
404 --source acl_tables_locking_tests_big_debug_helper.inc
405 --connection default
406 DROP ROLE r1;
407
408 --echo # -------------------------------
409 --echo # Case 2.11: REVOKE privilege + FLUSH PRIVILEGES
410
411 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
412 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
413 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
414 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
415 --let $EXECUTE_SQL=REVOKE SELECT ON *.* FROM u2
416 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
417 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
418 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
419 --let $EXECUTE_EXPECT_ERROR=1
420 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_NONEXISTING_GRANT
421 --source acl_tables_locking_tests_big_debug_helper.inc
422 --connection default
423
424 --echo # -------------------------------
425 --echo # Case 2.12: SET DEFAULT ROLE + FLUSH PRIVILEGES
426
427 CREATE ROLE r1;
428 --let $ATTEMPT_SQL=FLUSH PRIVILEGES
429 --let $ATTEMPT_DEBUG=wl14084_simulate_flush_privileges_timeout
430 --let $ATTEMPT_DEBUG_SYNC=wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now
431 --let $ATTEMPT_EXPECTED_ERROR_NUM=ER_LOCK_WAIT_TIMEOUT
432 --let $EXECUTE_SQL=SET DEFAULT ROLE r1 TO u2
433 --let $EXECUTE_DEBUG=wl14084_acl_ddl_error_before_cache_reload_trigger_timeout
434 --let $EXECUTE_DEBUG_SYNC_1=now WAIT_FOR proceed_with_acl_ddl
435 --let $EXECUTE_DEBUG_SYNC_2=wl14084_after_table_locks SIGNAL flush_now
436 --let $EXECUTE_EXPECT_ERROR=1
437 --let $EXECUTE_EXPECTED_ERROR_NUM=ER_ROLE_NOT_GRANTED
438 --source acl_tables_locking_tests_big_debug_helper.inc
439 --connection default
440 DROP ROLE r1;
441
442 --disconnect conn_attempt_flush_privileges
443 --disconnect conn_execute_acl_ddl
444
445 --echo # -----------------------------------------------------------------------
446 --echo # Cleanup
447 --echo # Restore initial values
448
449 --connection default
450 SET @@global.transaction_isolation = @global_start_value;
451 SELECT @@global.transaction_isolation;
452 SET @@session.transaction_isolation = @session_start_value;
453 SELECT @@session.transaction_isolation;