Debian Codegraph @

mysql-test/suite/opt_trace/include/security.inc

raw
1 # Test that trace does not show information forbidden
2 # by lack of privileges.
3
4 --source include/have_optimizer_trace.inc
5 connection default;
6 let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
7 set @old_size = @@global.optimizer_trace_max_mem_size;
8 eval set global optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
9
10 connection default;
11 select user();
12 create database somedb;
13 use somedb;
14 create table t1(a varchar(100));
15 insert into t1 values("first");
16 create table t2(a varchar(100));
17 insert into t2 values("first");
18 create table t3(a varchar(100));
19 insert into t3 values("first");
20 SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
21 delimiter |;
22 create procedure p1() sql security definer
23 begin
24 declare b int;
25 if (select count(*) from t1)
26 then
27 select 22 into b from dual;
28 end if;
29 select a into b from t1 limit 1;
30 insert into t1 values(current_user());
31 end|
32 create function f1() returns int sql security definer
33 begin
34 declare b int;
35 select 48 into b from dual;
36 select a into b from t1 limit 1;
37 insert into t1 values(current_user());
38 return 36;
39 end|
40 create trigger trg2 before insert on t2 for each row
41 begin
42 insert into t3 select * from t3;
43 end|
44 delimiter ;|
45 SET sql_mode = default;
46 create sql security definer view v1 as select * from t1;
47 create user user1@localhost identified by '';
48 grant all on *.* to user1@localhost with grant option;
49 connect (con_user1, localhost, user1,, somedb);
50
51 --echo
52 connection con_user1;
53 select user();
54 set optimizer_trace="enabled=on";
55 # SHOW GRANTS scans a hash, which gives a random order
56 --sorted_result
57 show grants;
58
59 --echo
60 --echo # ==========================================================
61 --echo # Part A.
62 --echo # Test that security context changes are allowed when, and only
63 --echo # when, invoker has all global privileges.
64 --echo # ==========================================================
65 --echo
66
67 --echo # Because invoker has all global privileges, all traces are visible:
68 set optimizer_trace_offset=0,optimizer_trace_limit=100;
69 call p1();
70 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
71 --echo # this SET always purges all remembered traces
72 set optimizer_trace_offset=0,optimizer_trace_limit=100;
73 select f1();
74 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
75 set optimizer_trace_offset=0,optimizer_trace_limit=100;
76 select * from v1;
77 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
78 set optimizer_trace_offset=0,optimizer_trace_limit=100;
79 insert into t2 values(current_user());
80 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
81 --echo
82 --echo # Show that really all global privileges are needed: let root
83 --echo # revoke just one from user1. Because user1 does not have all global
84 --echo # privileges anymore, security context changes are forbidden,
85 --echo # thus there is no trace.
86 --echo
87
88 connection default;
89 select user();
90 revoke shutdown on *.* from user1@localhost;
91 # removing a global privilege never affects an existing connection:
92 disconnect con_user1;
93 connect (con_user1, localhost, user1,, somedb);
94
95 --echo
96 connection con_user1;
97 select user();
98 set optimizer_trace="enabled=on";
99 --sorted_result
100 show grants;
101
102 set optimizer_trace_offset=0,optimizer_trace_limit=100;
103 call p1();
104 --echo # In CALL we execute stored procedure and notice a security
105 --echo # context change. The context change is probably only relevant
106 --echo # for substatements, but we still hide CALL. This is to be
107 --echo # consistent with what we do when routine body should not be
108 --echo # exposed. And it also feels safer to disable I_S output as
109 --echo # soon as possible.
110 --echo # Ps-protocol-specific note: mysqltest uses normal protocol for CALL
111 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
112 set optimizer_trace_offset=0,optimizer_trace_limit=100;
113 select f1();
114 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
115 set optimizer_trace_offset=0,optimizer_trace_limit=100;
116 select * from v1;
117 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
118 set optimizer_trace_offset=0,optimizer_trace_limit=100;
119 insert into t2 values(current_user());
120 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
121
122 --echo
123 --echo # Verify that user1 cannot circumvent security checks by
124 --echo # setting @@optimizer_trace_offset so that I_S output is disabled
125 --echo # before the object (routine) is checked, and enabled in the
126 --echo # middle of object usage, when 'offset' is passed.
127 --echo
128
129 set optimizer_trace_offset=2,optimizer_trace_limit=1;
130 call p1();
131 --echo # Even though the routine's execution started before
132 --echo # 'offset', it detected the security context changes. So the
133 --echo # trace of CALL gets the "missing privilege" mark but we don't
134 --echo # see it as CALL was before 'offset'.
135 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
136
137 --echo
138 --echo # Finally, verify that if the routine's definer does modify
139 --echo # @@optimizer_trace from "enabled=off" to "enabled=on", in the
140 --echo # body of the routine, then tracing works. This is no security
141 --echo # issue, as it was done by the routine's definer.
142 --echo
143
144 connection default;
145 select user();
146 delimiter |;
147 create procedure p2() sql security definer
148 begin
149 declare b int;
150 set optimizer_trace="enabled=on";
151 select 22 into b from dual;
152 end|
153 delimiter ;|
154
155 --echo
156 connection con_user1;
157 select user();
158
159 set optimizer_trace="enabled=off";
160 set optimizer_trace_offset=0,optimizer_trace_limit=100;
161 call p2();
162 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
163 --echo # Variable is as set by the routine
164 select @@optimizer_trace;
165
166 --echo
167 --echo # ==========================================================
168 --echo # Part B.
169 --echo # Do same tests but with SQL SECURITY INVOKER objects, to verify that
170 --echo # the restriction on security context changes is not present.
171 --echo # ==========================================================
172 --echo
173
174 connection default;
175 select user();
176 alter procedure p1 sql security invoker;
177 alter function f1 sql security invoker;
178 alter sql security invoker view v1 as select * from t1;
179 --echo # Triggers cannot be SQL SECURITY INVOKER so we don't test
180 --echo # them here.
181 alter procedure p2 sql security invoker;
182 delete from t1 where a<>"first";
183
184 --echo
185 connection con_user1;
186 select user();
187
188 set optimizer_trace_offset=0,optimizer_trace_limit=100;
189 call p1();
190 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
191 set optimizer_trace_offset=0,optimizer_trace_limit=100;
192 select f1();
193 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
194 set optimizer_trace_offset=0,optimizer_trace_limit=100;
195 select * from v1;
196 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
197 set optimizer_trace_offset=2,optimizer_trace_limit=1;
198 call p1();
199 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
200 set optimizer_trace="enabled=off";
201 set optimizer_trace_offset=0,optimizer_trace_limit=100;
202 call p2();
203 --echo # SELECT substatement is traced (no security context change)
204 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
205 select @@optimizer_trace;
206
207 --echo
208 --echo # ==========================================================
209 --echo # Part C.
210 --echo # User1 got traces. Determine the minimum set of privileges he
211 --echo # needed for that.
212 --echo # ==========================================================
213 --echo
214
215 connection default;
216 drop procedure p2; # p2 is not worth testing more
217 select user();
218 revoke all privileges, grant option from user1@localhost;
219 --echo # Grant minimum privileges to use the routines and views,
220 --echo # without considering optimizer trace:
221 grant execute on procedure p1 to user1@localhost;
222 grant execute on function f1 to user1@localhost;
223 grant select (a) on v1 to user1@localhost;
224 --echo # Objects above are SQL SECURITY INVOKER, so invoker needs
225 --echo # privileges on objects used internally:
226 grant select (a) on t1 to user1@localhost;
227 grant insert (a) on t1 to user1@localhost;
228 delete from t1 where a<>"first";
229 disconnect con_user1;
230 connect (con_user1, localhost, user1,, somedb);
231
232 --echo
233 connection con_user1;
234 select user();
235 set optimizer_trace="enabled=on";
236 --sorted_result
237 show grants;
238
239 --echo
240 --echo # Those privileges are not enough to see traces:
241 set optimizer_trace_offset=0,optimizer_trace_limit=100;
242 call p1();
243 --echo # In CALL we execute stored procedure and notice that body should
244 --echo # not be exposed. The trace of this CALL would not expose the
245 --echo # body. Trace of substatements would. But, due to
246 --echo # implementation, CALL is hidden.
247 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
248 set optimizer_trace_offset=0,optimizer_trace_limit=100;
249 select f1();
250 --echo # SELECT is hidden (same reason as for CALL).
251 --echo # Ps-protocol-specific note: preparation of SELECT above does not
252 --echo # execute f1, so does not risk exposing body, so its trace is
253 --echo # visible.
254 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
255 set optimizer_trace_offset=0,optimizer_trace_limit=100;
256 select * from v1;
257 --echo # Cannot see anything as it would expose body of view
258 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
259
260 --echo
261 --echo # C.0) Add more privileges:
262 --echo
263
264 connection default;
265 select user();
266 --echo # - for use of t1 in routines and view:
267 grant select on t1 to user1@localhost;
268 --echo # - for use of view:
269 grant select, show view on v1 to user1@localhost;
270 delete from t1 where a<>"first";
271
272 --echo
273 connection con_user1;
274 select user();
275
276 set optimizer_trace_offset=0,optimizer_trace_limit=100;
277 call p1();
278 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
279 --echo # Trace exposed body of routine, and content of t1, which we
280 --echo # could see anyway:
281 show create procedure p1;
282 select * from t1 limit 1;
283 set optimizer_trace_offset=0,optimizer_trace_limit=100;
284 select f1();
285 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
286 --echo # Trace exposed body of routine, and content of t1, which we
287 --echo # could see anyway:
288 show create function f1;
289 set optimizer_trace_offset=0,optimizer_trace_limit=100;
290 select * from v1;
291 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
292 --echo # Trace exposed body of view, and content of t1, which we
293 --echo # could see anyway:
294 show create view v1;
295
296 --echo
297 --echo # Now remove each privilege to verify that it was needed:
298 --echo # C.1) remove table-level SELECT privilege on t1
299 --echo
300 connection default;
301 select user();
302 revoke select on t1 from user1@localhost;
303 grant select (a) on t1 to user1@localhost;
304 delete from t1 where a<>"first";
305
306 --echo
307 connection con_user1;
308 select user();
309
310 set optimizer_trace_offset=0,optimizer_trace_limit=100;
311 call p1();
312 --echo # Cannot see those substatements which use t1
313 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
314 set optimizer_trace_offset=0,optimizer_trace_limit=100;
315 select f1();
316 --echo # Cannot see those substatements which use t1
317 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
318 --echo # Trace exposed body of routine, which we could see anyway:
319 set optimizer_trace="enabled=off";
320 show create function f1;
321 set optimizer_trace="enabled=on";
322 set optimizer_trace_offset=0,optimizer_trace_limit=100;
323 select * from v1;
324 --echo # Cannot see anything as it might expose some data from columns
325 --echo # of t1
326 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
327
328 --echo
329 --echo # C.2) remove table-level SELECT privilege on view
330 --echo
331
332 connection default;
333 select user();
334 --echo # Put back privilege removed in C.1
335 grant select on t1 to user1@localhost;
336 --echo # And remove a next one:
337 revoke select on v1 from user1@localhost;
338 grant select (a) on v1 to user1@localhost;
339 delete from t1 where a<>"first";
340
341 --echo
342 connection con_user1;
343 select user();
344
345 set optimizer_trace_offset=0,optimizer_trace_limit=100;
346 select * from v1;
347 --echo # Cannot see anything as it might expose some data from columns
348 --echo # of v1
349 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
350
351 --echo
352 --echo # C.3) remove SHOW VIEW privilege on view
353 --echo
354
355 connection default;
356 select user();
357 --echo # Put back privilege removed in C.3
358 grant select on v1 to user1@localhost;
359 --echo # And remove a next one:
360 revoke show view on v1 from user1@localhost;
361 delete from t1 where a<>"first";
362
363 --echo
364 connection con_user1;
365 select user();
366
367 set optimizer_trace="enabled=off";
368 --echo # We have no right to see view's body:
369 --error ER_TABLEACCESS_DENIED_ERROR
370 show create view v1;
371 set optimizer_trace="enabled=on";
372 --echo # Verify that optimizer trace does not influence the privilege
373 --echo # checking in SHOW CREATE:
374 --error ER_TABLEACCESS_DENIED_ERROR
375 show create view v1;
376
377 set optimizer_trace_offset=0,optimizer_trace_limit=100;
378 select * from v1;
379 --echo # Cannot see anything as it would expose body of view
380 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
381
382 --echo
383 --echo # ==========================================================
384 --echo # Part D.
385 --echo # Like Part C, but instead of SQL SECURITY INVOKER objects
386 --echo # created by root and used by User1, let's have SQL SECURITY
387 --echo # DEFINER objects created and used by User1. Determine the
388 --echo # minimum set of privileges he needs for that.
389 --echo # ==========================================================
390 --echo
391
392 connection default;
393 select user();
394 drop procedure p1;
395 drop function f1;
396 drop view v1;
397 drop trigger trg2;
398 revoke all privileges, grant option from user1@localhost;
399 --echo # Grant minimum privileges to create and use objects,
400 --echo # without considering optimizer trace:
401 grant create routine on somedb.* to user1@localhost;
402 grant trigger on t2 to user1@localhost;
403 grant create view on somedb.* to user1@localhost;
404 grant select (a) on t1 to user1@localhost;
405 grant insert (a) on t1 to user1@localhost;
406 grant insert (a) on t2 to user1@localhost;
407 grant select (a) on t3 to user1@localhost;
408 grant insert (a) on t3 to user1@localhost;
409 delete from t1 where a<>"first";
410 disconnect con_user1;
411 connect (con_user1, localhost, user1,, somedb);
412
413 --echo
414 connection con_user1;
415 select user();
416 set optimizer_trace="enabled=on";
417 SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
418 delimiter |;
419 create procedure p1() sql security definer
420 begin
421 declare b int;
422 if (select count(*) from t1)
423 then
424 select 22 into b from dual;
425 end if;
426 select a into b from t1 limit 1;
427 insert into t1 values(current_user());
428 end|
429 create function f1() returns int sql security definer
430 begin
431 declare b int;
432 select 48 into b from dual;
433 select a into b from t1 limit 1;
434 insert into t1 values(current_user());
435 return 36;
436 end|
437 create trigger trg2 before insert on t2 for each row
438 begin
439 insert into t3 select * from t3;
440 end|
441 delimiter ;|
442 create sql security definer view v1 as select * from t1;
443 SET sql_mode = default;
444 --echo # Creating a view is not enough to be able to SELECT it...
445 connection default;
446 select user();
447 grant select (a) on v1 to user1@localhost;
448
449 --echo
450 connection con_user1;
451 select user();
452
453 --echo # Those privileges are not enough to see traces:
454 set optimizer_trace_offset=0,optimizer_trace_limit=100;
455 call p1();
456 --echo # Can see body of routine (as definer), but not statements using t1
457 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
458 set optimizer_trace_offset=0,optimizer_trace_limit=100;
459 select f1();
460 --echo # Can see body of routine (as definer), but not statements using t1
461 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
462 show create function f1;
463 set optimizer_trace_offset=0,optimizer_trace_limit=100;
464 select * from v1;
465 --echo # Cannot see anything as it might expose some data from columns
466 --echo # of t1
467 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
468 set optimizer_trace_offset=0,optimizer_trace_limit=100;
469 insert into t2 values(current_user());
470 --echo # Cannot see anything as it might expose some data from
471 --echo # columns of t2
472 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
473 --echo # Also test a query accessing t1 in FROM clause:
474 set optimizer_trace_offset=0,optimizer_trace_limit=100;
475 select a from (select a from t1 where a like "f%") as tt where a like "fi%";
476 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
477
478 --echo
479 --echo # D.0) Add more privileges:
480 --echo
481
482 connection default;
483 select user();
484 --echo # - for use of t1 in routines and view:
485 grant select on t1 to user1@localhost;
486 --echo # - for use of view:
487 grant select, show view on v1 to user1@localhost;
488 --echo # - for use of trigger
489 grant select on t2 to user1@localhost;
490 grant select on t3 to user1@localhost;
491 delete from t1 where a<>"first";
492
493 --echo
494 connection con_user1;
495 select user();
496
497 set optimizer_trace_offset=0,optimizer_trace_limit=100;
498 call p1();
499 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
500 --echo # Trace exposed body of routine, and content of t1, which we
501 --echo # could see anyway:
502 show create procedure p1;
503 select * from t1 limit 1;
504 set optimizer_trace_offset=0,optimizer_trace_limit=100;
505 select f1();
506 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
507 --echo # Trace exposed body of routine, and content of t1, which we
508 --echo # could see anyway:
509 show create function f1;
510 set optimizer_trace_offset=0,optimizer_trace_limit=100;
511 select * from v1;
512 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
513 --echo # Trace exposed body of view, and content of t1, which we
514 --echo # could see anyway:
515 show create view v1;
516 set optimizer_trace_offset=0,optimizer_trace_limit=100;
517 insert into t2 values(current_user());
518 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
519 --echo # Trace exposed body of trigger, and content of t2/t3, which we
520 --echo # could see anyway:
521 --replace_column 7 #
522 show create trigger trg2;
523 select * from t2, t3 order by t2.a, t3.a limit 1;
524 --echo # Trace exposed content of t1 which we could see anyway:
525 set optimizer_trace_offset=0,optimizer_trace_limit=100;
526 select a from (select a from t1 where a like "f%") as tt where a like "fi%";
527 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
528
529 --echo
530 --echo # For routines, as they only use t1 and we added only one
531 --echo # privilege on t1, we have nothing to remove.
532 --echo
533 --echo # Now remove each privilege to verify that it was needed for
534 --echo # the view.
535 --echo # D.1) remove table-level SELECT privilege on v1
536 --echo
537
538 connection default;
539 select user();
540
541 revoke select on v1 from user1@localhost;
542 grant select (a) on v1 to user1@localhost;
543
544 --echo
545 connection con_user1;
546 select user();
547
548 set optimizer_trace_offset=0,optimizer_trace_limit=100;
549 select * from v1;
550 --echo # Cannot see anything as it might expose some data from columns
551 --echo # of v1
552 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
553
554 --echo
555 --echo # D.2) remove table-level SHOW VIEW privilege on v1
556 --echo
557
558 connection default;
559 select user();
560
561 --echo # Put back privilege removed in D.1
562 grant select on v1 to user1@localhost;
563 --echo # And remove a next one:
564 revoke show view on v1 from user1@localhost;
565
566 --echo
567 connection con_user1;
568 select user();
569
570 --echo # We have no right to see view's body:
571 --error ER_TABLEACCESS_DENIED_ERROR
572 show create view v1;
573 set optimizer_trace_offset=0,optimizer_trace_limit=100;
574 select * from v1;
575 --echo # Cannot see anything as it would expose body of view
576 select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
577
578 --echo
579 --echo # D.3) remove table-level SELECT privilege on t1
580 --echo
581
582 connection default;
583 select user();
584
585 --echo # Put back privilege removed in D.2
586 grant show view on v1 to user1@localhost;
587 --echo # And remove a next one:
588 revoke select on t1 from user1@localhost;
589 grant select (a) on t1 to user1@localhost;
590
591 --echo
592 connection con_user1;
593 select user();
594
595 set optimizer_trace_offset=0,optimizer_trace_limit=100;
596 select * from v1;
597 --echo # Cannot see anything as it might expose some data from columns
598 --echo # of t1
599 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
600
601 --echo
602 --echo # Now remove each privilege to verify that it was needed for
603 --echo # the trigger:
604 --echo # D.4) remove table-level SELECT privilege on t2
605 --echo
606
607 connection default;
608 select user();
609
610 revoke select on t2 from user1@localhost;
611 grant select (a) on t2 to user1@localhost;
612
613 --echo
614 connection con_user1;
615 select user();
616
617 set optimizer_trace_offset=0,optimizer_trace_limit=100;
618 insert into t2 values(current_user());
619 --echo # Cannot see anything as it might expose some data from
620 --echo # columns of t2
621 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
622
623 --echo
624 --echo # D.5) remove table-level SELECT privilege on t3
625 --echo
626
627 --echo
628 connection default;
629 select user();
630
631 --echo # Put back privilege removed in D.4
632 grant select on t2 to user1@localhost;
633 --echo # And remove a next one:
634 revoke select on t3 from user1@localhost;
635 grant select (a) on t3 to user1@localhost;
636
637 --echo
638 connection con_user1;
639 select user();
640
641 set optimizer_trace_offset=0,optimizer_trace_limit=100;
642 insert into t2 values(current_user());
643 --echo # Cannot see substatement as it might expose some data from
644 --echo # columns of t3
645 select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
646
647 --echo
648 --echo # Cleanup
649 connection default;
650 select user();
651 drop user user1@localhost;
652 disconnect con_user1;
653
654 --echo
655 --echo # ==========================================================
656 --echo # Part E.
657 --echo # Misc tests.
658 --echo # ==========================================================
659 --echo
660
661 connection default;
662 select user();
663 drop view v1;
664 create sql security definer view v1 as select * from t1 where 'secret';
665 create user user1@localhost identified by '';
666 grant create, insert, select on somedb.* to user1@localhost;
667 grant create routine on somedb.* to user1@localhost;
668 connect (con_user1, localhost, user1,, somedb);
669
670 --echo
671 connection con_user1;
672 select user();
673
674 --echo user1 cannot see view's body:
675 --error 1142
676 show create view v1;
677 SET sql_mode = 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION';
678 --echo user1 creates a procedure
679 delimiter |;
680 create procedure proc() sql security definer
681 begin
682 set optimizer_trace="enabled=on";
683 set optimizer_trace_offset=0,optimizer_trace_limit=100;
684 select * from v1 limit 0;
685 create table leak select * from information_schema.optimizer_trace;
686 set optimizer_trace="enabled=off";
687 end|
688 delimiter ;|
689 SET sql_mode = default;
690 connection default;
691 select user();
692
693 --echo root runs procedure, without fear of risk as it is SQL SECURITY DEFINER
694 call proc();
695
696 --echo
697 connection con_user1;
698 select user();
699 --echo user1 cannot see view's body:
700 select * from leak;
701
702 --echo
703 --echo # Cleanup
704 connection default;
705 select user();
706 drop database somedb;
707 drop user user1@localhost;
708 set @@global.optimizer_trace_max_mem_size = @old_size;