Debian Codegraph @

mysql-test/suite/information_schema/r/role_routine_grants.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.ROLE_ROUTINE_GRANTS ;
99 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
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.ROLE_ROUTINE_GRANTS ;
112 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
113 root localhost hr_rules % def test weekly_report def test weekly_report Execute 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.ROLE_ROUTINE_GRANTS ;
132 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
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.ROLE_ROUTINE_GRANTS ;
145 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
146 root localhost hr_rules % def test weekly_report def test weekly_report Execute 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.ROLE_ROUTINE_GRANTS ;
166 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
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.ROLE_ROUTINE_GRANTS ;
179 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
180 root localhost hr_access % def test weekly_report def test weekly_report Execute NO
181 root localhost hr_rules % def test weekly_report def test weekly_report Execute NO
182 SELECT CURRENT_ROLE();
183 CURRENT_ROLE()
184 `hr_access`@`%`
185 SHOW GRANTS;
186 Grants for lead_user1@%
187 GRANT USAGE ON *.* TO `lead_user1`@`%`
188 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
189 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
190 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
191 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
192 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
193 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
194 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
195 Disconnect lead_user1
196 SET GLOBAL activate_all_roles_on_login=on;
197 # Connect as lead_user1
198 # With the roles activated during connection.
199 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
200 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
201 root localhost hr_access % def test weekly_report def test weekly_report Execute NO
202 root localhost hr_rules % def test weekly_report def test weekly_report Execute NO
203 root localhost task1_lead % def test daily_report def test daily_report Execute NO
204 SELECT CURRENT_ROLE();
205 CURRENT_ROLE()
206 `hr_access`@`%`,`task1_lead`@`%`
207 SHOW GRANTS;
208 Grants for lead_user1@%
209 GRANT USAGE ON *.* TO `lead_user1`@`%`
210 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
211 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
212 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
213 GRANT SELECT, SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger1` TO `lead_user1`@`%`
214 GRANT SELECT (`f2`), INSERT, UPDATE ON `test`.`task1_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
215 GRANT UPDATE ON `test`.`task2_ledger1` TO `lead_user1`@`%`
216 GRANT UPDATE ON `test`.`task2_ledger2` TO `lead_user1`@`%`
217 GRANT EXECUTE ON PROCEDURE `test`.`daily_report` TO `lead_user1`@`%`
218 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
219 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
220 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
221 GRANT `hr_rules`@`%`,`reader`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
222 # Enable roles;
223 SET ROLE hr_access;
224 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
225 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
226 root localhost hr_access % def test weekly_report def test weekly_report Execute NO
227 root localhost hr_rules % def test weekly_report def test weekly_report Execute NO
228 SELECT CURRENT_ROLE();
229 CURRENT_ROLE()
230 `hr_access`@`%`
231 SHOW GRANTS;
232 Grants for lead_user1@%
233 GRANT USAGE ON *.* TO `lead_user1`@`%`
234 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user1`@`%`
235 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user1`@`%` WITH GRANT OPTION
236 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user1`@`%`
237 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user1`@`%`
238 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user1`@`%`
239 GRANT `hr_access`@`%`,`task1_lead`@`%` TO `lead_user1`@`%`
240 GRANT `hr_rules`@`%` TO `lead_user1`@`%` WITH ADMIN OPTION
241 Disconnect lead_user1
242 SET GLOBAL activate_all_roles_on_login=off;
243 SET GLOBAL mandatory_roles = default;
244 #
245 # Case 4: Login as lead_user2 with default roles.
246 #
247 SET DEFAULT ROLE hr_access TO lead_user2;
248 # Connect as lead_user2
249 # With the roles activated during connection.
250 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
251 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
252 root localhost hr_access % def test weekly_report def test weekly_report Execute NO
253 root localhost hr_rules % def test weekly_report def test weekly_report Execute NO
254 SELECT CURRENT_ROLE();
255 CURRENT_ROLE()
256 `hr_access`@`%`
257 SHOW GRANTS;
258 Grants for lead_user2@%
259 GRANT USAGE ON *.* TO `lead_user2`@`%`
260 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `lead_user2`@`%`
261 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
262 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
263 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
264 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
265 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
266 GRANT `hr_rules`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
267 # Enable roles;
268 SET ROLE task2_lead;
269 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
270 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
271 root localhost task2_lead % def test weekly_report def test weekly_report Execute NO
272 SELECT CURRENT_ROLE();
273 CURRENT_ROLE()
274 `task2_lead`@`%`
275 SHOW GRANTS;
276 Grants for lead_user2@%
277 GRANT USAGE ON *.* TO `lead_user2`@`%`
278 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `lead_user2`@`%`
279 GRANT SELECT, SELECT (`f2`), UPDATE ON `test`.`task1_ledger1` TO `lead_user2`@`%`
280 GRANT SELECT (`f2`), UPDATE ON `test`.`task1_ledger2` TO `lead_user2`@`%` WITH GRANT OPTION
281 GRANT INSERT, UPDATE ON `test`.`task2_ledger1` TO `lead_user2`@`%`
282 GRANT INSERT, UPDATE ON `test`.`task2_ledger2` TO `lead_user2`@`%`
283 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `lead_user2`@`%`
284 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `lead_user2`@`%`
285 GRANT `hr_access`@`%`,`task2_lead`@`%` TO `lead_user2`@`%`
286 GRANT `reader`@`%` TO `lead_user2`@`%` WITH ADMIN OPTION
287 Disconnect lead_user2
288 SET DEFAULT ROLE NONE TO lead_user1;
289 #
290 # Case 5: Login as project_lead_user with role graph.
291 #
292 # Connect as project_lead_user
293 # With the roles activated during connection.
294 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
295 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
296 SELECT CURRENT_ROLE();
297 CURRENT_ROLE()
298 NONE
299 SHOW GRANTS;
300 Grants for project_lead_user@%
301 GRANT USAGE ON *.* TO `project_lead_user`@`%`
302 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
303 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
304 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
305 # Enable roles;
306 SET ROLE hr_access;
307 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
308 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
309 root localhost hr_access % def test weekly_report def test weekly_report Execute NO
310 root localhost hr_rules % def test weekly_report def test weekly_report Execute NO
311 SELECT CURRENT_ROLE();
312 CURRENT_ROLE()
313 `hr_access`@`%`
314 SHOW GRANTS;
315 Grants for project_lead_user@%
316 GRANT USAGE ON *.* TO `project_lead_user`@`%`
317 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
318 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
319 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
320 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
321 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
322 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
323 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
324 Disconnect project_lead_user
325 #
326 # Case 6: Login as project_lead_user with cyclic role graph.
327 #
328 # Form a role graph loop.
329 #
330 # reader
331 # |
332 # V
333 # updater<------------\
334 # | |
335 # |``````````````| |
336 # V V |
337 # task1_lead task2_lead |
338 # | | |
339 # ````````|``````` |
340 # V |
341 # project_lead-----------/
342 #
343 INSERT INTO mysql.role_edges (FROM_USER,FROM_HOST,TO_USER,TO_HOST)
344 VALUES('project_lead','%','updater','%');
345 FLUSH PRIVILEGES;
346 # Connect as project_lead_user
347 # With the roles activated during connection.
348 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
349 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
350 SELECT CURRENT_ROLE();
351 CURRENT_ROLE()
352 NONE
353 SHOW GRANTS;
354 Grants for project_lead_user@%
355 GRANT USAGE ON *.* TO `project_lead_user`@`%`
356 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
357 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
358 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
359 # Enable roles;
360 SET ROLE hr_access;
361 SELECT * FROM INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS ;
362 GRANTOR GRANTOR_HOST GRANTEE GRANTEE_HOST SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME PRIVILEGE_TYPE IS_GRANTABLE
363 root localhost hr_access % def test weekly_report def test weekly_report Execute NO
364 root localhost hr_rules % def test weekly_report def test weekly_report Execute NO
365 SELECT CURRENT_ROLE();
366 CURRENT_ROLE()
367 `hr_access`@`%`
368 SHOW GRANTS;
369 Grants for project_lead_user@%
370 GRANT USAGE ON *.* TO `project_lead_user`@`%`
371 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger1` TO `project_lead_user`@`%`
372 GRANT SELECT, SELECT (`f2`) ON `test`.`hr_ledger2` TO `project_lead_user`@`%` WITH GRANT OPTION
373 GRANT SELECT, SELECT (`f1`) ON `test`.`t1` TO `project_lead_user`@`%`
374 GRANT EXECUTE ON PROCEDURE `test`.`proc1` TO `project_lead_user`@`%`
375 GRANT EXECUTE ON PROCEDURE `test`.`weekly_report` TO `project_lead_user`@`%`
376 GRANT `hr_access`@`%`,`project_lead`@`%` TO `project_lead_user`@`%`
377 GRANT `hr_rules`@`%` TO `project_lead_user`@`%` WITH ADMIN OPTION
378 Disconnect project_lead_user
379 REVOKE project_lead FROM updater;
380 # Cleanup
381 DROP TABLE task1_ledger1;
382 DROP TABLE task1_ledger2;
383 DROP TABLE task2_ledger1;
384 DROP TABLE task2_ledger2;
385 DROP PROCEDURE daily_report;
386 DROP PROCEDURE weekly_report;
387 DROP PROCEDURE proc1;
388 DROP TABLE hr_ledger1;
389 DROP TABLE hr_ledger2;
390 DROP TABLE t1;
391 DROP USER user0;
392 DROP USER user1, user2;
393 DROP USER lead_user1, lead_user2;
394 DROP USER project_lead_user;
395 DROP USER product_lead_user;
396 DROP ROLE reader, updater;
397 DROP ROLE task1, task1_lead, task2, task2_lead;
398 DROP ROLE project_lead;
399 DROP ROLE product_lead;
400 DROP ROLE hr_access;
401 DROP ROLE hr_rules;