Debian Codegraph @

mysql-test/suite/perfschema/r/processlist_acl.result

raw
1 ##
2 ## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
3 ##
4 ## Verify handling of the SELECT and PROCESS privileges.
5 ##
6 ## Test cases:
7 ## - Execute SHOW PROCESSLIST (new and legacy) with all privileges
8 ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with all privileges
9 ## - Execute SHOW PROCESSLIST (new and legacy) with no privileges
10 ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with no privileges
11 ##
12 ## Results must be manually verified.
13
14 ### Setup ###
15
16 select @@global.performance_schema_show_processlist into @save_processlist;
17
18 # Control users
19 create user user_00@localhost, user_01@localhost;
20 grant ALL on *.* to user_00@localhost;
21 grant ALL on *.* to user_01@localhost;
22
23 # Test users
24 create user user_all@localhost, user_none@localhost;
25 grant ALL on *.* to user_all@localhost;
26 grant USAGE on *.* to user_none@localhost;
27
28 flush privileges;
29
30 show grants for user_all@localhost;
31 Grants for user_all@localhost
32 GRANT ALL PRIVILEGES ON *.* TO 'user_all'@'localhost'
33
34 show grants for user_none@localhost;
35 Grants for user_none@localhost
36 GRANT USAGE ON *.* TO 'user_none'@'localhost'
37
38 use test;
39 create table test.t1 (s1 int, s2 int, s3 int, s4 int);
40
41 # Connect (con_00, localhost, user_00, , )
42 # Connect (con_01, localhost, user_01, , )
43
44 insert into test.t1 values(1, 1, 1, 1);
45 insert into test.t1 values(2, 2, 2, 2);
46 insert into test.t1 values(3, 3, 3, 3);
47 insert into test.t1 values(4, 4, 4, 4);
48
49 # Lock test.t1, insert/update/deletes will block
50 lock tables t1 read;
51
52 # Establish 2 connections for user_all
53 # Connect (con_all_1, localhost, user_all, , )
54 # Connect (con_all_2, localhost, user_all, , )
55 insert into test.t1 values (0, 0, 0, 0);
56
57 # Establish 4 connections for user_none
58 # Connect (con_none_1, localhost, user_none, , )
59 # Connect (con_none_2, localhost, user_none, , )
60 # Connect (con_none_3, localhost, user_none, , )
61 # Connect (con_none_4, localhost, user_none, , )
62 update test.t1 set s1 = s1 + 1, s2 = s2 + 2;;
63
64 # Connection con_all_1
65
66 # Wait for queries to appear in the processlist table
67
68 ### Execute SHOW PROCESSLIST with all privileges
69 ### Expect all users
70
71 # New SHOW PROCESSLIST
72 set @@global.performance_schema_show_processlist = on;
73
74 SHOW FULL PROCESSLIST;
75 Id User Host db Command Time State Info
76 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
77 <Id> root <Host> test <Command> <Time> <State> NULL
78 <Id> user_00 <Host> test <Command> <Time> <State> NULL
79 <Id> user_01 <Host> test <Command> <Time> <State> NULL
80 <Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
81 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
82 <Id> user_none <Host> test <Command> <Time> <State> NULL
83 <Id> user_none <Host> test <Command> <Time> <State> NULL
84 <Id> user_none <Host> test <Command> <Time> <State> NULL
85 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
86
87 # Performance Schema processlist table
88
89 select * from performance_schema.processlist order by user, id;
90 ID USER HOST DB COMMAND TIME STATE INFO
91 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
92 <Id> root <Host> test <Command> <Time> <State> NULL
93 <Id> user_00 <Host> test <Command> <Time> <State> NULL
94 <Id> user_01 <Host> test <Command> <Time> <State> NULL
95 <Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
96 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
97 <Id> user_none <Host> test <Command> <Time> <State> NULL
98 <Id> user_none <Host> test <Command> <Time> <State> NULL
99 <Id> user_none <Host> test <Command> <Time> <State> NULL
100 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
101
102 # Information Schema processlist table
103
104 select * from information_schema.processlist order by user, id;
105 ID USER HOST DB COMMAND TIME STATE INFO
106 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
107 <Id> root <Host> test <Command> <Time> <State> NULL
108 <Id> user_00 <Host> test <Command> <Time> <State> NULL
109 <Id> user_01 <Host> test <Command> <Time> <State> NULL
110 <Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
111 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
112 <Id> user_none <Host> test <Command> <Time> <State> NULL
113 <Id> user_none <Host> test <Command> <Time> <State> NULL
114 <Id> user_none <Host> test <Command> <Time> <State> NULL
115 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
116
117 # Legacy SHOW PROCESSLIST
118 set @@global.performance_schema_show_processlist = off;
119
120 SHOW FULL PROCESSLIST;
121 Id User Host db Command Time State Info
122 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
123 <Id> root <Host> test <Command> <Time> <State> NULL
124 <Id> user_00 <Host> test <Command> <Time> <State> NULL
125 <Id> user_01 <Host> test <Command> <Time> <State> NULL
126 <Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
127 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
128 <Id> user_none <Host> test <Command> <Time> <State> NULL
129 <Id> user_none <Host> test <Command> <Time> <State> NULL
130 <Id> user_none <Host> test <Command> <Time> <State> NULL
131 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
132
133 # Performance Schema processlist table
134
135 select * from performance_schema.processlist order by user, id;
136 ID USER HOST DB COMMAND TIME STATE INFO
137 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
138 <Id> root <Host> test <Command> <Time> <State> NULL
139 <Id> user_00 <Host> test <Command> <Time> <State> NULL
140 <Id> user_01 <Host> test <Command> <Time> <State> NULL
141 <Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
142 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
143 <Id> user_none <Host> test <Command> <Time> <State> NULL
144 <Id> user_none <Host> test <Command> <Time> <State> NULL
145 <Id> user_none <Host> test <Command> <Time> <State> NULL
146 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
147
148 # Information Schema processlist table
149
150 select * from information_schema.processlist order by user, id;
151 ID USER HOST DB COMMAND TIME STATE INFO
152 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
153 <Id> root <Host> test <Command> <Time> <State> NULL
154 <Id> user_00 <Host> test <Command> <Time> <State> NULL
155 <Id> user_01 <Host> test <Command> <Time> <State> NULL
156 <Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
157 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
158 <Id> user_none <Host> test <Command> <Time> <State> NULL
159 <Id> user_none <Host> test <Command> <Time> <State> NULL
160 <Id> user_none <Host> test <Command> <Time> <State> NULL
161 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
162
163
164 ### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges
165 ### Expect processes only from user_none
166
167 # New SHOW PROCESSLIST
168 set @@global.performance_schema_show_processlist = on;
169
170 # Connection con_none_1
171
172 SHOW FULL PROCESSLIST;
173 Id User Host db Command Time State Info
174 <Id> user_none <Host> test <Command> <Time> <State> NULL
175 <Id> user_none <Host> test <Command> <Time> <State> NULL
176 <Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
177 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
178
179 # Performance Schema processlist table
180
181 select * from performance_schema.processlist order by user, id;
182 ID USER HOST DB COMMAND TIME STATE INFO
183 <Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
184 <Id> user_none <Host> test <Command> <Time> <State> NULL
185 <Id> user_none <Host> test <Command> <Time> <State> NULL
186 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
187
188 # Information Schema processlist table
189
190 select * from information_schema.processlist order by user, id;
191 ID USER HOST DB COMMAND TIME STATE INFO
192 <Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
193 <Id> user_none <Host> test <Command> <Time> <State> NULL
194 <Id> user_none <Host> test <Command> <Time> <State> NULL
195 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
196
197 # Confirm that only processes from user_none are visible
198
199 select count(*) as "Expect 0" from performance_schema.processlist
200 where user not in ('user_none');
201 Expect 0
202 0
203
204 # Legacy SHOW PROCESSLIST
205 set @@global.performance_schema_show_processlist = off;
206
207 # Connection con_none_1
208
209 SHOW FULL PROCESSLIST;
210 Id User Host db Command Time State Info
211 <Id> user_none <Host> test <Command> <Time> <State> NULL
212 <Id> user_none <Host> test <Command> <Time> <State> NULL
213 <Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
214 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
215
216 # Performance Schema processlist table
217
218 select * from performance_schema.processlist order by user, id;
219 ID USER HOST DB COMMAND TIME STATE INFO
220 <Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
221 <Id> user_none <Host> test <Command> <Time> <State> NULL
222 <Id> user_none <Host> test <Command> <Time> <State> NULL
223 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
224
225 # Information Schema processlist table
226
227 select * from information_schema.processlist order by user, id;
228 ID USER HOST DB COMMAND TIME STATE INFO
229 <Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
230 <Id> user_none <Host> test <Command> <Time> <State> NULL
231 <Id> user_none <Host> test <Command> <Time> <State> NULL
232 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
233
234
235 ### Clean up ###
236
237 # Disconnect con_00
238 # Connection con_01, unlock test.t1, disconnect
239 unlock tables;
240 # Disconnect con_all_1
241 # Reap con_all_2, disconnect
242 # Disconnect con_none_1
243 # Disconnect con_none_2
244 # Disconnect con_none_3
245 # Reap con_none_4, disconnect
246
247 # Connection default
248
249 drop table test.t1;
250 drop user user_00@localhost;
251 drop user user_01@localhost;
252 drop user user_all@localhost;
253 drop user user_none@localhost;
254
255 set @@global.performance_schema_show_processlist = @save_processlist;