Debian Codegraph @

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