Debian Codegraph @

mysql-test/suite/roles/definer.test

raw
1 # create view
2 # create trigger
3 # create procedure
4 # create event
5 # mysqldump dumping the definer
6
7 --source include/not_embedded.inc
8 --source include/default_charset.inc
9
10 call mtr.add_suppression('Event Scheduler:');
11
12 let MYSQLD_DATADIR=`select @@datadir`;
13
14 create database mysqltest1;
15 use mysqltest1;
16
17 create table t1 (a int, b int, c int);
18 insert t1 values (1,10,100),(2,20,200);
19
20 # non-priv role granted
21 create role role1;
22 grant select (a) on mysqltest1.t1 to role1;
23 grant event,execute,trigger on mysqltest1.* to role1;
24 grant select on test.* to role1;
25
26 grant role1 to current_user;
27
28 # priv role
29 create role role2;
30 grant insert,select on mysqltest1.t1 to role2;
31 grant event,execute,trigger on mysqltest1.* to role2;
32 grant select on test.* to role2;
33
34 # create a non-priv user and a priv role granted to him
35 create user foo@localhost;
36 grant create view on mysqltest1.* to foo@localhost;
37 grant select, create view on test.* to foo@localhost;
38 create role role4;
39 grant select on mysqltest1.t1 to role4;
40 grant role4 to foo@localhost;
41 grant select on test.* to role4;
42
43 ##################################################
44 # views
45 ##################################################
46
47 # no curent role = error
48 --error ER_MALFORMED_DEFINER
49 create definer=current_role view test.v1 as select a+b,c from t1;
50
51 # definer=current_role, but it has doesn't have enough privileges
52 set role role1;
53 create definer=current_role view test.v1 as select a+b,c from t1;
54 show create view test.v1;
55 set role none;
56
57 # definer=role_name, privileges ok
58 create definer=role2 view test.v2 as select a+b,c,current_role() from t1;
59 show create view test.v2;
60
61 # definer=non_existent_role
62 create definer=role3 view test.v3 as select a+b,c from t1;
63 show create view test.v3;
64
65 connect (c1, localhost, foo,,mysqltest1);
66 connection c1;
67 show grants;
68
69 # role1 doesn't have enough privileges for v1 to work
70 --error ER_VIEW_INVALID
71 select * from test.v1;
72
73 # role2 is ok, v2 is ok
74 select * from test.v2;
75
76 # role3 is treated as a user name role3@%, doesn't exist, v3 fails
77 --error ER_ACCESS_DENIED_ERROR
78 select * from test.v3;
79
80 # fails, no SUPER - cannot specify a definer arbitrarily
81 --error ER_TABLEACCESS_DENIED_ERROR
82 create definer=role4 view test.v4 as select a+b,c from t1;
83
84 --error ER_TABLEACCESS_DENIED_ERROR
85 select * from t1;
86 set role role4;
87 select * from t1;
88
89 # can select from t1, but the view won't work, by default definer=current_user
90 create view test.v4 as select a+b,c from t1;
91
92 # now role4 is the current_role, can be specified as a definer
93 create definer=role4 view test.v5 as select a+b,c from t1;
94
95 --error ER_VIEW_INVALID
96 select * from test.v4;
97 select * from test.v5;
98 set role none;
99 --error ER_VIEW_INVALID
100 select * from test.v4;
101 select * from test.v5;
102
103 connection default;
104
105 drop role role4;
106
107 show create view test.v5;
108 --error ER_NO_SUCH_USER
109 select * from test.v5;
110
111 create user role4;
112 grant select on mysqltest1.t1 to role4;
113 show create view test.v5;
114 --error ER_NO_SUCH_USER
115 select * from test.v5;
116
117 # pretend it's an old view from before 10.0.5
118 perl;
119 local $/;
120 my $f= "$ENV{MYSQLD_DATADIR}/test/v5.frm";
121 open(F, '<', $f) or die "open(<$f): $!";
122 $_=<F>;
123 s/create-version=2/create-version=1/;
124 open(F, '>', $f) or die "open(>$f): $!";
125 syswrite F, $_ or die "syswrite($f): $!"
126 EOF
127
128 flush tables;
129 show create view test.v5;
130 select * from test.v5;
131 drop user role4;
132
133
134 ##################################################
135 # trigger
136 ##################################################
137
138 create table t2 select * from t1;
139
140 # no curent role = error
141 --error ER_MALFORMED_DEFINER
142 create definer=current_role trigger tr1 before insert on t2 for each row
143 insert t1 values (111, 222, 333);
144
145 # definer=current_role, but it has doesn't have enough privileges
146 set role role1;
147 create definer=current_role trigger tr1 before insert on t2 for each row
148 insert t1 values (111, 222, 333);
149 --replace_column 7 #
150 show create trigger tr1;
151 set role none;
152
153 --error ER_TABLEACCESS_DENIED_ERROR
154 insert t2 values (11,22,33);
155 select * from t1;
156 select * from t2;
157
158 # definer=role_name, privileges ok
159 create definer=role2 trigger tr2 before delete on t2 for each row
160 insert t1 values (111, 222, 333);
161 --replace_column 7 #
162 show create trigger tr2;
163 delete from t2 where a=1;
164 select * from t1;
165 select * from t2;
166 delete from t1 where a=111;
167
168 # definer=non_existent_role
169 create definer=role3 trigger tr3 before update on t2 for each row
170 insert t1 values (111, 222, 333);
171 --replace_column 7 #
172 show create trigger tr3;
173 --error ER_NO_SUCH_USER
174 update t2 set b=2 where a=2;
175 select * from t1;
176 select * from t2;
177
178 flush tables;
179
180 # change triggers to use pre-10.0.5 definer with an empty hostname
181 perl;
182 local $/;
183 my $f= "$ENV{MYSQLD_DATADIR}/mysqltest1/t2.TRG";
184 open(F, '<', $f) or die "open(<$f): $!";
185 $_=<F>;
186 s/'role2'/'role2\@'/;
187 s/`role2`/$&\@``/;
188 open(F, '>', $f) or die "open(>$f): $!";
189 syswrite F, $_ or die "syswrite($f): $!"
190 EOF
191
192 --replace_column 7 #
193 show create trigger tr2;
194 --error ER_NO_SUCH_USER
195 delete from t2 where a=2;
196 select * from t1;
197 select * from t2;
198
199 ##################################################
200 # stored procedures
201 ##################################################
202
203 # no curent role = error
204 --error ER_MALFORMED_DEFINER
205 create definer=current_role procedure pr1() insert t1 values (111, 222, 333);
206
207 # definer=current_role, but it has doesn't have enough privileges
208 set role role1;
209 create definer=current_role procedure pr1() insert t1 values (111, 222, 333);
210 show create procedure pr1;
211 set role none;
212
213 --error ER_TABLEACCESS_DENIED_ERROR
214 call pr1();
215 select * from t1;
216
217 # definer=role_name, privileges ok
218 create definer=role2 procedure pr2() insert t1 values (111, 222, 333);
219 show create procedure pr2;
220 call pr2();
221 select * from t1;
222 delete from t1 where a=111;
223
224 # definer=non_existent_role
225 create definer=role3 procedure pr3() insert t1 values (111, 222, 333);
226 show create procedure pr3;
227 --error ER_NO_SUCH_USER
228 call pr3();
229 select * from t1;
230
231 # change a procedure to use pre-10.0.5 definer with an empty hostname
232 update mysql.proc set definer='role2@' where definer='role2';
233 --error ER_NO_SUCH_USER
234 call pr2();
235
236 ##################################################
237 # stored functions
238 ##################################################
239
240 # no curent role = error
241 --error ER_MALFORMED_DEFINER
242 create definer=current_role function fn1() returns int return (select sum(a+b) from t1);
243
244 # definer=current_role, but it has doesn't have enough privileges
245 set role role1;
246 create definer=current_role function fn1() returns int return (select sum(a+b) from t1);
247 show create function fn1;
248 set role none;
249
250 --error ER_COLUMNACCESS_DENIED_ERROR
251 select fn1();
252 select * from t1;
253
254 # definer=role_name, privileges ok
255 create definer=role2 function fn2() returns int return (select sum(a+b) from t1);
256 show create function fn2;
257 select fn2();
258
259 # definer=non_existent_role
260 create definer=role3 function fn3() returns int return (select sum(a+b) from t1);
261 show create function fn3;
262 --error ER_NO_SUCH_USER
263 select fn3();
264
265 ##################################################
266 # events
267 ##################################################
268
269 set global event_scheduler=on;
270
271 # no curent role = error
272 --error ER_MALFORMED_DEFINER
273 create definer=current_role event e1 on schedule every 1 second starts '2000-01-01' do
274 insert t1 values (111, 1, 0);
275
276 # definer=current_role, but it has doesn't have enough privileges
277 set role role1;
278 create definer=current_role event e1 on schedule every 1 second starts '2000-01-01' do
279 insert t1 values (111, 2, 0);
280 show create event e1;
281 set role none;
282
283 # definer=non_existent_role
284 create definer=role3 event e3 on schedule every 1 second starts '2000-01-01' do
285 insert t1 values (111, 3, 0);
286 show create event e3;
287
288 # definer=role_name, privileges ok
289 create definer=role2 event e2 on schedule every 1 second starts '2000-01-01' do
290 insert t1 values (111, 4, 0);
291 show create event e2;
292
293 let $wait_condition=select count(*) >= 4 from t1;
294 --source include/wait_condition.inc
295
296 set global event_scheduler=off;
297
298 --sorted_result
299 select distinct * from t1;
300 delete from t1 where a=111;
301
302 ##################################################
303 # mysqldump
304 ##################################################
305
306 # note that LOCK TABLES won't work because v3 has invalid definer
307
308 --exec $MYSQL_DUMP --no-autocommit=0 --compact --events --routines --skip-lock-tables --databases test mysqltest1
309
310 ##################################################
311 # cleanup
312 ##################################################
313
314 drop trigger tr1;
315 drop trigger tr2;
316 drop trigger tr3;
317 drop procedure pr1;
318 drop procedure pr2;
319 drop procedure pr3;
320 drop function fn1;
321 drop function fn2;
322 drop function fn3;
323 drop event e1;
324 drop event e2;
325 drop event e3;
326 drop view test.v1, test.v2, test.v3, test.v4, test.v5;
327 drop table t1, t2;
328 drop role role1, role2;
329 drop user foo@localhost;
330 drop database mysqltest1;
331 use test;
332
333 ##################################################
334 # reexecution
335 ##################################################
336
337 create user utest;
338 prepare stmt1 from 'grant select on *.* to utest';
339 execute stmt1;
340 show grants for utest;
341 drop user utest;
342 create role utest;
343 execute stmt1;
344 show grants for utest;
345 drop role utest;
346
347 --echo #
348 --echo # MDEV-13676: Field "create Procedure" is NULL, even if the the user
349 --echo # has role which is the definer. (SHOW CREATE PROCEDURE)
350 --echo #
351
352 create database rtest;
353 create role r1;
354 create role r2;
355 create role r3;
356 grant all privileges on rtest.* to r1;
357
358 create user user1;
359 grant r1 to user1;
360 grant r1 to r2;
361 grant r2 to user1;
362 grant r3 to user1;
363
364 connect (user1, localhost,user1,,"*NO-ONE*",,,);
365 set role r2;
366 use rtest;
367
368 DELIMITER //;
369 CREATE DEFINER=current_role() PROCEDURE user1_proc() SQL SECURITY INVOKER
370 BEGIN
371 SELECT NOW(), VERSION();
372 END;//
373 DELIMITER ;//
374
375 set role r2;
376 show create procedure user1_proc;
377
378 --echo #
379 --echo # Currently one can not use as definer any role except CURRENT_ROLE
380 --echo #
381 DELIMITER //;
382 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
383 CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER
384 BEGIN
385 SELECT NOW(), VERSION();
386 END;//
387 DELIMITER ;//
388
389 set role r1;
390 DELIMITER //;
391 CREATE DEFINER='r1' PROCEDURE user1_proc2() SQL SECURITY INVOKER
392 BEGIN
393 SELECT NOW(), VERSION();
394 END;//
395 DELIMITER ;//
396
397 show create procedure user1_proc2;
398 --echo #
399 --echo # Test to see if the user can still see the procedure code if the
400 --echo # role that owns it is granted to him indirectly.
401 --echo #
402 set role r2;
403 show create procedure user1_proc2;
404
405 --echo #
406 --echo # One should not be able to see the procedure code if the role that owns
407 --echo # the procedure is not set by the user or is not in the subgraph of the
408 --echo # currently active role.
409 --echo #
410 set role r3;
411 --error ER_SP_DOES_NOT_EXIST
412 show create procedure user1_proc2;
413
414 connection default;
415
416 use rtest;
417
418 --echo #
419 --echo # Try a few edge cases, with usernames identical to role name;
420 --echo #
421
422 create user user_like_role;
423 create user foo;
424 create role user_like_role;
425 grant select on rtest.* to user_like_role;
426 grant select on rtest.* to foo;
427 grant select on rtest.* to user_like_role@'%';
428
429 grant user_like_role to foo;
430
431 --echo #
432 --echo # Here we have a procedure that is owned by user_like_role USER
433 --echo # We don't want user_like_role ROLE to have access to its code.
434 --echo #
435 DELIMITER //;
436 CREATE DEFINER=`user_like_role`@`%` PROCEDURE sensitive_proc() SQL SECURITY INVOKER
437 BEGIN
438 SELECT NOW(), VERSION();
439 END;//
440 DELIMITER ;//
441
442 connect (user_like_role, localhost, user_like_role,,"*NO-ONE*",,,);
443 use rtest;
444 show create procedure sensitive_proc;
445
446 connect (foo, localhost, foo,,"*NO-ONE*",,,);
447 set role user_like_role;
448 use rtest;
449
450 --echo #
451 --echo # Foo has the set rolename identical to the procedure's definer's username.
452 --echo # Foo should not have access to this procedure.
453 --echo #
454 --error ER_SP_DOES_NOT_EXIST
455 show create procedure sensitive_proc;
456
457 connection default;
458 drop role r1;
459 drop role r2;
460 drop role r3;
461 drop role user_like_role;
462 drop user user1;
463 drop user foo;
464 drop user user_like_role;
465 drop procedure user1_proc;
466 drop procedure user1_proc2;
467 drop procedure sensitive_proc;
468 drop database rtest;