Debian Codegraph @

mysql-test/suite/roles/recursive.result

raw
1 create user foo@localhost;
2 grant select on test.* to foo@localhost;
3 create role role1;
4 create role role2;
5 create role role3;
6 create role role4;
7 create role role5;
8 create role role6;
9 create role role7;
10 create role role8;
11 create role role9;
12 create role role10;
13 grant role1 to role2;
14 grant role2 to role4;
15 grant role2 to role5;
16 grant role3 to role5;
17 grant role4 to role6;
18 grant role5 to role6;
19 grant role5 to role7;
20 grant role6 to role8;
21 grant role6 to role9;
22 grant role7 to role9;
23 grant role9 to role10;
24 grant role10 to foo@localhost;
25 grant role10 to role2;
26 ERROR HY000: Cannot grant role 'role10' to: 'role2'
27 connect foo, localhost, foo;
28 show grants;
29 Grants for foo@localhost
30 GRANT SELECT ON `test`.* TO `foo`@`localhost`
31 GRANT USAGE ON *.* TO `foo`@`localhost`
32 GRANT `role10` TO `foo`@`localhost`
33 select * from information_schema.applicable_roles;
34 GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT
35 foo@localhost role10 NO NO
36 role10 role9 NO NULL
37 role2 role1 NO NULL
38 role4 role2 NO NULL
39 role5 role2 NO NULL
40 role5 role3 NO NULL
41 role6 role4 NO NULL
42 role6 role5 NO NULL
43 role7 role5 NO NULL
44 role9 role6 NO NULL
45 role9 role7 NO NULL
46 show status like 'debug%';
47 Variable_name Value
48 connection default;
49 grant select on *.* to role1;
50 show status like 'debug%';
51 Variable_name Value
52 connection foo;
53 select count(*) from mysql.roles_mapping;
54 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
55 set role role10;
56 select count(*) from mysql.roles_mapping;
57 count(*)
58 22
59 show grants;
60 Grants for foo@localhost
61 GRANT SELECT ON *.* TO `role1`
62 GRANT SELECT ON `test`.* TO `foo`@`localhost`
63 GRANT USAGE ON *.* TO `foo`@`localhost`
64 GRANT USAGE ON *.* TO `role10`
65 GRANT USAGE ON *.* TO `role2`
66 GRANT USAGE ON *.* TO `role3`
67 GRANT USAGE ON *.* TO `role4`
68 GRANT USAGE ON *.* TO `role5`
69 GRANT USAGE ON *.* TO `role6`
70 GRANT USAGE ON *.* TO `role7`
71 GRANT USAGE ON *.* TO `role9`
72 GRANT `role10` TO `foo`@`localhost`
73 GRANT `role1` TO `role2`
74 GRANT `role2` TO `role4`
75 GRANT `role2` TO `role5`
76 GRANT `role3` TO `role5`
77 GRANT `role4` TO `role6`
78 GRANT `role5` TO `role6`
79 GRANT `role5` TO `role7`
80 GRANT `role6` TO `role9`
81 GRANT `role7` TO `role9`
82 GRANT `role9` TO `role10`
83 select * from information_schema.enabled_roles;
84 ROLE_NAME
85 role1
86 role10
87 role2
88 role3
89 role4
90 role5
91 role6
92 role7
93 role9
94 connection default;
95 revoke select on *.* from role1;
96 show status like 'debug%';
97 Variable_name Value
98 connection foo;
99 select count(*) from mysql.roles_mapping;
100 count(*)
101 22
102 set role none;
103 set role role10;
104 select count(*) from mysql.roles_mapping;
105 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
106 set role none;
107 connection default;
108 grant select on mysql.* to role1;
109 show status like 'debug%';
110 Variable_name Value
111 connection foo;
112 select count(*) from mysql.roles_mapping;
113 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
114 set role role10;
115 select count(*) from mysql.roles_mapping;
116 count(*)
117 22
118 show grants;
119 Grants for foo@localhost
120 GRANT SELECT ON `mysql`.* TO `role1`
121 GRANT SELECT ON `test`.* TO `foo`@`localhost`
122 GRANT USAGE ON *.* TO `foo`@`localhost`
123 GRANT USAGE ON *.* TO `role10`
124 GRANT USAGE ON *.* TO `role1`
125 GRANT USAGE ON *.* TO `role2`
126 GRANT USAGE ON *.* TO `role3`
127 GRANT USAGE ON *.* TO `role4`
128 GRANT USAGE ON *.* TO `role5`
129 GRANT USAGE ON *.* TO `role6`
130 GRANT USAGE ON *.* TO `role7`
131 GRANT USAGE ON *.* TO `role9`
132 GRANT `role10` TO `foo`@`localhost`
133 GRANT `role1` TO `role2`
134 GRANT `role2` TO `role4`
135 GRANT `role2` TO `role5`
136 GRANT `role3` TO `role5`
137 GRANT `role4` TO `role6`
138 GRANT `role5` TO `role6`
139 GRANT `role5` TO `role7`
140 GRANT `role6` TO `role9`
141 GRANT `role7` TO `role9`
142 GRANT `role9` TO `role10`
143 connection default;
144 revoke select on mysql.* from role1;
145 show status like 'debug%';
146 Variable_name Value
147 connection foo;
148 select count(*) from mysql.roles_mapping;
149 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
150 set role none;
151 connection default;
152 grant select on mysql.roles_mapping to role1;
153 show status like 'debug%';
154 Variable_name Value
155 connection foo;
156 select count(*) from mysql.roles_mapping;
157 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
158 set role role10;
159 select count(*) from mysql.roles_mapping;
160 count(*)
161 22
162 show grants;
163 Grants for foo@localhost
164 GRANT SELECT ON `mysql`.`roles_mapping` TO `role1`
165 GRANT SELECT ON `test`.* TO `foo`@`localhost`
166 GRANT USAGE ON *.* TO `foo`@`localhost`
167 GRANT USAGE ON *.* TO `role10`
168 GRANT USAGE ON *.* TO `role1`
169 GRANT USAGE ON *.* TO `role2`
170 GRANT USAGE ON *.* TO `role3`
171 GRANT USAGE ON *.* TO `role4`
172 GRANT USAGE ON *.* TO `role5`
173 GRANT USAGE ON *.* TO `role6`
174 GRANT USAGE ON *.* TO `role7`
175 GRANT USAGE ON *.* TO `role9`
176 GRANT `role10` TO `foo`@`localhost`
177 GRANT `role1` TO `role2`
178 GRANT `role2` TO `role4`
179 GRANT `role2` TO `role5`
180 GRANT `role3` TO `role5`
181 GRANT `role4` TO `role6`
182 GRANT `role5` TO `role6`
183 GRANT `role5` TO `role7`
184 GRANT `role6` TO `role9`
185 GRANT `role7` TO `role9`
186 GRANT `role9` TO `role10`
187 connection default;
188 revoke select on mysql.roles_mapping from role1;
189 show status like 'debug%';
190 Variable_name Value
191 connection foo;
192 select count(*) from mysql.roles_mapping;
193 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
194 set role none;
195 connection default;
196 grant select(User) on mysql.roles_mapping to role1;
197 show status like 'debug%';
198 Variable_name Value
199 connection foo;
200 select count(*) from mysql.roles_mapping;
201 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
202 set role role10;
203 select count(concat(User,Host,Role)) from mysql.roles_mapping;
204 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'Host' in table 'roles_mapping'
205 select count(concat(User)) from mysql.roles_mapping;
206 count(concat(User))
207 22
208 show grants;
209 Grants for foo@localhost
210 GRANT SELECT (`User`) ON `mysql`.`roles_mapping` TO `role1`
211 GRANT SELECT ON `test`.* TO `foo`@`localhost`
212 GRANT USAGE ON *.* TO `foo`@`localhost`
213 GRANT USAGE ON *.* TO `role10`
214 GRANT USAGE ON *.* TO `role1`
215 GRANT USAGE ON *.* TO `role2`
216 GRANT USAGE ON *.* TO `role3`
217 GRANT USAGE ON *.* TO `role4`
218 GRANT USAGE ON *.* TO `role5`
219 GRANT USAGE ON *.* TO `role6`
220 GRANT USAGE ON *.* TO `role7`
221 GRANT USAGE ON *.* TO `role9`
222 GRANT `role10` TO `foo`@`localhost`
223 GRANT `role1` TO `role2`
224 GRANT `role2` TO `role4`
225 GRANT `role2` TO `role5`
226 GRANT `role3` TO `role5`
227 GRANT `role4` TO `role6`
228 GRANT `role5` TO `role6`
229 GRANT `role5` TO `role7`
230 GRANT `role6` TO `role9`
231 GRANT `role7` TO `role9`
232 GRANT `role9` TO `role10`
233 connection default;
234 grant select(Host) on mysql.roles_mapping to role3;
235 show status like 'debug%';
236 Variable_name Value
237 connection foo;
238 select count(concat(User,Host,Role)) from mysql.roles_mapping;
239 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'Role' in table 'roles_mapping'
240 select count(concat(User,Host)) from mysql.roles_mapping;
241 count(concat(User,Host))
242 22
243 show grants;
244 Grants for foo@localhost
245 GRANT SELECT (`Host`) ON `mysql`.`roles_mapping` TO `role3`
246 GRANT SELECT (`User`) ON `mysql`.`roles_mapping` TO `role1`
247 GRANT SELECT ON `test`.* TO `foo`@`localhost`
248 GRANT USAGE ON *.* TO `foo`@`localhost`
249 GRANT USAGE ON *.* TO `role10`
250 GRANT USAGE ON *.* TO `role1`
251 GRANT USAGE ON *.* TO `role2`
252 GRANT USAGE ON *.* TO `role3`
253 GRANT USAGE ON *.* TO `role4`
254 GRANT USAGE ON *.* TO `role5`
255 GRANT USAGE ON *.* TO `role6`
256 GRANT USAGE ON *.* TO `role7`
257 GRANT USAGE ON *.* TO `role9`
258 GRANT `role10` TO `foo`@`localhost`
259 GRANT `role1` TO `role2`
260 GRANT `role2` TO `role4`
261 GRANT `role2` TO `role5`
262 GRANT `role3` TO `role5`
263 GRANT `role4` TO `role6`
264 GRANT `role5` TO `role6`
265 GRANT `role5` TO `role7`
266 GRANT `role6` TO `role9`
267 GRANT `role7` TO `role9`
268 GRANT `role9` TO `role10`
269 connection default;
270 revoke select(User) on mysql.roles_mapping from role1;
271 show status like 'debug%';
272 Variable_name Value
273 connection foo;
274 select count(concat(User,Host)) from mysql.roles_mapping;
275 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'User' in table 'roles_mapping'
276 select count(concat(Host)) from mysql.roles_mapping;
277 count(concat(Host))
278 22
279 connection default;
280 revoke select(Host) on mysql.roles_mapping from role3;
281 show status like 'debug%';
282 Variable_name Value
283 connection foo;
284 select count(concat(Host)) from mysql.roles_mapping;
285 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
286 set role none;
287 connection default;
288 create procedure pr1() select "pr1";
289 create function fn1() returns char(10) return "fn1";
290 grant execute on procedure test.pr1 to role1;
291 show status like 'debug%';
292 Variable_name Value
293 connection foo;
294 call pr1();
295 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.pr1'
296 set role role10;
297 call pr1();
298 pr1
299 pr1
300 select fn1();
301 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.fn1'
302 connection default;
303 grant execute on function test.fn1 to role5;
304 show status like 'debug%';
305 Variable_name Value
306 connection foo;
307 select fn1();
308 fn1()
309 fn1
310 connection default;
311 revoke execute on procedure test.pr1 from role1;
312 show status like 'debug%';
313 Variable_name Value
314 connection foo;
315 call pr1();
316 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.pr1'
317 select fn1();
318 fn1()
319 fn1
320 connection default;
321 revoke execute on function test.fn1 from role5;
322 show status like 'debug%';
323 Variable_name Value
324 connection foo;
325 select fn1();
326 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.fn1'
327 set role none;
328 connection default;
329 drop procedure pr1;
330 drop function fn1;
331 grant select on mysql.roles_mapping to role3;
332 show status like 'debug%';
333 Variable_name Value
334 grant select on mysql.roles_mapping to role1;
335 show status like 'debug%';
336 Variable_name Value
337 revoke select on mysql.roles_mapping from role3;
338 show status like 'debug%';
339 Variable_name Value
340 revoke select on mysql.roles_mapping from role1;
341 show status like 'debug%';
342 Variable_name Value
343 grant select on mysql.* to role1;
344 show status like 'debug%';
345 Variable_name Value
346 grant select on test.* to role1;
347 show status like 'debug%';
348 Variable_name Value
349 revoke select on mysql.* from role1;
350 show status like 'debug%';
351 Variable_name Value
352 revoke select on test.* from role1;
353 show status like 'debug%';
354 Variable_name Value
355 connection default;
356 drop user foo@localhost;
357 drop role role1;
358 drop role role2;
359 drop role role3;
360 drop role role4;
361 drop role role5;
362 drop role role6;
363 drop role role7;
364 drop role role8;
365 drop role role9;
366 drop role role10;