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
|
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', '%', 'NO', 'NO');
set @orig_sql_mode= @@sql_mode;
set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
grant ALL on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
set sql_mode= @orig_sql_mode;
flush privileges;
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user1 localhost
# Switch to (con2, localhost, user2, , )
connect con2, localhost, user2, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user2 localhost
# Switch to connection default
connection default;
update performance_schema.setup_actors set ENABLED='NO' where USER='user1';
update performance_schema.setup_actors set ENABLED='YES' where USER='user2';
# Switch to connection con1
connection con1;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user1 localhost
# Switch to connection con2
connection con2;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user2 localhost
# Disconnect con1 and con2
disconnect con1;
disconnect con2;
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user1 localhost
# Switch to (con2 localhost, user2, , )
connect con2, localhost, user2, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user2 localhost
# Disconnect con1 and con2
disconnect con1;
disconnect con2;
# Switch to connection default
connection default;
revoke update on *.* from 'user2'@'localhost';
flush privileges;
# Switch to (con2 localhost, user2, , )
connect con2, localhost, user2, , ;
update performance_schema.setup_actors
set ENABLED='NO';
ERROR 42000: UPDATE command denied to user 'user2'@'localhost' for table `performance_schema`.`setup_actors`
disconnect con2;
connection default;
insert into performance_schema.setup_actors
values ('%', 'user1', '%', 'YES', 'YES');
insert into performance_schema.setup_actors
values ('localhost', '%', '%', 'NO', 'NO');
insert into performance_schema.setup_actors
values ('%', '%', '%', 'YES', 'YES');
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user1 localhost
disconnect con1;
connection default;
delete from performance_schema.setup_actors where
HOST='localhost' and USER='user1';
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user1 localhost
disconnect con1;
connection default;
delete from performance_schema.setup_actors where
HOST='%' and USER='user1';
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user1 localhost
disconnect con1;
connection default;
delete from performance_schema.setup_actors where
HOST='localhost' and USER='%';
# Switch to (con1, localhost, user1, , )
connect con1, localhost, user1, , ;
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user1 localhost
disconnect con1;
connection default;
revoke all privileges, grant option from user1@localhost;
revoke all privileges, grant option from user2@localhost;
drop user user1@localhost;
drop user user2@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
|
