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