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
|
select * from performance_schema.setup_actors;
HOST USER ROLE ENABLED HISTORY
% % % YES YES
truncate table performance_schema.setup_actors;
insert into performance_schema.setup_actors
values ('localhost', 'user1', '%', 'YES', 'YES');
insert into performance_schema.setup_actors
values ('localhost', 'user2', '%', 'YES', 'NO');
insert into performance_schema.setup_actors
values ('localhost', 'user3', '%', 'NO', 'YES');
insert into performance_schema.setup_actors
values ('localhost', 'user4', '%', 'NO', 'NO');
create user user1@localhost;
create user user2@localhost;
create user user3@localhost;
create user user4@localhost;
grant ALL on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
grant ALL on *.* to user3@localhost;
grant ALL on *.* to user4@localhost;
flush privileges;
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES YES user1 localhost
# Switch to (con2, localhost, user2, , )
connect con2, localhost, user2, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES NO user2 localhost
# Switch to (con3, localhost, user3, , )
connect con3, localhost, user3, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO YES user3 localhost
# Switch to (con4, localhost, user4, , )
connect con4, localhost, user4, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO NO user4 localhost
# Switch to connection default
connection default;
update performance_schema.setup_actors
set HISTORY='NO' where USER in ('user1', 'user3');
update performance_schema.setup_actors
set HISTORY='YES' where USER in ('user2', 'user4');
# Switch to connection con1
connection con1;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES YES user1 localhost
# Switch to connection con2
connection con2;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES NO user2 localhost
# Switch to connection con3
connection con3;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO YES user3 localhost
# Switch to connection con4
connection con4;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO NO user4 localhost
# Disconnect all con
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES NO user1 localhost
# Switch to (con2 localhost, user2, , )
connect con2, localhost, user2, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES YES user2 localhost
# Switch to (con3, localhost, user3, , )
connect con3, localhost, user3, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO NO user3 localhost
# Switch to (con4 localhost, user4, , )
connect con4, localhost, user4, , ;
select NAME, TYPE, INSTRUMENTED, HISTORY, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED HISTORY PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO YES user4 localhost
# Disconnect all con
disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
# Switch to connection default
connection default;
revoke all privileges, grant option from user1@localhost;
revoke all privileges, grant option from user2@localhost;
revoke all privileges, grant option from user3@localhost;
revoke all privileges, grant option from user4@localhost;
drop user user1@localhost;
drop user user2@localhost;
drop user user3@localhost;
drop user user4@localhost;
flush privileges;
truncate table performance_schema.setup_actors;
insert into performance_schema.setup_actors
values ('%', '%', '%', 'YES', 'YES');
select * from performance_schema.setup_actors;
HOST USER ROLE ENABLED HISTORY
% % % YES YES
|
