| 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. |