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
|
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 ('hosta', 'user1', '%', 'YES', 'YES');
insert into performance_schema.setup_actors
values ('%', 'user2', '%', 'YES', 'YES');
insert into performance_schema.setup_actors
values ('localhost', 'user3', '%', 'YES', 'YES');
insert into performance_schema.setup_actors
values ('hostb', '%', '%', 'YES', 'YES');
select * from performance_schema.setup_actors
order by USER, HOST, ROLE;
HOST USER ROLE ENABLED HISTORY
hostb % % YES YES
hosta user1 % YES YES
% user2 % YES YES
localhost user3 % YES YES
create user user1@localhost;
grant ALL on *.* to user1@localhost;
create user user2@localhost;
grant ALL on *.* to user2@localhost;
create user user3@localhost;
grant ALL on *.* to user3@localhost;
create user user4@localhost;
grant ALL on *.* to user4@localhost;
create user user5@localhost;
grant select on test.* to user5@localhost;
flush privileges;
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
connection default;
insert into performance_schema.setup_actors
values ('%', 'user1', '%', 'YES', 'YES');
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 NO user1 localhost
disconnect con1;
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 con2;
connection default;
drop table if exists test.t1;
create table test.t1 (col1 bigint);
lock table test.t1 write;
connect con3, localhost, user3, , ;
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 user3 localhost
# Send a statement to the server, but do not wait till the result
# comes back. We will pull this later.
insert into test.t1 set col1 = 1;
connect con4, localhost, user4, , ;
# Poll till INFO is no more NULL and State = 'Waiting for table metadata lock'.
select count(*) = 1
from performance_schema.threads T inner join information_schema.PROCESSLIST P
on T.PROCESSLIST_ID = P.ID and T.PROCESSLIST_USER = P.USER and
T.PROCESSLIST_HOST = P.HOST and T.PROCESSLIST_DB = P.DB and
T.PROCESSLIST_COMMAND = P.COMMAND and T.PROCESSLIST_INFO = P.INFO
where T.PROCESSLIST_USER = 'user3' and T.NAME = 'thread/sql/one_connection';
count(*) = 1
1
connection default;
unlock tables;
connection con3;
# Reap the result of the no more blocked insert
connection default;
drop table test.t1;
disconnect con3;
connection con4;
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 user4 localhost
disconnect con4;
connection default;
insert into performance_schema.setup_actors
values ('localhost', '%', '%', 'YES', 'YES');
select * from performance_schema.setup_actors
order by USER, HOST, ROLE;
HOST USER ROLE ENABLED HISTORY
hostb % % YES YES
localhost % % YES YES
% user1 % YES YES
hosta user1 % YES YES
% user2 % YES YES
localhost user3 % YES YES
connect con4b, localhost, user4, , ;
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 user4 localhost
disconnect con4b;
connection default;
insert into performance_schema.setup_actors
values ('%', 'user5', '%', 'YES', 'YES');
create sql security definer view test.v1 as select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.threads
where PROCESSLIST_ID = connection_id();
connect con5, localhost, user5, , ;
select * from performance_schema.threads;
ERROR 42000: SELECT command denied to user 'user5'@'localhost' for table 'threads'
select * from test.v1;
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user5 localhost
disconnect con5;
connection default;
drop view test.v1;
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;
revoke all privileges, grant option from user5@localhost;
drop user user1@localhost;
drop user user2@localhost;
drop user user3@localhost;
drop user user4@localhost;
drop user user5@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
|
