Debian Codegraph @

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

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6
7 --echo # ----------------------------------------------------------------------
8
9 --echo # Begin : Tests for GRANT : Global grants
10
11 CREATE USER userX, userY, userZ;
12
13 --source include/save_binlog_position.inc
14
15 SHOW GRANTS FOR userX;
16 SHOW GRANTS FOR userY;
17 SHOW GRANTS FOR userZ;
18
19 --echo # Case 1 : Trying to create user with empty password
20 --error ER_CANT_CREATE_USER_WITH_GRANT
21 GRANT SUPER ON *.* TO userX, userY, userA;
22
23 --echo # Grants for userX, userY, userZ should not have changed
24 SHOW GRANTS FOR userX;
25 SHOW GRANTS FOR userY;
26 SHOW GRANTS FOR userZ;
27 --echo # Check mysql.user table too
28 SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
29
30 --let $invert= 1
31 --let $event= !Q(GRANT SUPER ON.*userX.*userY.*userA.*)
32 --source ../include/auth_sec_assert_binlog_events.inc
33 --let $invert= 0
34
35 --echo # Case 3 : GRANT to a user with empty plugin
36
37 --echo # Create userW and get rid of the plugin value for userW
38 CREATE USER userW IDENTIFIED BY 'abcd';
39 UPDATE mysql.user SET plugin="" WHERE user like 'userW';
40 call mtr.add_suppression("User entry .* has an empty plugin value. The user will be ignored and no one can login with this user anymore.");
41 # This would generate a warning in server log
42 FLUSH PRIVILEGES;
43 --source include/save_binlog_position.inc
44
45 --echo # Attempt to GRANT privileges to userW
46 --error ER_CANT_CREATE_USER_WITH_GRANT
47 GRANT SUPER ON *.* TO userX, userW, userY;
48
49 --echo Grants for userX, userY, userZ should not have changed
50 SHOW GRANTS FOR userX;
51 SHOW GRANTS FOR userY;
52 SHOW GRANTS FOR userZ;
53 --echo # Check mysql.user table too
54 SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
55
56 --let $invert= 1
57 --let $event= !Q(GRANT SUPER ON.*userX.*userW.*userY.*)
58 --source ../include/auth_sec_assert_binlog_events.inc
59 --let $invert= 0
60
61 --echo # Restore userW to its former state
62 UPDATE mysql.user SET plugin="caching_sha2_password" WHERE user like 'userW';
63 FLUSH PRIVILEGES;
64
65 --source include/save_binlog_position.inc
66
67 --echo # Case 4 : Use unprivileged account to create new user using GRANT
68
69 CREATE USER userSUPER;
70 GRANT SUPER ON *.* TO userSUPER WITH GRANT OPTION;
71 --source include/save_binlog_position.inc
72
73 connect(conn_userSUPER, localhost, userSUPER,,,,,);
74
75 --error ER_CANT_CREATE_USER_WITH_GRANT
76 GRANT SUPER ON *.* TO userX, userY, userA , userB;
77
78 --connection default
79 disconnect conn_userSUPER;
80 --echo Grants for userX, userY, userZ should not have changed
81 SHOW GRANTS FOR userX;
82 SHOW GRANTS FOR userY;
83 SHOW GRANTS FOR userZ;
84 --echo # Check mysql.user table too
85 SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
86
87 --let $invert= 1
88 --let $event= !Q(GRANT SUPER ON.*userX.*userY.*userA.*userB)
89 --source ../include/auth_sec_assert_binlog_events.inc
90 --let $invert= 0
91
92 --echo # Case 5 : Valid grant
93 GRANT SUPER ON *.* TO userX, userY, userZ;
94
95 --echo Grants for userX, userY, userZ should show SUPER
96 SHOW GRANTS FOR userX;
97 SHOW GRANTS FOR userY;
98 SHOW GRANTS FOR userZ;
99 --echo # Check mysql.user table too
100 SELECT user, Super_priv FROM mysql.user WHERE user like 'user%';
101
102 --let $event= !Q(GRANT SUPER ON.*userX.*userY.*userZ.*)
103 --source ../include/auth_sec_assert_binlog_events.inc
104
105 DROP USER userW, userX, userY, userZ, userSUPER;
106
107 --source include/save_binlog_position.inc
108
109 --echo # End : Tests for GRANT : Global grants
110
111 --echo # ----------------------------------------------------------------------
112
113 --echo # Begin : Tests for GRANT : DB grants
114
115 CREATE USER userX, userY, userZ;
116
117 --echo # Grant some DB privileges to some of the users so that
118 --echo # we cover both path : One creates new entry in mysql.db and
119 --echo # the other which updates existing entries.
120
121 GRANT INSERT ON mysql.* TO userX;
122
123 --source include/save_binlog_position.inc
124
125 SHOW GRANTS FOR userX;
126 SHOW GRANTS FOR userY;
127 SHOW GRANTS FOR userZ;
128
129 --echo # Case 1 : Trying to create user with empty password
130 --error ER_CANT_CREATE_USER_WITH_GRANT
131 GRANT SELECT ON mysql.* TO userX, userY, userA;
132
133 --echo # Grants for userX, userY, userZ should not have changed
134 SHOW GRANTS FOR userX;
135 SHOW GRANTS FOR userY;
136 SHOW GRANTS FOR userZ;
137 --echo # Check mysql.db table too
138 SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
139
140 --let $invert= 1
141 --let $event= !Q(GRANT SELECT ON.*mysql.*userX.*userY.*userA)
142 --source ../include/auth_sec_assert_binlog_events.inc
143 --let $invert= 0
144
145 --echo # Case 3 : GRANT to a user with empty plugin
146
147 --echo # Create userW and get rid of the plugin value for userW
148 CREATE USER userW IDENTIFIED BY 'abcd';
149 UPDATE mysql.user SET plugin="" WHERE user like 'userW';
150 call mtr.add_suppression("User entry .* has an empty plugin value. The user will be ignored and no one can login with this user anymore.");
151 # This would generate a warning in server log
152 FLUSH PRIVILEGES;
153 --source include/save_binlog_position.inc
154
155 --echo # Attempt to GRANT privileges to userW
156 --error ER_CANT_CREATE_USER_WITH_GRANT
157 GRANT SELECT ON mysql.* TO userX, userW, userY;
158
159 --echo Grants for userX, userY, userZ should show SUPER
160 SHOW GRANTS FOR userX;
161 SHOW GRANTS FOR userY;
162 SHOW GRANTS FOR userZ;
163 --echo # Check mysql.db table too
164 SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
165
166 --let $invert= 1
167 --let $event= !Q(GRANT SELECT ON.*mysql.*userX.*userW.*userY.*)
168 --source ../include/auth_sec_assert_binlog_events.inc
169 --let $invert= 0
170
171
172 --echo # Restore userW to its former state
173 UPDATE mysql.user SET plugin="caching_sha2_password" WHERE user like 'userW';
174 FLUSH PRIVILEGES;
175
176 --echo # Case 4 : Use unprivileged account to create new user using GRANT
177
178 CREATE USER userSELECT;
179 GRANT SELECT ON mysql.* TO userSELECT WITH GRANT OPTION;
180
181 --source include/save_binlog_position.inc
182
183 connect(conn_userSELECT, localhost, userSELECT,,,,,);
184
185 --error ER_CANT_CREATE_USER_WITH_GRANT
186 GRANT SELECT ON mysql.* TO userX, userY, userA , userB;
187
188 --connection default
189 disconnect conn_userSELECT;
190
191 --echo Grants for userX, userY, userZ should show SUPER
192 SHOW GRANTS FOR userX;
193 SHOW GRANTS FOR userY;
194 SHOW GRANTS FOR userZ;
195 --echo # Check mysql.db table too
196 SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
197
198 --let $invert= 1
199 --let $event= !Q(GRANT SELECT ON.*mysql.*userX.*userW.*userA.*userB.*)
200 --source ../include/auth_sec_assert_binlog_events.inc
201 --let $invert= 0
202
203 --echo # Case 5 : Valid grant
204 GRANT SELECT ON mysql.* TO userX, userY, userZ;
205
206 --echo Grants for userX, userY, userZ should show SELECT on mysql.*
207 SHOW GRANTS FOR userX;
208 SHOW GRANTS FOR userY;
209 SHOW GRANTS FOR userZ;
210 --echo # Check mysql.db table too
211 SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
212
213 --let $event= !Q(GRANT SELECT ON.*mysql.*userX.*userY.*userZ.*)
214 --source ../include/auth_sec_assert_binlog_events.inc
215
216 --source include/save_binlog_position.inc
217
218 --echo # Case 6 : Wrong set of privileges for DB grant
219 --error ER_WRONG_USAGE
220 GRANT SUPER ON mysql.* TO userX, userY, userZ;
221
222 --echo Grants for userX, userY, userZ should not have changed
223 SHOW GRANTS FOR userX;
224 SHOW GRANTS FOR userY;
225 SHOW GRANTS FOR userZ;
226 --echo # Check mysql.db table too
227 SELECT user, db, Insert_priv, Select_priv FROM mysql.db WHERE user like 'user%';
228
229 --let $invert= 1
230 --let $event= !Q(GRANT SUPER ON.*mysql.*userX.*userY.*userZ.*)
231 --source ../include/auth_sec_assert_binlog_events.inc
232 --let $invert= 0
233
234 DROP USER userW, userX, userY, userZ, userSELECT;
235
236 --source include/save_binlog_position.inc
237
238 --echo # End : Tests for GRANT : DB grants
239
240 --echo # ----------------------------------------------------------------------
241
242 --echo # Begin : Tests for GRANT : Proxy grants
243
244 CREATE USER userX, userY, userZ;
245 CREATE USER proxied;
246
247 --source include/save_binlog_position.inc
248
249 SHOW GRANTS FOR userX;
250 SHOW GRANTS FOR userY;
251 SHOW GRANTS FOR userZ;
252
253 --echo # Case 1 : Trying to create user with empty password
254 --error ER_CANT_CREATE_USER_WITH_GRANT
255 GRANT PROXY ON proxied TO userX, userY, userA;
256
257 --echo # Grants for userX, userY, userZ should not have changed
258 SHOW GRANTS FOR userX;
259 SHOW GRANTS FOR userY;
260 SHOW GRANTS FOR userZ;
261
262 --let $invert= 1
263 --let $event= !Q(GRANT PROXY ON.*proxied.*TO.*mysql.*userX.*userY.*userZ.*)
264 --source ../include/auth_sec_assert_binlog_events.inc
265 --let $invert= 0
266
267 --echo # Case 3 : GRANT to a user with empty plugin
268
269 --echo # Create userW and get rid of the plugin value for userW
270 CREATE USER userW IDENTIFIED BY 'abcd';
271 UPDATE mysql.user SET plugin="" WHERE user like 'userW';
272 call mtr.add_suppression("User entry .* has an empty plugin value. The user will be ignored and no one can login with this user anymore.");
273 # This would generate a warning in server log
274 FLUSH PRIVILEGES;
275 --source include/save_binlog_position.inc
276
277 --echo # Attempt to GRANT privileges to userW
278 --error ER_CANT_CREATE_USER_WITH_GRANT
279 GRANT PROXY ON proxied TO userX, userW, userY;
280
281 --echo Grants for userX, userY, userZ should show SUPER
282 SHOW GRANTS FOR userX;
283 SHOW GRANTS FOR userY;
284 SHOW GRANTS FOR userZ;
285
286 --let $invert= 1
287 --let $event= !Q(GRANT PROXY ON.*proxied.*TO.*mysql.*userX.*userW.*userY.*)
288 --source ../include/auth_sec_assert_binlog_events.inc
289 --let $invert= 0
290
291 --echo # Restore userW to its former state
292 UPDATE mysql.user SET plugin="caching_sha2_password" WHERE user like 'userW';
293 FLUSH PRIVILEGES;
294
295 --source include/save_binlog_position.inc
296
297 --echo # Case 4 : Use unprivileged account to create new user using GRANT
298
299 CREATE USER userPROXY;
300 connect(conn_userPROXY, localhost, userPROXY,,,,,);
301
302 --connection default
303 GRANT PROXY ON proxied TO userPROXY WITH GRANT OPTION;
304 --source include/save_binlog_position.inc
305
306 --connection conn_userPROXY
307 --error ER_CANT_CREATE_USER_WITH_GRANT
308 GRANT PROXY ON proxied TO userX, userY, userA, userB;
309
310 --connection default
311 disconnect conn_userPROXY;
312
313 --echo Grants for userX, userY, userZ should show SUPER
314 SHOW GRANTS FOR userX;
315 SHOW GRANTS FOR userY;
316 SHOW GRANTS FOR userZ;
317
318 --let $invert= 1
319 --let $event= !Q(GRANT PROXY ON.*proxied.*TO.*mysql.*userX.*userY.*userA.*userB.*)
320 --source ../include/auth_sec_assert_binlog_events.inc
321 --let $invert= 0
322
323 --echo # Case 5 : Valid grant
324 GRANT PROXY ON proxied TO userX, userY, userZ;
325
326 --echo Grants for userX, userY, userZ should show SELECT on mysql.*
327 SHOW GRANTS FOR userX;
328 SHOW GRANTS FOR userY;
329 SHOW GRANTS FOR userZ;
330
331 --let $event= !Q(GRANT PROXY ON.*proxied.*TO.*userX.*userY.*userZ.*)
332 --source ../include/auth_sec_assert_binlog_events.inc
333 --source include/save_binlog_position.inc
334
335 DROP USER proxied, userW, userX, userY, userZ, userPROXY;
336
337 --source include/save_binlog_position.inc
338
339 --echo # End : Tests for GRANT : Proxy grants
340
341 --echo # ----------------------------------------------------------------------
342
343 # Wait till we reached the initial number of concurrent sessions
344 --source include/wait_until_count_sessions.inc