Debian Codegraph @

mysql-test/suite/auth_sec/include/grant_as_ddl.inc

raw
1 ###############################################################################
2 # This script executes GRANT AS scenarios #
3 ###############################################################################
4
5 --echo -------------------------------------------------------------------------
6 --echo # 1. GRANT AS sanity checks
7 --echo -------------------------------------------------------------------------
8
9 CREATE USER foo, bar, baz;
10 CREATE DATABASE db1;
11 CREATE TABLE db1.t1(c1 int);
12
13 --error ER_UNSUPPORTED_USE_OF_GRANT_AS
14 GRANT SELECT ON db1.* TO foo AS bar;
15
16 --error ER_UNSUPPORTED_USE_OF_GRANT_AS
17 GRANT SELECT ON db1.t1 TO foo AS bar;
18
19 --error ER_UNSUPPORTED_USE_OF_GRANT_AS
20 GRANT SELECT(c1) ON db1.t1 TO foo AS bar;
21
22 --error ER_PARSE_ERROR
23 GRANT PROXY ON foo TO bar AS foo;
24
25 --error ER_PARSE_ERROR
26 GRANT baz TO bar AS foo;
27
28 --error ER_PARSE_ERROR
29 GRANT SELECT ON *.* TO bar WITH ROLE foo;
30
31 --error ER_PARSE_ERROR
32 GRANT SELECT ON *.* TO bar WITH ROLE NONE;
33
34 --error ER_PARSE_ERROR
35 GRANT SELECT ON *.* TO bar WITH ROLE ALL;
36
37 --error ER_PARSE_ERROR
38 GRANT SELECT ON *.* TO bar WITH ROLE DEFAULT;
39
40 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
41 GRANT SELECT ON *.* TO foo AS hohoho;
42
43 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
44 GRANT SELECT ON *.* TO foo AS bar WITH ROLE hohoho;
45
46 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
47 GRANT SELECT ON *.* TO foo AS bar WITH ROLE baz, hohoho;
48
49 DROP TABLE db1.t1;
50 DROP DATABASE db1;
51 DROP USER foo, bar, baz;
52
53 --echo -------------------------------------------------------------------------
54 --echo # 2. Permission checks
55 --echo -------------------------------------------------------------------------
56
57 CREATE USER foo, bar;
58 CREATE ROLE baz, qux;
59 CREATE DATABASE db1;
60
61 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
62 GRANT SELECT ON *.* TO foo AS bar WITH ROLE baz;
63
64 GRANT SELECT ON *.* TO foo AS `root`@`localhost`;
65
66 GRANT SELECT ON *.* TO foo WITH GRANT OPTION;
67 REVOKE SELECT ON db1.* FROM foo;
68
69 --connect(conn_foo, localhost, foo,,,,,)
70
71 # Impersonating a user more privileged than you is not allowed
72 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
73 GRANT SELECT ON *.* TO bar AS `root`@`localhost`;
74
75 --connection default
76 GRANT SYSTEM_USER ON *.* TO foo;
77
78 --connection conn_foo
79 # Impersonating a user more privileged than you is not allowed
80 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
81 GRANT SELECT ON *.* TO bar AS `root`@`localhost`;
82
83 --connection default
84 --disconnect conn_foo
85 REVOKE ALL ON *.* FROM foo;
86 GRANT SYSTEM_USER ON *.* TO foo;
87 GRANT INSERT ON *.* TO foo WITH GRANT OPTION;
88 GRANT SELECT ON *.* TO baz WITH GRANT OPTION;
89 GRANT SELECT ON db1.* to qux;
90 GRANT baz, qux TO foo;
91
92 --connect(conn_foo, localhost, foo,,,,,)
93 SET ROLE baz;
94 GRANT SELECT ON *.* TO bar AS `root`@`localhost`;
95
96 SET ROLE qux;
97 --error ER_ACCESS_DENIED_ERROR
98 GRANT SELECT ON *.* TO bar;
99
100 --connection default
101 --disconnect conn_foo
102
103 DROP DATABASE db1;
104 DROP ROLE baz, qux;
105 DROP USER foo, bar;
106
107 CREATE USER foo, bar, baz;
108 CREATE ROLE r1, r2, r3;
109 GRANT SELECT ON *.* TO foo WITH GRANT OPTION;
110 GRANT SYSTEM_USER ON *.* TO r2;
111 GRANT r2 TO r3;
112 GRANT r1, r3 TO bar;
113
114 --connect(conn_foo, localhost, foo,,,,,)
115 # Should succeed
116 GRANT SELECT ON *.* TO baz AS bar;
117 GRANT SELECT ON *.* TO baz AS bar WITH ROLE NONE;
118 GRANT SELECT ON *.* TO baz AS bar WITH ROLE r1;
119
120 # Impersonating a user more privileged than you is not allowed
121 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
122 GRANT SELECT ON *.* TO baz AS bar WITH ROLE r2;
123 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
124 GRANT SELECT ON *.* TO baz AS bar WITH ROLE r1, r2;
125
126 GRANT SELECT ON *.* TO baz AS bar WITH ROLE ALL;
127
128 --connection default
129 --disconnect conn_foo
130 SHOW GRANTS FOR baz;
131
132 DROP ROLE r1, r2, r3;
133 DROP USER foo, bar, baz;
134
135 --echo -------------------------------------------------------------------------
136 --echo # 3. User test
137 --echo -------------------------------------------------------------------------
138
139 CREATE USER foo, bar;
140 CREATE DATABASE db1;
141
142 GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER();
143 SHOW GRANTS FOR bar;
144
145 REVOKE ALL ON *.* FROM bar;
146 GRANT SELECT ON *.* TO bar AS foo;
147 SHOW GRANTS FOR bar;
148
149 REVOKE ALL ON *.* FROM bar;
150 GRANT SELECT ON *.* TO foo;
151 REVOKE SELECT ON db1.* FROM foo;
152
153 GRANT SELECT ON *.* TO bar AS foo;
154 SHOW GRANTS FOR bar;
155
156 REVOKE ALL ON *.* FROM bar;
157 GRANT SELECT, INSERT ON *.* TO bar AS foo;
158 SHOW GRANTS FOR bar;
159
160 DROP DATABASE db1;
161 DROP USER foo, bar;
162
163 --echo -------------------------------------------------------------------------
164 --echo # 4. Roles test
165 --echo -------------------------------------------------------------------------
166
167 CREATE USER foo, bar;
168 CREATE ROLE r1, r2, r3, r4;
169 CREATE DATABASE db1;
170
171 GRANT SELECT ON *.* TO r1;
172 REVOKE SELECT ON db1.* FROM r1;
173 GRANT r1 TO r2;
174
175 GRANT INSERT ON *.* TO r3;
176 REVOKE INSERT ON db1.* FROM r3;
177 GRANT r3 TO r4;
178
179 GRANT r2, r4 TO foo;
180
181 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL;
182 SHOW GRANTS FOR bar;
183
184 REVOKE ALL ON *.* FROM bar;
185 ALTER USER foo DEFAULT ROLE ALL;
186
187 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL;
188 SHOW GRANTS FOR bar;
189
190 REVOKE ALL ON *.* FROM bar;
191
192 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL EXCEPT r4;
193 SHOW GRANTS FOR bar;
194
195 REVOKE ALL ON *.* FROM bar;
196 ALTER USER foo DEFAULT ROLE ALL;
197
198 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r2, r4;
199 SHOW GRANTS FOR bar;
200
201 DROP DATABASE db1;
202 DROP ROLE r1, r2, r3, r4;
203 DROP USER foo, bar;
204
205 --echo -------------------------------------------------------------------------
206 --echo # 6. Without --partial_revokes
207 --echo -------------------------------------------------------------------------
208
209 SET @save_partial_revokes = @@global.partial_revokes;
210 SET @@global.partial_revokes = OFF;
211
212 CREATE USER foo, bar;
213 CREATE ROLE r1, r2;
214
215 GRANT r1, r2 TO foo;
216 ALTER USER foo DEFAULT ROLE ALL;
217
218 GRANT SELECT, INSERT ON *.* TO bar AS foo;
219 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE NONE;
220 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL;
221 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL EXCEPT r1;
222 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE DEFAULT;
223
224 DROP USER foo, bar;
225 DROP ROLE r1, r2;
226
227 SET @@global.partial_revokes = @save_partial_revokes;
228
229 --echo -------------------------------------------------------------------------
230 --echo # 7. Binlog tests
231 --echo -------------------------------------------------------------------------
232
233 --let $mask_anonymous_gtid_events= 1
234 --let $mask_user_password_events= 1
235
236 CREATE USER foo, bar;
237 CREATE ROLE r1, r2, r3, r4;
238 CREATE DATABASE db1;
239
240 GRANT SELECT ON *.* TO r1 WITH GRANT OPTION;
241 REVOKE SELECT ON db1.* FROM r1;
242 GRANT r1 TO r2;
243
244 GRANT INSERT ON *.* TO r3 WITH GRANT OPTION;
245 REVOKE INSERT ON db1.* FROM r3;
246 GRANT r3 TO r4;
247
248 GRANT r2, r4 TO foo;
249
250 ALTER USER foo DEFAULT ROLE ALL;
251
252 --source include/save_binlog_position.inc
253 --let $binlog_start = $binlog_position
254
255 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL;
256 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE NONE;
257 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE ALL EXCEPT r2;
258 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE DEFAULT;
259 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r2, r4;
260
261 GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER();
262 GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER() WITH ROLE ALL;
263 GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER() WITH ROLE NONE;
264 GRANT SELECT, INSERT ON *.* TO bar AS CURRENT_USER() WITH ROLE DEFAULT;
265
266 GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE ALL;
267 GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE NONE;
268 GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE ALL EXCEPT r2;
269 GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE DEFAULT;
270 GRANT SELECT, INSERT ON *.* TO foo AS foo WITH ROLE r2, r4;
271
272 --connect(conn_foo, localhost, foo,,,,,)
273
274 GRANT SELECT, INSERT ON *.* TO bar;
275
276 --connection default
277 --disconnect conn_foo
278 --source include/show_binlog_events.inc
279
280 GRANT SELECT, INSERT ON *.* TO foo;
281 REVOKE SELECT, INSERT ON db1.* FROM foo;
282
283 --source include/save_binlog_position.inc
284 --let $binlog_start = $binlog_position
285
286 GRANT SELECT, INSERT ON *.* TO bar AS foo;
287
288 --source include/show_binlog_events.inc
289
290 DROP DATABASE db1;
291 DROP ROLE r1, r2, r3, r4;
292 DROP USER foo, bar;
293
294 --echo -------------------------------------------------------------------------
295 --echo # 8. Current user in AS caluse
296 --echo -------------------------------------------------------------------------
297
298 CREATE USER foo, bar, baz;
299 CREATE ROLE r1, r2;
300 GRANT SELECT, INSERT ON *.* TO r1 WITH GRANT OPTION;
301 REVOKE SELECT ON mysql.* FROM r1;
302 GRANT SELECT, INSERT ON *.* TO r2 WITH GRANT OPTION;
303 REVOKE INSERT ON mysql.* FROM r2;
304 GRANT r1, r2 TO foo;
305 ALTER USER foo DEFAULT ROLE ALL;
306
307 --connect(conn_foo, localhost, foo,,,,,)
308
309 GRANT SELECT ON *.* TO bar AS current_user() WITH ROLE r1;
310
311 GRANT INSERT ON *.* TO baz AS foo WITH ROLE r2;
312
313 --connection default
314 --disconnect conn_foo
315 SHOW GRANTS FOR bar;
316 SHOW GRANTS FOR baz;
317
318 DROP ROLE r1, r2;
319 DROP USER foo, bar, baz;
320
321 CREATE USER foo, bar;
322 CREATE ROLE r1, r2;
323 GRANT r1, r2 TO foo;
324 GRANT SELECT ON *.* TO r1 WITH GRANT OPTION;
325 REVOKE SELECT ON mysql.* FROM r1;
326 GRANT INSERT ON *.* TO r2 WITH GRANT OPTION;
327 REVOKE INSERT ON mysql.* FROM r2;
328 ALTER USER foo DEFAULT ROLE ALL;
329
330 --connect(conn_foo, localhost, foo,,,,,)
331
332 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
333 GRANT SELECT, INSERT ON *.* TO bar AS current_user();
334 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
335 GRANT SELECT, INSERT ON *.* TO bar AS current_user() WITH ROLE r1;
336 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
337 GRANT SELECT, INSERT ON *.* TO bar AS current_user() WITH ROLE r2;
338
339 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
340 GRANT SELECT, INSERT ON *.* TO bar AS foo;
341 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
342 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r1;
343 --error ER_UKNOWN_AUTH_ID_OR_ACCESS_DENIED_FOR_GRANT_AS
344 GRANT SELECT, INSERT ON *.* TO bar AS foo WITH ROLE r2;
345
346 --connection default
347 --disconnect conn_foo
348 DROP ROLE r1, r2;
349 DROP USER foo, bar;
350
351 --echo -------------------------------------------------------------------------
352 --echo # 9. General log
353 --echo -------------------------------------------------------------------------
354
355 SET @save_partial_revokes = @@global.partial_revokes;
356
357 CREATE USER u1, u2;
358 CREATE ROLE r1, r2;
359 GRANT r1, r2 TO u1;
360 GRANT SELECT ON *.* TO r1 WITH GRANT OPTION;
361 GRANT INSERT ON *.* TO r2 WITH GRANT OPTION;
362 ALTER USER u1 DEFAULT ROLE ALL;
363 --connect(conn_u1, localhost, u1,,,,,)
364
365 --connection default
366
367 # Make sure we start with a clean slate. log_tables.test says this is OK.
368 TRUNCATE TABLE mysql.general_log;
369 SET @old_log_output= @@global.log_output;
370 SET @old_general_log= @@global.general_log;
371 SET @old_general_log_file= @@global.general_log_file;
372
373 let $general_file_off = $MYSQLTEST_VARDIR/log/grant_as_ddl_general.log;
374 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
375 eval SET GLOBAL general_log_file = '$general_file_off';
376 SET GLOBAL log_output = 'FILE,TABLE';
377 SET GLOBAL general_log= 'ON';
378
379 SET @@global.partial_revokes = ON;
380
381 REVOKE SELECT ON mysql.* FROM r1;
382 REVOKE INSERT ON mysql.* FROM r2;
383
384 GRANT SELECT, INSERT ON *.* TO u2;
385 GRANT SELECT, INSERT ON *.* TO u2 AS u1;
386 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE NONE;
387 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE DEFAULT;
388 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL;
389 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r1;
390
391 --connection conn_u1
392 SET ROLE NONE;
393
394 SET ROLE r1;
395 GRANT SELECT ON *.* TO u2;
396
397 SET ROLE r1, r2;
398 GRANT SELECT, INSERT ON *.* TO u2;
399
400 SET ROLE ALL;
401 GRANT SELECT, INSERT ON *.* TO u2;
402
403 SET ROLE DEFAULT;
404 GRANT SELECT, INSERT ON *.* TO u2;
405 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE r1;
406 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL;
407 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE DEFAULT;
408 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r2;
409
410 SET ROLE NONE;
411
412 --connection default
413 GRANT SELECT ON *.* TO r1;
414 GRANT INSERT ON *.* TO r2;
415
416 SET @@global.partial_revokes = OFF;
417
418 GRANT SELECT, INSERT ON *.* TO u2;
419 GRANT SELECT, INSERT ON *.* TO u2 AS u1;
420 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE NONE;
421 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE DEFAULT;
422 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL;
423 GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r1;
424
425 --connection conn_u1
426 SET ROLE NONE;
427
428 SET ROLE r1;
429 GRANT SELECT ON *.* TO u2;
430
431 SET ROLE r1, r2;
432 GRANT SELECT, INSERT ON *.* TO u2;
433
434 SET ROLE ALL;
435 GRANT SELECT, INSERT ON *.* TO u2;
436
437 SET ROLE DEFAULT;
438 GRANT SELECT, INSERT ON *.* TO u2;
439 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE r1;
440 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL;
441 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE DEFAULT;
442 GRANT SELECT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r2;
443
444 SET ROLE NONE;
445
446 --connection default
447 --disconnect conn_u1
448 DROP ROLE r1, r2;
449 DROP USER u1, u2;
450
451 SET @@global.partial_revokes = @save_partial_revokes;
452
453 --echo Show what is logged:
454
455 --echo ------ rewrite ------
456 SELECT COUNT(*) = 8 AS NO_AS_CLAUSE_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2' AND command_type NOT LIKE 'Prepare';
457 SELECT COUNT(*) = 2 AS WITH_AS_WITHOUT_ROLE_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1' AND command_type NOT LIKE 'Prepare';
458 SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_NONE_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE NONE' AND command_type NOT LIKE 'Prepare';
459 SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_DEFAULT_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE DEFAULT' AND command_type NOT LIKE 'Prepare';
460 SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_ALL_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL' AND command_type NOT LIKE 'Prepare';
461 SELECT COUNT(*) = 2 AS WITH_AS_WITH_ROLE_ALL_EXCEPT_COUNT FROM mysql.general_log WHERE argument LIKE 'GRANT SELECT, INSERT ON *.* TO u2 AS u1 WITH ROLE ALL EXCEPT r1' AND command_type NOT LIKE 'Prepare';
462 --echo ------ done ------
463
464 --remove_file $general_file_off
465
466 SET GLOBAL general_log_file= @old_general_log_file;
467 SET GLOBAL general_log= @old_general_log;
468 SET GLOBAL log_output= @old_log_output;
469 TRUNCATE TABLE mysql.general_log;
470
471 --echo -------------------------------------------------------------------------
472