File: role_routine_grants.result

package info (click to toggle)
mysql-8.0 8.0.43-3
  • links: PTS, VCS
  • area: main
  • in suites: sid
  • size: 1,273,924 kB
  • sloc: cpp: 4,684,605; ansic: 412,450; pascal: 108,398; java: 83,641; perl: 30,221; cs: 27,067; sql: 26,594; sh: 24,181; python: 21,816; yacc: 17,169; php: 11,522; xml: 7,388; javascript: 7,076; makefile: 2,194; lex: 1,075; awk: 670; asm: 520; objc: 183; ruby: 97; lisp: 86
file content (401 lines) | stat: -rw-r--r-- 17,543 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
# SETUP SCHEMA
#
# SETUP SCHEMA
#
use test;
#
#  CREATE ROLES
#
CREATE ROLE reader, updater;
GRANT reader TO updater WITH ADMIN OPTION;
CREATE ROLE task1, task1_lead, task2, task2_lead;
GRANT updater TO task1_lead;
GRANT updater TO task2_lead;
GRANT task1 TO task1_lead;
GRANT task2 TO task2_lead;
CREATE ROLE project_lead;
GRANT task1_lead TO project_lead;
GRANT task2_lead TO project_lead;
CREATE ROLE product_lead;
GRANT project_lead TO product_lead;
CREATE ROLE hr_rules, hr_access;
GRANT hr_rules TO hr_access WITH ADMIN OPTION;
#
# USER ACCOUNT AND ASSIGN ROLE's TO THEM.
#
CREATE USER user0 IDENTIFIED BY 'foo';
CREATE USER user1 IDENTIFIED BY 'foo';
CREATE USER user2 IDENTIFIED BY 'foo';
GRANT reader, hr_rules TO user1;
GRANT updater, hr_rules TO user2;
CREATE USER lead_user1 IDENTIFIED BY 'foo';
CREATE USER lead_user2 IDENTIFIED BY 'foo';
GRANT task1_lead, hr_access TO lead_user1;
GRANT task2_lead, hr_access TO lead_user2;
CREATE USER project_lead_user IDENTIFIED BY 'foo';
GRANT project_lead, hr_access TO project_lead_user;
CREATE USER product_lead_user IDENTIFIED BY 'foo';
GRANT product_lead, hr_access TO product_lead_user;
#
# CREATE DATABASE OBJECTS
#
CREATE TABLE task1_ledger1 (f1 INT, f2 INT);
CREATE TABLE task1_ledger2 (f1 INT, f2 INT);
CREATE TABLE task2_ledger1 (f1 INT, f2 INT);
CREATE TABLE task2_ledger2 (f1 INT, f2 INT);
CREATE PROCEDURE daily_report (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END//
CREATE PROCEDURE weekly_report (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END//
CREATE PROCEDURE proc1 (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t1;
END//
CREATE TABLE hr_ledger1 (f1 INT, f2 INT);
CREATE TABLE hr_ledger2 (f1 INT, f2 INT);
CREATE TABLE t1 (f1 INT);
#
# GRANTS TO ROLE
#
GRANT SELECT ON task1_ledger1 TO reader;
GRANT UPDATE ON task1_ledger1 TO updater;
GRANT UPDATE ON task1_ledger2 TO updater;
GRANT UPDATE ON task2_ledger1 TO updater;
GRANT UPDATE ON task2_ledger2 TO updater;
GRANT INSERT ON task1_ledger1 TO task1_lead;
GRANT INSERT ON task1_ledger2 TO task1_lead;
GRANT INSERT ON task2_ledger1 TO task2_lead;
GRANT INSERT ON task2_ledger2 TO task2_lead;
GRANT DELETE ON task2_ledger1 TO project_lead;
GRANT DELETE ON task2_ledger2 TO project_lead;
GRANT SELECT ON hr_ledger1 TO hr_rules;
GRANT SELECT ON hr_ledger2 to hr_access;
GRANT SELECT (f2) ON task1_ledger1 to reader;
GRANT SELECT (f2) ON task1_ledger2 to reader WITH GRANT OPTION;
GRANT SELECT (f2) ON task2_ledger2 to product_lead;
GRANT SELECT (f2) ON task2_ledger2 to product_lead WITH GRANT OPTION;
GRANT SELECT (f2) ON hr_ledger1 TO hr_rules;
GRANT SELECT (f2) ON hr_ledger2 to hr_access WITH GRANT OPTION;
GRANT EXECUTE ON PROCEDURE daily_report TO task1_lead;
GRANT EXECUTE ON PROCEDURE weekly_report TO task2_lead;
GRANT ALTER ROUTINE ON PROCEDURE daily_report TO product_lead;
GRANT ALTER ROUTINE ON PROCEDURE weekly_report TO product_lead;
GRANT EXECUTE ON PROCEDURE weekly_report TO hr_rules;
GRANT EXECUTE ON PROCEDURE weekly_report TO hr_access;
GRANT SELECT ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
GRANT SELECT (f1) ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
GRANT EXECUTE ON PROCEDURE proc1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
# USER WITH ROLES.
#
# Case 1: Login as user1
#
# Connect as user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for user1@%
GRANT USAGE ON *.* TO `user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
# Enable roles;
SET ROLE hr_rules;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_rules`@`%`
SHOW GRANTS;
Grants for user1@%
GRANT USAGE ON *.* TO `user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `user1`@`%`
Disconnect user1
#
# Case 2: Login as user2
#
# Connect as user2
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for user2@%
GRANT USAGE ON *.* TO `user2`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
# Enable roles;
SET ROLE hr_rules;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_rules`@`%`
SHOW GRANTS;
Grants for user2@%
GRANT USAGE ON *.* TO `user2`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `user2`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `user2`@`%`
GRANT `hr_rules`@`%`,`updater`@`%` TO `user2`@`%`
Disconnect user2
#
# Case 3: Login as lead_user1 with mandatory roles.
#
SET GLOBAL mandatory_roles = 'hr_access';
# Connect as lead_user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_access	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
Disconnect lead_user1
SET GLOBAL activate_all_roles_on_login=on;
# Connect as lead_user1
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_access	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	task1_lead	%	def	test	daily_report	def	test	daily_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`,`task1_lead`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_access	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for lead_user1@%
GRANT USAGE ON *.* TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
Disconnect lead_user1
SET GLOBAL activate_all_roles_on_login=off;
SET GLOBAL mandatory_roles = default;
#
# Case 4: Login as lead_user2 with default roles.
#
SET DEFAULT ROLE hr_access TO lead_user2;
# Connect as lead_user2
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_access	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for lead_user2@%
GRANT USAGE ON *.* TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
GRANT `hr_rules`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
# Enable roles;
SET ROLE task2_lead;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	task2_lead	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`task2_lead`@`%`
SHOW GRANTS;
Grants for lead_user2@%
GRANT USAGE ON *.* TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
GRANT SELECT, SELECT (`f2`), UPDATE ON `test`.`task1_ledger1` TO `lead_user2`@`%`
GRANT SELECT (`f2`), UPDATE ON `test`.`task1_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
GRANT INSERT, UPDATE ON `test`.`task2_ledger1` TO `lead_user2`@`%`
GRANT INSERT, UPDATE ON `test`.`task2_ledger2` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
GRANT `reader`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
Disconnect lead_user2
SET DEFAULT ROLE NONE TO lead_user1;
#
# Case 5: Login as project_lead_user with role graph.
#
# Connect as project_lead_user
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_access	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
Disconnect project_lead_user
#
# Case 6: Login as project_lead_user with cyclic role graph.
#
# Form a role graph loop.
#
#              reader
#                |
#                V
#             updater<------------\
#                |                |
#        |``````````````|         |
#        V              V         |
#   task1_lead     task2_lead     |
#        |              |         |
#        ````````|```````         |
#                V                |
#          project_lead-----------/
#
INSERT INTO mysql.role_edges (FROM_USER,FROM_HOST,TO_USER,TO_HOST)
VALUES('project_lead','%','updater','%');
FLUSH PRIVILEGES;
# Connect as project_lead_user
# With the roles activated during connection.
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT CURRENT_ROLE();
CURRENT_ROLE()
NONE
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
# Enable roles;
SET ROLE hr_access;
SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
GRANTOR	GRANTOR_HOST	GRANTEE	GRANTEE_HOST	SPECIFIC_CATALOG	SPECIFIC_SCHEMA	SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
root	localhost	hr_access	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
root	localhost	hr_rules	%	def	test	weekly_report	def	test	weekly_report	Execute	NO
SELECT CURRENT_ROLE();
CURRENT_ROLE()
`hr_access`@`%`
SHOW GRANTS;
Grants for project_lead_user@%
GRANT USAGE ON *.* TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
Disconnect project_lead_user
REVOKE project_lead FROM updater;
# Cleanup
DROP TABLE task1_ledger1;
DROP TABLE task1_ledger2;
DROP TABLE task2_ledger1;
DROP TABLE task2_ledger2;
DROP PROCEDURE daily_report;
DROP PROCEDURE weekly_report;
DROP PROCEDURE proc1;
DROP TABLE hr_ledger1;
DROP TABLE hr_ledger2;
DROP TABLE t1;
DROP USER user0;
DROP USER user1, user2;
DROP USER lead_user1, lead_user2;
DROP USER project_lead_user;
DROP USER product_lead_user;
DROP ROLE reader, updater;
DROP ROLE task1, task1_lead, task2, task2_lead;
DROP ROLE project_lead;
DROP ROLE product_lead;
DROP ROLE hr_access;
DROP ROLE hr_rules;