Debian Codegraph @

mysql-test/suite/perfschema/t/setup_actors_enabled.test

raw
1 # Check the impact of changes done in ENABLED column in
2 # performance_schema.setup_actors.
3
4 --source include/not_windows.inc
5
6 # The initial number of rows is 1. The initial row always looks like this:
7 # mysql> select * from performance_schema.setup_actors;
8 # +------+------+------+---------+---------+
9 # | HOST | USER | ROLE | ENABLED | HISTORY |
10 # +------+------+------+---------+---------+
11 # | % | % | % | YES | YES |
12 # +------+------+------+---------+---------+
13 select * from performance_schema.setup_actors;
14
15 truncate table performance_schema.setup_actors;
16
17 insert into performance_schema.setup_actors
18 values ('localhost', 'user1', '%', 'YES', 'YES');
19
20 insert into performance_schema.setup_actors
21 values ('localhost', 'user2', '%', 'NO', 'NO');
22
23 create user user1@localhost, user2@localhost;
24 grant ALL on *.* to user1@localhost;
25 grant ALL on *.* to user2@localhost;
26
27 flush privileges;
28
29 --echo # Switch to (con1, localhost, user1, , )
30 connect (con1, localhost, user1, , );
31
32 # INSTRUMENTED must be YES because there is a match in
33 # performance_schema.setup_actors and its ENABLED
34 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
35 from performance_schema.threads
36 where PROCESSLIST_ID = connection_id();
37 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
38 where PROCESSLIST_ID = connection_id()`;
39
40 --echo # Switch to (con2, localhost, user2, , )
41 connect (con2, localhost, user2, , );
42
43 # INSTRUMENTED must be NO because there is a match in
44 # performance_schema.setup_actors but its DISABLED.
45 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
46 from performance_schema.threads
47 where PROCESSLIST_ID = connection_id();
48 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
49 where PROCESSLIST_ID = connection_id()`;
50
51 --echo # Switch to connection default
52 --connection default
53 update performance_schema.setup_actors set ENABLED='NO' where USER='user1';
54 update performance_schema.setup_actors set ENABLED='YES' where USER='user2';
55
56 --echo # Switch to connection con1
57 --connection con1
58
59 # INSTRUMENTED must still be YES because update to setup_actors doesn't affect
60 # existing connetions.
61 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
62 from performance_schema.threads
63 where PROCESSLIST_ID = connection_id();
64 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
65 where PROCESSLIST_ID = connection_id()`;
66
67 --echo # Switch to connection con2
68 --connection con2
69
70 # INSTRUMENTED must still be NO because update to setup_actors doesn't affect
71 # existing connetions.
72 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
73 from performance_schema.threads
74 where PROCESSLIST_ID = connection_id();
75 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
76 where PROCESSLIST_ID = connection_id()`;
77
78 --echo # Disconnect con1 and con2
79 --disconnect con1
80 --disconnect con2
81
82 # Now reconnect
83 --echo # Switch to (con1, localhost, user1, , )
84 connect (con1, localhost, user1, , );
85
86 # INSTRUMENTED must still be NO because update to setup_actors affects
87 # new connetions.
88 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
89 from performance_schema.threads
90 where PROCESSLIST_ID = connection_id();
91 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
92 where PROCESSLIST_ID = connection_id()`;
93
94 --echo # Switch to (con2 localhost, user2, , )
95 connect (con2, localhost, user2, , );
96
97 # INSTRUMENTED must still be YES because update to setup_actors affects
98 # new connetions.
99 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
100 from performance_schema.threads
101 where PROCESSLIST_ID = connection_id();
102 let $con1_thread_id= `select THREAD_ID from performance_schema.threads
103 where PROCESSLIST_ID = connection_id()`;
104
105 --echo # Disconnect con1 and con2
106 --disconnect con1
107 --disconnect con2
108
109 --echo # Switch to connection default
110 --connection default
111
112 # Checking if update is allowed on performance_schema.setup_actors ENABLED
113 # column after revoke update privilege
114
115 revoke update on *.* from 'user2'@'localhost';
116
117 flush privileges;
118
119 --echo # Switch to (con2 localhost, user2, , )
120 connect (con2, localhost, user2, , );
121 --error ER_TABLEACCESS_DENIED_ERROR
122 update performance_schema.setup_actors
123 set ENABLED='NO';
124
125 --disconnect con2
126 --connection default
127
128 # Checking for Rules Order
129 # 1. a match for 'U1' and 'H1'.
130 # 2. then for 'U1' and '%'.
131 # 3. then for '%' and 'H1'.
132 # 4. then for '%' and '%'.
133
134 insert into performance_schema.setup_actors
135 values ('%', 'user1', '%', 'YES', 'YES');
136
137
138 insert into performance_schema.setup_actors
139 values ('localhost', '%', '%', 'NO', 'NO');
140
141 insert into performance_schema.setup_actors
142 values ('%', '%', '%', 'YES', 'YES');
143
144
145 --echo # Switch to (con1, localhost, user1, , )
146 connect (con1, localhost, user1, , );
147
148 # INSTRUMENTED must still be NO as it will match rule1
149 # new connetions.
150 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
151 from performance_schema.threads
152 where PROCESSLIST_ID = connection_id();
153
154 --disconnect con1
155 --connection default
156
157 delete from performance_schema.setup_actors where
158 HOST='localhost' and USER='user1';
159
160 --echo # Switch to (con1, localhost, user1, , )
161 connect (con1, localhost, user1, , );
162
163 # INSTRUMENTED must still be YES as it will match rule2
164 # new connetions.
165 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
166 from performance_schema.threads
167 where PROCESSLIST_ID = connection_id();
168
169
170 --disconnect con1
171 --connection default
172
173 delete from performance_schema.setup_actors where
174 HOST='%' and USER='user1';
175
176 --echo # Switch to (con1, localhost, user1, , )
177 connect (con1, localhost, user1, , );
178
179 # INSTRUMENTED must still be NO as it will match rule3
180 # new connetions.
181 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
182 from performance_schema.threads
183 where PROCESSLIST_ID = connection_id();
184
185 --disconnect con1
186 --connection default
187
188 delete from performance_schema.setup_actors where
189 HOST='localhost' and USER='%';
190
191 --echo # Switch to (con1, localhost, user1, , )
192 connect (con1, localhost, user1, , );
193
194 # INSTRUMENTED must still be YES as it will match rule4
195 # new connetions.
196 select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
197 from performance_schema.threads
198 where PROCESSLIST_ID = connection_id();
199
200 --disconnect con1
201 --connection default
202
203 revoke all privileges, grant option from user1@localhost;
204 revoke all privileges, grant option from user2@localhost;
205 drop user user1@localhost;
206 drop user user2@localhost;
207 flush privileges;
208
209 truncate table performance_schema.setup_actors;
210
211 insert into performance_schema.setup_actors
212 values ('%', '%', '%', 'YES', 'YES');
213
214 select * from performance_schema.setup_actors;
215