Debian Codegraph @

mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result

raw
1 USE information_schema;
2 ####################################################################################
3 1 Prepare test.
4 connection default (user=root)
5 ####################################################################################
6 ####################################################################################
7 1.1 Create two user
8 ####################################################################################
9 DROP USER ddicttestuser1@'localhost';
10 DROP USER ddicttestuser2@'localhost';
11 CREATE USER ddicttestuser1@'localhost';
12 CREATE USER ddicttestuser2@'localhost';
13 SET PASSWORD FOR ddicttestuser1@'localhost' = 'ddictpass';
14 SET PASSWORD FOR ddicttestuser2@'localhost' = 'ddictpass';
15 ####################################################################################
16 1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege):
17 ####################################################################################
18 ####################################################################################
19 2 connection default(user=root with default privileges):
20 SHOW/SELECT shows all processes/threads.
21 ####################################################################################
22 Timeout in wait_condition.inc for SELECT COUNT(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST
23 WHERE DB = 'information_schema' AND COMMAND = 'Sleep' AND USER = 'ddicttestuser1'
24 SHOW CREATE TABLE processlist;
25 Table Create Table
26 PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` (
27 `ID` bigint unsigned NOT NULL DEFAULT '0',
28 `USER` varchar(32) NOT NULL DEFAULT '',
29 `HOST` varchar(261) NOT NULL DEFAULT '',
30 `DB` varchar(64) DEFAULT NULL,
31 `COMMAND` varchar(16) NOT NULL DEFAULT '',
32 `TIME` int NOT NULL DEFAULT '0',
33 `STATE` varchar(64) DEFAULT NULL,
34 `INFO` longtext
35 ) ENGINE=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8mb3
36 SHOW processlist;
37 Id User Host db Command Time State Info
38 ID 1287 HOST_NAME
39 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
40 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
41 ID root HOST_NAME information_schema Query TIME STATE SHOW processlist
42 Warnings:
43 SELECT * FROM processlist ORDER BY id;
44 ID USER HOST DB COMMAND TIME STATE INFO
45 ID 1287 HOST_NAME
46 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
47 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
48 ID root HOST_NAME information_schema Query TIME executing SELECT * FROM processlist ORDER BY id
49 Warnings:
50 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id;
51 ID USER HOST DB COMMAND TIME STATE INFO
52 ID 1287 HOST_NAME
53 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
54 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
55 ID root HOST_NAME information_schema Query TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id
56 Warnings:
57 CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
58 Warnings:
59 Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
60 UPDATE test.t_processlist SET user='horst' WHERE id=1 ;
61 INSERT INTO processlist SELECT * FROM test.t_processlist;
62 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
63 DROP TABLE test.t_processlist;
64 CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION;
65 ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist'
66 CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist;
67 UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1;
68 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
69 DROP VIEW test.v_processlist;
70 UPDATE processlist SET user='any_user' WHERE id=1 ;
71 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
72 DELETE FROM processlist WHERE id=1 ;
73 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
74 REVOKE ALL ON processlist FROM current_user;
75 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
76 GRANT INSERT,UPDATE ON processlist TO current_user;
77 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
78 SHOW GRANTS;
79 Grants for root@localhost
80 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 `root`@`localhost` WITH GRANT OPTION
81 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 `root`@`localhost` WITH GRANT OPTION
82 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
83 CREATE INDEX i_processlist ON processlist (user);
84 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
85 DROP TABLE processlist;
86 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
87 ALTER TABLE processlist DROP COLUMN user;
88 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
89 ALTER TABLE processlist ADD COLUMN (my_column INT);
90 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
91 RENAME TABLE processlist TO new_processlist;
92 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
93 RENAME TABLE processlist TO files;
94 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
95 CREATE TABLE new_processlist AS SELECT * FROM processlist;
96 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
97 DROP DATABASE information_schema;
98 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
99 RENAME DATABASE information_schema TO info_schema;
100 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1
101 ####################################################################################
102 3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege):
103 SHOW/SELECT shows only the processes (1) of the user.
104 ####################################################################################
105 SHOW CREATE TABLE processlist;
106 Table Create Table
107 PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` (
108 `ID` bigint unsigned NOT NULL DEFAULT '0',
109 `USER` varchar(32) NOT NULL DEFAULT '',
110 `HOST` varchar(261) NOT NULL DEFAULT '',
111 `DB` varchar(64) DEFAULT NULL,
112 `COMMAND` varchar(16) NOT NULL DEFAULT '',
113 `TIME` int NOT NULL DEFAULT '0',
114 `STATE` varchar(64) DEFAULT NULL,
115 `INFO` longtext
116 ) ENGINE=TMP_TABLE_ENGINE DEFAULT CHARSET=utf8mb3
117 SHOW processlist;
118 Id User Host db Command Time State Info
119 ID 1287 HOST_NAME
120 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
121 Warnings:
122 SELECT * FROM processlist ORDER BY id;
123 ID USER HOST DB COMMAND TIME STATE INFO
124 ID 1287 HOST_NAME
125 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM processlist ORDER BY id
126 Warnings:
127 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id;
128 ID USER HOST DB COMMAND TIME STATE INFO
129 ID 1287 HOST_NAME
130 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id
131 Warnings:
132 CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
133 Warnings:
134 Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
135 UPDATE test.t_processlist SET user='horst' WHERE id=1 ;
136 INSERT INTO processlist SELECT * FROM test.t_processlist;
137 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
138 DROP TABLE test.t_processlist;
139 CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION;
140 ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist'
141 CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist;
142 UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1;
143 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
144 DROP VIEW test.v_processlist;
145 UPDATE processlist SET user='any_user' WHERE id=1 ;
146 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
147 DELETE FROM processlist WHERE id=1 ;
148 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
149 REVOKE ALL ON processlist FROM current_user;
150 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
151 GRANT INSERT,UPDATE ON processlist TO current_user;
152 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
153 SHOW GRANTS;
154 Grants for ddicttestuser1@localhost
155 GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost`
156 CREATE INDEX i_processlist ON processlist (user);
157 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
158 DROP TABLE processlist;
159 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
160 ALTER TABLE processlist DROP COLUMN user;
161 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
162 ALTER TABLE processlist ADD COLUMN (my_column INT);
163 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
164 RENAME TABLE processlist TO new_processlist;
165 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
166 RENAME TABLE processlist TO files;
167 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
168 CREATE TABLE new_processlist AS SELECT * FROM processlist;
169 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
170 DROP DATABASE information_schema;
171 ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
172 RENAME DATABASE information_schema TO info_schema;
173 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1
174 ####################################################################################
175 4 Grant PROCESS privilege to ddicttestuser1
176 connection default (user=root)
177 ####################################################################################
178 GRANT PROCESS ON *.* TO ddicttestuser1@'localhost';
179 ####################################################################################
180 4.1 Existing connection con100 (ddicttestuser1)
181 The user ddicttestuser1 has the PROCESS privilege, but the connection was
182 established before PROCESS was granted.
183 SHOW/SELECT shows only the processes (1) of the user.
184 ####################################################################################
185 SHOW GRANTS;
186 Grants for ddicttestuser1@localhost
187 GRANT PROCESS ON *.* TO `ddicttestuser1`@`localhost`
188 SHOW processlist;
189 Id User Host db Command Time State Info
190 ID 1287 HOST_NAME
191 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
192 Warnings:
193 SELECT * FROM information_schema.processlist;
194 ID USER HOST DB COMMAND TIME STATE INFO
195 ID 1287 HOST_NAME
196 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
197 Warnings:
198 ####################################################################################
199 4.2 New connection con101 (ddicttestuser1 with PROCESS privilege)
200 SHOW/SELECT shows all processes/threads.
201 ####################################################################################
202 SHOW GRANTS;
203 Grants for ddicttestuser1@localhost
204 GRANT PROCESS ON *.* TO `ddicttestuser1`@`localhost`
205 SHOW processlist;
206 Id User Host db Command Time State Info
207 ID 1287 HOST_NAME
208 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
209 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
210 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
211 ID root HOST_NAME information_schema Sleep TIME NULL
212 Warnings:
213 SELECT * FROM information_schema.processlist;
214 ID USER HOST DB COMMAND TIME STATE INFO
215 ID 1287 HOST_NAME
216 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
217 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
218 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
219 ID root HOST_NAME information_schema Sleep TIME NULL
220 Warnings:
221 ####################################################################################
222 5 Grant PROCESS privilege to anonymous user.
223 connection default (user=root)
224 ####################################################################################
225 CREATE USER ''@'localhost';
226 GRANT PROCESS ON *.* TO ''@'localhost';
227 ####################################################################################
228 5.1 Establish connection (anonymous1,localhost,'',,information_schema)
229 anonymous user with PROCESS privilege
230 SHOW/SELECT shows all processes/threads.
231 ####################################################################################
232 SHOW GRANTS;
233 Grants for @localhost
234 GRANT PROCESS ON *.* TO ``@`localhost`
235 SHOW processlist;
236 Id User Host db Command Time State Info
237 ID HOST_NAME information_schema Query TIME STATE SHOW processlist
238 ID 1287 HOST_NAME
239 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
240 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
241 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
242 ID root HOST_NAME information_schema Sleep TIME NULL
243 Warnings:
244 SELECT * FROM information_schema.processlist;
245 ID USER HOST DB COMMAND TIME STATE INFO
246 ID HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
247 ID 1287 HOST_NAME
248 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
249 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
250 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
251 ID root HOST_NAME information_schema Sleep TIME NULL
252 Warnings:
253 ####################################################################################
254 6 Revoke PROCESS privilege from ddicttestuser1
255 connection default (user=root)
256 ####################################################################################
257 REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost';
258 ####################################################################################
259 6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege)
260 Again (compared to state before GRANT PROCESS) only the processes of
261 ddicttestuser1 are visible.
262 ####################################################################################
263 SHOW GRANTS;
264 Grants for ddicttestuser1@localhost
265 GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost`
266 SHOW processlist;
267 Id User Host db Command Time State Info
268 ID 1287 HOST_NAME
269 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
270 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
271 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
272 Warnings:
273 SELECT * FROM information_schema.processlist;
274 ID USER HOST DB COMMAND TIME STATE INFO
275 ID 1287 HOST_NAME
276 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
277 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
278 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
279 Warnings:
280 ####################################################################################
281 7 Revoke PROCESS privilege from anonymous user
282 connection default (user=root)
283 ####################################################################################
284 REVOKE PROCESS ON *.* FROM ''@'localhost';
285 ####################################################################################
286 7.1 New connection (anonymous2,localhost,'',,information_schema)
287 The anonymous user has no more the PROCESS privilege
288 Again only the processes of the anonymous user are visible.
289 ####################################################################################
290 SHOW GRANTS FOR ''@'localhost';
291 Grants for @localhost
292 GRANT USAGE ON *.* TO ``@`localhost`
293 SELECT * FROM information_schema.processlist;
294 ID USER HOST DB COMMAND TIME STATE INFO
295 ID HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
296 ID HOST_NAME information_schema Sleep TIME NULL
297 ID 1287 HOST_NAME
298 Warnings:
299 ####################################################################################
300 8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1
301 connection default (user=root)
302 ####################################################################################
303 GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost';
304 Warnings:
305 Warning 1287 The SUPER privilege identifier is deprecated
306 ####################################################################################
307 8.1 New connection con103 (ddicttestuser1 with SUPER privilege)
308 Only the processes of ddicttestuser1 user are visible.
309 ####################################################################################
310 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
311 Grants for ddicttestuser1@localhost
312 GRANT SUPER ON *.* TO `ddicttestuser1`@`localhost`
313 SHOW processlist;
314 Id User Host db Command Time State Info
315 ID 1287 HOST_NAME
316 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
317 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
318 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
319 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
320 Warnings:
321 SELECT * FROM information_schema.processlist;
322 ID USER HOST DB COMMAND TIME STATE INFO
323 ID 1287 HOST_NAME
324 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
325 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
326 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
327 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
328 Warnings:
329 ####################################################################################
330 9 Revoke SUPER privilege from user ddicttestuser1
331 connection default (user=root)
332 ####################################################################################
333 REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost';
334 Warnings:
335 Warning 1287 The SUPER privilege identifier is deprecated
336 ####################################################################################
337 9.1 New connection con104 (ddicttestuser1 without SUPER privilege)
338 ddicttestuser1 has no more the SUPER privilege.
339 Only the processes of ddicttestuser1 are visible.
340 ####################################################################################
341 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
342 Grants for ddicttestuser1@localhost
343 GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost`
344 SHOW processlist;
345 Id User Host db Command Time State Info
346 ID 1287 HOST_NAME
347 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
348 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
349 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
350 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
351 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
352 Warnings:
353 SELECT * FROM information_schema.processlist;
354 ID USER HOST DB COMMAND TIME STATE INFO
355 ID 1287 HOST_NAME
356 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
357 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
358 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
359 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
360 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
361 Warnings:
362 ####################################################################################
363 10 Grant SUPER privilege with grant option to user ddicttestuser1.
364 connection default (user=root)
365 ####################################################################################
366 GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
367 Warnings:
368 Warning 1287 The SUPER privilege identifier is deprecated
369 ####################################################################################
370 10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION)
371 Try to grant PROCESS privilege to user ddicttestuser2 without having it.
372 ####################################################################################
373 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
374 Grants for ddicttestuser1@localhost
375 GRANT SUPER ON *.* TO `ddicttestuser1`@`localhost` WITH GRANT OPTION
376 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
377 ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES)
378 ####################################################################################
379 10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1
380 connection default (user=root)
381 ####################################################################################
382 GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
383 Warnings:
384 Warning 1287 The SUPER privilege identifier is deprecated
385 ####################################################################################
386 10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION)
387 Grant PROCESS privilege to user ddicttestuser2
388 ####################################################################################
389 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
390 Grants for ddicttestuser1@localhost
391 GRANT PROCESS, SUPER ON *.* TO `ddicttestuser1`@`localhost` WITH GRANT OPTION
392 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
393 ####################################################################################
394 10.4 New connection con200 (ddicttestuser2 with PROCESS privilege)
395 ddicttestuser2 has now the PROCESS privilege and sees all connections
396 ####################################################################################
397 SHOW GRANTS FOR 'ddicttestuser2'@'localhost';
398 Grants for ddicttestuser2@localhost
399 GRANT PROCESS ON *.* TO `ddicttestuser2`@`localhost`
400 SHOW processlist;
401 Id User Host db Command Time State Info
402 ID HOST_NAME information_schema Sleep TIME NULL
403 ID HOST_NAME information_schema Sleep TIME NULL
404 ID 1287 HOST_NAME
405 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
406 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
407 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
408 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
409 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
410 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
411 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
412 ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist
413 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
414 ID root HOST_NAME information_schema Sleep TIME NULL
415 Warnings:
416 SELECT * FROM information_schema.processlist;
417 ID USER HOST DB COMMAND TIME STATE INFO
418 ID HOST_NAME information_schema Sleep TIME NULL
419 ID HOST_NAME information_schema Sleep TIME NULL
420 ID 1287 HOST_NAME
421 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
422 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
423 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
424 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
425 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
426 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
427 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
428 ID ddicttestuser2 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
429 ID event_scheduler HOST_NAME NULL Daemon TIME Waiting on empty queue NULL
430 ID root HOST_NAME information_schema Sleep TIME NULL
431 Warnings:
432 ####################################################################################
433 11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2
434 connection ddicttestuser1;
435 ####################################################################################
436 REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost';
437 ####################################################################################
438 11.1 New connection con201 (ddicttestuser2)
439 ddicttestuser2 has no more the PROCESS privilege and can only see own connects
440 ####################################################################################
441 SHOW GRANTS;
442 Grants for ddicttestuser2@localhost
443 GRANT USAGE ON *.* TO `ddicttestuser2`@`localhost`
444 SHOW processlist;
445 Id User Host db Command Time State Info
446 ID 1287 HOST_NAME
447 ID ddicttestuser2 HOST_NAME information_schema Query TIME STATE SHOW processlist
448 ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL
449 Warnings:
450 SELECT * FROM information_schema.processlist;
451 ID USER HOST DB COMMAND TIME STATE INFO
452 ID 1287 HOST_NAME
453 ID ddicttestuser2 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
454 ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL
455 Warnings:
456 ####################################################################################
457 11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1
458 connection default (user=root)
459 ####################################################################################
460 REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost';
461 Warnings:
462 Warning 1287 The SUPER privilege identifier is deprecated
463 ####################################################################################
464 11.3 New connection con107 (ddicttestuser1)
465 ddicttestuser1 has no more the PROCESS privilege and can only see own connects
466 He is also unable to GRANT the PROCESS privilege to ddicttestuser2
467 ####################################################################################
468 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
469 Grants for ddicttestuser1@localhost
470 GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost`
471 GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
472 ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES)
473 SHOW processlist;
474 Id User Host db Command Time State Info
475 ID 1287 HOST_NAME
476 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
477 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
478 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
479 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
480 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
481 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
482 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
483 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
484 Warnings:
485 SELECT * FROM information_schema.processlist;
486 ID USER HOST DB COMMAND TIME STATE INFO
487 ID 1287 HOST_NAME
488 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
489 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
490 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
491 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
492 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
493 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
494 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
495 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
496 Warnings:
497 ####################################################################################
498 12 Revoke the SELECT privilege from user ddicttestuser1
499 connection default (user=root)
500 ####################################################################################
501 REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost';
502 ####################################################################################
503 12.1 New connection con108 (ddicttestuser1)
504 ddicttestuser1 has neither PROCESS nor SELECT privilege
505 Manual says: Each MySQL user has the right to access these tables, but can see
506 only the rows ...
507 Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST.
508 ####################################################################################
509 SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
510 Grants for ddicttestuser1@localhost
511 GRANT USAGE ON *.* TO `ddicttestuser1`@`localhost`
512 SHOW processlist;
513 Id User Host db Command Time State Info
514 ID 1287 HOST_NAME
515 ID ddicttestuser1 HOST_NAME information_schema Query TIME STATE SHOW processlist
516 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
517 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
518 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
519 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
520 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
521 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
522 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
523 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
524 Warnings:
525 SELECT * FROM information_schema.processlist;
526 ID USER HOST DB COMMAND TIME STATE INFO
527 ID 1287 HOST_NAME
528 ID ddicttestuser1 HOST_NAME information_schema Query TIME executing SELECT * FROM information_schema.processlist
529 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
530 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
531 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
532 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
533 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
534 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
535 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
536 ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL
537 Warnings:
538 ####################################################################################
539 12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1.
540 connection default (user=root)
541 ####################################################################################
542 REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost';
543 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
544 ####################################################################################
545 connection default (user=root)
546 Cleanup: close connections, DROP USER etc.
547 ####################################################################################
548 DROP USER ddicttestuser1@'localhost';
549 DROP USER ddicttestuser2@'localhost';
550 REVOKE USAGE ON *.* FROM ''@'localhost';
551 DROP USER ''@'localhost';