Debian Codegraph @

mysql-test/suite/auth_sec/t/system_user_kill_connection.test

raw
1 ###############################################################################
2 # WL#12364: Kill administration for system users #
3 # #
4 # This test file covers the scenarios around prohibiting the killing #
5 # connections of a user who is granted the SYSTEM_USER dynamic privilege #
6 # from the connections of a user who is not granted the SYSTEM_USER #
7 # dynamic privilege. #
8 # #
9 ###############################################################################
10
11 # Save the initial number of concurrent sessions
12 --source include/count_sessions.inc
13
14 --echo # Setup
15 CREATE USER sys_user, non_sys_user;
16 CREATE ROLE system_user_role;
17 GRANT SYSTEM_USER ON *.* TO system_user_role;
18 GRANT CONNECTION_ADMIN ON *.* TO non_sys_user;
19 connect(sys_user_con,localhost,sys_user,,);
20 connect(non_sys_user_con,localhost,non_sys_user,,);
21 connection default;
22
23 --echo #------------------------------------------------------------------------
24 --echo # 1. User without SYSTEM_USER privilege cannot kill the connection of
25 --echo # the user who has SYSTEM_USER privilege.
26 --echo #------------------------------------------------------------------------
27 connection sys_user_con;
28 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
29 connection non_sys_user_con;
30 --echo # non_sys_user should be able to kill as sys_user does not have
31 --echo # SYSTEM_USER privilege.
32 --replace_result $SU_CON_ID <CONNECTION_ID>
33 eval KILL $SU_CON_ID;
34 connection default;
35 --echo # Grant SYSTEM_USER privilege to sys_user and then try to kill its
36 --echo # session through non_sys_user.
37 GRANT SYSTEM_USER ON *.* TO sys_user;
38 connection sys_user_con;
39 --enable_reconnect
40 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
41 connection non_sys_user_con;
42 --replace_result $SU_CON_ID <CONNECTION_ID>
43 --error ER_KILL_DENIED_ERROR
44 eval KILL $SU_CON_ID;
45 --echo # Existing connection of sys_user cannot be killed by non_sys_user user
46 --echo # even after revoking the SYSTEM_USER privilege from former.
47 connection default;
48 REVOKE SYSTEM_USER ON *.* FROM sys_user;
49 connection non_sys_user_con;
50 --replace_result $SU_CON_ID <CONNECTION_ID>
51 --error ER_KILL_DENIED_ERROR
52 eval KILL $SU_CON_ID;
53 --echo # New connection of sys_user can be killed by non_sys_user user
54 --echo # after revoking the SYSTEM_USER privilege from former.
55 disconnect sys_user_con;
56 connect(sys_user_con,localhost,sys_user,,);
57 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
58 connection non_sys_user_con;
59 --replace_result $SU_CON_ID <CONNECTION_ID>
60 eval KILL $SU_CON_ID;
61
62 --echo #------------------------------------------------------------------------
63 --echo # 2. Grant SYSTEM_USER privilege to non_sys_user and try to kill the
64 --echo # connection of the sys_user who already had SYSTEM_USER privilege
65 --echo #------------------------------------------------------------------------
66 connection default;
67 GRANT SYSTEM_USER ON *.* TO sys_user,non_sys_user;
68 disconnect sys_user_con;
69 connect(sys_user_con,localhost,sys_user,,);
70 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
71 connection non_sys_user_con;
72 --echo # Must fail; Since THD::is_susytem_user of the existing session is not
73 --echo # updated about the SYSTEM_USER privilege granted
74 --replace_result $SU_CON_ID <CONNECTION_ID>
75 --error ER_KILL_DENIED_ERROR
76 eval KILL $SU_CON_ID;
77 connection default;
78 disconnect non_sys_user_con;
79 connect(non_sys_user_con,localhost,non_sys_user,,);
80 --echo # Must be able to kill since the THD of new connection would know that
81 --echo # it has SYSTEM_USER privilege
82 --replace_result $SU_CON_ID <CONNECTION_ID>
83 eval KILL $SU_CON_ID;
84
85 connection default;
86 disconnect non_sys_user_con;
87 REVOKE SYSTEM_USER ON *.* FROM non_sys_user, sys_user;
88
89 --echo #------------------------------------------------------------------------
90 --echo # 3. Grant SYSTEM_USER privilege through roles
91 --echo #------------------------------------------------------------------------
92 connection default;
93 GRANT system_user_role TO sys_user;
94 connection sys_user_con;
95 --enable_reconnect
96 SET ROLE system_user_role;
97 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
98 connect(non_sys_user_con,localhost,non_sys_user,,);
99 --echo # Must fail. non_sys_user does not have SYSTEM_USER privilege while the
100 --echo # sys_user has activated that privilege through role.
101 --replace_result $SU_CON_ID <CONNECTION_ID>
102 --error ER_KILL_DENIED_ERROR
103 eval KILL $SU_CON_ID;
104 connection default;
105 GRANT system_user_role TO non_sys_user;
106 disconnect non_sys_user_con;
107 connect(non_sys_user_con,localhost,non_sys_user,,);
108 --echo # Must fail. non_sys_user has desired role but latter is not yet
109 --echo # activated.
110 --replace_result $SU_CON_ID <CONNECTION_ID>
111 --error ER_KILL_DENIED_ERROR
112 eval KILL $SU_CON_ID;
113 --echo # Must work. non_sys_user gets the SYSTEM_USER privilege through roles
114 --echo # activation.
115 SET ROLE system_user_role;
116 --replace_result $SU_CON_ID <CONNECTION_ID>
117 eval KILL $SU_CON_ID;
118 connection sys_user_con;
119 --enable_reconnect
120 SET ROLE system_user_role;
121 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
122 connection non_sys_user_con;
123 SET ROLE NONE;
124 --echo # Must fail. non_sys_user has deactivated all roles.
125 --replace_result $SU_CON_ID <CONNECTION_ID>
126 --error ER_KILL_DENIED_ERROR
127 eval KILL $SU_CON_ID;
128 SET ROLE system_user_role;
129 SET ROLE ALL EXCEPT system_user_role;
130 --echo # Must fail. non_sys_user has all roles activated except the one that
131 --echo # activates the SYSTEM_USER privilege
132 --replace_result $SU_CON_ID <CONNECTION_ID>
133 --error ER_KILL_DENIED_ERROR
134 eval KILL $SU_CON_ID;
135 SET ROLE ALL;
136 --echo # Must Work. non_sys_user has all roles activated.
137 --replace_result $SU_CON_ID <CONNECTION_ID>
138 eval KILL $SU_CON_ID;
139
140 connection default;
141 REVOKE system_user_role FROM non_sys_user;
142 # Make MTR system happy by granting privilege to test DB
143 GRANT SELECT ON test.* TO sys_user;
144 disconnect sys_user_con;
145 disconnect non_sys_user_con;
146
147 --echo #------------------------------------------------------------------------
148 --echo # 4. Grant SYSTEM_USER privilege through default roles
149 --echo #------------------------------------------------------------------------
150 SET DEFAULT ROLE system_user_role TO sys_user;
151 connect(sys_user_con,localhost,sys_user);
152 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
153 connect(non_sys_user_con,localhost,non_sys_user,,);
154 --echo # Must fail. non_sys_user does not have the SYSTEM_USER privilege
155 --replace_result $SU_CON_ID <CONNECTION_ID>
156 --error ER_KILL_DENIED_ERROR
157 eval KILL $SU_CON_ID;
158 connection default;
159 GRANT SYSTEM_USER ON *.* TO non_sys_user;
160 connection non_sys_user_con;
161 --echo # Must fail. non_sys_user is granted the SYSTEM_USER privilege but
162 --echo # existing session does not know about it.
163 --replace_result $SU_CON_ID <CONNECTION_ID>
164 --error ER_KILL_DENIED_ERROR
165 eval KILL $SU_CON_ID;
166 disconnect non_sys_user_con;
167 connect(non_sys_user_con,localhost,non_sys_user,,);
168 --echo # Must work. non_sys_user has the SYSTEM_USER privilege
169 --replace_result $SU_CON_ID <CONNECTION_ID>
170 eval KILL $SU_CON_ID;
171 connection default;
172 REVOKE SYSTEM_USER ON *.* FROM non_sys_user;
173 SET DEFAULT ROLE NONE TO sys_user;
174 connection sys_user_con;
175 --enable_reconnect
176 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
177 disconnect non_sys_user_con;
178 connect(non_sys_user_con,localhost,non_sys_user,,);
179 --echo # Must work. non_sys_user and sys_user both do not have SYSTEM_USER
180 --echo # privilege.
181 --replace_result $SU_CON_ID <CONNECTION_ID>
182 eval KILL $SU_CON_ID;
183
184 --echo #------------------------------------------------------------------------
185 --echo # 5. Grant SYSTEM_USER privilege through role as well as GRANT statement
186 --echo #------------------------------------------------------------------------
187 connection sys_user_con;
188 --enable_reconnect
189 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
190 connection default;
191 GRANT SYSTEM_USER ON *.* TO sys_user;
192 connection sys_user_con;
193 SET ROLE NONE;
194 connection non_sys_user_con;
195 --echo # Must fail due to cumulative effect of SET ROLE and GRANT statement
196 --echo # on existing connection of sys_user.
197 --replace_result $SU_CON_ID <CONNECTION_ID>
198 --error ER_KILL_DENIED_ERROR
199 eval KILL $SU_CON_ID;
200 connection default;
201 REVOKE SYSTEM_USER ON *.* FROM sys_user;
202 connection sys_user_con;
203 SET ROLE system_user_role;
204 connection non_sys_user_con;
205 --echo # Must fail due to cumulative effect of SET ROLE and REVOKE statement
206 --echo # on existing connection of sys_user.
207 --replace_result $SU_CON_ID <CONNECTION_ID>
208 --error ER_KILL_DENIED_ERROR
209 eval KILL $SU_CON_ID;
210 connection sys_user_con;
211 SET ROLE NONE;
212 connection non_sys_user_con;
213 --echo # Must work since existing connection does not have SYSTEM_USER even
214 --echo # cumulatively.
215 --replace_result $SU_CON_ID <CONNECTION_ID>
216 eval KILL $SU_CON_ID;
217 connection default;
218 REVOKE SYSTEM_USER ON *.* FROM sys_user;
219
220 --echo #------------------------------------------------------------------------
221 --echo # 6. Killing another sessions of itself
222 --echo #------------------------------------------------------------------------
223 --echo # 6.1 Through Roles
224 connection default;
225 disconnect sys_user_con;
226 connect(sys_user_con,localhost,sys_user,,);
227 --enable_reconnect
228 SET ROLE system_user_role;
229 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
230 connect(sys_user_con_1,localhost,sys_user,,);
231 --echo # Must fail. Other session has SYSTEM_USER privilege through role
232 --echo # activation.
233 --replace_result $SU_CON_ID <CONNECTION_ID>
234 --error ER_KILL_DENIED_ERROR
235 eval KILL $SU_CON_ID;
236 --echo # Must work. After activating the role in current session.
237 SET ROLE system_user_role;
238 --replace_result $SU_CON_ID <CONNECTION_ID>
239 eval KILL $SU_CON_ID;
240 --echo # 6.2 Through usual grant statement
241 connection default;
242 disconnect sys_user_con_1;
243 disconnect sys_user_con;
244 GRANT SYSTEM_USER ON *.* TO sys_user;
245 connect(sys_user_con,localhost,sys_user,,);
246 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
247 connect(sys_user_con_1,localhost,sys_user,,);
248 --replace_result $SU_CON_ID <CONNECTION_ID>
249 eval KILL $SU_CON_ID;
250
251 connection default;
252 disconnect sys_user_con_1;
253 REVOKE SYSTEM_USER ON *.* FROM sys_user;
254
255 --echo #------------------------------------------------------------------------
256 --echo # 7. SYSTEM_USER definer must not elevate session from regular to power
257 --echo #------------------------------------------------------------------------
258
259 --echo # 7.1 Verify through SET_ROLE statement
260 disconnect non_sys_user_con;
261 CREATE USER baz@localhost;
262 GRANT EXECUTE ON *.* TO non_sys_user;
263 GRANT SET_USER_ID, EXECUTE ON *.* TO baz@localhost;
264 GRANT system_user_role TO baz@localhost;
265 CREATE DEFINER=baz@localhost PROCEDURE test.role_proc()SET ROLE system_user_role;
266
267 connect(non_sys_user_con,localhost,non_sys_user,,);
268 CALL role_proc();
269 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
270
271 connect(non_sys_user_con_1,localhost,non_sys_user,,);
272 --echo # Stored procedure should not have elevated the other session to
273 --echo # power_session.
274 --replace_result $SU_CON_ID <CONNECTION_ID>
275 eval KILL $SU_CON_ID;
276
277 --echo #7.2 Verify through GRANT statement
278 connection default;
279 CREATE USER foo@localhost;
280 GRANT CONNECTION_ADMIN ON *.* TO foo@localhost;
281 # Create stored procedure.
282 DELIMITER $$;
283 CREATE DEFINER=root@localhost PROCEDURE test.grant_proc()
284 BEGIN
285 GRANT SYSTEM_USER ON *.* TO non_sys_user;
286 END $$
287 DELIMITER ;$$
288
289 connection non_sys_user_con;
290 --enable_reconnect
291 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
292 call grant_proc();
293 --echo # non_sys_user must have SYSTEM_USER privilege.
294 SHOW GRANTS;
295
296 connect(foo_con,localhost,foo,,);
297 --echo # We should be able to kill the existing connection because stored
298 --echo # procedure should not have elevated the other session to power_session.
299 --replace_result $SU_CON_ID <CONNECTION_ID>
300 eval KILL $SU_CON_ID;
301
302 # Cleanup
303 connection default;
304 disconnect non_sys_user_con_1;
305 disconnect foo_con;
306 DROP PROCEDURE test.role_proc;
307 DROP PROCEDURE test.grant_proc;
308 DROP USER baz@localhost, foo@localhost;
309 REVOKE EXECUTE,SYSTEM_USER ON *.* FROM non_sys_user;
310
311 --echo #------------------------------------------------------------------------
312 --echo # 8. SYSTEM_USER invoker may elevate session from regular to power
313 --echo #------------------------------------------------------------------------
314
315 --echo # 8.1 Verify through SET_ROLE statement
316 disconnect non_sys_user_con;
317 CREATE USER baz@localhost;
318 GRANT EXECUTE ON *.* TO non_sys_user;
319 GRANT SET_USER_ID, EXECUTE ON *.* TO baz@localhost;
320 GRANT system_user_role TO baz@localhost, non_sys_user;
321 CREATE DEFINER=baz@localhost PROCEDURE test.role_proc() SQL SECURITY INVOKER
322 SET ROLE system_user_role;
323
324 connect(non_sys_user_con,localhost,non_sys_user,,);
325 CALL role_proc();
326 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
327
328 connect(non_sys_user_con_1,localhost,non_sys_user,,);
329 --echo # Stored procedure execution should have elevated the other session to
330 --echo # power_session.
331 --replace_result $SU_CON_ID <CONNECTION_ID>
332 --error ER_KILL_DENIED_ERROR
333 eval KILL $SU_CON_ID;
334
335
336 # Cleanup
337 connection default;
338 disconnect non_sys_user_con_1;
339 disconnect non_sys_user_con;
340 DROP PROCEDURE test.role_proc;
341 DROP USER baz@localhost;
342 REVOKE EXECUTE,SYSTEM_USER ON *.* FROM non_sys_user;
343 REVOKE system_user_role FROM non_sys_user;
344
345 --echo #------------------------------------------------------------------------
346 --echo # 9. Change the user in current session (COM_CHANGE_USER)
347 --echo #------------------------------------------------------------------------
348 disconnect sys_user_con;
349 GRANT SYSTEM_USER ON *.* TO sys_user;
350 CREATE USER baz;
351
352 connect(sys_user_con,localhost,sys_user,,);
353 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
354 --echo # Regular_user should not be able to kill the power_session
355 connect(non_sys_user_con,localhost,non_sys_user,,);
356 --replace_result $SU_CON_ID <CONNECTION_ID>
357 --error ER_KILL_DENIED_ERROR
358 eval KILL CONNECTION $SU_CON_ID;
359 --echo # Change the user in power_session that does not have SYSTEM_USER
360 --echo # privilege. In other word change the user from power to regular
361 connection sys_user_con;
362 change_user baz;
363 --echo # Now, regular session should be able to kill the previous session that
364 --echo # has been demoted from power_session to regular_session
365 connection non_sys_user_con;
366 --replace_result $SU_CON_ID <CONNECTION_ID>
367 eval KILL CONNECTION $SU_CON_ID;
368 --echo # This time, change the user in regular session to power user. It must
369 --echo # promote the session to power_session;
370 change_user sys_user;
371 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
372
373 connect(non_sys_user_con_1,localhost,non_sys_user,,);
374 --echo # Regular_user should not be able to kill the power_session
375 connection non_sys_user_con_1;
376 --replace_result $SU_CON_ID <CONNECTION_ID>
377 --error ER_KILL_DENIED_ERROR
378 eval KILL CONNECTION $SU_CON_ID;
379 --echo # Change user in current session as well. Now, should be able to kill
380 change_user sys_user;
381 --replace_result $SU_CON_ID <CONNECTION_ID>
382 eval KILL CONNECTION $SU_CON_ID;
383
384
385 # Cleanup
386 connection default;
387 DROP USER baz;
388 disconnect non_sys_user_con_1;
389
390 --echo # Cleanup
391 connection default;
392 disconnect sys_user_con;
393 disconnect non_sys_user_con;
394 DROP USER sys_user, non_sys_user;
395 DROP ROLE system_user_role;
396 # Wait till all disconnects are completed
397 --source include/wait_until_count_sessions.inc