summaryrefslogtreecommitdiff
path: root/mysql-test/suite/perfschema/r/setup_actors.result
blob: 3474b678321f2cdb5ee7680e6a2a2104843f5fe3 (plain)
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
%	%	%
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	%
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', '%');
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', '%', '%');
select * from performance_schema.setup_actors
order by USER, HOST, ROLE;
HOST	USER	ROLE
hostb	%	%
localhost	%	%
%	user1	%
hosta	user1	%
%	user2	%
localhost	user3	%
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', '%');
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 ('%', '%', '%');
select * from performance_schema.setup_actors;
HOST	USER	ROLE
%	%	%