Debian Codegraph @

mysql-test/suite/auth_sec/r/system_user_kill_connection.result

raw
1 # Setup
2 CREATE USER sys_user, non_sys_user;
3 CREATE ROLE system_user_role;
4 GRANT SYSTEM_USER ON *.* TO system_user_role;
5 GRANT CONNECTION_ADMIN ON *.* TO non_sys_user;
6 #------------------------------------------------------------------------
7 # 1. User without SYSTEM_USER privilege cannot kill the connection of
8 # the user who has SYSTEM_USER privilege.
9 #------------------------------------------------------------------------
10 # non_sys_user should be able to kill as sys_user does not have
11 # SYSTEM_USER privilege.
12 KILL <CONNECTION_ID>;
13 # Grant SYSTEM_USER privilege to sys_user and then try to kill its
14 # session through non_sys_user.
15 GRANT SYSTEM_USER ON *.* TO sys_user;
16 KILL <CONNECTION_ID>;
17 ERROR HY000: You are not owner of thread <CONNECTION_ID>
18 # Existing connection of sys_user cannot be killed by non_sys_user user
19 # even after revoking the SYSTEM_USER privilege from former.
20 REVOKE SYSTEM_USER ON *.* FROM sys_user;
21 KILL <CONNECTION_ID>;
22 ERROR HY000: You are not owner of thread <CONNECTION_ID>
23 # New connection of sys_user can be killed by non_sys_user user
24 # after revoking the SYSTEM_USER privilege from former.
25 KILL <CONNECTION_ID>;
26 #------------------------------------------------------------------------
27 # 2. Grant SYSTEM_USER privilege to non_sys_user and try to kill the
28 # connection of the sys_user who already had SYSTEM_USER privilege
29 #------------------------------------------------------------------------
30 GRANT SYSTEM_USER ON *.* TO sys_user,non_sys_user;
31 # Must fail; Since THD::is_susytem_user of the existing session is not
32 # updated about the SYSTEM_USER privilege granted
33 KILL <CONNECTION_ID>;
34 ERROR HY000: You are not owner of thread <CONNECTION_ID>
35 # Must be able to kill since the THD of new connection would know that
36 # it has SYSTEM_USER privilege
37 KILL <CONNECTION_ID>;
38 REVOKE SYSTEM_USER ON *.* FROM non_sys_user, sys_user;
39 #------------------------------------------------------------------------
40 # 3. Grant SYSTEM_USER privilege through roles
41 #------------------------------------------------------------------------
42 GRANT system_user_role TO sys_user;
43 SET ROLE system_user_role;
44 # Must fail. non_sys_user does not have SYSTEM_USER privilege while the
45 # sys_user has activated that privilege through role.
46 KILL <CONNECTION_ID>;
47 ERROR HY000: You are not owner of thread <CONNECTION_ID>
48 GRANT system_user_role TO non_sys_user;
49 # Must fail. non_sys_user has desired role but latter is not yet
50 # activated.
51 KILL <CONNECTION_ID>;
52 ERROR HY000: You are not owner of thread <CONNECTION_ID>
53 # Must work. non_sys_user gets the SYSTEM_USER privilege through roles
54 # activation.
55 SET ROLE system_user_role;
56 KILL <CONNECTION_ID>;
57 SET ROLE system_user_role;
58 SET ROLE NONE;
59 # Must fail. non_sys_user has deactivated all roles.
60 KILL <CONNECTION_ID>;
61 ERROR HY000: You are not owner of thread <CONNECTION_ID>
62 SET ROLE system_user_role;
63 SET ROLE ALL EXCEPT system_user_role;
64 # Must fail. non_sys_user has all roles activated except the one that
65 # activates the SYSTEM_USER privilege
66 KILL <CONNECTION_ID>;
67 ERROR HY000: You are not owner of thread <CONNECTION_ID>
68 SET ROLE ALL;
69 # Must Work. non_sys_user has all roles activated.
70 KILL <CONNECTION_ID>;
71 REVOKE system_user_role FROM non_sys_user;
72 GRANT SELECT ON test.* TO sys_user;
73 #------------------------------------------------------------------------
74 # 4. Grant SYSTEM_USER privilege through default roles
75 #------------------------------------------------------------------------
76 SET DEFAULT ROLE system_user_role TO sys_user;
77 # Must fail. non_sys_user does not have the SYSTEM_USER privilege
78 KILL <CONNECTION_ID>;
79 ERROR HY000: You are not owner of thread <CONNECTION_ID>
80 GRANT SYSTEM_USER ON *.* TO non_sys_user;
81 # Must fail. non_sys_user is granted the SYSTEM_USER privilege but
82 # existing session does not know about it.
83 KILL <CONNECTION_ID>;
84 ERROR HY000: You are not owner of thread <CONNECTION_ID>
85 # Must work. non_sys_user has the SYSTEM_USER privilege
86 KILL <CONNECTION_ID>;
87 REVOKE SYSTEM_USER ON *.* FROM non_sys_user;
88 SET DEFAULT ROLE NONE TO sys_user;
89 # Must work. non_sys_user and sys_user both do not have SYSTEM_USER
90 # privilege.
91 KILL <CONNECTION_ID>;
92 #------------------------------------------------------------------------
93 # 5. Grant SYSTEM_USER privilege through role as well as GRANT statement
94 #------------------------------------------------------------------------
95 GRANT SYSTEM_USER ON *.* TO sys_user;
96 SET ROLE NONE;
97 # Must fail due to cumulative effect of SET ROLE and GRANT statement
98 # on existing connection of sys_user.
99 KILL <CONNECTION_ID>;
100 ERROR HY000: You are not owner of thread <CONNECTION_ID>
101 REVOKE SYSTEM_USER ON *.* FROM sys_user;
102 SET ROLE system_user_role;
103 # Must fail due to cumulative effect of SET ROLE and REVOKE statement
104 # on existing connection of sys_user.
105 KILL <CONNECTION_ID>;
106 ERROR HY000: You are not owner of thread <CONNECTION_ID>
107 SET ROLE NONE;
108 # Must work since existing connection does not have SYSTEM_USER even
109 # cumulatively.
110 KILL <CONNECTION_ID>;
111 REVOKE SYSTEM_USER ON *.* FROM sys_user;
112 #------------------------------------------------------------------------
113 # 6. Killing another sessions of itself
114 #------------------------------------------------------------------------
115 # 6.1 Through Roles
116 SET ROLE system_user_role;
117 # Must fail. Other session has SYSTEM_USER privilege through role
118 # activation.
119 KILL <CONNECTION_ID>;
120 ERROR HY000: You are not owner of thread <CONNECTION_ID>
121 # Must work. After activating the role in current session.
122 SET ROLE system_user_role;
123 KILL <CONNECTION_ID>;
124 # 6.2 Through usual grant statement
125 GRANT SYSTEM_USER ON *.* TO sys_user;
126 KILL <CONNECTION_ID>;
127 REVOKE SYSTEM_USER ON *.* FROM sys_user;
128 #------------------------------------------------------------------------
129 # 7. SYSTEM_USER definer must not elevate session from regular to power
130 #------------------------------------------------------------------------
131 # 7.1 Verify through SET_ROLE statement
132 CREATE USER baz@localhost;
133 GRANT EXECUTE ON *.* TO non_sys_user;
134 GRANT SET_USER_ID, EXECUTE ON *.* TO baz@localhost;
135 GRANT system_user_role TO baz@localhost;
136 CREATE DEFINER=baz@localhost PROCEDURE test.role_proc()SET ROLE system_user_role;
137 CALL role_proc();
138 # Stored procedure should not have elevated the other session to
139 # power_session.
140 KILL <CONNECTION_ID>;
141 #7.2 Verify through GRANT statement
142 CREATE USER foo@localhost;
143 GRANT CONNECTION_ADMIN ON *.* TO foo@localhost;
144 CREATE DEFINER=root@localhost PROCEDURE test.grant_proc()
145 BEGIN
146 GRANT SYSTEM_USER ON *.* TO non_sys_user;
147 END $$
148 call grant_proc();
149 # non_sys_user must have SYSTEM_USER privilege.
150 SHOW GRANTS;
151 Grants for non_sys_user@%
152 GRANT EXECUTE ON *.* TO `non_sys_user`@`%`
153 GRANT CONNECTION_ADMIN,SYSTEM_USER ON *.* TO `non_sys_user`@`%`
154 # We should be able to kill the existing connection because stored
155 # procedure should not have elevated the other session to power_session.
156 KILL <CONNECTION_ID>;
157 DROP PROCEDURE test.role_proc;
158 DROP PROCEDURE test.grant_proc;
159 DROP USER baz@localhost, foo@localhost;
160 REVOKE EXECUTE,SYSTEM_USER ON *.* FROM non_sys_user;
161 #------------------------------------------------------------------------
162 # 8. SYSTEM_USER invoker may elevate session from regular to power
163 #------------------------------------------------------------------------
164 # 8.1 Verify through SET_ROLE statement
165 CREATE USER baz@localhost;
166 GRANT EXECUTE ON *.* TO non_sys_user;
167 GRANT SET_USER_ID, EXECUTE ON *.* TO baz@localhost;
168 GRANT system_user_role TO baz@localhost, non_sys_user;
169 CREATE DEFINER=baz@localhost PROCEDURE test.role_proc() SQL SECURITY INVOKER
170 SET ROLE system_user_role;
171 CALL role_proc();
172 # Stored procedure execution should have elevated the other session to
173 # power_session.
174 KILL <CONNECTION_ID>;
175 ERROR HY000: You are not owner of thread <CONNECTION_ID>
176 DROP PROCEDURE test.role_proc;
177 DROP USER baz@localhost;
178 REVOKE EXECUTE,SYSTEM_USER ON *.* FROM non_sys_user;
179 REVOKE system_user_role FROM non_sys_user;
180 #------------------------------------------------------------------------
181 # 9. Change the user in current session (COM_CHANGE_USER)
182 #------------------------------------------------------------------------
183 GRANT SYSTEM_USER ON *.* TO sys_user;
184 CREATE USER baz;
185 # Regular_user should not be able to kill the power_session
186 KILL CONNECTION <CONNECTION_ID>;
187 ERROR HY000: You are not owner of thread <CONNECTION_ID>
188 # Change the user in power_session that does not have SYSTEM_USER
189 # privilege. In other word change the user from power to regular
190 # Now, regular session should be able to kill the previous session that
191 # has been demoted from power_session to regular_session
192 KILL CONNECTION <CONNECTION_ID>;
193 # This time, change the user in regular session to power user. It must
194 # promote the session to power_session;
195 # Regular_user should not be able to kill the power_session
196 KILL CONNECTION <CONNECTION_ID>;
197 ERROR HY000: You are not owner of thread <CONNECTION_ID>
198 # Change user in current session as well. Now, should be able to kill
199 KILL CONNECTION <CONNECTION_ID>;
200 DROP USER baz;
201 # Cleanup
202 DROP USER sys_user, non_sys_user;
203 DROP ROLE system_user_role;