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