1
USE information_schema;
2
####################################################################################
4
connection default (user=root)
5
####################################################################################
6
####################################################################################
8
####################################################################################
9
DROP USER ddicttestuser1@'localhost';
10
ERROR HY000: Operation DROP USER failed for 'ddicttestuser1'@'localhost'
11
DROP USER ddicttestuser2@'localhost';
12
ERROR HY000: Operation DROP USER failed for 'ddicttestuser2'@'localhost'
13
CREATE USER ddicttestuser1@'localhost';
14
CREATE USER ddicttestuser2@'localhost';
15
SET PASSWORD FOR ddicttestuser1@'localhost' = PASSWORD('ddictpass');
16
SET PASSWORD FOR ddicttestuser2@'localhost' = PASSWORD('ddictpass');
17
####################################################################################
18
1.2 Establish connection con100 (user=ddicttestuser1 with no PROCESS privilege):
19
####################################################################################
20
####################################################################################
21
2 connection default(user=root with default privileges):
22
SHOW/SELECT shows all processes/threads.
23
####################################################################################
24
SHOW CREATE TABLE processlist;
26
PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` (
27
`ID` bigint(4) NOT NULL DEFAULT '0',
28
`USER` varchar(16) NOT NULL DEFAULT '',
29
`HOST` varchar(64) NOT NULL DEFAULT '',
30
`DB` varchar(64) DEFAULT NULL,
31
`COMMAND` varchar(16) NOT NULL DEFAULT '',
32
`TIME` bigint(7) NOT NULL DEFAULT '0',
33
`STATE` varchar(64) DEFAULT NULL,
35
) ENGINE=MyISAM DEFAULT CHARSET=utf8
37
Id User Host db Command Time State Info
38
ID root localhost information_schema Query TIME NULL SHOW processlist
39
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
40
SELECT * FROM processlist ORDER BY id;
41
ID USER HOST DB COMMAND TIME STATE INFO
42
ID root localhost information_schema Execute TIME executing SELECT * FROM processlist ORDER BY id
43
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
44
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id;
45
ID USER HOST DB COMMAND TIME STATE INFO
46
ID root localhost information_schema Execute TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id
47
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
48
CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
49
UPDATE test.t_processlist SET user='horst' WHERE id=1 ;
50
INSERT INTO processlist SELECT * FROM test.t_processlist;
51
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
52
DROP TABLE test.t_processlist;
53
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION;
54
ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist'
55
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist;
56
UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1;
57
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
58
DROP VIEW test.v_processlist;
59
UPDATE processlist SET user='any_user' WHERE id=1 ;
60
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
61
DELETE FROM processlist WHERE id=1 ;
62
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
63
REVOKE ALL ON processlist FROM current_user;
64
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
65
GRANT INSERT,UPDATE ON processlist TO current_user;
66
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
68
Grants for root@localhost
69
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
70
CREATE INDEX i_processlist ON processlist (user);
71
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
72
DROP TABLE processlist;
73
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
74
ALTER TABLE processlist DROP COLUMN user;
75
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
76
ALTER TABLE processlist ADD COLUMN (my_column INT);
77
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
78
RENAME TABLE processlist TO new_processlist;
79
ERROR 42S02: Unknown table 'new_processlist' in information_schema
80
RENAME TABLE processlist TO files;
81
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
82
CREATE TABLE new_processlist AS SELECT * FROM processlist;
83
ERROR 42S02: Unknown table 'new_processlist' in information_schema
84
DROP DATABASE information_schema;
85
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
86
RENAME DATABASE information_schema TO info_schema;
87
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
88
ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME;
89
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
90
####################################################################################
91
3 Switch to connection con100 (user=ddicttestuser1 with no PROCESS privilege):
92
SHOW/SELECT shows only the processes (1) of the user.
93
####################################################################################
94
SHOW CREATE TABLE processlist;
96
PROCESSLIST CREATE TEMPORARY TABLE `PROCESSLIST` (
97
`ID` bigint(4) NOT NULL DEFAULT '0',
98
`USER` varchar(16) NOT NULL DEFAULT '',
99
`HOST` varchar(64) NOT NULL DEFAULT '',
100
`DB` varchar(64) DEFAULT NULL,
101
`COMMAND` varchar(16) NOT NULL DEFAULT '',
102
`TIME` bigint(7) NOT NULL DEFAULT '0',
103
`STATE` varchar(64) DEFAULT NULL,
105
) ENGINE=MyISAM DEFAULT CHARSET=utf8
107
Id User Host db Command Time State Info
108
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
109
SELECT * FROM processlist ORDER BY id;
110
ID USER HOST DB COMMAND TIME STATE INFO
111
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM processlist ORDER BY id
112
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id;
113
ID USER HOST DB COMMAND TIME STATE INFO
114
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM processlist ORDER BY id
115
CREATE TEMPORARY TABLE test.t_processlist AS SELECT * FROM processlist;
116
UPDATE test.t_processlist SET user='horst' WHERE id=1 ;
117
INSERT INTO processlist SELECT * FROM test.t_processlist;
118
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
119
DROP TABLE test.t_processlist;
120
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist WITH CHECK OPTION;
121
ERROR HY000: CHECK OPTION on non-updatable view 'test.v_processlist'
122
CREATE VIEW test.v_processlist (ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO) AS SELECT * FROM processlist;
123
UPDATE test.v_processlist SET TIME=NOW() WHERE id = 1;
124
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
125
DROP VIEW test.v_processlist;
126
UPDATE processlist SET user='any_user' WHERE id=1 ;
127
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
128
DELETE FROM processlist WHERE id=1 ;
129
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
130
REVOKE ALL ON processlist FROM current_user;
131
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
132
GRANT INSERT,UPDATE ON processlist TO current_user;
133
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
135
Grants for ddicttestuser1@localhost
136
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
137
CREATE INDEX i_processlist ON processlist (user);
138
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
139
DROP TABLE processlist;
140
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
141
ALTER TABLE processlist DROP COLUMN user;
142
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
143
ALTER TABLE processlist ADD COLUMN (my_column INT);
144
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
145
RENAME TABLE processlist TO new_processlist;
146
ERROR 42S02: Unknown table 'new_processlist' in information_schema
147
RENAME TABLE processlist TO files;
148
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
149
CREATE TABLE new_processlist AS SELECT * FROM processlist;
150
ERROR 42S02: Unknown table 'new_processlist' in information_schema
151
DROP DATABASE information_schema;
152
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
153
RENAME DATABASE information_schema TO info_schema;
154
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
155
ALTER DATABASE information_schema UPGRADE DATA DIRECTORY NAME;
156
ERROR 42000: Access denied for user 'ddicttestuser1'@'localhost' to database 'information_schema'
157
####################################################################################
158
4 Grant PROCESS privilege to ddicttestuser1
159
connection default (user=root)
160
####################################################################################
161
GRANT PROCESS ON *.* TO ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass';
162
####################################################################################
163
4.1 Existing connection con100 (ddicttestuser1)
164
The user ddicttestuser1 has the PROCESS privilege, but the connection was
165
established before PROCESS was granted.
166
SHOW/SELECT shows only the processes (1) of the user.
167
####################################################################################
169
Grants for ddicttestuser1@localhost
170
GRANT PROCESS ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
172
Id User Host db Command Time State Info
173
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
174
SELECT * FROM information_schema.processlist;
175
ID USER HOST DB COMMAND TIME STATE INFO
176
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
177
####################################################################################
178
4.2 New connection con101 (ddicttestuser1 with PROCESS privilege)
179
SHOW/SELECT shows all processes/threads.
180
####################################################################################
182
Grants for ddicttestuser1@localhost
183
GRANT PROCESS ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
185
Id User Host db Command Time State Info
186
ID root localhost information_schema Sleep TIME NULL NULL
187
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
188
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
189
SELECT * FROM information_schema.processlist;
190
ID USER HOST DB COMMAND TIME STATE INFO
191
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
192
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
193
ID root localhost information_schema Sleep TIME NULL NULL
194
####################################################################################
195
5 Grant PROCESS privilege to anonymous user.
196
connection default (user=root)
197
####################################################################################
198
GRANT PROCESS ON *.* TO ''@'localhost';
199
####################################################################################
200
5.1 Establish connection (anonymous1,localhost,'',,information_schema)
201
anonymous user with PROCESS privilege
202
SHOW/SELECT shows all processes/threads.
203
####################################################################################
205
Grants for @localhost
206
GRANT PROCESS ON *.* TO ''@'localhost'
208
Id User Host db Command Time State Info
209
ID root localhost information_schema Sleep TIME NULL NULL
210
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
211
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
212
ID localhost information_schema Query TIME NULL SHOW processlist
213
SELECT * FROM information_schema.processlist;
214
ID USER HOST DB COMMAND TIME STATE INFO
215
ID localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
216
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
217
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
218
ID root localhost information_schema Sleep TIME NULL NULL
219
####################################################################################
220
6 Revoke PROCESS privilege from ddicttestuser1
221
connection default (user=root)
222
####################################################################################
223
REVOKE PROCESS ON *.* FROM ddicttestuser1@'localhost' IDENTIFIED BY 'ddictpass';
224
####################################################################################
225
6.1 New connection con102 (ddicttestuser1 has no more PROCESS privilege)
226
Again (compared to state before GRANT PROCESS) only the processes of
227
ddicttestuser1 are visible.
228
####################################################################################
230
Grants for ddicttestuser1@localhost
231
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
233
Id User Host db Command Time State Info
234
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
235
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
236
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
237
SELECT * FROM information_schema.processlist;
238
ID USER HOST DB COMMAND TIME STATE INFO
239
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
240
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
241
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
242
####################################################################################
243
7 Revoke PROCESS privilege from anonymous user + disconnect ddicttestuser1
244
connection default (user=root)
245
####################################################################################
246
REVOKE PROCESS ON *.* FROM ''@'localhost';
247
####################################################################################
248
7.1 New connection (anonymous2,localhost,'',,information_schema)
249
The anonymous user has no more the PROCESS privilege
250
Again only the processes of the anonymous user are visible.
251
####################################################################################
252
SHOW GRANTS FOR ''@'localhost';
253
Grants for @localhost
254
GRANT USAGE ON *.* TO ''@'localhost'
255
SELECT * FROM information_schema.processlist;
256
ID USER HOST DB COMMAND TIME STATE INFO
257
ID localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
258
ID localhost information_schema Sleep TIME NULL NULL
259
####################################################################################
260
8 Grant SUPER (does not imply PROCESS) privilege to ddicttestuser1
261
connection default (user=root)
262
####################################################################################
263
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost';
264
####################################################################################
265
8.1 New connection con103 (ddicttestuser1 with SUPER privilege)
266
Only the processes of ddicttestuser1 user are visible.
267
####################################################################################
268
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
269
Grants for ddicttestuser1@localhost
270
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
272
Id User Host db Command Time State Info
273
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
274
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
275
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
276
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
277
SELECT * FROM information_schema.processlist;
278
ID USER HOST DB COMMAND TIME STATE INFO
279
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
280
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
281
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
282
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
283
####################################################################################
284
9 Revoke SUPER privilege from user ddicttestuser1
285
connection default (user=root)
286
####################################################################################
287
REVOKE SUPER ON *.* FROM 'ddicttestuser1'@'localhost';
288
####################################################################################
289
9.1 New connection con104 (ddicttestuser1 without SUPER privilege)
290
ddicttestuser1 has no more the SUPER privilege.
291
Only the processes of ddicttestuser1 are visible.
292
####################################################################################
293
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
294
Grants for ddicttestuser1@localhost
295
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
297
Id User Host db Command Time State Info
298
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
299
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
300
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
301
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
302
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
303
SELECT * FROM information_schema.processlist;
304
ID USER HOST DB COMMAND TIME STATE INFO
305
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
306
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
307
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
308
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
309
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
310
####################################################################################
311
10 Grant SUPER privilege with grant option to user ddicttestuser1.
312
connection default (user=root)
313
####################################################################################
314
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
315
####################################################################################
316
10.1 New connection con105 (ddicttestuser1 with SUPER privilege and GRANT OPTION)
317
Try to grant PROCESS privilege to user ddicttestuser2 without having it.
318
####################################################################################
319
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
320
Grants for ddicttestuser1@localhost
321
GRANT SUPER ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' WITH GRANT OPTION
322
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
323
ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES)
324
####################################################################################
325
10.2 Grant SUPER and PROCESS privilege with grant option to user ddicttestuser1
326
connection default (user=root)
327
####################################################################################
328
GRANT SUPER,PROCESS ON *.* TO 'ddicttestuser1'@'localhost' WITH GRANT OPTION;
329
####################################################################################
330
10.3 New connection con106 (ddicttestuser1 with SUPER,PROCESS WITH GRANT OPTION)
331
Grant PROCESS privilege to user ddicttestuser2
332
####################################################################################
333
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
334
Grants for ddicttestuser1@localhost
335
GRANT PROCESS, SUPER ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1' WITH GRANT OPTION
336
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
337
####################################################################################
338
10.4 New connection con200 (ddicttestuser2 with PROCESS privilege)
339
ddicttestuser2 has now the PROCESS privilege and sees all connections
340
####################################################################################
341
SHOW GRANTS FOR 'ddicttestuser2'@'localhost';
342
Grants for ddicttestuser2@localhost
343
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
345
Id User Host db Command Time State Info
346
ID root localhost information_schema Sleep TIME NULL NULL
347
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
348
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
349
ID localhost information_schema Sleep TIME NULL NULL
350
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
351
ID localhost information_schema Sleep TIME NULL NULL
352
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
353
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
354
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
355
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
356
ID ddicttestuser2 localhost information_schema Query TIME NULL SHOW processlist
357
SELECT * FROM information_schema.processlist;
358
ID USER HOST DB COMMAND TIME STATE INFO
359
ID ddicttestuser2 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
360
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
361
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
362
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
363
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
364
ID localhost information_schema Sleep TIME NULL NULL
365
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
366
ID localhost information_schema Sleep TIME NULL NULL
367
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
368
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
369
ID root localhost information_schema Sleep TIME NULL NULL
370
####################################################################################
371
11 User ddicttestuser1 revokes PROCESS privilege from user ddicttestuser2
372
connection ddicttestuser1;
373
####################################################################################
374
REVOKE PROCESS ON *.* FROM 'ddicttestuser2'@'localhost';
375
####################################################################################
376
11.1 New connection con201 (ddicttestuser2)
377
ddicttestuser2 has no more the PROCESS privilege and can only see own connects
378
####################################################################################
380
Grants for ddicttestuser2@localhost
381
GRANT USAGE ON *.* TO 'ddicttestuser2'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
383
Id User Host db Command Time State Info
384
ID ddicttestuser2 localhost information_schema Sleep TIME NULL NULL
385
ID ddicttestuser2 localhost information_schema Query TIME NULL SHOW processlist
386
SELECT * FROM information_schema.processlist;
387
ID USER HOST DB COMMAND TIME STATE INFO
388
ID ddicttestuser2 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
389
ID ddicttestuser2 localhost information_schema Sleep TIME NULL NULL
390
####################################################################################
391
11.2 Revoke SUPER,PROCESS,GRANT OPTION privilege from user ddicttestuser1
392
connection default (user=root)
393
####################################################################################
394
REVOKE SUPER,PROCESS,GRANT OPTION ON *.* FROM 'ddicttestuser1'@'localhost';
395
####################################################################################
396
11.3 New connection con107 (ddicttestuser1)
397
ddicttestuser1 has no more the PROCESS privilege and can only see own connects
398
He is also unable to GRANT the PROCESS privilege to ddicttestuser2
399
####################################################################################
400
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
401
Grants for ddicttestuser1@localhost
402
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
403
GRANT PROCESS ON *.* TO 'ddicttestuser2'@'localhost';
404
ERROR 28000: Access denied for user 'ddicttestuser1'@'localhost' (using password: YES)
406
Id User Host db Command Time State Info
407
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
408
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
409
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
410
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
411
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
412
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
413
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
414
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
415
SELECT * FROM information_schema.processlist;
416
ID USER HOST DB COMMAND TIME STATE INFO
417
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
418
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
419
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
420
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
421
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
422
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
423
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
424
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
425
####################################################################################
426
12 Revoke the SELECT privilege from user ddicttestuser1
427
connection default (user=root)
428
####################################################################################
429
REVOKE SELECT ON *.* FROM 'ddicttestuser1'@'localhost';
430
####################################################################################
431
12.1 New connection con108 (ddicttestuser1)
432
ddicttestuser1 has neither PROCESS nor SELECT privilege
433
Manual says: Each MySQL user has the right to access these tables, but can see
435
Therefore the missing SELECT privilege does not affect SELECTs on PROCESSLIST.
436
####################################################################################
437
SHOW GRANTS FOR 'ddicttestuser1'@'localhost';
438
Grants for ddicttestuser1@localhost
439
GRANT USAGE ON *.* TO 'ddicttestuser1'@'localhost' IDENTIFIED BY PASSWORD '*22DA61451703738F203CDB9DB041ACBA1F4760B1'
441
Id User Host db Command Time State Info
442
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
443
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
444
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
445
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
446
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
447
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
448
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
449
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
450
ID ddicttestuser1 localhost information_schema Query TIME NULL SHOW processlist
451
SELECT * FROM information_schema.processlist;
452
ID USER HOST DB COMMAND TIME STATE INFO
453
ID ddicttestuser1 localhost information_schema Execute TIME executing SELECT * FROM information_schema.processlist
454
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
455
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
456
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
457
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
458
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
459
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
460
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
461
ID ddicttestuser1 localhost information_schema Sleep TIME NULL NULL
462
####################################################################################
463
12.2 Revoke only the SELECT privilege on the information_schema from ddicttestuser1.
464
connection default (user=root)
465
####################################################################################
466
REVOKE SELECT ON information_schema.* FROM 'ddicttestuser3'@'localhost';
467
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
468
####################################################################################
469
connection default (user=root)
470
Cleanup: close connections, DROP USER etc.
471
####################################################################################
472
DROP USER ddicttestuser1@'localhost';
473
DROP USER ddicttestuser2@'localhost';
474
REVOKE USAGE ON *.* FROM ''@'localhost';
475
DROP USER ''@'localhost';
added
removed
mysql-test/suite/funcs_1/r/processlist_priv_ps.result
