Debian Codegraph @

mysql-test/suite/information_schema/r/applicable_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 #
93 # USER WITH NO ROLES ASSIGNED.
94 #
95 # List all APPLICABLE ROLES for user0
96 SELECT
97 USER, /* SQL Standard */
98 HOST, /* MySQL Specific */
99 GRANTEE, /* SQL Standard */
100 GRANTEE_HOST, /* MySQL Specific */
101 ROLE_NAME, /* SQL Standard */
102 ROLE_HOST, /* MySQL Specific */
103 IS_GRANTABLE, /* SQL Standard */
104 IS_DEFAULT, /* MySQL Specific */
105 IS_MANDATORY /* MySQL Specific */
106 FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
107 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
108 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS;
109 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
110 # USER WITH ROLES.
111 #
112 # Case 1: Login as user1
113 #
114 # Connect as user1
115 # With the roles activated during connection.
116 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
117 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
118 user1 % user1 % hr_rules % NO NO NO
119 user1 % user1 % reader % NO NO NO
120 SELECT CURRENT_ROLE();
121 CURRENT_ROLE()
122 NONE
123 SHOW GRANTS;
124 Grants for user1@%
125 GRANT USAGE ON *.* TO `user1`@`%`
126 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
127 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
128 GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
129 # Enable roles;
130 SET ROLE hr_rules;
131 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
132 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
133 user1 % user1 % hr_rules % NO NO NO
134 user1 % user1 % reader % NO NO NO
135 SELECT CURRENT_ROLE();
136 CURRENT_ROLE()
137 `hr_rules`@`%`
138 SHOW GRANTS;
139 Grants for user1@%
140 GRANT USAGE ON *.* TO `user1`@`%`
141 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user1`@`%`
142 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
143 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
144 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user1`@`%`
145 GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
146 Disconnect user1
147 # Connect as user1
148 # With the roles activated during connection.
149 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
150 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
151 SELECT CURRENT_ROLE();
152 CURRENT_ROLE()
153 NONE
154 SHOW GRANTS;
155 Grants for user1@%
156 GRANT USAGE ON *.* TO `user1`@`%`
157 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
158 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
159 GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
160 # Enable roles;
161 SET ROLE hr_rules;
162 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
163 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
164 SELECT CURRENT_ROLE();
165 CURRENT_ROLE()
166 `hr_rules`@`%`
167 SHOW GRANTS;
168 Grants for user1@%
169 GRANT USAGE ON *.* TO `user1`@`%`
170 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user1`@`%`
171 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
172 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
173 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user1`@`%`
174 GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
175 Disconnect user1
176 #
177 # Case 2: Login as user2
178 #
179 # Connect as user2
180 # With the roles activated during connection.
181 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
182 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
183 user2 % updater % reader % YES NO NO
184 user2 % user2 % hr_rules % NO NO NO
185 user2 % user2 % updater % NO NO NO
186 SELECT CURRENT_ROLE();
187 CURRENT_ROLE()
188 NONE
189 SHOW GRANTS;
190 Grants for user2@%
191 GRANT USAGE ON *.* TO `user2`@`%`
192 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
193 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
194 GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
195 # Enable roles;
196 SET ROLE hr_rules;
197 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
198 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
199 user2 % updater % reader % YES NO NO
200 user2 % user2 % hr_rules % NO NO NO
201 user2 % user2 % updater % NO NO NO
202 SELECT CURRENT_ROLE();
203 CURRENT_ROLE()
204 `hr_rules`@`%`
205 SHOW GRANTS;
206 Grants for user2@%
207 GRANT USAGE ON *.* TO `user2`@`%`
208 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user2`@`%`
209 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
210 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
211 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user2`@`%`
212 GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
213 Disconnect user2
214 # Connect as user2
215 # With the roles activated during connection.
216 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
217 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
218 user2 % updater % reader % YES NO NO
219 SELECT CURRENT_ROLE();
220 CURRENT_ROLE()
221 NONE
222 SHOW GRANTS;
223 Grants for user2@%
224 GRANT USAGE ON *.* TO `user2`@`%`
225 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
226 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
227 GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
228 # Enable roles;
229 SET ROLE hr_rules;
230 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
231 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
232 user2 % updater % reader % YES NO NO
233 SELECT CURRENT_ROLE();
234 CURRENT_ROLE()
235 `hr_rules`@`%`
236 SHOW GRANTS;
237 Grants for user2@%
238 GRANT USAGE ON *.* TO `user2`@`%`
239 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user2`@`%`
240 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
241 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
242 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user2`@`%`
243 GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
244 Disconnect user2
245 #
246 # Case 3: Login as lead_user1 with mandatory roles.
247 #
248 SET GLOBAL mandatory_roles = 'task2';
249 # Connect as lead_user1
250 # With the roles activated during connection.
251 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
252 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
253 lead_user1 % hr_access % hr_rules % YES NO NO
254 lead_user1 % lead_user1 % hr_access % NO NO NO
255 lead_user1 % lead_user1 % task1_lead % NO NO NO
256 lead_user1 % lead_user1 % task2 % NO NO YES
257 lead_user1 % task1_lead % task1 % NO NO NO
258 lead_user1 % task1_lead % updater % NO NO NO
259 lead_user1 % updater % reader % YES NO NO
260 SELECT CURRENT_ROLE();
261 CURRENT_ROLE()
262 NONE
263 SHOW GRANTS;
264 Grants for lead_user1@%
265 GRANT USAGE ON *.* TO `lead_user1`@`%`
266 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
267 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
268 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
269 # Enable roles;
270 SET ROLE NONE;
271 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
272 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
273 lead_user1 % hr_access % hr_rules % YES NO NO
274 lead_user1 % lead_user1 % hr_access % NO NO NO
275 lead_user1 % lead_user1 % task1_lead % NO NO NO
276 lead_user1 % lead_user1 % task2 % NO NO YES
277 lead_user1 % task1_lead % task1 % NO NO NO
278 lead_user1 % task1_lead % updater % NO NO NO
279 lead_user1 % updater % reader % YES NO NO
280 SELECT CURRENT_ROLE();
281 CURRENT_ROLE()
282 NONE
283 SHOW GRANTS;
284 Grants for lead_user1@%
285 GRANT USAGE ON *.* TO `lead_user1`@`%`
286 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
287 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
288 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
289 Disconnect lead_user1
290 # Connect as lead_user1
291 # With the roles activated during connection.
292 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
293 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
294 lead_user1 % hr_access % hr_rules % YES NO NO
295 lead_user1 % updater % reader % YES NO NO
296 SELECT CURRENT_ROLE();
297 CURRENT_ROLE()
298 NONE
299 SHOW GRANTS;
300 Grants for lead_user1@%
301 GRANT USAGE ON *.* TO `lead_user1`@`%`
302 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
303 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
304 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
305 # Enable roles;
306 SET ROLE NONE;
307 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
308 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
309 lead_user1 % hr_access % hr_rules % YES NO NO
310 lead_user1 % updater % reader % YES NO NO
311 SELECT CURRENT_ROLE();
312 CURRENT_ROLE()
313 NONE
314 SHOW GRANTS;
315 Grants for lead_user1@%
316 GRANT USAGE ON *.* TO `lead_user1`@`%`
317 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
318 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
319 GRANT `hr_access`@`%`,`task1_lead`@`%`,`task2`@`%` TO `lead_user1`@`%`
320 Disconnect lead_user1
321 SET GLOBAL mandatory_roles = 'hr_access';
322 # Connect as lead_user1
323 # With the roles activated during connection.
324 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
325 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
326 lead_user1 % hr_access % hr_rules % YES NO NO
327 lead_user1 % lead_user1 % hr_access % NO NO YES
328 lead_user1 % lead_user1 % task1_lead % NO NO NO
329 lead_user1 % task1_lead % task1 % NO NO NO
330 lead_user1 % task1_lead % updater % NO NO NO
331 lead_user1 % updater % reader % YES NO NO
332 SELECT CURRENT_ROLE();
333 CURRENT_ROLE()
334 NONE
335 SHOW GRANTS;
336 Grants for lead_user1@%
337 GRANT USAGE ON *.* TO `lead_user1`@`%`
338 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
339 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
340 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
341 # Enable roles;
342 SET ROLE hr_access;
343 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
344 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
345 lead_user1 % hr_access % hr_rules % YES NO NO
346 lead_user1 % lead_user1 % hr_access % NO NO YES
347 lead_user1 % lead_user1 % task1_lead % NO NO NO
348 lead_user1 % task1_lead % task1 % NO NO NO
349 lead_user1 % task1_lead % updater % NO NO NO
350 lead_user1 % updater % reader % YES NO NO
351 SELECT CURRENT_ROLE();
352 CURRENT_ROLE()
353 `hr_access`@`%`
354 SHOW GRANTS;
355 Grants for lead_user1@%
356 GRANT USAGE ON *.* TO `lead_user1`@`%`
357 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
358 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
359 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
360 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
361 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
362 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
363 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
364 Disconnect lead_user1
365 # Connect as lead_user1
366 # With the roles activated during connection.
367 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
368 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
369 lead_user1 % hr_access % hr_rules % YES NO NO
370 lead_user1 % updater % reader % YES NO NO
371 SELECT CURRENT_ROLE();
372 CURRENT_ROLE()
373 NONE
374 SHOW GRANTS;
375 Grants for lead_user1@%
376 GRANT USAGE ON *.* TO `lead_user1`@`%`
377 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
378 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
379 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
380 # Enable roles;
381 SET ROLE hr_access;
382 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
383 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
384 lead_user1 % hr_access % hr_rules % YES NO NO
385 lead_user1 % updater % reader % YES NO NO
386 SELECT CURRENT_ROLE();
387 CURRENT_ROLE()
388 `hr_access`@`%`
389 SHOW GRANTS;
390 Grants for lead_user1@%
391 GRANT USAGE ON *.* TO `lead_user1`@`%`
392 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
393 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
394 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
395 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
396 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
397 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
398 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
399 Disconnect lead_user1
400 SET GLOBAL activate_all_roles_on_login=on;
401 # Connect as lead_user1
402 # With the roles activated during connection.
403 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
404 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
405 lead_user1 % hr_access % hr_rules % YES NO NO
406 lead_user1 % lead_user1 % hr_access % NO NO YES
407 lead_user1 % lead_user1 % task1_lead % NO NO NO
408 lead_user1 % task1_lead % task1 % NO NO NO
409 lead_user1 % task1_lead % updater % NO NO NO
410 lead_user1 % updater % reader % YES NO NO
411 SELECT CURRENT_ROLE();
412 CURRENT_ROLE()
413 `hr_access`@`%`,`task1_lead`@`%`
414 SHOW GRANTS;
415 Grants for lead_user1@%
416 GRANT USAGE ON *.* TO `lead_user1`@`%`
417 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
418 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
419 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
420 GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
421 GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
422 GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
423 GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
424 GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
425 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
426 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
427 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
428 GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
429 # Enable roles;
430 SET ROLE hr_access;
431 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
432 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
433 lead_user1 % hr_access % hr_rules % YES NO NO
434 lead_user1 % lead_user1 % hr_access % NO NO YES
435 lead_user1 % lead_user1 % task1_lead % NO NO NO
436 lead_user1 % task1_lead % task1 % NO NO NO
437 lead_user1 % task1_lead % updater % NO NO NO
438 lead_user1 % updater % reader % YES NO NO
439 SELECT CURRENT_ROLE();
440 CURRENT_ROLE()
441 `hr_access`@`%`
442 SHOW GRANTS;
443 Grants for lead_user1@%
444 GRANT USAGE ON *.* TO `lead_user1`@`%`
445 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
446 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
447 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
448 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
449 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
450 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
451 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
452 Disconnect lead_user1
453 # Connect as lead_user1
454 # With the roles activated during connection.
455 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
456 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
457 lead_user1 % hr_access % hr_rules % YES NO NO
458 lead_user1 % updater % reader % YES NO NO
459 SELECT CURRENT_ROLE();
460 CURRENT_ROLE()
461 `hr_access`@`%`,`task1_lead`@`%`
462 SHOW GRANTS;
463 Grants for lead_user1@%
464 GRANT USAGE ON *.* TO `lead_user1`@`%`
465 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
466 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
467 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
468 GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
469 GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
470 GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
471 GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
472 GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
473 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
474 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
475 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
476 GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
477 # Enable roles;
478 SET ROLE hr_access;
479 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
480 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
481 lead_user1 % hr_access % hr_rules % YES NO NO
482 lead_user1 % updater % reader % YES NO NO
483 SELECT CURRENT_ROLE();
484 CURRENT_ROLE()
485 `hr_access`@`%`
486 SHOW GRANTS;
487 Grants for lead_user1@%
488 GRANT USAGE ON *.* TO `lead_user1`@`%`
489 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
490 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
491 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
492 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
493 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
494 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
495 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
496 Disconnect lead_user1
497 SET GLOBAL activate_all_roles_on_login=off;
498 SET GLOBAL mandatory_roles = default;
499 #
500 # Case 4: Login as lead_user2 with default roles.
501 #
502 SET DEFAULT ROLE hr_access TO lead_user2;
503 # Connect as lead_user2
504 # With the roles activated during connection.
505 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
506 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
507 lead_user2 % hr_access % hr_rules % YES NO NO
508 lead_user2 % lead_user2 % hr_access % NO YES NO
509 lead_user2 % lead_user2 % task2_lead % NO NO NO
510 lead_user2 % task2_lead % task2 % NO NO NO
511 lead_user2 % task2_lead % updater % NO NO NO
512 lead_user2 % updater % reader % YES NO NO
513 SELECT CURRENT_ROLE();
514 CURRENT_ROLE()
515 `hr_access`@`%`
516 SHOW GRANTS;
517 Grants for lead_user2@%
518 GRANT USAGE ON *.* TO `lead_user2`@`%`
519 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user2`@`%`
520 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
521 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
522 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
523 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
524 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
525 GRANT `hr_rules`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
526 # Enable roles;
527 SET ROLE task2_lead;
528 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
529 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
530 lead_user2 % hr_access % hr_rules % YES NO NO
531 lead_user2 % lead_user2 % hr_access % NO YES NO
532 lead_user2 % lead_user2 % task2_lead % NO NO NO
533 lead_user2 % task2_lead % task2 % NO NO NO
534 lead_user2 % task2_lead % updater % NO NO NO
535 lead_user2 % updater % reader % YES NO NO
536 SELECT CURRENT_ROLE();
537 CURRENT_ROLE()
538 `task2_lead`@`%`
539 SHOW GRANTS;
540 Grants for lead_user2@%
541 GRANT USAGE ON *.* TO `lead_user2`@`%`
542 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
543 GRANT SELECT, SELECT (`f2`), UPDATE ON `test`.`task1_ledger1` TO `lead_user2`@`%`
544 GRANT SELECT (`f2`), UPDATE ON `test`.`task1_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
545 GRANT INSERT, UPDATE ON `test`.`task2_ledger1` TO `lead_user2`@`%`
546 GRANT INSERT, UPDATE ON `test`.`task2_ledger2` TO `lead_user2`@`%`
547 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
548 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
549 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
550 GRANT `reader`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
551 Disconnect lead_user2
552 # Connect as lead_user2
553 # With the roles activated during connection.
554 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
555 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
556 lead_user2 % hr_access % hr_rules % YES NO NO
557 lead_user2 % updater % reader % YES NO NO
558 SELECT CURRENT_ROLE();
559 CURRENT_ROLE()
560 `hr_access`@`%`
561 SHOW GRANTS;
562 Grants for lead_user2@%
563 GRANT USAGE ON *.* TO `lead_user2`@`%`
564 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user2`@`%`
565 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
566 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
567 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
568 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
569 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
570 GRANT `hr_rules`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
571 # Enable roles;
572 SET ROLE task2_lead;
573 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
574 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
575 lead_user2 % hr_access % hr_rules % YES NO NO
576 lead_user2 % updater % reader % YES NO NO
577 SELECT CURRENT_ROLE();
578 CURRENT_ROLE()
579 `task2_lead`@`%`
580 SHOW GRANTS;
581 Grants for lead_user2@%
582 GRANT USAGE ON *.* TO `lead_user2`@`%`
583 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
584 GRANT SELECT, SELECT (`f2`), UPDATE ON `test`.`task1_ledger1` TO `lead_user2`@`%`
585 GRANT SELECT (`f2`), UPDATE ON `test`.`task1_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
586 GRANT INSERT, UPDATE ON `test`.`task2_ledger1` TO `lead_user2`@`%`
587 GRANT INSERT, UPDATE ON `test`.`task2_ledger2` TO `lead_user2`@`%`
588 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
589 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
590 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
591 GRANT `reader`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
592 Disconnect lead_user2
593 SET DEFAULT ROLE NONE TO lead_user1;
594 #
595 # Case 5: Login as project_lead_user with role graph.
596 #
597 # Connect as project_lead_user
598 # With the roles activated during connection.
599 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
600 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
601 project_lead_user % hr_access % hr_rules % YES NO NO
602 project_lead_user % project_lead % task1_lead % NO NO NO
603 project_lead_user % project_lead % task2_lead % NO NO NO
604 project_lead_user % project_lead_user % hr_access % NO NO NO
605 project_lead_user % project_lead_user % project_lead % NO NO NO
606 project_lead_user % task1_lead % task1 % NO NO NO
607 project_lead_user % task1_lead % updater % NO NO NO
608 project_lead_user % task2_lead % task2 % NO NO NO
609 project_lead_user % task2_lead % updater % NO NO NO
610 project_lead_user % updater % reader % YES NO NO
611 SELECT CURRENT_ROLE();
612 CURRENT_ROLE()
613 NONE
614 SHOW GRANTS;
615 Grants for project_lead_user@%
616 GRANT USAGE ON *.* TO `project_lead_user`@`%`
617 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
618 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
619 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
620 # Enable roles;
621 SET ROLE hr_access;
622 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
623 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
624 project_lead_user % hr_access % hr_rules % YES NO NO
625 project_lead_user % project_lead % task1_lead % NO NO NO
626 project_lead_user % project_lead % task2_lead % NO NO NO
627 project_lead_user % project_lead_user % hr_access % NO NO NO
628 project_lead_user % project_lead_user % project_lead % NO NO NO
629 project_lead_user % task1_lead % task1 % NO NO NO
630 project_lead_user % task1_lead % updater % NO NO NO
631 project_lead_user % task2_lead % task2 % NO NO NO
632 project_lead_user % task2_lead % updater % NO NO NO
633 project_lead_user % updater % reader % YES NO NO
634 SELECT CURRENT_ROLE();
635 CURRENT_ROLE()
636 `hr_access`@`%`
637 SHOW GRANTS;
638 Grants for project_lead_user@%
639 GRANT USAGE ON *.* TO `project_lead_user`@`%`
640 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
641 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
642 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
643 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
644 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
645 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
646 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
647 Disconnect project_lead_user
648 # Connect as project_lead_user
649 # With the roles activated during connection.
650 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
651 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
652 project_lead_user % hr_access % hr_rules % YES NO NO
653 project_lead_user % updater % reader % YES NO NO
654 SELECT CURRENT_ROLE();
655 CURRENT_ROLE()
656 NONE
657 SHOW GRANTS;
658 Grants for project_lead_user@%
659 GRANT USAGE ON *.* TO `project_lead_user`@`%`
660 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
661 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
662 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
663 # Enable roles;
664 SET ROLE hr_access;
665 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
666 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
667 project_lead_user % hr_access % hr_rules % YES NO NO
668 project_lead_user % updater % reader % YES NO NO
669 SELECT CURRENT_ROLE();
670 CURRENT_ROLE()
671 `hr_access`@`%`
672 SHOW GRANTS;
673 Grants for project_lead_user@%
674 GRANT USAGE ON *.* TO `project_lead_user`@`%`
675 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
676 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
677 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
678 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
679 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
680 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
681 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
682 Disconnect project_lead_user
683 #
684 # Case 6: Login as project_lead_user with cyclic role graph.
685 #
686 # Form a role graph loop.
687 #
688 # reader
689 # |
690 # V
691 # updater<------------\
692 # | |
693 # |``````````````| |
694 # V V |
695 # task1_lead task2_lead |
696 # | | |
697 # ````````|``````` |
698 # V |
699 # project_lead-----------/
700 #
701 INSERT INTO mysql.role_edges (FROM_USER,FROM_HOST,TO_USER,TO_HOST)
702 VALUES('project_lead','%','updater','%');
703 FLUSH PRIVILEGES;
704 # Connect as project_lead_user
705 # With the roles activated during connection.
706 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
707 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
708 project_lead_user % hr_access % hr_rules % YES NO NO
709 project_lead_user % project_lead % task1_lead % NO NO NO
710 project_lead_user % project_lead % task2_lead % NO NO NO
711 project_lead_user % project_lead_user % hr_access % NO NO NO
712 project_lead_user % project_lead_user % project_lead % NO NO NO
713 project_lead_user % task1_lead % task1 % NO NO NO
714 project_lead_user % task1_lead % updater % NO NO NO
715 project_lead_user % task2_lead % task2 % NO NO NO
716 project_lead_user % task2_lead % updater % NO NO NO
717 project_lead_user % updater % project_lead % NO NO NO
718 project_lead_user % updater % reader % YES NO NO
719 SELECT CURRENT_ROLE();
720 CURRENT_ROLE()
721 NONE
722 SHOW GRANTS;
723 Grants for project_lead_user@%
724 GRANT USAGE ON *.* TO `project_lead_user`@`%`
725 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
726 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
727 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
728 # Enable roles;
729 SET ROLE hr_access;
730 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES ORDER BY GRANTEE, ROLE_NAME;
731 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
732 project_lead_user % hr_access % hr_rules % YES NO NO
733 project_lead_user % project_lead % task1_lead % NO NO NO
734 project_lead_user % project_lead % task2_lead % NO NO NO
735 project_lead_user % project_lead_user % hr_access % NO NO NO
736 project_lead_user % project_lead_user % project_lead % NO NO NO
737 project_lead_user % task1_lead % task1 % NO NO NO
738 project_lead_user % task1_lead % updater % NO NO NO
739 project_lead_user % task2_lead % task2 % NO NO NO
740 project_lead_user % task2_lead % updater % NO NO NO
741 project_lead_user % updater % project_lead % NO NO NO
742 project_lead_user % updater % reader % YES NO NO
743 SELECT CURRENT_ROLE();
744 CURRENT_ROLE()
745 `hr_access`@`%`
746 SHOW GRANTS;
747 Grants for project_lead_user@%
748 GRANT USAGE ON *.* TO `project_lead_user`@`%`
749 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
750 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
751 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
752 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
753 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
754 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
755 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
756 Disconnect project_lead_user
757 # Connect as project_lead_user
758 # With the roles activated during connection.
759 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
760 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
761 project_lead_user % hr_access % hr_rules % YES NO NO
762 project_lead_user % updater % reader % YES NO NO
763 SELECT CURRENT_ROLE();
764 CURRENT_ROLE()
765 NONE
766 SHOW GRANTS;
767 Grants for project_lead_user@%
768 GRANT USAGE ON *.* TO `project_lead_user`@`%`
769 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
770 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
771 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
772 # Enable roles;
773 SET ROLE hr_access;
774 SELECT * FROM INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS ORDER BY GRANTEE, ROLE_NAME;
775 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
776 project_lead_user % hr_access % hr_rules % YES NO NO
777 project_lead_user % updater % reader % YES NO NO
778 SELECT CURRENT_ROLE();
779 CURRENT_ROLE()
780 `hr_access`@`%`
781 SHOW GRANTS;
782 Grants for project_lead_user@%
783 GRANT USAGE ON *.* TO `project_lead_user`@`%`
784 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
785 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
786 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
787 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
788 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
789 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
790 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
791 Disconnect project_lead_user
792 REVOKE project_lead FROM updater;
793 #
794 # Bug#30292102 WL10895 APPLICABLE_ROLES DISPLAYS
795 # DUPLICATE ENTRY FOR ROLE
796 #
797 CREATE USER u1 IDENTIFIED BY 'foo';
798 CREATE ROLE r1;
799 GRANT r1 to u1;
800 GRANT SELECT ON test.* TO u1;
801 SET GLOBAL mandatory_roles='r1';
802 GRANT r1 TO u1 WITH ADMIN OPTION;
803 SET GLOBAL activate_all_roles_on_login = 'ON';
804 # Should not show duplicate entry for r1->u1 edge after the fix.
805 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
806 USER HOST GRANTEE GRANTEE_HOST ROLE_NAME ROLE_HOST IS_GRANTABLE IS_DEFAULT IS_MANDATORY
807 u1 % u1 % r1 % YES NO YES
808 SET GLOBAL mandatory_roles=default;
809 SET GLOBAL activate_all_roles_on_login=default;
810 DROP USER u1;
811 DROP ROLE r1;
812 # Cleanup
813 DROP TABLE task1_ledger1;
814 DROP TABLE task1_ledger2;
815 DROP TABLE task2_ledger1;
816 DROP TABLE task2_ledger2;
817 DROP PROCEDURE daily_report;
818 DROP PROCEDURE weekly_report;
819 DROP PROCEDURE proc1;
820 DROP TABLE hr_ledger1;
821 DROP TABLE hr_ledger2;
822 DROP TABLE t1;
823 DROP USER user0;
824 DROP USER user1, user2;
825 DROP USER lead_user1, lead_user2;
826 DROP USER project_lead_user;
827 DROP USER product_lead_user;
828 DROP ROLE reader, updater;
829 DROP ROLE task1, task1_lead, task2, task2_lead;
830 DROP ROLE project_lead;
831 DROP ROLE product_lead;
832 DROP ROLE hr_access;
833 DROP ROLE hr_rules;