Debian Codegraph @

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