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
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
|
USE information_schema;
####################################################################################
1 Prepare test.
connection default (user=root)
####################################################################################
####################################################################################
1.1 Create two user
####################################################################################
DROP USER ddicttestuser1@'localhost';
DROP USER ddicttestuser2@'localhost';
CREATE USER ddicttestuser1@'localhost';
CREATE USER ddicttestuser2@'localhost';
SET PASSWORD FOR ddicttestuser1@'localhost' = PASSWORD('ddictpass');
SET PASSWORD FOR ddicttestuser2@'localhost' = PASSWORD('ddictpass');
####################################################################################
1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege):
####################################################################################
####################################################################################
2 connection default(user=root with default privileges):
SHOW/SELECT shows all processes/threads.
####################################################################################
SHOW CREATE TABLE processlist;
Table Create Table
PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` (
`ID` bigint(4) NOT NULL DEFAULT '0',
`USER` varchar(128) NOT NULL DEFAULT '',
`HOST` varchar(64) NOT NULL DEFAULT '',
`DB` varchar(64) DEFAULT NULL,
`COMMAND` varchar(16) NOT NULL DEFAULT '',
`TIME` int(7) NOT NULL DEFAULT '0',
`STATE` varchar(64) DEFAULT NULL,
`INFO` longtext,
`TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000',
`STAGE` tinyint(2) NOT NULL DEFAULT '0',
`MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0',
`PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000',
`MEMORY_USED` int(7) NOT NULL DEFAULT '0',
`EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0',
`QUERY_ID` bigint(4) NOT NULL DEFAULT '0',
`INFO_BINARY` blob
) DEFAULT CHARSET=utf8
SHOW processlist;
Id User Host db Command Time State Info Progress
ID root HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
SELECT * FROM processlist ORDER BY id;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID root HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM processlist ORDER BY id TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM processlist ORDER BY id
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY FROM processlist ORDER BY id;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID root HOST_NAME information_schema Execute TIME Filling schema table SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY FROM processlist ORDER BY id TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY FROM processlist ORDER BY id
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
UPDATE test.t_processlist SET user='horst' WHERE id=1 ;
INSERT INTO processlist SELECT * FROM test.t_processlist;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE test.t_processlist;
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY) AS SELECT * FROM processlist WITH CHECK OPTION;
ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist'
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY) AS SELECT * FROM processlist;
DROP VIEW test.v_processlist;
UPDATE processlist SET user='any_user' WHERE id=1 ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM processlist WHERE id=1 ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
REVOKE ALL ON processlist FROM current_user;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
GRANT INSERT,UPDATE ON processlist TO current_user;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
SHOW GRANTS;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
CREATE INDEX i_processlist ON processlist (user);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE processlist;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE processlist DROP COLUMN user;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE processlist ADD COLUMN (my_column INT);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
RENAME TABLE processlist TO new_processlist;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
RENAME TABLE processlist TO files;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE TABLE new_processlist AS SELECT * FROM processlist;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE information_schema;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
RENAME DATABASE information_schema TO info_schema;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1
ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
####################################################################################
3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege):
SHOW/SELECT shows only the processes (1) of the user.
####################################################################################
SHOW CREATE TABLE processlist;
Table Create Table
PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` (
`ID` bigint(4) NOT NULL DEFAULT '0',
`USER` varchar(128) NOT NULL DEFAULT '',
`HOST` varchar(64) NOT NULL DEFAULT '',
`DB` varchar(64) DEFAULT NULL,
`COMMAND` varchar(16) NOT NULL DEFAULT '',
`TIME` int(7) NOT NULL DEFAULT '0',
`STATE` varchar(64) DEFAULT NULL,
`INFO` longtext,
`TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000',
`STAGE` tinyint(2) NOT NULL DEFAULT '0',
`MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0',
`PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000',
`MEMORY_USED` int(7) NOT NULL DEFAULT '0',
`EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0',
`QUERY_ID` bigint(4) NOT NULL DEFAULT '0',
`INFO_BINARY` blob
) DEFAULT CHARSET=utf8
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM processlist ORDER BY id;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM processlist ORDER BY id TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM processlist ORDER BY id
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY FROM processlist ORDER BY id;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY FROM processlist ORDER BY id TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY FROM processlist ORDER BY id
CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
UPDATE test.t_processlist SET user='horst' WHERE id=1 ;
INSERT INTO processlist SELECT * FROM test.t_processlist;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
DROP TABLE test.t_processlist;
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY) AS SELECT * FROM processlist WITH CHECK OPTION;
ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist'
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE, MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID, INFO_BINARY) AS SELECT * FROM processlist;
DROP VIEW test.v_processlist;
UPDATE processlist SET user='any_user' WHERE id=1 ;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
DELETE FROM processlist WHERE id=1 ;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
REVOKE ALL ON processlist FROM current_user;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
GRANT INSERT,UPDATE ON processlist TO current_user;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
SHOW GRANTS;
Grants for ddicttestuser1@localhost
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
CREATE INDEX i_processlist ON processlist (user);
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
DROP TABLE processlist;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
ALTER TABLE processlist DROP COLUMN user;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
ALTER TABLE processlist ADD COLUMN (my_column INT);
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
RENAME TABLE processlist TO new_processlist;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
RENAME TABLE processlist TO files;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
CREATE TABLE new_processlist AS SELECT * FROM processlist;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
DROP DATABASE information_schema;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
RENAME DATABASE information_schema TO info_schema;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DATABASE information_schema TO info_schema' at line 1
ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME;
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
####################################################################################
4 Grant PROCESS privilege to ddicttestuser1
connection default (user=root)
####################################################################################
GRANT PROCESS ON *.* TO ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass';
####################################################################################
4.1 Existing connection con100 (ddicttestuser1)
The user ddicttestuser1 has the PROCESS privilege, but the connection was
established before PROCESS was granted.
SHOW/SELECT shows only the processes (1) of the user.
####################################################################################
SHOW GRANTS;
Grants for ddicttestuser1@localhost
GRANT PROCESS ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
####################################################################################
4.2 New connection con101 (ddicttestuser1 with PROCESS privilege)
SHOW/SELECT shows all processes/threads.
####################################################################################
SHOW GRANTS;
Grants for ddicttestuser1@localhost
GRANT PROCESS ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
5 Grant PROCESS privilege to anonymous user.
connection default (user=root)
####################################################################################
create user ''@'localhost';
GRANT PROCESS ON *.* TO ''@'localhost';
####################################################################################
5.1 Establish connection (anonymous1,localhost,'',,information_schema)
anonymous user with PROCESS privilege
SHOW/SELECT shows all processes/threads.
####################################################################################
SHOW GRANTS;
Grants for @localhost
GRANT PROCESS ON *.* TO ''@'localhost'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
6 Revoke PROCESS privilege from ddicttestuser1
connection default (user=root)
####################################################################################
REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost';
####################################################################################
6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege)
Again (compared to state before GRANT PROCESS) only the processes of
ddicttestuser1 are visible.
####################################################################################
SHOW GRANTS;
Grants for ddicttestuser1@localhost
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
7 Revoke PROCESS privilege from anonymous user
connection default (user=root)
####################################################################################
REVOKE PROCESS ON *.* FROM ''@'localhost';
####################################################################################
7.1 New connection (anonymous2,localhost,'',,information_schema)
The anonymous user has no more the PROCESS privilege
Again only the processes of the anonymous user are visible.
####################################################################################
SHOW GRANTS FOR ''@'localhost';
Grants for @localhost
GRANT USAGE ON *.* TO ''@'localhost'
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1
connection default (user=root)
####################################################################################
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost';
####################################################################################
8.1 New connection con103 (ddicttestuser1 with SUPER privilege)
Only the processes of ddicttestuser1 user are visible.
####################################################################################
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
Grants for ddicttestuser1@localhost
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
9 Revoke SUPER privilege from user ddicttestuser1
connection default (user=root)
####################################################################################
REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost';
####################################################################################
9.1 New connection con104 (ddicttestuser1 without SUPER privilege)
ddicttestuser1 has no more the SUPER privilege.
Only the processes of ddicttestuser1 are visible.
####################################################################################
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
Grants for ddicttestuser1@localhost
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
10 Grant SUPER privilege with grant option to user ddicttestuser1.
connection default (user=root)
####################################################################################
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
####################################################################################
10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION)
Try to grant PROCESS privilege to user ddicttestuser2 without having it.
####################################################################################
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
Grants for ddicttestuser1@localhost
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' WITH GRANT OPTION
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES)
####################################################################################
10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1
connection default (user=root)
####################################################################################
GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
####################################################################################
10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION)
Grant PROCESS privilege to user ddicttestuser2
####################################################################################
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
Grants for ddicttestuser1@localhost
GRANT PROCESS, SUPER ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' WITH GRANT OPTION
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
####################################################################################
10.4 New connection con200 (ddicttestuser2 with PROCESS privilege)
ddicttestuser2 has now the PROCESS privilege and sees all connections
####################################################################################
SHOW GRANTS FOR 'ddicttestuser2'@'localhost';
Grants for ddicttestuser2@localhost
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser2 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser2 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID root HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2
connection ddicttestuser1;
####################################################################################
REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost';
####################################################################################
11.1 New connection con201 (ddicttestuser2)
ddicttestuser2 has no more the PROCESS privilege and can only see own connects
####################################################################################
SHOW GRANTS;
Grants for ddicttestuser2@localhost
GRANT USAGE ON *.* TO 'ddicttestuser2'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser2 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser2 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser2 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1
connection default (user=root)
####################################################################################
REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost';
####################################################################################
11.3 New connection con107 (ddicttestuser1)
ddicttestuser1 has no more the PROCESS privilege and can only see own connects
He is also unable to GRANT the PROCESS privilege to ddicttestuser2
####################################################################################
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
Grants for ddicttestuser1@localhost
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES)
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
12 Revoke the SELECT privilege from user ddicttestuser1
connection default (user=root)
####################################################################################
REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost';
####################################################################################
12.1 New connection con108 (ddicttestuser1)
ddicttestuser1 has neither PROCESS nor SELECT privilege
Manual says: Each MySQL user has the right to access these tables, but can see
only the rows ...
Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST.
####################################################################################
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
Grants for ddicttestuser1@localhost
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
SHOW processlist;
Id User Host db Command Time State Info Progress
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS
ID ddicttestuser1 HOST_NAME information_schema Query TIME init SHOW processlist TIME_MS
SELECT * FROM information_schema.processlist;
ID USER HOST DB COMMAND TIME STATE INFO TIME_MS STAGE MAX_STAGE PROGRESS MEMORY_USED EXAMINED_ROWS QUERY_ID INFO_BINARY
ID ddicttestuser1 HOST_NAME information_schema Execute TIME Filling schema table SELECT * FROM information_schema.processlist TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID SELECT * FROM information_schema.processlist
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
ID ddicttestuser1 HOST_NAME information_schema Sleep TIME NULL TIME_MS 0 0 0.000 MEMORY ROWS QUERY_ID NULL
####################################################################################
12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1.
connection default (user=root)
####################################################################################
REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
####################################################################################
connection default (user=root)
Cleanup: close connections, DROP USER etc.
####################################################################################
DROP USER ddicttestuser1@'localhost';
DROP USER ddicttestuser2@'localhost';
REVOKE USAGE ON *.* FROM ''@'localhost';
DROP USER ''@'localhost';
|
