Debian Codegraph @

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

raw
1 ###############################################################################
2 # WL#12364: Kill administration for system users #
3 # #
4 # This test file covers the scenarios around prohibiting the killing #
5 # queries 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 # Skip test if DEBUG_SYNC is not enabled
12 --source include/have_debug_sync.inc
13
14 # Save the initial number of concurrent sessions
15 --source include/count_sessions.inc
16
17 --echo # Setup
18 CREATE USER sys_user, non_sys_user;
19 GRANT CONNECTION_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO non_sys_user;
20 GRANT SYSTEM_USER, CREATE, SELECT, INSERT, EXECUTE,SET_USER_ID,
21 SYSTEM_VARIABLES_ADMIN ON *.* TO sys_user;
22 connect(sys_user_con,localhost,sys_user,,);
23 connect(non_sys_user_con,localhost,non_sys_user,,);
24 connection default;
25
26 --echo #------------------------------------------------------------------------
27 --echo # 1. User without SYSTEM_USER privilege cannot the kill query running
28 --echo # in the power session
29 --echo #------------------------------------------------------------------------
30 connection sys_user_con;
31 let $SU_CON_ID= `SELECT CONNECTION_ID()`;
32 --disable_query_log
33 let $tab_count= 5;
34
35 let $i= $tab_count;
36 while ($i)
37 {
38 eval CREATE TABLE t$i (a$i INT, KEY(a$i));
39 eval INSERT INTO t$i VALUES (1),(2),(3),(4),(5),(6),(7);
40 dec $i ;
41 }
42 SET SESSION optimizer_search_depth=0;
43
44 let $i=$tab_count;
45 while ($i)
46 {
47 let $a= a$i;
48 let $t= t$i;
49 dec $i;
50 if ($i)
51 {
52 let $comma=,;
53 let $from=$comma$t$from;
54 let $where=a$i=$a $and $where;
55 }
56 if (!$i)
57 {
58 let $from=FROM $t$from;
59 let $where=WHERE $where;
60 }
61 let $and=AND;
62 }
63 --enable_query_log
64
65 SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync WAIT_FOR continue';
66 eval PREPARE stmt FROM 'SELECT count(*) $from $where';
67 send EXECUTE stmt;
68
69 --echo # Regular session should not be able to to kill the query running in
70 --echo # power session.
71 connection non_sys_user_con;
72 SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
73 --replace_result $SU_CON_ID <CONNECTION_ID>
74 --error ER_KILL_DENIED_ERROR
75 eval KILL QUERY $SU_CON_ID;
76 SET DEBUG_SYNC= 'now SIGNAL continue';
77 connection sys_user_con;
78 --disable_warnings
79 reap;
80 --enable_warnings
81 SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync WAIT_FOR continue';
82 send EXECUTE stmt;
83
84 --echo # The running query cannot be killed by non_sys_user user
85 --echo # even after revoking the SYSTEM_USER privilege from sys_user
86 connection default;
87 REVOKE SYSTEM_USER ON *.* FROM sys_user;
88
89 connection non_sys_user_con;
90 SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
91 --replace_result $SU_CON_ID <CONNECTION_ID>
92 --error ER_KILL_DENIED_ERROR
93 eval KILL QUERY $SU_CON_ID;
94 SET DEBUG_SYNC= 'now SIGNAL continue';
95
96 connection sys_user_con;
97 --disable_warnings
98 reap;
99 --enable_warnings
100 SET DEBUG_SYNC= 'before_join_optimize SIGNAL in_sync WAIT_FOR continue';
101 send EXECUTE stmt;
102
103 --echo # Another power_session should be able to kill the query.
104 connection default;
105 SET DEBUG_SYNC= 'now WAIT_FOR in_sync';
106 --replace_result $SU_CON_ID <CONNECTION_ID>
107 eval KILL QUERY $SU_CON_ID;
108 SET DEBUG_SYNC= 'now SIGNAL continue';
109
110 --echo # Verify that query was killed
111 connection sys_user_con;
112 --error ER_QUERY_INTERRUPTED
113 reap;
114 SET DEBUG_SYNC = 'RESET';
115
116 # Cleanup
117 connection default;
118 --disable_query_log
119 let $i= $tab_count;
120 while ($i)
121 {
122 eval DROP TABLE t$i;
123 dec $i ;
124 }
125 --enable_query_log
126
127 --echo # Test Cleanup
128 connection default;
129 disconnect sys_user_con;
130 disconnect non_sys_user_con;
131 DROP USER sys_user, non_sys_user;
132 # Wait till all disconnects are completed
133 --source include/wait_until_count_sessions.inc