Debian Codegraph @

mysql-test/suite/auth_sec/r/acl_tables_locking_big_positive_ser.result

raw
1 # -----------------------------------------------------------------------
2 # Setup
3 # Saving initial values
4 SET @global_start_value = @@global.transaction_isolation;
5 SELECT @global_start_value;
6 @global_start_value
7 REPEATABLE-READ
8 SET @session_start_value = @@session.transaction_isolation;
9 SELECT @session_start_value;
10 @session_start_value
11 REPEATABLE-READ
12 # Setting transaction isolation to 'SERIALIZABLE'
13 SET GLOBAL transaction_isolation = 'SERIALIZABLE';
14 # -----------------------------------------------------------------------
15 # ACL DDLs + Simple SELECTs
16 CREATE USER u1 IDENTIFIED BY 'abcd';
17 GRANT SELECT, UPDATE ON *.* TO u1;
18 connect conn_u1, localhost, u1, abcd,,,;
19 SET @@autocommit=0;
20 connect conn_root_1, localhost, root,,,,;
21 SET @@lock_wait_timeout=1;
22 connection conn_u1;
23 SELECT COUNT(*) FROM mysql.user WHERE user LIKE 'u1';
24 COUNT(*)
25 1
26 Warnings:
27 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
28 connection conn_root_1;
29 CREATE USER u2;
30 CREATE ROLE r1;
31 GRANT SELECT ON *.* TO r1;
32 GRANT r1 TO u2;
33 SET DEFAULT ROLE r1 TO u2;
34 ALTER USER u2 IDENTIFIED BY 'abcd';
35 RENAME USER u2 TO u3;
36 RENAME USER u3 TO u2;
37 SET PASSWORD FOR u2 = 'abcd';
38 REVOKE r1 FROM u2;
39 REVOKE SELECT ON *.* FROM r1;
40 DROP ROLE r1;
41 DROP USER u2;
42 connection conn_u1;
43 ROLLBACK;
44 connection conn_root_1;
45 CREATE USER u2;
46 CREATE ROLE r1;
47 GRANT SELECT ON *.* TO r1;
48 GRANT r1 TO u2;
49 SET DEFAULT ROLE r1 TO u2;
50 ALTER USER u2 IDENTIFIED BY 'abcd';
51 RENAME USER u2 TO u3;
52 RENAME USER u3 TO u2;
53 SET PASSWORD FOR u2 = 'abcd';
54 REVOKE r1 FROM u2;
55 REVOKE SELECT ON *.* FROM r1;
56 DROP ROLE r1;
57 DROP USER u2;
58 connection default;
59 disconnect conn_root_1;
60 disconnect conn_u1;
61 DROP USER u1;
62 CREATE USER u1 IDENTIFIED BY 'abcd';
63 GRANT SELECT, UPDATE ON *.* TO u1;
64 connect conn_u1, localhost, u1, abcd,,,;
65 SET @@autocommit=0;
66 connect conn_root_1, localhost, root,,,,;
67 SET @@lock_wait_timeout=1;
68 connection conn_u1;
69 SELECT COUNT(*) FROM mysql.db WHERE user LIKE 'u1';;
70 COUNT(*)
71 0
72 Warnings:
73 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
74 connection conn_root_1;
75 CREATE USER u2;
76 CREATE ROLE r1;
77 GRANT SELECT ON *.* TO r1;
78 GRANT r1 TO u2;
79 SET DEFAULT ROLE r1 TO u2;
80 ALTER USER u2 IDENTIFIED BY 'abcd';
81 RENAME USER u2 TO u3;
82 RENAME USER u3 TO u2;
83 SET PASSWORD FOR u2 = 'abcd';
84 REVOKE r1 FROM u2;
85 REVOKE SELECT ON *.* FROM r1;
86 DROP ROLE r1;
87 DROP USER u2;
88 connection conn_u1;
89 ROLLBACK;
90 connection conn_root_1;
91 CREATE USER u2;
92 CREATE ROLE r1;
93 GRANT SELECT ON *.* TO r1;
94 GRANT r1 TO u2;
95 SET DEFAULT ROLE r1 TO u2;
96 ALTER USER u2 IDENTIFIED BY 'abcd';
97 RENAME USER u2 TO u3;
98 RENAME USER u3 TO u2;
99 SET PASSWORD FOR u2 = 'abcd';
100 REVOKE r1 FROM u2;
101 REVOKE SELECT ON *.* FROM r1;
102 DROP ROLE r1;
103 DROP USER u2;
104 connection default;
105 disconnect conn_root_1;
106 disconnect conn_u1;
107 DROP USER u1;
108 CREATE USER u1 IDENTIFIED BY 'abcd';
109 GRANT SELECT, UPDATE ON *.* TO u1;
110 connect conn_u1, localhost, u1, abcd,,,;
111 SET @@autocommit=0;
112 connect conn_root_1, localhost, root,,,,;
113 SET @@lock_wait_timeout=1;
114 connection conn_u1;
115 SELECT COUNT(*) FROM mysql.default_roles WHERE user LIKE 'u1';
116 COUNT(*)
117 0
118 Warnings:
119 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
120 connection conn_root_1;
121 CREATE USER u2;
122 CREATE ROLE r1;
123 GRANT SELECT ON *.* TO r1;
124 GRANT r1 TO u2;
125 SET DEFAULT ROLE r1 TO u2;
126 ALTER USER u2 IDENTIFIED BY 'abcd';
127 RENAME USER u2 TO u3;
128 RENAME USER u3 TO u2;
129 SET PASSWORD FOR u2 = 'abcd';
130 REVOKE r1 FROM u2;
131 REVOKE SELECT ON *.* FROM r1;
132 DROP ROLE r1;
133 DROP USER u2;
134 connection conn_u1;
135 ROLLBACK;
136 connection conn_root_1;
137 CREATE USER u2;
138 CREATE ROLE r1;
139 GRANT SELECT ON *.* TO r1;
140 GRANT r1 TO u2;
141 SET DEFAULT ROLE r1 TO u2;
142 ALTER USER u2 IDENTIFIED BY 'abcd';
143 RENAME USER u2 TO u3;
144 RENAME USER u3 TO u2;
145 SET PASSWORD FOR u2 = 'abcd';
146 REVOKE r1 FROM u2;
147 REVOKE SELECT ON *.* FROM r1;
148 DROP ROLE r1;
149 DROP USER u2;
150 connection default;
151 disconnect conn_root_1;
152 disconnect conn_u1;
153 DROP USER u1;
154 CREATE USER u1 IDENTIFIED BY 'abcd';
155 GRANT SELECT, UPDATE ON *.* TO u1;
156 connect conn_u1, localhost, u1, abcd,,,;
157 SET @@autocommit=0;
158 connect conn_root_1, localhost, root,,,,;
159 SET @@lock_wait_timeout=1;
160 connection conn_u1;
161 SELECT COUNT(*) FROM mysql.role_edges WHERE from_user LIKE 'u1';
162 COUNT(*)
163 0
164 Warnings:
165 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
166 connection conn_root_1;
167 CREATE USER u2;
168 CREATE ROLE r1;
169 GRANT SELECT ON *.* TO r1;
170 GRANT r1 TO u2;
171 SET DEFAULT ROLE r1 TO u2;
172 ALTER USER u2 IDENTIFIED BY 'abcd';
173 RENAME USER u2 TO u3;
174 RENAME USER u3 TO u2;
175 SET PASSWORD FOR u2 = 'abcd';
176 REVOKE r1 FROM u2;
177 REVOKE SELECT ON *.* FROM r1;
178 DROP ROLE r1;
179 DROP USER u2;
180 connection conn_u1;
181 ROLLBACK;
182 connection conn_root_1;
183 CREATE USER u2;
184 CREATE ROLE r1;
185 GRANT SELECT ON *.* TO r1;
186 GRANT r1 TO u2;
187 SET DEFAULT ROLE r1 TO u2;
188 ALTER USER u2 IDENTIFIED BY 'abcd';
189 RENAME USER u2 TO u3;
190 RENAME USER u3 TO u2;
191 SET PASSWORD FOR u2 = 'abcd';
192 REVOKE r1 FROM u2;
193 REVOKE SELECT ON *.* FROM r1;
194 DROP ROLE r1;
195 DROP USER u2;
196 connection default;
197 disconnect conn_root_1;
198 disconnect conn_u1;
199 DROP USER u1;
200 CREATE USER u1 IDENTIFIED BY 'abcd';
201 GRANT SELECT, UPDATE ON *.* TO u1;
202 connect conn_u1, localhost, u1, abcd,,,;
203 SET @@autocommit=0;
204 connect conn_root_1, localhost, root,,,,;
205 SET @@lock_wait_timeout=1;
206 connection conn_u1;
207 SELECT COUNT(*) FROM mysql.global_grants WHERE user LIKE 'u1';
208 COUNT(*)
209 0
210 Warnings:
211 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
212 connection conn_root_1;
213 CREATE USER u2;
214 CREATE ROLE r1;
215 GRANT SELECT ON *.* TO r1;
216 GRANT r1 TO u2;
217 SET DEFAULT ROLE r1 TO u2;
218 ALTER USER u2 IDENTIFIED BY 'abcd';
219 RENAME USER u2 TO u3;
220 RENAME USER u3 TO u2;
221 SET PASSWORD FOR u2 = 'abcd';
222 REVOKE r1 FROM u2;
223 REVOKE SELECT ON *.* FROM r1;
224 DROP ROLE r1;
225 DROP USER u2;
226 connection conn_u1;
227 ROLLBACK;
228 connection conn_root_1;
229 CREATE USER u2;
230 CREATE ROLE r1;
231 GRANT SELECT ON *.* TO r1;
232 GRANT r1 TO u2;
233 SET DEFAULT ROLE r1 TO u2;
234 ALTER USER u2 IDENTIFIED BY 'abcd';
235 RENAME USER u2 TO u3;
236 RENAME USER u3 TO u2;
237 SET PASSWORD FOR u2 = 'abcd';
238 REVOKE r1 FROM u2;
239 REVOKE SELECT ON *.* FROM r1;
240 DROP ROLE r1;
241 DROP USER u2;
242 connection default;
243 disconnect conn_root_1;
244 disconnect conn_u1;
245 DROP USER u1;
246 CREATE USER u1 IDENTIFIED BY 'abcd';
247 GRANT SELECT, UPDATE ON *.* TO u1;
248 connect conn_u1, localhost, u1, abcd,,,;
249 SET @@autocommit=0;
250 connect conn_root_1, localhost, root,,,,;
251 SET @@lock_wait_timeout=1;
252 connection conn_u1;
253 SELECT COUNT(*) FROM mysql.password_history WHERE user LIKE 'u1';
254 COUNT(*)
255 0
256 Warnings:
257 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
258 connection conn_root_1;
259 CREATE USER u2;
260 CREATE ROLE r1;
261 GRANT SELECT ON *.* TO r1;
262 GRANT r1 TO u2;
263 SET DEFAULT ROLE r1 TO u2;
264 ALTER USER u2 IDENTIFIED BY 'abcd';
265 RENAME USER u2 TO u3;
266 RENAME USER u3 TO u2;
267 SET PASSWORD FOR u2 = 'abcd';
268 REVOKE r1 FROM u2;
269 REVOKE SELECT ON *.* FROM r1;
270 DROP ROLE r1;
271 DROP USER u2;
272 connection conn_u1;
273 ROLLBACK;
274 connection conn_root_1;
275 CREATE USER u2;
276 CREATE ROLE r1;
277 GRANT SELECT ON *.* TO r1;
278 GRANT r1 TO u2;
279 SET DEFAULT ROLE r1 TO u2;
280 ALTER USER u2 IDENTIFIED BY 'abcd';
281 RENAME USER u2 TO u3;
282 RENAME USER u3 TO u2;
283 SET PASSWORD FOR u2 = 'abcd';
284 REVOKE r1 FROM u2;
285 REVOKE SELECT ON *.* FROM r1;
286 DROP ROLE r1;
287 DROP USER u2;
288 connection default;
289 disconnect conn_root_1;
290 disconnect conn_u1;
291 DROP USER u1;
292 CREATE USER u1 IDENTIFIED BY 'abcd';
293 GRANT SELECT, UPDATE ON *.* TO u1;
294 connect conn_u1, localhost, u1, abcd,,,;
295 SET @@autocommit=0;
296 connect conn_root_1, localhost, root,,,,;
297 SET @@lock_wait_timeout=1;
298 connection conn_u1;
299 SELECT COUNT(*) FROM mysql.tables_priv WHERE user LIKE 'u1';
300 COUNT(*)
301 0
302 Warnings:
303 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
304 connection conn_root_1;
305 CREATE USER u2;
306 CREATE ROLE r1;
307 GRANT SELECT ON *.* TO r1;
308 GRANT r1 TO u2;
309 SET DEFAULT ROLE r1 TO u2;
310 ALTER USER u2 IDENTIFIED BY 'abcd';
311 RENAME USER u2 TO u3;
312 RENAME USER u3 TO u2;
313 SET PASSWORD FOR u2 = 'abcd';
314 REVOKE r1 FROM u2;
315 REVOKE SELECT ON *.* FROM r1;
316 DROP ROLE r1;
317 DROP USER u2;
318 connection conn_u1;
319 ROLLBACK;
320 connection conn_root_1;
321 CREATE USER u2;
322 CREATE ROLE r1;
323 GRANT SELECT ON *.* TO r1;
324 GRANT r1 TO u2;
325 SET DEFAULT ROLE r1 TO u2;
326 ALTER USER u2 IDENTIFIED BY 'abcd';
327 RENAME USER u2 TO u3;
328 RENAME USER u3 TO u2;
329 SET PASSWORD FOR u2 = 'abcd';
330 REVOKE r1 FROM u2;
331 REVOKE SELECT ON *.* FROM r1;
332 DROP ROLE r1;
333 DROP USER u2;
334 connection default;
335 disconnect conn_root_1;
336 disconnect conn_u1;
337 DROP USER u1;
338 CREATE USER u1 IDENTIFIED BY 'abcd';
339 GRANT SELECT, UPDATE ON *.* TO u1;
340 connect conn_u1, localhost, u1, abcd,,,;
341 SET @@autocommit=0;
342 connect conn_root_1, localhost, root,,,,;
343 SET @@lock_wait_timeout=1;
344 connection conn_u1;
345 SELECT COUNT(*) FROM mysql.columns_priv WHERE user LIKE 'u1';
346 COUNT(*)
347 0
348 Warnings:
349 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
350 connection conn_root_1;
351 CREATE USER u2;
352 CREATE ROLE r1;
353 GRANT SELECT ON *.* TO r1;
354 GRANT r1 TO u2;
355 SET DEFAULT ROLE r1 TO u2;
356 ALTER USER u2 IDENTIFIED BY 'abcd';
357 RENAME USER u2 TO u3;
358 RENAME USER u3 TO u2;
359 SET PASSWORD FOR u2 = 'abcd';
360 REVOKE r1 FROM u2;
361 REVOKE SELECT ON *.* FROM r1;
362 DROP ROLE r1;
363 DROP USER u2;
364 connection conn_u1;
365 ROLLBACK;
366 connection conn_root_1;
367 CREATE USER u2;
368 CREATE ROLE r1;
369 GRANT SELECT ON *.* TO r1;
370 GRANT r1 TO u2;
371 SET DEFAULT ROLE r1 TO u2;
372 ALTER USER u2 IDENTIFIED BY 'abcd';
373 RENAME USER u2 TO u3;
374 RENAME USER u3 TO u2;
375 SET PASSWORD FOR u2 = 'abcd';
376 REVOKE r1 FROM u2;
377 REVOKE SELECT ON *.* FROM r1;
378 DROP ROLE r1;
379 DROP USER u2;
380 connection default;
381 disconnect conn_root_1;
382 disconnect conn_u1;
383 DROP USER u1;
384 CREATE USER u1 IDENTIFIED BY 'abcd';
385 GRANT SELECT, UPDATE ON *.* TO u1;
386 connect conn_u1, localhost, u1, abcd,,,;
387 SET @@autocommit=0;
388 connect conn_root_1, localhost, root,,,,;
389 SET @@lock_wait_timeout=1;
390 connection conn_u1;
391 SELECT COUNT(*) FROM mysql.procs_priv WHERE user LIKE 'u1';
392 COUNT(*)
393 0
394 Warnings:
395 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
396 connection conn_root_1;
397 CREATE USER u2;
398 CREATE ROLE r1;
399 GRANT SELECT ON *.* TO r1;
400 GRANT r1 TO u2;
401 SET DEFAULT ROLE r1 TO u2;
402 ALTER USER u2 IDENTIFIED BY 'abcd';
403 RENAME USER u2 TO u3;
404 RENAME USER u3 TO u2;
405 SET PASSWORD FOR u2 = 'abcd';
406 REVOKE r1 FROM u2;
407 REVOKE SELECT ON *.* FROM r1;
408 DROP ROLE r1;
409 DROP USER u2;
410 connection conn_u1;
411 ROLLBACK;
412 connection conn_root_1;
413 CREATE USER u2;
414 CREATE ROLE r1;
415 GRANT SELECT ON *.* TO r1;
416 GRANT r1 TO u2;
417 SET DEFAULT ROLE r1 TO u2;
418 ALTER USER u2 IDENTIFIED BY 'abcd';
419 RENAME USER u2 TO u3;
420 RENAME USER u3 TO u2;
421 SET PASSWORD FOR u2 = 'abcd';
422 REVOKE r1 FROM u2;
423 REVOKE SELECT ON *.* FROM r1;
424 DROP ROLE r1;
425 DROP USER u2;
426 connection default;
427 disconnect conn_root_1;
428 disconnect conn_u1;
429 DROP USER u1;
430 CREATE USER u1 IDENTIFIED BY 'abcd';
431 GRANT SELECT, UPDATE ON *.* TO u1;
432 connect conn_u1, localhost, u1, abcd,,,;
433 SET @@autocommit=0;
434 connect conn_root_1, localhost, root,,,,;
435 SET @@lock_wait_timeout=1;
436 connection conn_u1;
437 SELECT COUNT(*) FROM mysql.proxies_priv WHERE user LIKE 'u1';
438 COUNT(*)
439 0
440 Warnings:
441 Warning 4009 Reads with serializable isolation/SELECT FOR SHARE are not supported for ACL tables.
442 connection conn_root_1;
443 CREATE USER u2;
444 CREATE ROLE r1;
445 GRANT SELECT ON *.* TO r1;
446 GRANT r1 TO u2;
447 SET DEFAULT ROLE r1 TO u2;
448 ALTER USER u2 IDENTIFIED BY 'abcd';
449 RENAME USER u2 TO u3;
450 RENAME USER u3 TO u2;
451 SET PASSWORD FOR u2 = 'abcd';
452 REVOKE r1 FROM u2;
453 REVOKE SELECT ON *.* FROM r1;
454 DROP ROLE r1;
455 DROP USER u2;
456 connection conn_u1;
457 ROLLBACK;
458 connection conn_root_1;
459 CREATE USER u2;
460 CREATE ROLE r1;
461 GRANT SELECT ON *.* TO r1;
462 GRANT r1 TO u2;
463 SET DEFAULT ROLE r1 TO u2;
464 ALTER USER u2 IDENTIFIED BY 'abcd';
465 RENAME USER u2 TO u3;
466 RENAME USER u3 TO u2;
467 SET PASSWORD FOR u2 = 'abcd';
468 REVOKE r1 FROM u2;
469 REVOKE SELECT ON *.* FROM r1;
470 DROP ROLE r1;
471 DROP USER u2;
472 connection default;
473 disconnect conn_root_1;
474 disconnect conn_u1;
475 DROP USER u1;
476 # -----------------------------------------------------------------------
477 # Cleanup
478 # Restore initial values
479 SET @@global.transaction_isolation = @global_start_value;
480 SELECT @@global.transaction_isolation;
481 @@global.transaction_isolation
482 REPEATABLE-READ
483 SET @@session.transaction_isolation = @session_start_value;
484 SELECT @@session.transaction_isolation;
485 @@session.transaction_isolation
486 REPEATABLE-READ