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 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `user_all`@`localhost`
33 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `user_all`@`localhost`
34
35 show grants for user_none@localhost;
36 Grants for user_none@localhost
37 GRANT USAGE ON *.* TO `user_none`@`localhost`
38
39 use test;
40 create table test.t1 (s1 int, s2 int, s3 int, s4 int);
41
42 # Connect (con_00, localhost, user_00, , )
43 # Connect (con_01, localhost, user_01, , )
44
45 insert into test.t1 values(1, 1, 1, 1);
46 insert into test.t1 values(2, 2, 2, 2);
47 insert into test.t1 values(3, 3, 3, 3);
48 insert into test.t1 values(4, 4, 4, 4);
49
50 # Lock test.t1, insert/update/deletes will block
51 lock tables t1 read;
52
53 # Establish 2 connections for user_all
54 # Connect (con_all_1, localhost, user_all, , )
55 # Connect (con_all_2, localhost, user_all, , )
56 insert into test.t1 values (0, 0, 0, 0);
57
58 # Establish 4 connections for user_none
59 # Connect (con_none_1, localhost, user_none, , )
60 # Connect (con_none_2, localhost, user_none, , )
61 # Connect (con_none_3, localhost, user_none, , )
62 # Connect (con_none_4, localhost, user_none, , )
63 update test.t1 set s1 = s1 + 1, s2 = s2 + 2;;
64
65 # Connection con_all_1
66
67 # Wait for queries to appear in the processlist table
68
69 ### Execute SHOW PROCESSLIST with all privileges
70 ### Expect all users
71
72 # New SHOW PROCESSLIST
73 set @@global.performance_schema_show_processlist = on;
74 Warnings:
75 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
76
77 SHOW FULL PROCESSLIST;
78 Id User Host db Command Time State Info
79 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
80 <Id> root <Host> test <Command> <Time> <State> NULL
81 <Id> user_00 <Host> test <Command> <Time> <State> NULL
82 <Id> user_01 <Host> test <Command> <Time> <State> NULL
83 <Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
84 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
85 <Id> user_none <Host> test <Command> <Time> <State> NULL
86 <Id> user_none <Host> test <Command> <Time> <State> NULL
87 <Id> user_none <Host> test <Command> <Time> <State> NULL
88 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
89
90 # Performance Schema processlist table
91
92 select * from performance_schema.processlist order by user, id;
93 ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
94 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL PRIMARY
95 <Id> root <Host> test <Command> <Time> <State> NULL PRIMARY
96 <Id> user_00 <Host> test <Command> <Time> <State> NULL PRIMARY
97 <Id> user_01 <Host> test <Command> <Time> <State> NULL PRIMARY
98 <Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
99 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) PRIMARY
100 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
101 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
102 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
103 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2 PRIMARY
104
105 # Information Schema processlist table
106
107 select * from information_schema.processlist order by user, id;
108 ID USER HOST DB COMMAND TIME STATE INFO
109 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
110 <Id> root <Host> test <Command> <Time> <State> NULL
111 <Id> user_00 <Host> test <Command> <Time> <State> NULL
112 <Id> user_01 <Host> test <Command> <Time> <State> NULL
113 <Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
114 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
115 <Id> user_none <Host> test <Command> <Time> <State> NULL
116 <Id> user_none <Host> test <Command> <Time> <State> NULL
117 <Id> user_none <Host> test <Command> <Time> <State> NULL
118 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
119 Warnings:
120 <Id> 1287 <Host>
121
122 # Legacy SHOW PROCESSLIST
123 set @@global.performance_schema_show_processlist = off;
124 Warnings:
125 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
126
127 SHOW FULL PROCESSLIST;
128 Id User Host db Command Time State Info
129 <Id> 1287 <Host>
130 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
131 <Id> root <Host> test <Command> <Time> <State> NULL
132 <Id> user_00 <Host> test <Command> <Time> <State> NULL
133 <Id> user_01 <Host> test <Command> <Time> <State> NULL
134 <Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
135 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
136 <Id> user_none <Host> test <Command> <Time> <State> NULL
137 <Id> user_none <Host> test <Command> <Time> <State> NULL
138 <Id> user_none <Host> test <Command> <Time> <State> NULL
139 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
140 Warnings:
141
142 # Performance Schema processlist table
143
144 select * from performance_schema.processlist order by user, id;
145 ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
146 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL PRIMARY
147 <Id> root <Host> test <Command> <Time> <State> NULL PRIMARY
148 <Id> user_00 <Host> test <Command> <Time> <State> NULL PRIMARY
149 <Id> user_01 <Host> test <Command> <Time> <State> NULL PRIMARY
150 <Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
151 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) PRIMARY
152 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
153 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
154 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
155 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2 PRIMARY
156
157 # Information Schema processlist table
158
159 select * from information_schema.processlist order by user, id;
160 ID USER HOST DB COMMAND TIME STATE INFO
161 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
162 <Id> root <Host> test <Command> <Time> <State> NULL
163 <Id> user_00 <Host> test <Command> <Time> <State> NULL
164 <Id> user_01 <Host> test <Command> <Time> <State> NULL
165 <Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
166 <Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
167 <Id> user_none <Host> test <Command> <Time> <State> NULL
168 <Id> user_none <Host> test <Command> <Time> <State> NULL
169 <Id> user_none <Host> test <Command> <Time> <State> NULL
170 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
171 Warnings:
172 <Id> 1287 <Host>
173
174
175 ### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges
176 ### Expect processes only from user_none
177
178 # New SHOW PROCESSLIST
179 set @@global.performance_schema_show_processlist = on;
180 Warnings:
181 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
182
183 # Connection con_none_1
184
185 SHOW FULL PROCESSLIST;
186 Id User Host db Command Time State Info
187 <Id> user_none <Host> test <Command> <Time> <State> NULL
188 <Id> user_none <Host> test <Command> <Time> <State> NULL
189 <Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
190 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
191
192 # Performance Schema processlist table
193
194 select * from performance_schema.processlist order by user, id;
195 ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
196 <Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
197 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
198 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
199 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2 PRIMARY
200
201 # Information Schema processlist table
202
203 select * from information_schema.processlist order by user, id;
204 ID USER HOST DB COMMAND TIME STATE INFO
205 <Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
206 <Id> user_none <Host> test <Command> <Time> <State> NULL
207 <Id> user_none <Host> test <Command> <Time> <State> NULL
208 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
209 Warnings:
210 <Id> 1287 <Host>
211
212 # Confirm that only processes from user_none are visible
213
214 select count(*) as "Expect 0" from performance_schema.processlist
215 where user not in ('user_none');
216 Expect 0
217 0
218
219 # Legacy SHOW PROCESSLIST
220 set @@global.performance_schema_show_processlist = off;
221 Warnings:
222 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
223
224 # Connection con_none_1
225
226 SHOW FULL PROCESSLIST;
227 Id User Host db Command Time State Info
228 <Id> 1287 <Host>
229 <Id> user_none <Host> test <Command> <Time> <State> NULL
230 <Id> user_none <Host> test <Command> <Time> <State> NULL
231 <Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
232 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
233 Warnings:
234
235 # Performance Schema processlist table
236
237 select * from performance_schema.processlist order by user, id;
238 ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
239 <Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
240 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
241 <Id> user_none <Host> test <Command> <Time> <State> NULL PRIMARY
242 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2 PRIMARY
243
244 # Information Schema processlist table
245
246 select * from information_schema.processlist order by user, id;
247 ID USER HOST DB COMMAND TIME STATE INFO
248 <Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
249 <Id> user_none <Host> test <Command> <Time> <State> NULL
250 <Id> user_none <Host> test <Command> <Time> <State> NULL
251 <Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
252 Warnings:
253 <Id> 1287 <Host>
254
255
256 ### Clean up ###
257
258 # Disconnect con_00
259 # Connection con_01, unlock test.t1, disconnect
260 unlock tables;
261 # Disconnect con_all_1
262 # Reap con_all_2, disconnect
263 # Disconnect con_none_1
264 # Disconnect con_none_2
265 # Disconnect con_none_3
266 # Reap con_none_4, disconnect
267
268 # Connection default
269
270 drop table test.t1;
271 drop user user_00@localhost;
272 drop user user_01@localhost;
273 drop user user_all@localhost;
274 drop user user_none@localhost;
275
276 set @@global.performance_schema_show_processlist = @save_processlist;
277 Warnings:
278 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.