Debian Codegraph @

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