Debian Codegraph @

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

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