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