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
|
select * from performance_schema.setup_actors;
HOST USER ROLE
% % %
truncate table performance_schema.setup_actors;
insert into performance_schema.setup_actors
values ('hosta', 'user1', '%');
insert into performance_schema.setup_actors
values ('%', 'user2', '%');
insert into performance_schema.setup_actors
values ('localhost', 'user3', '%');
insert into performance_schema.setup_actors
values ('hostb', '%', '%');
select * from performance_schema.setup_actors
order by USER, HOST, ROLE;
HOST USER ROLE
hostb % %
hosta user1 %
% user2 %
localhost user3 %
grant ALL on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
grant ALL on *.* to user3@localhost;
grant ALL on *.* to user4@localhost;
grant select on test.* to user5@localhost;
flush privileges;
# Switch to (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 connection default
insert into performance_schema.setup_actors
values ('%', 'user1', '%');
# Switch to 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
# Switch to (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
# Switch to connection default
drop table if exists test.t1;
create table test.t1 (col1 bigint);
lock table test.t1 write;
# Switch to (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;
# Switch to (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
# Switch to connection default
unlock tables;
# Switch to connection con3 and reap the result of the no more blocked insert
# Switch to connection default
drop table test.t1;
# Disconnect con3
# Switch to 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
# Switch to connection default
insert into performance_schema.setup_actors
values ('localhost', '%', '%');
select * from performance_schema.setup_actors
order by USER, HOST, ROLE;
HOST USER ROLE
hostb % %
localhost % %
% user1 %
hosta user1 %
% user2 %
localhost user3 %
# Switch to (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
# Switch to connection default
insert into performance_schema.setup_actors
values ('%', 'user5', '%');
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();
# Switch to (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
# Switch to connection default and cleanup
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 ('%', '%', '%');
select * from performance_schema.setup_actors;
HOST USER ROLE
% % %
|
