Debian Codegraph @

mysql-test/suite/funcs_1/datadict/processlist_priv.inc

raw
1 ############ suite/funcs_1/datadict/processlist_priv.inc ###############
2 # #
3 # Testing of privileges around #
4 # SELECT ... PROCESSLIST/SHOW PROCESSLIST #
5 # #
6 # Note(mleich): #
7 # There is a significant risk to get an unstable test because of #
8 # timing issues. #
9 # Example1: #
10 # 1. Disconnect connection X #
11 # 2. Switch to connection Y #
12 # 3. SHOW PROCESSLIST might present a record like #
13 # <ID> <user> <host> <db> Quit 0 cleaning up NULL #
14 # or even a row where connection X is without #
15 # "Quit" or "cleaning up". #
16 # That means our SHOW PROCESSLIST can come too early. #
17 # Solution: #
18 # Close the connections at the end of the test. #
19 # Example2 (2008-08-14 again observed): #
20 # 1. connection X: SHOW PROCESSLIST/GRANT ... etc. #
21 # 2. Switch to connection Y #
22 # 3. SHOW PROCESSLIST might present a record like #
23 # <ID> <user> <host> <db> Query TIME cleaning up <command> #
24 # <ID> <user> <host> <db> Query TIME writing to net <command> #
25 # Problems happens more often in case of slow filesystem! #
26 # First Solution: #
27 # Insert a dummy SQL command where the cleanup is most probably #
28 # fast before switching to another connection and running #
29 # SHOW/SELECT PROCESSLIST. #
30 # Suppress writing to protocol by assignment to $variable. #
31 # let $my_var= `SELECT 1`; #
32 # Even the 'SELECT 1' was in some cases in state #
33 # "writing to net". #
34 # Final Solution: #
35 # --sleep 0.3 #
36 # This value was at least on my box sufficient. #
37 # Please inform us if this test fails so that we can adjust #
38 # the sleep time better or switch to poll routines. #
39 # #
40 # Storage engine variants of this test do not make sense. #
41 # - I_S tables use the MEMORY storage engine whenever possible. #
42 # - There are some I_S table which need column data types which #
43 # are not supported by MEMORY. Example: LONGTEXT/BLOB #
44 # MyISAM will be used for such tables. #
45 # The column PROCESSLIST.INFO is of data type LONGTEXT #
46 # ----> MyISAM #
47 # - There is no impact of the GLOBAL(server) or SESSION default #
48 # storage engine setting on the engine used for I_S tables. #
49 # That means we cannot get NDB or InnoDB instead. #
50 # #
51 # Creation: #
52 # 2007-08 hhunger Implement this test as part of #
53 # WL#3982 Test information_schema.processlist #
54 # #
55 # Last update: #
56 # 2008-08-14 mleich Bug#38270 Test "processlist_priv_ps" fails on #
57 # varying "processlist" output #
58 # - Replace one sleep by a poll routines #
59 # - Remove or disable superfluous sleeps #
60 # #
61 ########################################################################
62
63 # The following variables are used in "datadict_priv.inc" and here.
64 #
65 # information_schema table to be tested
66 let $table= processlist;
67 #
68 # columns of the information_schema table e.g. to use in a select.
69 let $columns= ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO;
70 #
71 # Where clause for an update.
72 let $update_where= WHERE id=1 ;
73 #
74 # Column to be used in the SET of an update.
75 let $set_column= user='any_user' ;
76 #
77 # Where clause of a delete.
78 let $delete_where= WHERE id=1 ;
79 #
80 # Column to be dropped.
81 let $drop_column= user;
82 #
83 # Column to be indexed
84 let $index_col= user;
85
86 USE information_schema;
87
88 --echo ####################################################################################
89 --echo 1 Prepare test.
90 --echo connection default (user=root)
91 --echo ####################################################################################
92 if (`SELECT COUNT(*) <> 2 FROM processlist`)
93 {
94 --echo This test expects one connection to the server.
95 --echo Expectation: USER HOST DB COMMAND STATE INFO
96 --echo Expectation: root localhost information_schema Query executing SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID
97 --echo But we found in the moment:
98 SELECT USER,HOST,DB,COMMAND,STATE,INFO FROM processlist ORDER BY ID;
99 --echo Maybe
100 --echo - the base configuration (no of parallel auxiliary sessions) of the server has changed
101 --echo - a parallel test intended for another server accidently connected to our current one
102 --echo We cannot proceed in this situation. Abort
103 exit;
104 }
105
106 --echo ####################################################################################
107 --echo 1.1 Create two user
108 --echo ####################################################################################
109 # access to info tables as normal user
110 --error 0, ER_CANNOT_USER
111 DROP USER ddicttestuser1@'localhost';
112 --error 0, ER_CANNOT_USER
113 DROP USER ddicttestuser2@'localhost';
114 CREATE USER ddicttestuser1@'localhost';
115 CREATE USER ddicttestuser2@'localhost';
116 SET PASSWORD FOR ddicttestuser1@'localhost' = 'ddictpass';
117 SET PASSWORD FOR ddicttestuser2@'localhost' = 'ddictpass';
118
119 --echo ####################################################################################
120 --echo 1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege):
121 connect (con100,localhost,ddicttestuser1,ddictpass,information_schema);
122 --echo ####################################################################################
123
124
125 --echo ####################################################################################
126 --echo 2 connection default(user=root with default privileges):
127 --echo SHOW/SELECT shows all processes/threads.
128 --echo ####################################################################################
129 connection default;
130 # Avoid Bug#38270 Test "processlist_priv_ps" fails on varying "processlist" output
131 # This subtest expects that the connection con100 is in state 'Sleep'.
132 # Poll till the connection con100 is in state COMMAND = 'Sleep'.
133 let $wait_timeout= 10;
134 let $wait_condition=
135 SELECT COUNT(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST
136 WHERE DB = 'information_schema' AND COMMAND = 'Sleep' AND USER = 'ddicttestuser1';
137 --source include/wait_condition.inc
138 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
139 eval SHOW CREATE TABLE $table;
140 --replace_column 1 ID 3 HOST_NAME 6 TIME
141 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
142 --sorted_result
143 eval SHOW $table;
144 --replace_column 1 ID 3 HOST_NAME 6 TIME
145 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
146 --sorted_result
147 eval SELECT * FROM $table $select_where ORDER BY id;
148 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
149 --sorted_result
150 --replace_column 1 ID 3 HOST_NAME 6 TIME
151 eval SELECT $columns FROM $table $select_where ORDER BY id;
152 --source suite/funcs_1/datadict/datadict_priv.inc
153
154
155 --echo ####################################################################################
156 --echo 3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege):
157 connection con100;
158 --echo SHOW/SELECT shows only the processes (1) of the user.
159 --echo ####################################################################################
160 # No need for poll routine here.
161 # The current state of the default session might depend on load of testing box
162 # but "ddicttestuser1" must not see anything of the root session.
163 --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE
164 eval SHOW CREATE TABLE $table;
165 --replace_column 1 ID 3 HOST_NAME 6 TIME
166 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
167 --sorted_result
168 eval SHOW $table;
169 --replace_column 1 ID 3 HOST_NAME 6 TIME
170 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
171 --sorted_result
172 eval SELECT * FROM $table $select_where ORDER BY id;
173 --replace_column 1 ID 3 HOST_NAME 6 TIME
174 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
175 --sorted_result
176 eval SELECT $columns FROM $table $select_where ORDER BY id;
177 --source suite/funcs_1/datadict/datadict_priv.inc
178 --sleep 0.3
179
180
181 --echo ####################################################################################
182 --echo 4 Grant PROCESS privilege to ddicttestuser1
183 --echo connection default (user=root)
184 --echo ####################################################################################
185 connection default;
186 GRANT PROCESS ON *.* TO ddicttestuser1@'localhost';
187 --sleep 0.3
188
189 --echo ####################################################################################
190 --echo 4.1 Existing connection con100 (ddicttestuser1)
191 --echo The user ddicttestuser1 has the PROCESS privilege, but the connection was
192 --echo established before PROCESS was granted.
193 --echo SHOW/SELECT shows only the processes (1) of the user.
194 --echo ####################################################################################
195 connection con100;
196 SHOW GRANTS;
197 --replace_column 1 ID 3 HOST_NAME 6 TIME
198 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
199 --sorted_result
200 SHOW processlist;
201 --replace_column 1 ID 3 HOST_NAME 6 TIME
202 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
203 --sorted_result
204 SELECT * FROM information_schema.processlist;
205 --sleep 0.3
206
207 --echo ####################################################################################
208 --echo 4.2 New connection con101 (ddicttestuser1 with PROCESS privilege)
209 --echo SHOW/SELECT shows all processes/threads.
210 --echo ####################################################################################
211 connect (con101,localhost,ddicttestuser1,ddictpass,information_schema);
212 SHOW GRANTS;
213 --replace_column 1 ID 3 HOST_NAME 6 TIME
214 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
215 --sorted_result
216 SHOW processlist;
217 --replace_column 1 ID 3 HOST_NAME 6 TIME
218 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
219 --sorted_result
220 SELECT * FROM information_schema.processlist;
221 --sleep 0.3
222
223
224 --echo ####################################################################################
225 --echo 5 Grant PROCESS privilege to anonymous user.
226 --echo connection default (user=root)
227 --echo ####################################################################################
228 connection default;
229 CREATE USER ''@'localhost';
230 GRANT PROCESS ON *.* TO ''@'localhost';
231 --sleep 0.3
232
233 --echo ####################################################################################
234 --echo 5.1 Establish connection (anonymous1,localhost,'',,information_schema)
235 --echo anonymous user with PROCESS privilege
236 --echo SHOW/SELECT shows all processes/threads.
237 --echo ####################################################################################
238 connect (anonymous1,localhost,"''",,information_schema);
239 SHOW GRANTS;
240 --replace_column 1 ID 3 HOST_NAME 6 TIME
241 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
242 --sorted_result
243 SHOW processlist;
244 --replace_column 1 ID 3 HOST_NAME 6 TIME
245 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
246 --sorted_result
247 SELECT * FROM information_schema.processlist;
248 --sleep 0.3
249
250
251 --echo ####################################################################################
252 --echo 6 Revoke PROCESS privilege from ddicttestuser1
253 --echo connection default (user=root)
254 --echo ####################################################################################
255 connection default;
256 REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost';
257 --sleep 0.3
258
259 --echo ####################################################################################
260 --echo 6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege)
261 connect (con102,localhost,ddicttestuser1,ddictpass,information_schema);
262 --echo Again (compared to state before GRANT PROCESS) only the processes of
263 --echo ddicttestuser1 are visible.
264 --echo ####################################################################################
265 SHOW GRANTS;
266 --replace_column 1 ID 3 HOST_NAME 6 TIME
267 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
268 --sorted_result
269 SHOW processlist;
270 --replace_column 1 ID 3 HOST_NAME 6 TIME
271 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
272 --sorted_result
273 SELECT * FROM information_schema.processlist;
274 --sleep 0.3
275
276
277 --echo ####################################################################################
278 --echo 7 Revoke PROCESS privilege from anonymous user
279 --echo connection default (user=root)
280 --echo ####################################################################################
281 connection default;
282 REVOKE PROCESS ON *.* FROM ''@'localhost';
283 --sleep 0.3
284
285 --echo ####################################################################################
286 --echo 7.1 New connection (anonymous2,localhost,'',,information_schema)
287 connect (anonymous2,localhost,"''",,information_schema);
288 --echo The anonymous user has no more the PROCESS privilege
289 --echo Again only the processes of the anonymous user are visible.
290 --echo ####################################################################################
291 SHOW GRANTS FOR ''@'localhost';
292 if ($fixed_bug_30395)
293 {
294 # Bug#30395 strange results after REVOKE PROCESS ON *.* FROM ...
295 --replace_column 1 ID 3 HOST_NAME 6 TIME
296 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
297 --sorted_result
298 SHOW processlist;
299 }
300 --replace_column 1 ID 3 HOST_NAME 6 TIME
301 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
302 --sorted_result
303 SELECT * FROM information_schema.processlist;
304 --sleep 0.3
305
306
307 --echo ####################################################################################
308 --echo 8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1
309 --echo connection default (user=root)
310 --echo ####################################################################################
311 connection default;
312 GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost';
313 --sleep 0.3
314
315 --echo ####################################################################################
316 --echo 8.1 New connection con103 (ddicttestuser1 with SUPER privilege)
317 connect (con103,localhost,ddicttestuser1,ddictpass,information_schema);
318 --echo Only the processes of ddicttestuser1 user are visible.
319 --echo ####################################################################################
320 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
321 --replace_column 1 ID 3 HOST_NAME 6 TIME
322 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
323 --sorted_result
324 SHOW processlist;
325 --replace_column 1 ID 3 HOST_NAME 6 TIME
326 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
327 --sorted_result
328 SELECT * FROM information_schema.processlist;
329 --sleep 0.3
330
331
332 --echo ####################################################################################
333 --echo 9 Revoke SUPER privilege from user ddicttestuser1
334 --echo connection default (user=root)
335 --echo ####################################################################################
336 connection default;
337 REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost';
338 --sleep 0.3
339
340 --echo ####################################################################################
341 --echo 9.1 New connection con104 (ddicttestuser1 without SUPER privilege)
342 connect (con104,localhost,ddicttestuser1,ddictpass,information_schema);
343 --echo ddicttestuser1 has no more the SUPER privilege.
344 --echo Only the processes of ddicttestuser1 are visible.
345 --echo ####################################################################################
346 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
347 --replace_column 1 ID 3 HOST_NAME 6 TIME
348 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
349 --sorted_result
350 SHOW processlist;
351 --replace_column 1 ID 3 HOST_NAME 6 TIME
352 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
353 --sorted_result
354 SELECT * FROM information_schema.processlist;
355 --sleep 0.3
356
357
358 --echo ####################################################################################
359 --echo 10 Grant SUPER privilege with grant option to user ddicttestuser1.
360 --echo connection default (user=root)
361 --echo ####################################################################################
362 connection default;
363 GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
364 --sleep 0.3
365
366 --echo ####################################################################################
367 --echo 10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION)
368 connect (con105,localhost,ddicttestuser1,ddictpass,information_schema);
369 --echo Try to grant PROCESS privilege to user ddicttestuser2 without having it.
370 --echo ####################################################################################
371 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
372 --error ER_ACCESS_DENIED_ERROR
373 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
374
375 --echo ####################################################################################
376 --echo 10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1
377 --echo connection default (user=root)
378 --echo ####################################################################################
379 connection default;
380 GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
381 --sleep 0.3
382
383 --echo ####################################################################################
384 --echo 10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION)
385 connect (con106,localhost,ddicttestuser1,ddictpass,information_schema);
386 --echo Grant PROCESS privilege to user ddicttestuser2
387 --echo ####################################################################################
388 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
389 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
390 --sleep 0.3
391
392 --echo ####################################################################################
393 --echo 10.4 New connection con200 (ddicttestuser2 with PROCESS privilege)
394 connect (con200,localhost,ddicttestuser2,ddictpass,information_schema);
395 --echo ddicttestuser2 has now the PROCESS privilege and sees all connections
396 --echo ####################################################################################
397 SHOW GRANTS FOR 'ddicttestuser2'@'localhost';
398 --replace_column 1 ID 3 HOST_NAME 6 TIME
399 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
400 --sorted_result
401 SHOW processlist;
402 --replace_column 1 ID 3 HOST_NAME 6 TIME
403 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
404 --sorted_result
405 SELECT * FROM information_schema.processlist;
406 --sleep 0.3
407
408
409 --echo ####################################################################################
410 --echo 11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2
411 --echo connection ddicttestuser1;
412 --echo ####################################################################################
413 connection con106;
414 REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost';
415 --sleep 0.3
416
417 --echo ####################################################################################
418 --echo 11.1 New connection con201 (ddicttestuser2)
419 connect (con201,localhost,ddicttestuser2,ddictpass,information_schema);
420 --echo ddicttestuser2 has no more the PROCESS privilege and can only see own connects
421 --echo ####################################################################################
422 SHOW GRANTS;
423 --replace_column 1 ID 3 HOST_NAME 6 TIME
424 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
425 --sorted_result
426 SHOW processlist;
427 --replace_column 1 ID 3 HOST_NAME 6 TIME
428 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
429 --sorted_result
430 SELECT * FROM information_schema.processlist;
431 --sleep 0.3
432
433 --echo ####################################################################################
434 --echo 11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1
435 --echo connection default (user=root)
436 --echo ####################################################################################
437 connection default;
438 REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost';
439 --sleep 0.3
440
441 --echo ####################################################################################
442 --echo 11.3 New connection con107 (ddicttestuser1)
443 connect (con107,localhost,ddicttestuser1,ddictpass,information_schema);
444 --echo ddicttestuser1 has no more the PROCESS privilege and can only see own connects
445 --echo He is also unable to GRANT the PROCESS privilege to ddicttestuser2
446 --echo ####################################################################################
447 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
448 --error ER_ACCESS_DENIED_ERROR
449 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
450 --replace_column 1 ID 3 HOST_NAME 6 TIME
451 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
452 --sorted_result
453 SHOW processlist;
454 --replace_column 1 ID 3 HOST_NAME 6 TIME
455 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
456 --sorted_result
457 SELECT * FROM information_schema.processlist;
458 --sleep 0.3
459
460
461 --echo ####################################################################################
462 --echo 12 Revoke the SELECT privilege from user ddicttestuser1
463 --echo connection default (user=root)
464 --echo ####################################################################################
465 connection default;
466 REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost';
467 --sleep 0.3
468
469 --echo ####################################################################################
470 --echo 12.1 New connection con108 (ddicttestuser1)
471 connect (con108,localhost,ddicttestuser1,ddictpass,information_schema);
472 --echo ddicttestuser1 has neither PROCESS nor SELECT privilege
473 --echo Manual says: Each MySQL user has the right to access these tables, but can see
474 --echo only the rows ...
475 --echo Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST.
476 --echo ####################################################################################
477 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
478 --replace_column 1 ID 3 HOST_NAME 6 TIME
479 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
480 --sorted_result
481 SHOW processlist;
482 --replace_column 1 ID 3 HOST_NAME 6 TIME
483 --replace_result "init" STATE "starting" STATE "cleaning up" STATE
484 --sorted_result
485 SELECT * FROM information_schema.processlist;
486 --sleep 0.3
487
488 --echo ####################################################################################
489 --echo 12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1.
490 --echo connection default (user=root)
491 --echo ####################################################################################
492 connection default;
493 --error ER_DBACCESS_DENIED_ERROR
494 REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost';
495 --sleep 0.3
496
497
498 --echo ####################################################################################
499 --echo connection default (user=root)
500 --echo Cleanup: close connections, DROP USER etc.
501 --echo ####################################################################################
502 connection default;
503 disconnect con100;
504 disconnect con101;
505 disconnect con102;
506 disconnect con103;
507 disconnect con104;
508 disconnect con105;
509 disconnect con106;
510 disconnect con107;
511 disconnect con108;
512 disconnect con200;
513 disconnect con201;
514 disconnect anonymous1;
515 disconnect anonymous2;
516 DROP USER ddicttestuser1@'localhost';
517 DROP USER ddicttestuser2@'localhost';
518 REVOKE USAGE ON *.* FROM ''@'localhost';
519 DROP USER ''@'localhost';