Debian Codegraph @

mysql-test/suite/information_schema/include/roles_schema.inc

raw
1 --echo #
2 --echo # SETUP SCHEMA
3 --echo #
4
5 #
6 # Testing INFORMATION_SCHEMA tables for ROLES
7 #
8 # Let us consider following Role dependency graph:
9 #
10 # reader
11 # |
12 # V
13 # task1 updater task2
14 # | | | hr_rules
15 # | |``````````````| | |
16 # V V V V V
17 # task1_lead task2_lead hr_access
18 # | |
19 # ````````|```````
20 # V
21 # project_lead
22 # |
23 # V
24 # product_lead
25 #
26 #
27
28 use test;
29
30 --echo #
31 --echo # CREATE ROLES
32 --echo #
33
34 CREATE ROLE reader, updater;
35 GRANT reader TO updater WITH ADMIN OPTION;
36
37 CREATE ROLE task1, task1_lead, task2, task2_lead;
38 GRANT updater TO task1_lead;
39 GRANT updater TO task2_lead;
40 GRANT task1 TO task1_lead;
41 GRANT task2 TO task2_lead;
42
43 CREATE ROLE project_lead;
44 GRANT task1_lead TO project_lead;
45 GRANT task2_lead TO project_lead;
46
47 CREATE ROLE product_lead;
48 GRANT project_lead TO product_lead;
49
50 # Assigned to user and not role, in the test.
51 CREATE ROLE hr_rules, hr_access;
52 GRANT hr_rules TO hr_access WITH ADMIN OPTION;
53
54 --echo #
55 --echo # USER ACCOUNT AND ASSIGN ROLE's TO THEM.
56 --echo #
57
58 # user0 has no roles.
59 CREATE USER user0 IDENTIFIED BY 'foo';
60
61 CREATE USER user1 IDENTIFIED BY 'foo';
62 CREATE USER user2 IDENTIFIED BY 'foo';
63 GRANT reader, hr_rules TO user1;
64 GRANT updater, hr_rules TO user2;
65
66 CREATE USER lead_user1 IDENTIFIED BY 'foo';
67 CREATE USER lead_user2 IDENTIFIED BY 'foo';
68 GRANT task1_lead, hr_access TO lead_user1;
69 GRANT task2_lead, hr_access TO lead_user2;
70
71 CREATE USER project_lead_user IDENTIFIED BY 'foo';
72 GRANT project_lead, hr_access TO project_lead_user;
73
74 CREATE USER product_lead_user IDENTIFIED BY 'foo';
75 GRANT product_lead, hr_access TO product_lead_user;
76
77
78 --echo #
79 --echo # CREATE DATABASE OBJECTS
80 --echo #
81 CREATE TABLE task1_ledger1 (f1 INT, f2 INT);
82 CREATE TABLE task1_ledger2 (f1 INT, f2 INT);
83
84 CREATE TABLE task2_ledger1 (f1 INT, f2 INT);
85 CREATE TABLE task2_ledger2 (f1 INT, f2 INT);
86
87 delimiter //;
88 CREATE PROCEDURE daily_report (OUT param1 INT)
89 BEGIN
90 SELECT COUNT(*) INTO param1 FROM t1;
91 END//
92
93 CREATE PROCEDURE weekly_report (OUT param1 INT)
94 BEGIN
95 SELECT COUNT(*) INTO param1 FROM t1;
96 END//
97
98 CREATE PROCEDURE proc1 (OUT param1 INT)
99 BEGIN
100 SELECT COUNT(*) INTO param1 FROM t1;
101 END//
102 delimiter ;//
103
104 CREATE TABLE hr_ledger1 (f1 INT, f2 INT);
105 CREATE TABLE hr_ledger2 (f1 INT, f2 INT);
106
107 CREATE TABLE t1 (f1 INT);
108
109 --echo #
110 --echo # GRANTS TO ROLE
111 --echo #
112
113 # TABLE GRANT.
114 GRANT SELECT ON task1_ledger1 TO reader;
115
116 GRANT UPDATE ON task1_ledger1 TO updater;
117 GRANT UPDATE ON task1_ledger2 TO updater;
118 GRANT UPDATE ON task2_ledger1 TO updater;
119 GRANT UPDATE ON task2_ledger2 TO updater;
120
121 GRANT INSERT ON task1_ledger1 TO task1_lead;
122 GRANT INSERT ON task1_ledger2 TO task1_lead;
123 GRANT INSERT ON task2_ledger1 TO task2_lead;
124 GRANT INSERT ON task2_ledger2 TO task2_lead;
125
126 GRANT DELETE ON task2_ledger1 TO project_lead;
127 GRANT DELETE ON task2_ledger2 TO project_lead;
128
129 GRANT SELECT ON hr_ledger1 TO hr_rules;
130 GRANT SELECT ON hr_ledger2 to hr_access;
131
132 # Column grant.
133 GRANT SELECT (f2) ON task1_ledger1 to reader;
134 GRANT SELECT (f2) ON task1_ledger2 to reader WITH GRANT OPTION;
135
136 GRANT SELECT (f2) ON task2_ledger2 to product_lead;
137 GRANT SELECT (f2) ON task2_ledger2 to product_lead WITH GRANT OPTION;
138
139 GRANT SELECT (f2) ON hr_ledger1 TO hr_rules;
140 GRANT SELECT (f2) ON hr_ledger2 to hr_access WITH GRANT OPTION;
141
142 # Routine grant.
143 GRANT EXECUTE ON PROCEDURE daily_report TO task1_lead;
144 GRANT EXECUTE ON PROCEDURE weekly_report TO task2_lead;
145
146 GRANT ALTER ROUTINE ON PROCEDURE daily_report TO product_lead;
147 GRANT ALTER ROUTINE ON PROCEDURE weekly_report TO product_lead;
148
149 GRANT EXECUTE ON PROCEDURE weekly_report TO hr_rules;
150 GRANT EXECUTE ON PROCEDURE weekly_report TO hr_access;
151
152 # Grants to users are not shown in
153 # Applicable_roles, Enabled_roles, Role_*_grants I_S tables.
154 GRANT SELECT ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
155 GRANT SELECT (f1) ON t1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;
156 GRANT EXECUTE ON PROCEDURE proc1 TO user1, user2, lead_user1, lead_user2, project_lead_user, product_lead_user;