Debian Codegraph @

mysql-test/suite/information_schema/r/enabled_roles.result

raw
1 # SETUP SCHEMA
2 #
3 # SETUP SCHEMA
4 #
5 use test;
6 #
7 # CREATE ROLES
8 #
9 CREATE ROLE reader, updater;
10 GRANT reader TO updater WITH ADMIN OPTION;
11 CREATE ROLE task1, task1_lead, task2, task2_lead;
12 GRANT updater TO task1_lead;
13 GRANT updater TO task2_lead;
14 GRANT task1 TO task1_lead;
15 GRANT task2 TO task2_lead;
16 CREATE ROLE project_lead;
17 GRANT task1_lead TO project_lead;
18 GRANT task2_lead TO project_lead;
19 CREATE ROLE product_lead;
20 GRANT project_lead TO product_lead;
21 CREATE ROLE hr_rules, hr_access;
22 GRANT hr_rules TO hr_access WITH ADMIN OPTION;
23 #
24 # USER ACCOUNT AND ASSIGN ROLE's TO THEM.
25 #
26 CREATE USER user0 IDENTIFIED BY 'foo';
27 CREATE USER user1 IDENTIFIED BY 'foo';
28 CREATE USER user2 IDENTIFIED BY 'foo';
29 GRANT reader, hr_rules TO user1;
30 GRANT updater, hr_rules TO user2;
31 CREATE USER lead_user1 IDENTIFIED BY 'foo';
32 CREATE USER lead_user2 IDENTIFIED BY 'foo';
33 GRANT task1_lead, hr_access TO lead_user1;
34 GRANT task2_lead, hr_access TO lead_user2;
35 CREATE USER project_lead_user IDENTIFIED BY 'foo';
36 GRANT project_lead, hr_access TO project_lead_user;
37 CREATE USER product_lead_user IDENTIFIED BY 'foo';
38 GRANT product_lead, hr_access TO product_lead_user;
39 #
40 # CREATE DATABASE OBJECTS
41 #
42 CREATE TABLE task1_ledger1 (f1 INT, f2 INT);
43 CREATE TABLE task1_ledger2 (f1 INT, f2 INT);
44 CREATE TABLE task2_ledger1 (f1 INT, f2 INT);
45 CREATE TABLE task2_ledger2 (f1 INT, f2 INT);
46 CREATE PROCEDURE daily_report (OUT param1 INT)
47 BEGIN
48 SELECT COUNT(*) INTO param1 FROM t1;
49 END//
50 CREATE PROCEDURE weekly_report (OUT param1 INT)
51 BEGIN
52 SELECT COUNT(*) INTO param1 FROM t1;
53 END//
54 CREATE PROCEDURE proc1 (OUT param1 INT)
55 BEGIN
56 SELECT COUNT(*) INTO param1 FROM t1;
57 END//
58 CREATE TABLE hr_ledger1 (f1 INT, f2 INT);
59 CREATE TABLE hr_ledger2 (f1 INT, f2 INT);
60 CREATE TABLE t1 (f1 INT);
61 #
62 # GRANTS TO ROLE
63 #
64 GRANT SELECT ON task1_ledger1 TO reader;
65 GRANT UPDATE ON task1_ledger1 TO updater;
66 GRANT UPDATE ON task1_ledger2 TO updater;
67 GRANT UPDATE ON task2_ledger1 TO updater;
68 GRANT UPDATE ON task2_ledger2 TO updater;
69 GRANT INSERT ON task1_ledger1 TO task1_lead;
70 GRANT INSERT ON task1_ledger2 TO task1_lead;
71 GRANT INSERT ON task2_ledger1 TO task2_lead;
72 GRANT INSERT ON task2_ledger2 TO task2_lead;
73 GRANT DELETE ON task2_ledger1 TO project_lead;
74 GRANT DELETE ON task2_ledger2 TO project_lead;
75 GRANT SELECT ON hr_ledger1 TO hr_rules;
76 GRANT SELECT ON hr_ledger2 to hr_access;
77 GRANT SELECT (f2) ON task1_ledger1 to reader;
78 GRANT SELECT (f2) ON task1_ledger2 to reader WITH GRANT OPTION;
79 GRANT SELECT (f2) ON task2_ledger2 to product_lead;
80 GRANT SELECT (f2) ON task2_ledger2 to product_lead WITH GRANT OPTION;
81 GRANT SELECT (f2) ON hr_ledger1 TO hr_rules;
82 GRANT SELECT (f2) ON hr_ledger2 to hr_access WITH GRANT OPTION;
83 GRANT EXECUTE ON PROCEDURE daily_report TO task1_lead;
84 GRANT EXECUTE ON PROCEDURE weekly_report TO task2_lead;
85 GRANT ALTER ROUTINE ON PROCEDURE daily_report TO product_lead;
86 GRANT ALTER ROUTINE ON PROCEDURE weekly_report TO product_lead;
87 GRANT EXECUTE ON PROCEDURE weekly_report TO hr_rules;
88 GRANT EXECUTE ON PROCEDURE weekly_report TO hr_access;
89 GRANT SELECT ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
90 GRANT SELECT (f1) ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
91 GRANT EXECUTE ON PROCEDURE proc1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
92 # USER WITH ROLES.
93 #
94 # Case 1: Login as user1
95 #
96 # Connect as user1
97 # With the roles activated during connection.
98 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
99 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
100 SELECT CURRENT_ROLE();
101 CURRENT_ROLE()
102 NONE
103 SHOW GRANTS;
104 Grants for user1@%
105 GRANT USAGE ON *.* TO `user1`@`%`
106 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
107 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
108 GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
109 # Enable roles;
110 SET ROLE hr_rules;
111 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
112 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
113 hr_rules % NO NO
114 SELECT CURRENT_ROLE();
115 CURRENT_ROLE()
116 `hr_rules`@`%`
117 SHOW GRANTS;
118 Grants for user1@%
119 GRANT USAGE ON *.* TO `user1`@`%`
120 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user1`@`%`
121 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
122 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
123 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user1`@`%`
124 GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
125 Disconnect user1
126 #
127 # Case 2: Login as user2
128 #
129 # Connect as user2
130 # With the roles activated during connection.
131 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
132 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
133 SELECT CURRENT_ROLE();
134 CURRENT_ROLE()
135 NONE
136 SHOW GRANTS;
137 Grants for user2@%
138 GRANT USAGE ON *.* TO `user2`@`%`
139 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
140 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
141 GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
142 # Enable roles;
143 SET ROLE hr_rules;
144 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
145 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
146 hr_rules % NO NO
147 SELECT CURRENT_ROLE();
148 CURRENT_ROLE()
149 `hr_rules`@`%`
150 SHOW GRANTS;
151 Grants for user2@%
152 GRANT USAGE ON *.* TO `user2`@`%`
153 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user2`@`%`
154 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
155 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
156 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user2`@`%`
157 GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
158 Disconnect user2
159 #
160 # Case 3: Login as lead_user1 with mandatory roles.
161 #
162 SET GLOBAL mandatory_roles = 'hr_access';
163 # Connect as lead_user1
164 # With the roles activated during connection.
165 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
166 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
167 SELECT CURRENT_ROLE();
168 CURRENT_ROLE()
169 NONE
170 SHOW GRANTS;
171 Grants for lead_user1@%
172 GRANT USAGE ON *.* TO `lead_user1`@`%`
173 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
174 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
175 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
176 # Enable roles;
177 SET ROLE hr_access;
178 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
179 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
180 hr_access % NO YES
181 SELECT CURRENT_ROLE();
182 CURRENT_ROLE()
183 `hr_access`@`%`
184 SHOW GRANTS;
185 Grants for lead_user1@%
186 GRANT USAGE ON *.* TO `lead_user1`@`%`
187 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
188 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
189 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
190 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
191 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
192 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
193 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
194 Disconnect lead_user1
195 SET GLOBAL activate_all_roles_on_login=on;
196 # Connect as lead_user1
197 # With the roles activated during connection.
198 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
199 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
200 task1_lead % NO NO
201 hr_access % NO YES
202 SELECT CURRENT_ROLE();
203 CURRENT_ROLE()
204 `hr_access`@`%`,`task1_lead`@`%`
205 SHOW GRANTS;
206 Grants for lead_user1@%
207 GRANT USAGE ON *.* TO `lead_user1`@`%`
208 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
209 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
210 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
211 GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
212 GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
213 GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
214 GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
215 GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
216 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
217 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
218 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
219 GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
220 # Enable roles;
221 SET ROLE hr_access;
222 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
223 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
224 hr_access % NO YES
225 SELECT CURRENT_ROLE();
226 CURRENT_ROLE()
227 `hr_access`@`%`
228 SHOW GRANTS;
229 Grants for lead_user1@%
230 GRANT USAGE ON *.* TO `lead_user1`@`%`
231 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
232 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
233 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
234 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
235 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
236 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
237 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
238 Disconnect lead_user1
239 SET GLOBAL activate_all_roles_on_login=off;
240 # Set 'task2' as mandatory which is not directly granted to lead_user1
241 # and then use SET ROLE ALL
242 SET GLOBAL mandatory_roles = 'task2';
243 # Connect as lead_user1
244 # With the roles activated during connection.
245 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
246 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
247 SELECT CURRENT_ROLE();
248 CURRENT_ROLE()
249 NONE
250 SHOW GRANTS;
251 Grants for lead_user1@%
252 GRANT USAGE ON *.* TO `lead_user1`@`%`
253 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
254 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
255 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
256 # Enable roles;
257 SET ROLE ALL;
258 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
259 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
260 task1_lead % NO NO
261 hr_access % NO NO
262 task2 % NO YES
263 SELECT CURRENT_ROLE();
264 CURRENT_ROLE()
265 `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%`
266 SHOW GRANTS;
267 Grants for lead_user1@%
268 GRANT USAGE ON *.* TO `lead_user1`@`%`
269 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
270 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
271 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
272 GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
273 GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
274 GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
275 GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
276 GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
277 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
278 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
279 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
280 GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
281 Disconnect lead_user1
282 # Set 'task2' as mandatory which is not directly granted to lead_user1
283 # and then use SET ROLE ALL EXCEPT 'task2'
284 SET GLOBAL mandatory_roles = 'task2';
285 # Connect as lead_user1
286 # With the roles activated during connection.
287 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
288 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
289 SELECT CURRENT_ROLE();
290 CURRENT_ROLE()
291 NONE
292 SHOW GRANTS;
293 Grants for lead_user1@%
294 GRANT USAGE ON *.* TO `lead_user1`@`%`
295 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
296 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
297 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
298 # Enable roles;
299 SET ROLE ALL EXCEPT task2;
300 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
301 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
302 task1_lead % NO NO
303 hr_access % NO NO
304 SELECT CURRENT_ROLE();
305 CURRENT_ROLE()
306 `hr_access`@`%`,`task1_lead`@`%`
307 SHOW GRANTS;
308 Grants for lead_user1@%
309 GRANT USAGE ON *.* TO `lead_user1`@`%`
310 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
311 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
312 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
313 GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
314 GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
315 GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
316 GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
317 GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
318 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
319 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
320 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
321 GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
322 Disconnect lead_user1
323 SET GLOBAL mandatory_roles = default;
324 #
325 # Case 4: Login as lead_user2 with default roles.
326 #
327 SET DEFAULT ROLE hr_access TO lead_user2;
328 # Connect as lead_user2
329 # With the roles activated during connection.
330 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
331 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
332 hr_access % YES NO
333 SELECT CURRENT_ROLE();
334 CURRENT_ROLE()
335 `hr_access`@`%`
336 SHOW GRANTS;
337 Grants for lead_user2@%
338 GRANT USAGE ON *.* TO `lead_user2`@`%`
339 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user2`@`%`
340 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
341 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
342 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
343 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
344 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
345 GRANT `hr_rules`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
346 # Enable roles;
347 SET ROLE task2_lead;
348 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
349 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
350 task2_lead % NO NO
351 SELECT CURRENT_ROLE();
352 CURRENT_ROLE()
353 `task2_lead`@`%`
354 SHOW GRANTS;
355 Grants for lead_user2@%
356 GRANT USAGE ON *.* TO `lead_user2`@`%`
357 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
358 GRANT SELECT, SELECT (`f2`), UPDATE ON `test`.`task1_ledger1` TO `lead_user2`@`%`
359 GRANT SELECT (`f2`), UPDATE ON `test`.`task1_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
360 GRANT INSERT, UPDATE ON `test`.`task2_ledger1` TO `lead_user2`@`%`
361 GRANT INSERT, UPDATE ON `test`.`task2_ledger2` TO `lead_user2`@`%`
362 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
363 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
364 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
365 GRANT `reader`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
366 Disconnect lead_user2
367 SET DEFAULT ROLE NONE TO lead_user1;
368 # Set 'task2' as mandatory while CREATEing and ALTERing the user.
369 CREATE USER lead_user3 IDENTIFIED BY 'foo' DEFAULT ROLE task2;
370 GRANT SELECT ON test.* TO lead_user3;
371 GRANT task2 TO lead_user3;
372 # Connect as lead_user3
373 # With the roles activated during connection.
374 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
375 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
376 task2 % YES NO
377 SELECT CURRENT_ROLE();
378 CURRENT_ROLE()
379 `task2`@`%`
380 SHOW GRANTS;
381 Grants for lead_user3@%
382 GRANT USAGE ON *.* TO `lead_user3`@`%`
383 GRANT SELECT ON `test`.* TO `lead_user3`@`%`
384 GRANT `task2`@`%` TO `lead_user3`@`%`
385 # Enable roles;
386 SET ROLE NONE;
387 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
388 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
389 SELECT CURRENT_ROLE();
390 CURRENT_ROLE()
391 NONE
392 SHOW GRANTS;
393 Grants for lead_user3@%
394 GRANT USAGE ON *.* TO `lead_user3`@`%`
395 GRANT SELECT ON `test`.* TO `lead_user3`@`%`
396 GRANT `task2`@`%` TO `lead_user3`@`%`
397 Disconnect lead_user3
398 ALTER USER lead_user3 DEFAULT ROLE NONE;
399 # Connect as lead_user3
400 # With the roles activated during connection.
401 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
402 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
403 SELECT CURRENT_ROLE();
404 CURRENT_ROLE()
405 NONE
406 SHOW GRANTS;
407 Grants for lead_user3@%
408 GRANT USAGE ON *.* TO `lead_user3`@`%`
409 GRANT SELECT ON `test`.* TO `lead_user3`@`%`
410 GRANT `task2`@`%` TO `lead_user3`@`%`
411 # Enable roles;
412 SET ROLE NONE;
413 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
414 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
415 SELECT CURRENT_ROLE();
416 CURRENT_ROLE()
417 NONE
418 SHOW GRANTS;
419 Grants for lead_user3@%
420 GRANT USAGE ON *.* TO `lead_user3`@`%`
421 GRANT SELECT ON `test`.* TO `lead_user3`@`%`
422 GRANT `task2`@`%` TO `lead_user3`@`%`
423 Disconnect lead_user3
424 ALTER USER lead_user3 DEFAULT ROLE task2;
425 # Connect as lead_user3
426 # With the roles activated during connection.
427 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
428 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
429 task2 % YES NO
430 SELECT CURRENT_ROLE();
431 CURRENT_ROLE()
432 `task2`@`%`
433 SHOW GRANTS;
434 Grants for lead_user3@%
435 GRANT USAGE ON *.* TO `lead_user3`@`%`
436 GRANT SELECT ON `test`.* TO `lead_user3`@`%`
437 GRANT `task2`@`%` TO `lead_user3`@`%`
438 # Enable roles;
439 SET ROLE NONE;
440 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
441 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
442 SELECT CURRENT_ROLE();
443 CURRENT_ROLE()
444 NONE
445 SHOW GRANTS;
446 Grants for lead_user3@%
447 GRANT USAGE ON *.* TO `lead_user3`@`%`
448 GRANT SELECT ON `test`.* TO `lead_user3`@`%`
449 GRANT `task2`@`%` TO `lead_user3`@`%`
450 Disconnect lead_user3
451 DROP USER lead_user3;
452 #
453 # Case 5: Login as project_lead_user with role graph.
454 #
455 # Connect as project_lead_user
456 # With the roles activated during connection.
457 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
458 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
459 SELECT CURRENT_ROLE();
460 CURRENT_ROLE()
461 NONE
462 SHOW GRANTS;
463 Grants for project_lead_user@%
464 GRANT USAGE ON *.* TO `project_lead_user`@`%`
465 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
466 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
467 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
468 # Enable roles;
469 SET ROLE hr_access;
470 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
471 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
472 hr_access % NO NO
473 SELECT CURRENT_ROLE();
474 CURRENT_ROLE()
475 `hr_access`@`%`
476 SHOW GRANTS;
477 Grants for project_lead_user@%
478 GRANT USAGE ON *.* TO `project_lead_user`@`%`
479 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
480 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
481 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
482 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
483 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
484 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
485 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
486 Disconnect project_lead_user
487 #
488 # Case 6: Login as project_lead_user with cyclic role graph.
489 #
490 # Form a role graph loop.
491 #
492 # reader
493 # |
494 # V
495 # updater<------------\
496 # | |
497 # |``````````````| |
498 # V V |
499 # task1_lead task2_lead |
500 # | | |
501 # ````````|``````` |
502 # V |
503 # project_lead-----------/
504 #
505 INSERT INTO mysql.role_edges (FROM_USER,FROM_HOST,TO_USER,TO_HOST)
506 VALUES('project_lead','%','updater','%');
507 FLUSH PRIVILEGES;
508 # Connect as project_lead_user
509 # With the roles activated during connection.
510 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
511 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
512 SELECT CURRENT_ROLE();
513 CURRENT_ROLE()
514 NONE
515 SHOW GRANTS;
516 Grants for project_lead_user@%
517 GRANT USAGE ON *.* TO `project_lead_user`@`%`
518 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
519 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
520 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
521 # Enable roles;
522 SET ROLE hr_access;
523 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES ;
524 ROLE_NAME ROLE_HOST IS_DEFAULT IS_MANDATORY
525 hr_access % NO NO
526 SELECT CURRENT_ROLE();
527 CURRENT_ROLE()
528 `hr_access`@`%`
529 SHOW GRANTS;
530 Grants for project_lead_user@%
531 GRANT USAGE ON *.* TO `project_lead_user`@`%`
532 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
533 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
534 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
535 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
536 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
537 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
538 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
539 Disconnect project_lead_user
540 REVOKE project_lead FROM updater;
541 # Cleanup
542 DROP TABLE task1_ledger1;
543 DROP TABLE task1_ledger2;
544 DROP TABLE task2_ledger1;
545 DROP TABLE task2_ledger2;
546 DROP PROCEDURE daily_report;
547 DROP PROCEDURE weekly_report;
548 DROP PROCEDURE proc1;
549 DROP TABLE hr_ledger1;
550 DROP TABLE hr_ledger2;
551 DROP TABLE t1;
552 DROP USER user0;
553 DROP USER user1, user2;
554 DROP USER lead_user1, lead_user2;
555 DROP USER project_lead_user;
556 DROP USER product_lead_user;
557 DROP ROLE reader, updater;
558 DROP ROLE task1, task1_lead, task2, task2_lead;
559 DROP ROLE project_lead;
560 DROP ROLE product_lead;
561 DROP ROLE hr_access;
562 DROP ROLE hr_rules;