Debian Codegraph @

mysql-test/suite/roles/recursive.inc

raw
1 #
2 # This file tests how privilege are propagated through a complex role graph.
3 # Here's a graph
4 #
5 # role1 ->- role2 -->- role4 -->- role6 ->- role8
6 # \ / \
7 # \->- role5 ->-/ \->- role9 ->- role10 ->- foo@localhost
8 # / \ /
9 # role3 ->-/ \->- role7 ->-/
10 #
11 # privilege checks verify that grants/revokes are propagated correctly
12 # from the role1 to role10. additionally debug status variables verify
13 # that propagation doesn't do unnecessary work (only touches the
14 # smallest possible number of nodes and doesn't merge privileges that
15 # didn't change)
16 #
17 source include/not_embedded.inc;
18
19 create user foo@localhost;
20 grant select on test.* to foo@localhost;
21 create role role1;
22 create role role2;
23 create role role3;
24 create role role4;
25 create role role5;
26 create role role6;
27 create role role7;
28 create role role8;
29 create role role9;
30 create role role10;
31
32 grant role1 to role2;
33 grant role2 to role4;
34 grant role2 to role5;
35 grant role3 to role5;
36 grant role4 to role6;
37 grant role5 to role6;
38 grant role5 to role7;
39 grant role6 to role8;
40 grant role6 to role9;
41 grant role7 to role9;
42 grant role9 to role10;
43 grant role10 to foo@localhost;
44
45 # try to create a cycle
46 --error ER_CANNOT_GRANT_ROLE
47 grant role10 to role2;
48
49 connect (foo, localhost, foo);
50 --sorted_result
51 show grants;
52 --sorted_result
53 select * from information_schema.applicable_roles;
54
55 show status like 'debug%';
56
57 #
58 # global privileges
59 #
60 connection default;
61 grant select on *.* to role1;
62 show status like 'debug%';
63 connection foo;
64 --error ER_TABLEACCESS_DENIED_ERROR
65 select count(*) from mysql.roles_mapping;
66 set role role10;
67 select count(*) from mysql.roles_mapping;
68 --sorted_result
69 show grants;
70 --sorted_result
71 select * from information_schema.enabled_roles;
72
73 connection default;
74 revoke select on *.* from role1;
75 show status like 'debug%';
76 connection foo;
77 # global privileges are cached in the THD, changes don't take effect immediately
78 select count(*) from mysql.roles_mapping;
79 set role none;
80 set role role10;
81 --error ER_TABLEACCESS_DENIED_ERROR
82 select count(*) from mysql.roles_mapping;
83 set role none;
84
85 #
86 # database privileges
87 #
88 connection default;
89 grant select on mysql.* to role1;
90 show status like 'debug%';
91 connection foo;
92 --error ER_TABLEACCESS_DENIED_ERROR
93 select count(*) from mysql.roles_mapping;
94 set role role10;
95 select count(*) from mysql.roles_mapping;
96 --sorted_result
97 show grants;
98
99 connection default;
100 revoke select on mysql.* from role1;
101 show status like 'debug%';
102 connection foo;
103 --error ER_TABLEACCESS_DENIED_ERROR
104 select count(*) from mysql.roles_mapping;
105 set role none;
106
107 #
108 # table privileges
109 #
110
111 connection default;
112 grant select on mysql.roles_mapping to role1;
113 show status like 'debug%';
114 connection foo;
115 --error ER_TABLEACCESS_DENIED_ERROR
116 select count(*) from mysql.roles_mapping;
117 set role role10;
118 select count(*) from mysql.roles_mapping;
119 --sorted_result
120 show grants;
121
122 connection default;
123 revoke select on mysql.roles_mapping from role1;
124 show status like 'debug%';
125 connection foo;
126 --error ER_TABLEACCESS_DENIED_ERROR
127 select count(*) from mysql.roles_mapping;
128 set role none;
129
130 #
131 # column privileges
132 #
133
134 connection default;
135 grant select(User) on mysql.roles_mapping to role1;
136 show status like 'debug%';
137 connection foo;
138 --error ER_TABLEACCESS_DENIED_ERROR
139 select count(*) from mysql.roles_mapping;
140 set role role10;
141 --error ER_COLUMNACCESS_DENIED_ERROR
142 select count(concat(User,Host,Role)) from mysql.roles_mapping;
143 select count(concat(User)) from mysql.roles_mapping;
144 --sorted_result
145 show grants;
146
147 connection default;
148 grant select(Host) on mysql.roles_mapping to role3;
149 show status like 'debug%';
150 connection foo;
151 --error ER_COLUMNACCESS_DENIED_ERROR
152 select count(concat(User,Host,Role)) from mysql.roles_mapping;
153 select count(concat(User,Host)) from mysql.roles_mapping;
154 --sorted_result
155 show grants;
156
157 connection default;
158 revoke select(User) on mysql.roles_mapping from role1;
159 show status like 'debug%';
160 connection foo;
161 --error ER_COLUMNACCESS_DENIED_ERROR
162 select count(concat(User,Host)) from mysql.roles_mapping;
163 select count(concat(Host)) from mysql.roles_mapping;
164
165 connection default;
166 revoke select(Host) on mysql.roles_mapping from role3;
167 show status like 'debug%';
168 connection foo;
169 --error ER_TABLEACCESS_DENIED_ERROR
170 select count(concat(Host)) from mysql.roles_mapping;
171 set role none;
172
173 #
174 # routine privileges
175 #
176
177 connection default;
178 create procedure pr1() select "pr1";
179 create function fn1() returns char(10) return "fn1";
180 grant execute on procedure test.pr1 to role1;
181 show status like 'debug%';
182 connection foo;
183 --error ER_PROCACCESS_DENIED_ERROR
184 call pr1();
185 set role role10;
186 call pr1();
187 --error ER_PROCACCESS_DENIED_ERROR
188 select fn1();
189
190 connection default;
191 grant execute on function test.fn1 to role5;
192 show status like 'debug%';
193 connection foo;
194 select fn1();
195
196 connection default;
197 revoke execute on procedure test.pr1 from role1;
198 show status like 'debug%';
199 connection foo;
200 --error ER_PROCACCESS_DENIED_ERROR
201 call pr1();
202 select fn1();
203
204 connection default;
205 revoke execute on function test.fn1 from role5;
206 show status like 'debug%';
207 connection foo;
208 --error ER_PROCACCESS_DENIED_ERROR
209 select fn1();
210 set role none;
211
212 connection default;
213 drop procedure pr1;
214 drop function fn1;
215
216 #
217 # test shortcuts
218 #
219
220 grant select on mysql.roles_mapping to role3;
221 show status like 'debug%';
222 # this grant only propagates to roles role2 and role4,
223 # and tries to propagate to role5, discovering that it already has it
224 grant select on mysql.roles_mapping to role1;
225 show status like 'debug%';
226 # this only tries to propagate to role5 and exits early
227 revoke select on mysql.roles_mapping from role3;
228 show status like 'debug%';
229 # propagates to all 8 roles, normally
230 revoke select on mysql.roles_mapping from role1;
231 show status like 'debug%';
232
233 grant select on mysql.* to role1;
234 show status like 'debug%';
235 # only entries for `test` are merged, not for `mysql`
236 grant select on test.* to role1;
237 show status like 'debug%';
238 revoke select on mysql.* from role1;
239 show status like 'debug%';
240 revoke select on test.* from role1;
241 show status like 'debug%';
242
243 #
244 # cleanup
245 #
246
247 connection default;
248 drop user foo@localhost;
249 drop role role1;
250 drop role role2;
251 drop role role3;
252 drop role role4;
253 drop role role5;
254 drop role role6;
255 drop role role7;
256 drop role role8;
257 drop role role9;
258 drop role role10;
259