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
|
--echo ##
--echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
--echo ##
--echo ## Verify handling of the SELECT and PROCESS privileges.
--echo ##
--echo ## Test cases:
--echo ## - Execute SHOW PROCESSLIST (new and legacy) with all privileges
--echo ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with all privileges
--echo ## - Execute SHOW PROCESSLIST (new and legacy) with no privileges
--echo ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with no privileges
--echo ##
--echo ## Results must be manually verified.
--echo
--source include/no_protocol.inc
--source include/not_embedded.inc
--echo ### Setup ###
--echo
select @@global.performance_schema_show_processlist into @save_processlist;
--echo
--echo # Control users
create user user_00@localhost, user_01@localhost;
grant ALL on *.* to user_00@localhost;
grant ALL on *.* to user_01@localhost;
--echo
--echo # Test users
create user user_all@localhost, user_none@localhost;
grant ALL on *.* to user_all@localhost;
grant USAGE on *.* to user_none@localhost;
--echo
flush privileges;
--echo
show grants for user_all@localhost;
--echo
show grants for user_none@localhost;
--echo
use test;
create table test.t1 (s1 int, s2 int, s3 int, s4 int);
--echo
--echo # Connect (con_00, localhost, user_00, , )
connect (con_00, localhost, user_00, , );
--echo # Connect (con_01, localhost, user_01, , )
connect (con_01, localhost, user_01, , );
--echo
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);
--echo
--echo # Lock test.t1, insert/update/deletes will block
lock tables t1 read;
--echo
--echo # Establish 2 connections for user_all
--echo # Connect (con_all_1, localhost, user_all, , )
connect (con_all_1, localhost, user_all, , );
--echo # Connect (con_all_2, localhost, user_all, , )
connect (con_all_2, localhost, user_all, , );
--send insert into test.t1 values (0, 0, 0, 0)
--echo
--echo # Establish 4 connections for user_none
--echo # Connect (con_none_1, localhost, user_none, , )
connect (con_none_1, localhost, user_none, , );
--echo # Connect (con_none_2, localhost, user_none, , )
connect (con_none_2, localhost, user_none, , );
--echo # Connect (con_none_3, localhost, user_none, , )
connect (con_none_3, localhost, user_none, , );
--echo # Connect (con_none_4, localhost, user_none, , )
connect (con_none_4, localhost, user_none, , );
--send update test.t1 set s1 = s1 + 1, s2 = s2 + 2;
--echo
--echo # Connection con_all_1
--connection con_all_1
# Note: When expecting processlist results for all users, the wait_condition
# and subsequent execution of SHOW PROCESSLIST should be conducted from the
# same connection to ensure consistent values in the Command and Info columns.
--echo
--echo # Wait for queries to appear in the processlist table
let $wait_condition = select count(*) >= 2 from information_schema.processlist
where state like '%metadata%';
--source include/wait_condition.inc
--echo
--echo ### Execute SHOW PROCESSLIST with all privileges
--echo ### Expect all users
--echo
--echo # New SHOW PROCESSLIST
let $pfs_spl = on;
--source ../include/processlist_set.inc
--source ../include/processlist_load.inc
--echo
--echo # Legacy SHOW PROCESSLIST
let $pfs_spl = off;
--source ../include/processlist_set.inc
--source ../include/processlist_load.inc
--echo
--echo
--echo ### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges
--echo ### Expect processes only from user_none
--echo
--echo # New SHOW PROCESSLIST
let $pfs_spl = on;
--source ../include/processlist_set.inc
--echo
--echo # Connection con_none_1
--connection con_none_1
--source ../include/processlist_load.inc
--echo
--echo # Confirm that only processes from user_none are visible
--echo
select count(*) as "Expect 0" from performance_schema.processlist
where user not in ('user_none');
--echo
--echo # Legacy SHOW PROCESSLIST
--connection con_00
let $pfs_spl = off;
--source ../include/processlist_set.inc
--echo
--echo # Connection con_none_1
--connection con_none_1
--source ../include/processlist_load.inc
--echo
--echo
--echo ### Clean up ###
--echo
--echo # Disconnect con_00
--connection con_00
--disconnect con_00
--echo # Connection con_01, unlock test.t1, disconnect
--connection con_01
unlock tables;
--disconnect con_01
--echo # Disconnect con_all_1
--connection con_all_1
--disconnect con_all_1
--echo # Reap con_all_2, disconnect
--connection con_all_2
--reap
--disconnect con_all_2
--echo # Disconnect con_none_1
--connection con_none_1
--disconnect con_none_1
--echo # Disconnect con_none_2
--connection con_none_2
--disconnect con_none_2
--echo # Disconnect con_none_3
--connection con_none_3
--disconnect con_none_3
--echo # Reap con_none_4, disconnect
--connection con_none_4
--reap
--disconnect con_none_4
--echo
--echo # Connection default
--connection default
--echo
drop table test.t1;
drop user user_00@localhost;
drop user user_01@localhost;
drop user user_all@localhost;
drop user user_none@localhost;
--echo
set @@global.performance_schema_show_processlist = @save_processlist;
|
