security.inc 24.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745
# Test that trace does not show information forbidden
# by lack of privileges.

--source include/have_optimizer_trace.inc
# Grant tests not performed with embedded server
-- source include/not_embedded.inc

connection default;
let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
set @old_size = @@global.optimizer_trace_max_mem_size;
eval set global optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;

connection default;
select user();
create database somedb;
use somedb;
create table t1(a varchar(100));
insert into t1 values("first");
create table t2(a varchar(100));
insert into t2 values("first");
create table t3(a varchar(100));
insert into t3 values("first");
delimiter |;
create procedure p1() sql security definer
begin
  declare b int;
  if (select count(*) from t1)
  then
    select 22 into b from dual;
  end if;
  select a into b from t1 limit 1;
  insert into t1 values(current_user());
end|
create function f1() returns int sql security definer
begin
  declare b int;
  select 48 into b from dual;
  select a into b from t1 limit 1;
  insert into t1 values(current_user());
  return 36;
end|
create trigger trg2 before insert on t2 for each row 
begin
  insert into t3 select * from t3;
end|
delimiter ;|
create sql security definer view v1 as select * from t1;
create user user1@localhost identified by '';
grant all on *.* to user1@localhost with grant option;
connect (con_user1, localhost, user1,, somedb);

--echo
connection con_user1;
select user();
set optimizer_trace="enabled=on";
# SHOW GRANTS scans a hash, which gives a random order
--sorted_result
show grants;

--echo
--echo # ==========================================================
--echo # Part A.
--echo # Test that security context changes are allowed when, and only
--echo # when, invoker has all global privileges.
--echo # ==========================================================
--echo

--echo # Because invoker has all global privileges, all traces are visible:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # this SET always purges all remembered traces
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # Show that really all global privileges are needed: let root
--echo # revoke just one from user1. Because user1 does not have all global
--echo # privileges anymore, security context changes are forbidden,
--echo # thus there is no trace.
--echo

connection default;
select user();
revoke shutdown on *.* from user1@localhost;
# removing a global privilege never affects an existing connection:
disconnect con_user1;
connect (con_user1, localhost, user1,, somedb);

--echo
connection con_user1;
select user();
set optimizer_trace="enabled=on";
--sorted_result
show grants;

set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
--echo # In CALL we execute stored procedure and notice a security
--echo # context change. The context change is probably only relevant
--echo # for substatements, but we still hide CALL. This is to be
--echo # consistent with what we do when routine body should not be
--echo # exposed. And it also feels safer to disable I_S output as
--echo # soon as possible.
--echo # Ps-protocol-specific note: mysqltest uses normal protocol for CALL
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # Verify that user1 cannot circumvent security checks by
--echo # setting @@optimizer_trace_offset so that I_S output is disabled
--echo # before the object (routine) is checked, and enabled in the
--echo # middle of object usage, when 'offset' is passed.
--echo

set optimizer_trace_offset=2,optimizer_trace_limit=1;
call p1();
--echo # Even though the routine's execution started before
--echo # 'offset', it detected the security context changes. So the
--echo # trace of CALL gets the "missing privilege" mark but we don't
--echo # see it as CALL was before 'offset'.
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # Finally, verify that if the routine's definer does modify
--echo # @@optimizer_trace from "enabled=off" to "enabled=on", in the
--echo # body of the routine, then tracing works. This is no security
--echo # issue, as it was done by the routine's definer.
--echo

connection default;
select user();
delimiter |;
create procedure p2() sql security definer
begin
  declare b int;
  set optimizer_trace="enabled=on";
  select 22 into b from dual;
end|
delimiter ;|

--echo
connection con_user1;
select user();

set optimizer_trace="enabled=off";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p2();
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Variable is as set by the routine
select @@optimizer_trace;

--echo
--echo # ==========================================================
--echo # Part B.
--echo # Do same tests but with SQL SECURITY INVOKER objects, to verify that
--echo # the restriction on security context changes is not present.
--echo # ==========================================================
--echo

connection default;
select user();
alter procedure p1 sql security invoker;
alter function f1 sql security invoker;
alter sql security invoker view v1 as select * from t1;
--echo # Triggers cannot be SQL SECURITY INVOKER so we don't test
--echo # them here.
alter procedure p2 sql security invoker;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=2,optimizer_trace_limit=1;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace="enabled=off";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p2();
--echo # SELECT substatement is traced (no security context change)
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
select @@optimizer_trace;

--echo
--echo # ==========================================================
--echo # Part C.
--echo # User1 got traces. Determine the minimum set of privileges he
--echo # needed for that.
--echo # ==========================================================
--echo

connection default;
drop procedure p2; # p2 is not worth testing more
select user();
revoke all privileges, grant option from user1@localhost;
--echo # Grant minimum privileges to use the routines and views,
--echo # without considering optimizer trace:
grant execute on procedure p1 to user1@localhost;
grant execute on function f1 to user1@localhost;
grant select (a) on v1 to user1@localhost;
--echo # Objects above are SQL SECURITY INVOKER, so invoker needs
--echo # privileges on objects used internally:
grant select (a) on t1 to user1@localhost;
grant insert (a) on t1 to user1@localhost;
delete from t1 where a<>"first";
disconnect con_user1;
connect (con_user1, localhost, user1,, somedb);

--echo
connection con_user1;
select user();
set optimizer_trace="enabled=on";
--sorted_result
show grants;

--echo
--echo # Those privileges are not enough to see traces:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
--echo # In CALL we execute stored procedure and notice that body should
--echo # not be exposed. The trace of this CALL would not expose the
--echo # body. Trace of substatements would. But, due to
--echo # implementation, CALL is hidden.
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
--echo # SELECT is hidden (same reason as for CALL).
--echo # Ps-protocol-specific note: preparation of SELECT above does not
--echo # execute f1, so does not risk exposing body, so its trace is
--echo # visible.
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it would expose body of view
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # C.0) Add more privileges:
--echo

connection default;
select user();
--echo # - for use of t1 in routines and view:
grant select on t1 to user1@localhost;
--echo # - for use of routines:
grant select on mysql.proc to user1@localhost;
--echo # - for use of view:
grant select, show view on v1 to user1@localhost;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of routine, and content of t1, which we
--echo # could see anyway:
show create procedure p1;
select * from t1 limit 1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of routine, and content of t1, which we
--echo # could see anyway:
show create function f1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of view, and content of t1, which we
--echo # could see anyway:
show create view v1;

--echo
--echo # Now remove each privilege to verify that it was needed:
--echo # C.1) remove table-level SELECT privilege on t1
--echo
connection default;
select user();
revoke select on t1 from user1@localhost;
grant select (a) on t1 to user1@localhost;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
--echo # Cannot see those substatements which use t1
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
--echo # Cannot see those substatements which use t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of routine, which we could see anyway:
set optimizer_trace="enabled=off";
show create function f1;
set optimizer_trace="enabled=on";
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it might expose some data from columns
--echo # of t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # C.2) remove table-level SELECT privilege on mysql.proc
--echo

connection default;
select user();
--echo # Put back privilege removed in C.1
grant select on t1 to user1@localhost;
--echo # And remove a next one:
revoke select on mysql.proc from user1@localhost;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

--echo # We have no right to see routines' bodies:
set optimizer_trace="enabled=off";
show create procedure p1;
show create function f1;
--echo # Verify that optimizer trace does not influence the privilege
--echo # checking in SHOW CREATE:
set optimizer_trace="enabled=on";
show create procedure p1;
show create function f1;

set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
--echo # Cannot see anything as it would expose body of routine
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # C.3) remove table-level SELECT privilege on view
--echo

connection default;
select user();
--echo # Put back privilege removed in C.2
grant select on mysql.proc to user1@localhost;
--echo # And remove a next one:
revoke select on v1 from user1@localhost;
grant select (a) on v1 to user1@localhost;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it might expose some data from columns
--echo # of v1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # C.4) remove SHOW VIEW privilege on view
--echo

connection default;
select user();
--echo # Put back privilege removed in C.3
grant select on v1 to user1@localhost;
--echo # And remove a next one:
revoke show view on v1 from user1@localhost;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

set optimizer_trace="enabled=off";
--echo # We have no right to see view's body:
--error ER_TABLEACCESS_DENIED_ERROR
show create view v1;
set optimizer_trace="enabled=on";
--echo # Verify that optimizer trace does not influence the privilege
--echo # checking in SHOW CREATE:
--error ER_TABLEACCESS_DENIED_ERROR
show create view v1;

set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it would expose body of view
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # ==========================================================
--echo # Part D.
--echo # Like Part C, but instead of SQL SECURITY INVOKER objects
--echo # created by root and used by User1, let's have SQL SECURITY
--echo # DEFINER objects created and used by User1. Determine the
--echo # minimum set of privileges he needs for that.
--echo # ==========================================================
--echo

connection default;
select user();
drop procedure p1;
drop function f1;
drop view v1;
drop trigger trg2;
revoke all privileges, grant option from user1@localhost;
--echo # Grant minimum privileges to create and use objects,
--echo # without considering optimizer trace:
grant create routine on somedb.* to user1@localhost;
grant trigger on t2 to user1@localhost;
grant create view on somedb.* to user1@localhost;
grant select (a) on t1 to user1@localhost;
grant insert (a) on t1 to user1@localhost;
grant insert (a) on t2 to user1@localhost;
grant select (a) on t3 to user1@localhost;
grant insert (a) on t3 to user1@localhost;
delete from t1 where a<>"first";
disconnect con_user1;
connect (con_user1, localhost, user1,, somedb);

--echo
connection con_user1;
select user();
set optimizer_trace="enabled=on";

delimiter |;
create procedure p1() sql security definer
begin
  declare b int;
  if (select count(*) from t1)
  then
    select 22 into b from dual;
  end if;
  select a into b from t1 limit 1;
  insert into t1 values(current_user());
end|
create function f1() returns int sql security definer
begin
  declare b int;
  select 48 into b from dual;
  select a into b from t1 limit 1;
  insert into t1 values(current_user());
  return 36;
end|
create trigger trg2 before insert on t2 for each row 
begin
  insert into t3 select * from t3;
end|
delimiter ;|
create sql security definer view v1 as select * from t1;

--echo # Creating a view is not enough to be able to SELECT it...
connection default;
select user();
grant select (a) on v1 to user1@localhost;

--echo
connection con_user1;
select user();

--echo # Those privileges are not enough to see traces:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
--echo # Can see body of routine (as definer), but not statements using t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
--echo # Can see body of routine (as definer), but not statements using t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
show create function f1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it might expose some data from columns
--echo # of t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
--echo # Cannot see anything as it might expose some data from
--echo # columns of t2
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Also test a query accessing t1 in FROM clause:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select a from (select a from t1 where a like "f%") as tt where a like "fi%";
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # D.0) Add more privileges:
--echo

connection default;
select user();
--echo # - for use of t1 in routines and view:
grant select on t1 to user1@localhost;
--echo # - for use of view:
grant select, show view on v1 to user1@localhost;
--echo # - for use of trigger
grant select on t2 to user1@localhost;
grant select on t3 to user1@localhost;
delete from t1 where a<>"first";

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
call p1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of routine, and content of t1, which we
--echo # could see anyway:
show create procedure p1;
select * from t1 limit 1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select f1();
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of routine, and content of t1, which we
--echo # could see anyway:
show create function f1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of view, and content of t1, which we
--echo # could see anyway:
show create view v1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;
--echo # Trace exposed body of trigger, and content of t2/t3, which we
--echo # could see anyway:
show create trigger trg2;
select * from t2, t3 limit 1;
--echo # Trace exposed content of t1 which we could see anyway:
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select a from (select a from t1 where a like "f%") as tt where a like "fi%";
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # For routines, as they only use t1 and we added only one
--echo # privilege on t1, we have nothing to remove.
--echo
--echo # Now remove each privilege to verify that it was needed for
--echo # the view.
--echo # D.1) remove table-level SELECT privilege on v1
--echo

connection default;
select user();

revoke select on v1 from user1@localhost;
grant select (a) on v1 to user1@localhost;

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it might expose some data from columns
--echo # of v1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # D.2) remove table-level SHOW VIEW privilege on v1
--echo

connection default;
select user();

--echo # Put back privilege removed in D.1
grant select on v1 to user1@localhost;
--echo # And remove a next one:
revoke show view on v1 from user1@localhost;

--echo
connection con_user1;
select user();

--echo # We have no right to see view's body:
--error ER_TABLEACCESS_DENIED_ERROR
show create view v1;
set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it would expose body of view
select QUERY, TRACE, INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # D.3) remove table-level SELECT privilege on t1
--echo

connection default;
select user();

--echo # Put back privilege removed in D.2
grant show view on v1 to user1@localhost;
--echo # And remove a next one:
revoke select on t1 from user1@localhost;
grant select (a) on t1 to user1@localhost;

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
select * from v1;
--echo # Cannot see anything as it might expose some data from columns
--echo # of t1
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # Now remove each privilege to verify that it was needed for
--echo # the trigger:
--echo # D.4) remove table-level SELECT privilege on t2
--echo

connection default;
select user();

revoke select on t2 from user1@localhost;
grant select (a) on t2 to user1@localhost;

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
--echo # Cannot see anything as it might expose some data from
--echo # columns of t2
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # D.5) remove table-level SELECT privilege on t3
--echo

--echo
connection default;
select user();

--echo # Put back privilege removed in D.4
grant select on t2 to user1@localhost;
--echo # And remove a next one:
revoke select on t3 from user1@localhost;
grant select (a) on t3 to user1@localhost;

--echo
connection con_user1;
select user();

set optimizer_trace_offset=0,optimizer_trace_limit=100;
insert into t2 values(current_user());
--echo # Cannot see substatement as it might expose some data from
--echo # columns of t3
select QUERY, length(TRACE), INSUFFICIENT_PRIVILEGES from information_schema.OPTIMIZER_TRACE;

--echo
--echo # Cleanup
connection default;
select user();
drop user user1@localhost;
disconnect con_user1;

--echo
--echo # ==========================================================
--echo # Part E.
--echo # Misc tests.
--echo # ==========================================================
--echo

connection default;
select user();
drop view v1;
create sql security definer view v1 as select * from t1 where 'secret';
create user user1@localhost identified by '';
grant create, insert, select on somedb.* to user1@localhost;
grant create routine on somedb.* to user1@localhost;
connect (con_user1, localhost, user1,, somedb);

--echo
connection con_user1;
select user();

--echo user1 cannot see view's body:
--error 1142
show create view v1;

--echo user1 creates a procedure
delimiter |;
create procedure proc() sql security definer
begin
  set optimizer_trace="enabled=on";
  set optimizer_trace_offset=0,optimizer_trace_limit=100;
  select * from v1 limit 0;
  create table leak select * from information_schema.optimizer_trace;
  set optimizer_trace="enabled=off";
end|
delimiter ;|

connection default;
select user();

--echo root runs procedure, without fear of risk as it is SQL SECURITY DEFINER
call proc();

--echo
connection con_user1;
select user();
--echo user1 cannot see view's body:
select * from leak;

--echo
--echo # Cleanup
connection default;
select user();
drop database somedb;
drop user user1@localhost;
set @@global.optimizer_trace_max_mem_size = @old_size;