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
|
##
## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
##
## Verify handling of the SELECT and PROCESS privileges.
##
## Test cases:
## - Execute SHOW PROCESSLIST (new and legacy) with all privileges
## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with all privileges
## - Execute SHOW PROCESSLIST (new and legacy) with no privileges
## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with no privileges
##
## Results must be manually verified.
### Setup ###
select @@global.performance_schema_show_processlist into @save_processlist;
# Control users
create user user_00@localhost, user_01@localhost;
grant ALL on *.* to user_00@localhost;
grant ALL on *.* to user_01@localhost;
# Test users
create user user_all@localhost, user_none@localhost;
grant ALL on *.* to user_all@localhost;
grant USAGE on *.* to user_none@localhost;
flush privileges;
show grants for user_all@localhost;
Grants for user_all@localhost
GRANT ALL PRIVILEGES ON *.* TO 'user_all'@'localhost'
show grants for user_none@localhost;
Grants for user_none@localhost
GRANT USAGE ON *.* TO 'user_none'@'localhost'
use test;
create table test.t1 (s1 int, s2 int, s3 int, s4 int);
# Connect (con_00, localhost, user_00, , )
# Connect (con_01, localhost, user_01, , )
insert into test.t1 values(1, 1, 1, 1);
insert into test.t1 values(2, 2, 2, 2);
insert into test.t1 values(3, 3, 3, 3);
insert into test.t1 values(4, 4, 4, 4);
# Lock test.t1, insert/update/deletes will block
lock tables t1 read;
# Establish 2 connections for user_all
# Connect (con_all_1, localhost, user_all, , )
# Connect (con_all_2, localhost, user_all, , )
insert into test.t1 values (0, 0, 0, 0);
# Establish 4 connections for user_none
# Connect (con_none_1, localhost, user_none, , )
# Connect (con_none_2, localhost, user_none, , )
# Connect (con_none_3, localhost, user_none, , )
# Connect (con_none_4, localhost, user_none, , )
update test.t1 set s1 = s1 + 1, s2 = s2 + 2;;
# Connection con_all_1
# Wait for queries to appear in the processlist table
### Execute SHOW PROCESSLIST with all privileges
### Expect all users
# New SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = on;
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Legacy SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = off;
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges
### Expect processes only from user_none
# New SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = on;
# Connection con_none_1
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Confirm that only processes from user_none are visible
select count(*) as "Expect 0" from performance_schema.processlist
where user not in ('user_none');
Expect 0
0
# Legacy SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = off;
# Connection con_none_1
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
### Clean up ###
# Disconnect con_00
# Connection con_01, unlock test.t1, disconnect
unlock tables;
# Disconnect con_all_1
# Reap con_all_2, disconnect
# Disconnect con_none_1
# Disconnect con_none_2
# Disconnect con_none_3
# Reap con_none_4, disconnect
# Connection default
drop table test.t1;
drop user user_00@localhost;
drop user user_01@localhost;
drop user user_all@localhost;
drop user user_none@localhost;
set @@global.performance_schema_show_processlist = @save_processlist;
|
