1# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not 2# supported in embedded server. So, this test should not be run on embedded 3# server. 4 5--source include/not_embedded.inc 6--source include/default_charset.inc 7 8########################################################################### 9# 10# Tests for WL#2818: 11# - Check that triggers are executed under the authorization of the definer. 12# - Check DEFINER clause of CREATE TRIGGER statement; 13# - Check that SUPER privilege required to create a trigger with different 14# definer. 15# - Check that if the user specified as DEFINER does not exist, a warning 16# is emitted. 17# - Check that the definer of a trigger does not exist, the trigger will 18# not be activated. 19# - Check that SHOW TRIGGERS statement provides "Definer" column. 20# - Check that if trigger contains NEW/OLD variables, the definer must have 21# SELECT privilege on the subject table (aka BUG#15166/BUG#15196). 22# 23# Let's also check that user name part of definer can contain '@' symbol (to 24# check that triggers are not affected by BUG#13310 "incorrect user parsing 25# by SP"). 26# 27########################################################################### 28 29# 30# Prepare environment. 31# 32DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 33DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 34DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 35DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 36FLUSH PRIVILEGES; 37 38--disable_warnings 39DROP DATABASE IF EXISTS mysqltest_db1; 40--enable_warnings 41 42CREATE DATABASE mysqltest_db1; 43 44CREATE USER mysqltest_dfn@localhost; 45CREATE USER mysqltest_inv@localhost; 46 47GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 48 49--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 50--connection wl2818_definer_con 51 52CREATE TABLE t1(num_value INT); 53CREATE TABLE t2(user_str TEXT); 54 55--disconnect wl2818_definer_con 56 57--connection default 58 59GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 60GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; 61 62# 63# Check that the user must have TRIGGER privilege to create a trigger. 64# 65 66--connection default 67 68GRANT SUPER ON *.* TO mysqltest_dfn@localhost; 69 70--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 71--connection wl2818_definer_con 72 73--error ER_TABLEACCESS_DENIED_ERROR 74CREATE TRIGGER trg1 AFTER INSERT ON t1 75 FOR EACH ROW 76 INSERT INTO t2 VALUES(CURRENT_USER()); 77 78--disconnect wl2818_definer_con 79 80# 81# Check that the user must have TRIGGER privilege to drop a trigger. 82# 83 84--connection default 85 86GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 87 88--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 89--connection wl2818_definer_con 90 91CREATE TRIGGER trg1 AFTER INSERT ON t1 92 FOR EACH ROW 93 INSERT INTO t2 VALUES(CURRENT_USER()); 94 95--disconnect wl2818_definer_con 96 97--connection default 98 99REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost; 100 101--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 102--connection wl2818_definer_con 103 104--error ER_TABLEACCESS_DENIED_ERROR 105DROP TRIGGER trg1; 106 107--disconnect wl2818_definer_con 108 109# 110# Check that the definer must have TRIGGER privilege to activate a trigger. 111# 112 113--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 114--connection wl2818_definer_con 115 116--error ER_TABLEACCESS_DENIED_ERROR 117INSERT INTO t1 VALUES(0); 118 119--disconnect wl2818_definer_con 120 121--connection default 122 123GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 124 125--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 126--connection wl2818_definer_con 127 128INSERT INTO t1 VALUES(0); 129 130# Cleanup for further tests. 131DROP TRIGGER trg1; 132TRUNCATE TABLE t1; 133TRUNCATE TABLE t2; 134 135--disconnect wl2818_definer_con 136 137--connection default 138 139REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost; 140 141# 142# Check that triggers are executed under the authorization of the definer: 143# - create two tables under "definer"; 144# - grant all privileges on the test db to "definer"; 145# - grant all privileges on the first table to "invoker"; 146# - grant only select privilege on the second table to "invoker"; 147# - create a trigger, which inserts a row into the second table after 148# inserting into the first table. 149# - insert a row into the first table under "invoker". A row also should be 150# inserted into the second table. 151# 152 153--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 154--connection wl2818_definer_con 155 156CREATE TRIGGER trg1 AFTER INSERT ON t1 157 FOR EACH ROW 158 INSERT INTO t2 VALUES(CURRENT_USER()); 159 160--connection default 161 162# Setup definer's privileges. 163 164GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; 165GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost; 166 167# Setup invoker's privileges. 168 169GRANT ALL PRIVILEGES ON mysqltest_db1.t1 170 TO 'mysqltest_inv'@localhost; 171 172GRANT SELECT ON mysqltest_db1.t2 173 TO 'mysqltest_inv'@localhost; 174 175--connection wl2818_definer_con 176 177use mysqltest_db1; 178 179INSERT INTO t1 VALUES(1); 180 181SELECT * FROM t1; 182SELECT * FROM t2; 183 184--connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1) 185--connection wl2818_invoker_con 186 187use mysqltest_db1; 188 189INSERT INTO t1 VALUES(2); 190 191SELECT * FROM t1; 192SELECT * FROM t2; 193 194# 195# Check that if definer lost some privilege required to execute (activate) a 196# trigger, the trigger will not be activated: 197# - create a trigger on insert into the first table, which will insert a row 198# into the second table; 199# - revoke INSERT privilege on the second table from the definer; 200# - insert a row into the first table; 201# - check that an error has been risen; 202# - check that no row has been inserted into the second table; 203# 204 205--connection default 206 207use mysqltest_db1; 208 209REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost; 210 211--connection wl2818_invoker_con 212 213use mysqltest_db1; 214 215--error ER_TABLEACCESS_DENIED_ERROR 216INSERT INTO t1 VALUES(3); 217 218SELECT * FROM t1; 219SELECT * FROM t2; 220 221# 222# Check DEFINER clause of CREATE TRIGGER statement. 223# 224# - Check that SUPER privilege required to create a trigger with different 225# definer: 226# - try to create a trigger with DEFINER="definer@localhost" under 227# "invoker"; 228# - analyze error code; 229# - Check that if the user specified as DEFINER does not exist, a warning is 230# emitted: 231# - create a trigger with DEFINER="non_existent_user@localhost" from 232# "definer"; 233# - check that a warning emitted; 234# - Check that the definer of a trigger does not exist, the trigger will not 235# be activated: 236# - activate just created trigger; 237# - check error code; 238# 239 240--connection wl2818_definer_con 241 242use mysqltest_db1; 243 244DROP TRIGGER trg1; 245 246# Check that SUPER is required to specify different DEFINER. 247 248--error ER_SPECIFIC_ACCESS_DENIED_ERROR 249CREATE DEFINER='mysqltest_inv'@'localhost' 250 TRIGGER trg1 BEFORE INSERT ON t1 251 FOR EACH ROW 252 SET @new_sum = 0; 253 254--connection default 255 256use mysqltest_db1; 257 258GRANT SUPER ON *.* TO mysqltest_dfn@localhost; 259 260--disconnect wl2818_definer_con 261--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 262--connection wl2818_definer_con 263 264CREATE DEFINER='mysqltest_inv'@'localhost' 265 TRIGGER trg1 BEFORE INSERT ON t1 266 FOR EACH ROW 267 SET @new_sum = 0; 268 269# Create with non-existent user. 270 271CREATE DEFINER='mysqltest_nonexs'@'localhost' 272 TRIGGER trg2 AFTER INSERT ON t1 273 FOR EACH ROW 274 SET @new_sum = 0; 275 276# Check that trg2 will not be activated. 277 278--error ER_NO_SUCH_USER 279INSERT INTO t1 VALUES(6); 280 281# 282# Check that SHOW TRIGGERS statement provides "Definer" column. 283# 284 285--replace_column 6 # 286SHOW TRIGGERS; 287 288# 289# Check that weird definer values do not break functionality. I.e. check the 290# following definer values: 291# - ''; 292# - '@'; 293# - '@abc@def@@'; 294# - '@hostname'; 295# - '@abc@def@@@hostname'; 296# 297 298DROP TRIGGER trg1; 299DROP TRIGGER trg2; 300 301CREATE TRIGGER trg1 BEFORE INSERT ON t1 302 FOR EACH ROW 303 SET @a = 1; 304 305CREATE TRIGGER trg2 AFTER INSERT ON t1 306 FOR EACH ROW 307 SET @a = 2; 308 309CREATE TRIGGER trg3 BEFORE UPDATE ON t1 310 FOR EACH ROW 311 SET @a = 3; 312 313CREATE TRIGGER trg4 AFTER UPDATE ON t1 314 FOR EACH ROW 315 SET @a = 4; 316 317CREATE TRIGGER trg5 BEFORE DELETE ON t1 318 FOR EACH ROW 319 SET @a = 5; 320 321# Replace definers with the "weird" definers 322let MYSQLD_DATADIR= `select @@datadir`; 323perl; 324use strict; 325use warnings; 326my $fname= "$ENV{'MYSQLD_DATADIR'}/mysqltest_db1/t1.TRG"; 327open(FILE, "<", $fname) or die; 328my @content= grep($_ !~ /^definers=/, <FILE>); 329close FILE; 330open(FILE, ">", $fname) or die; 331# Use binary file mode to avoid CR/LF's being added on windows 332binmode FILE; 333print FILE @content; 334print FILE "definers='' '\@' '\@abc\@def\@\@' '\@hostname' '\@abcdef\@\@\@hostname'\n"; 335close FILE; 336EOF 337 338--echo 339 340SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; 341 342--echo 343 344--replace_column 17 # 345SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; 346 347# 348# Cleanup 349# 350 351--connection default 352 353DROP USER mysqltest_dfn@localhost; 354DROP USER mysqltest_inv@localhost; 355 356DROP DATABASE mysqltest_db1; 357 358 359########################################################################### 360# 361# BUG#15166: Wrong update [was: select/update] permissions required to execute 362# triggers. 363# 364# BUG#15196: Wrong select permission required to execute triggers. 365# 366########################################################################### 367 368# 369# Prepare environment. 370# 371 372DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 373DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 374DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 375DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 376FLUSH PRIVILEGES; 377 378--disable_warnings 379DROP DATABASE IF EXISTS mysqltest_db1; 380--enable_warnings 381 382CREATE DATABASE mysqltest_db1; 383 384use mysqltest_db1; 385 386# Tables for tesing table-level privileges: 387CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger 388CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger 389 390# Tables for tesing column-level privileges: 391CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger 392CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger 393 394CREATE USER mysqltest_u1@localhost; 395REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; 396GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost; 397 398SET @mysqltest_var = NULL; 399 400--connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1) 401 402# parsing (CREATE TRIGGER) time: 403# - check that nor SELECT either UPDATE is required to execute triggger w/o 404# NEW/OLD variables. 405 406--connection default 407 408use mysqltest_db1; 409 410GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost; 411SHOW GRANTS FOR mysqltest_u1@localhost; 412 413--connection bug15166_u1_con 414 415use mysqltest_db1; 416 417CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1 418 FOR EACH ROW 419 SET @mysqltest_var = 'Hello, world!'; 420 421# parsing (CREATE TRIGGER) time: 422# - check that UPDATE is not enough to read the value; 423# - check that UPDATE is required to modify the value; 424 425--connection default 426 427use mysqltest_db1; 428 429GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 430GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 431 432GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; 433GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; 434 435--connection bug15166_u1_con 436 437use mysqltest_db1; 438 439# - table-level privileges 440 441# TODO: check privileges at CREATE TRIGGER time. 442# --error ER_COLUMNACCESS_DENIED_ERROR 443CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1 444 FOR EACH ROW 445 SET @mysqltest_var = NEW.col; 446DROP TRIGGER t1_trg_err_1; 447 448# TODO: check privileges at CREATE TRIGGER time. 449# --error ER_COLUMNACCESS_DENIED_ERROR 450CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1 451 FOR EACH ROW 452 SET @mysqltest_var = OLD.col; 453DROP TRIGGER t1_trg_err_2; 454 455CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2 456 FOR EACH ROW 457 SET NEW.col = 't2_trg_before_insert'; 458 459# - column-level privileges 460 461# TODO: check privileges at CREATE TRIGGER time. 462# --error ER_COLUMNACCESS_DENIED_ERROR 463CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3 464 FOR EACH ROW 465 SET @mysqltest_var = NEW.col; 466DROP TRIGGER t3_trg_err_1; 467 468# TODO: check privileges at CREATE TRIGGER time. 469# --error ER_COLUMNACCESS_DENIED_ERROR 470CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3 471 FOR EACH ROW 472 SET @mysqltest_var = OLD.col; 473DROP TRIGGER t3_trg_err_2; 474 475CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4 476 FOR EACH ROW 477 SET NEW.col = 't4_trg_before_insert'; 478 479# parsing (CREATE TRIGGER) time: 480# - check that SELECT is required to read the value; 481# - check that SELECT is not enough to modify the value; 482 483--connection default 484 485use mysqltest_db1; 486 487REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; 488REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; 489GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 490GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 491 492REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; 493REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; 494GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost; 495GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost; 496 497--connection bug15166_u1_con 498 499use mysqltest_db1; 500 501# - table-level privileges 502 503CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1 504 FOR EACH ROW 505 SET @mysqltest_var = NEW.col; 506 507CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1 508 FOR EACH ROW 509 SET @mysqltest_var = OLD.col; 510 511# TODO: check privileges at CREATE TRIGGER time. 512# --error ER_COLUMNACCESS_DENIED_ERROR 513CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2 514 FOR EACH ROW 515 SET NEW.col = 't2_trg_err_1'; 516DROP TRIGGER t2_trg_err_1; 517 518# TODO: check privileges at CREATE TRIGGER time. 519# --error ER_COLUMNACCESS_DENIED_ERROR 520CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2 521 FOR EACH ROW 522 SET NEW.col = CONCAT(OLD.col, '(updated)'); 523DROP TRIGGER t2_trg_err_2; 524 525# - column-level privileges 526 527CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3 528 FOR EACH ROW 529 SET @mysqltest_var = NEW.col; 530 531CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3 532 FOR EACH ROW 533 SET @mysqltest_var = OLD.col; 534 535# TODO: check privileges at CREATE TRIGGER time. 536# --error ER_COLUMNACCESS_DENIED_ERROR 537CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4 538 FOR EACH ROW 539 SET NEW.col = 't4_trg_err_1'; 540DROP TRIGGER t4_trg_err_1; 541 542# TODO: check privileges at CREATE TRIGGER time. 543# --error ER_COLUMNACCESS_DENIED_ERROR 544CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4 545 FOR EACH ROW 546 SET NEW.col = CONCAT(OLD.col, '(updated)'); 547DROP TRIGGER t4_trg_err_2; 548 549# execution time: 550# - check that UPDATE is not enough to read the value; 551# - check that UPDATE is required to modify the value; 552 553--connection default 554 555use mysqltest_db1; 556 557REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; 558REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; 559GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 560GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 561 562REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; 563REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; 564GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; 565GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; 566 567# - table-level privileges 568 569--error ER_COLUMNACCESS_DENIED_ERROR 570INSERT INTO t1 VALUES('line1'); 571 572SELECT * FROM t1; 573SELECT @mysqltest_var; 574 575INSERT INTO t2 VALUES('line2'); 576 577SELECT * FROM t2; 578 579# - column-level privileges 580 581--error ER_COLUMNACCESS_DENIED_ERROR 582INSERT INTO t3 VALUES('t3_line1'); 583 584SELECT * FROM t3; 585SELECT @mysqltest_var; 586 587INSERT INTO t4 VALUES('t4_line2'); 588 589SELECT * FROM t4; 590 591# execution time: 592# - check that SELECT is required to read the value; 593# - check that SELECT is not enough to modify the value; 594 595--connection default 596 597use mysqltest_db1; 598 599REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost; 600REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost; 601GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; 602GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost; 603 604REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost; 605REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost; 606GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost; 607GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost; 608 609# - table-level privileges 610 611INSERT INTO t1 VALUES('line3'); 612 613SELECT * FROM t1; 614SELECT @mysqltest_var; 615 616--error ER_COLUMNACCESS_DENIED_ERROR 617INSERT INTO t2 VALUES('line4'); 618 619SELECT * FROM t2; 620 621# - column-level privileges 622 623INSERT INTO t3 VALUES('t3_line2'); 624 625SELECT * FROM t3; 626SELECT @mysqltest_var; 627 628--error ER_COLUMNACCESS_DENIED_ERROR 629INSERT INTO t4 VALUES('t4_line2'); 630 631SELECT * FROM t4; 632 633# execution time: 634# - check that nor SELECT either UPDATE is required to execute triggger w/o 635# NEW/OLD variables. 636 637DELETE FROM t1; 638 639SELECT @mysqltest_var; 640 641# 642# Cleanup. 643# 644 645DROP USER mysqltest_u1@localhost; 646 647DROP DATABASE mysqltest_db1; 648 649 650# 651# Test for bug #14635 Accept NEW.x as INOUT parameters to stored 652# procedures from within triggers 653# 654# We require UPDATE privilege when NEW.x passed as OUT parameter, and 655# SELECT and UPDATE when NEW.x passed as INOUT parameter. 656# 657DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 658DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 659DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 660DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 661FLUSH PRIVILEGES; 662 663--disable_warnings 664DROP DATABASE IF EXISTS mysqltest_db1; 665--enable_warnings 666 667CREATE DATABASE mysqltest_db1; 668USE mysqltest_db1; 669 670CREATE TABLE t1 (i1 INT); 671CREATE TABLE t2 (i1 INT); 672 673CREATE USER mysqltest_dfn@localhost; 674CREATE USER mysqltest_inv@localhost; 675 676GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost; 677GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost; 678 679connect (definer,localhost,mysqltest_dfn,,mysqltest_db1); 680connect (invoker,localhost,mysqltest_inv,,mysqltest_db1); 681 682connection definer; 683CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3; 684CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5; 685 686# Check that having no privilege won't work. 687connection definer; 688CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 689 CALL p1(NEW.i1); 690CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 691 CALL p2(NEW.i1); 692 693connection invoker; 694--error ER_COLUMNACCESS_DENIED_ERROR 695INSERT INTO t1 VALUES (7); 696--error ER_COLUMNACCESS_DENIED_ERROR 697INSERT INTO t2 VALUES (11); 698 699connection definer; 700DROP TRIGGER t2_bi; 701DROP TRIGGER t1_bi; 702 703# Check that having only SELECT privilege is not enough. 704connection default; 705GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost; 706 707connection definer; 708CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 709 CALL p1(NEW.i1); 710CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 711 CALL p2(NEW.i1); 712 713connection invoker; 714--error ER_COLUMNACCESS_DENIED_ERROR 715INSERT INTO t1 VALUES (13); 716--error ER_COLUMNACCESS_DENIED_ERROR 717INSERT INTO t2 VALUES (17); 718 719connection default; 720REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 721 722connection definer; 723DROP TRIGGER t2_bi; 724DROP TRIGGER t1_bi; 725 726# Check that having only UPDATE privilege is enough for OUT parameter, 727# but not for INOUT parameter. 728connection default; 729GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 730 731connection definer; 732CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 733 CALL p1(NEW.i1); 734CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 735 CALL p2(NEW.i1); 736 737connection invoker; 738INSERT INTO t1 VALUES (19); 739--error ER_COLUMNACCESS_DENIED_ERROR 740INSERT INTO t2 VALUES (23); 741 742connection default; 743REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 744 745connection definer; 746DROP TRIGGER t2_bi; 747DROP TRIGGER t1_bi; 748 749# Check that having SELECT and UPDATE privileges is enough. 750connection default; 751GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 752 753connection definer; 754CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 755 CALL p1(NEW.i1); 756CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW 757 CALL p2(NEW.i1); 758 759connection invoker; 760INSERT INTO t1 VALUES (29); 761INSERT INTO t2 VALUES (31); 762 763connection default; 764REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 765 766connection definer; 767DROP TRIGGER t2_bi; 768DROP TRIGGER t1_bi; 769 770connection default; 771DROP PROCEDURE p2; 772DROP PROCEDURE p1; 773 774# Check that late procedure redefining won't open a security hole. 775connection default; 776GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost; 777 778connection definer; 779CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37; 780CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 781 CALL p1(NEW.i1); 782 783connection invoker; 784INSERT INTO t1 VALUES (41); 785 786connection definer; 787DROP PROCEDURE p1; 788CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43; 789 790connection invoker; 791--error ER_COLUMNACCESS_DENIED_ERROR 792INSERT INTO t1 VALUES (47); 793 794connection definer; 795DROP PROCEDURE p1; 796CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51; 797 798connection invoker; 799--error ER_COLUMNACCESS_DENIED_ERROR 800INSERT INTO t1 VALUES (53); 801 802connection default; 803DROP PROCEDURE p1; 804REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost; 805 806connection definer; 807DROP TRIGGER t1_bi; 808 809# Cleanup. 810disconnect definer; 811disconnect invoker; 812connection default; 813DROP USER mysqltest_inv@localhost; 814DROP USER mysqltest_dfn@localhost; 815DROP TABLE t2; 816DROP TABLE t1; 817DROP DATABASE mysqltest_db1; 818USE test; 819 820# 821# Bug #26162: Trigger DML ignores low_priority_updates setting 822# 823CREATE TABLE t1 (id INTEGER); 824CREATE TABLE t2 (id INTEGER); 825 826INSERT INTO t2 VALUES (1),(2); 827 828# trigger that produces the high priority insert, but should be low, adding 829# LOW_PRIORITY fixes this 830CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW 831 INSERT INTO t2 VALUES (new.id); 832 833CONNECT (rl_holder, localhost, root,,); 834CONNECT (rl_acquirer, localhost, root,,); 835CONNECT (wl_acquirer, localhost, root,,); 836CONNECT (rl_contender, localhost, root,,); 837 838CONNECTION rl_holder; 839SELECT GET_LOCK('B26162',120); 840 841CONNECTION rl_acquirer; 842let $rl_acquirer_thread_id = `SELECT @@pseudo_thread_id`; 843--send 844SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1; 845 846CONNECTION wl_acquirer; 847let $wl_acquirer_thread_id = `SELECT @@pseudo_thread_id`; 848SET SESSION LOW_PRIORITY_UPDATES=1; 849SET GLOBAL LOW_PRIORITY_UPDATES=1; 850#need to wait for rl_acquirer to lock on the B26162 lock 851let $wait_condition= 852 SELECT STATE = 'User lock' FROM INFORMATION_SCHEMA.PROCESSLIST 853 WHERE ID = $rl_acquirer_thread_id; 854--source include/wait_condition.inc 855--send 856INSERT INTO t1 VALUES (5); 857 858CONNECTION rl_contender; 859# Wait until wl_acquirer is waiting for the read lock on t2 to be released. 860let $wait_condition= 861 SELECT STATE = 'Waiting for table level lock' FROM INFORMATION_SCHEMA.PROCESSLIST 862 WHERE ID = $wl_acquirer_thread_id; 863--source include/wait_condition.inc 864# must not "see" the row inserted by the INSERT (as it must run before the 865# INSERT) 866--send 867SELECT 'rl_contender', id FROM t2 WHERE id > 1; 868 869CONNECTION rl_holder; 870#need to wait for wl_acquirer and rl_contender to lock on t2 871sleep 2; 872SELECT RELEASE_LOCK('B26162'); 873 874CONNECTION rl_acquirer; 875--reap 876SELECT RELEASE_LOCK('B26162'); 877CONNECTION wl_acquirer; 878--reap 879CONNECTION rl_contender; 880--reap 881 882CONNECTION default; 883DISCONNECT rl_acquirer; 884DISCONNECT wl_acquirer; 885DISCONNECT rl_contender; 886DISCONNECT rl_holder; 887 888DROP TRIGGER t1_test; 889DROP TABLE t1,t2; 890SET SESSION LOW_PRIORITY_UPDATES=DEFAULT; 891SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT; 892 893--echo End of 5.0 tests. 894 895# 896# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock 897# 898 899--disable_warnings 900drop table if exists t1; 901--enable_warnings 902create table t1 (i int); 903connect (flush,localhost,root,,test,,); 904connection default; 905lock tables t1 write; 906connection flush; 907--send flush tables with read lock; 908connection default; 909let $wait_condition= 910 select count(*) = 1 from information_schema.processlist 911 where state = "Waiting for global read lock"; 912--source include/wait_condition.inc 913create trigger t1_bi before insert on t1 for each row begin end; 914unlock tables; 915connection flush; 916--reap 917unlock tables; 918connection default; 919select * from t1; 920drop table t1; 921disconnect flush; 922 923# 924# Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data 925# 926CREATE DATABASE db1; 927CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY; 928CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW 929 INSERT INTO db1.t1 VALUES('Some very sensitive data goes here'); 930 931CREATE USER 'no_rights'@'localhost'; 932REVOKE ALL ON *.* FROM 'no_rights'@'localhost'; 933FLUSH PRIVILEGES; 934 935connect (con1,localhost,no_rights,,); 936SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS 937 WHERE trigger_schema = 'db1'; 938--error ER_SPECIFIC_ACCESS_DENIED_ERROR 939SHOW CREATE TRIGGER db1.trg; 940 941connection default; 942disconnect con1; 943DROP USER 'no_rights'@'localhost'; 944DROP DATABASE db1; 945 946# 947# Bug#55421 Protocol::end_statement(): Assertion `0' on multi-table UPDATE IGNORE 948# To reproduce a crash we need to provoke a trigger execution with 949# the following conditions: 950# - active SELECT statement during trigger execution 951# (i.e. LEX::current_select != NULL); 952# - IGNORE option (i.e. LEX::current_select->no_error == TRUE); 953--disable_warnings 954DROP DATABASE IF EXISTS mysqltest_db1; 955--enable_warnings 956 957CREATE DATABASE mysqltest_db1; 958USE mysqltest_db1; 959 960CREATE USER mysqltest_u1@localhost; 961GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost; 962 963--connect(con1,localhost,mysqltest_u1,,mysqltest_db1) 964 965CREATE TABLE t1 ( 966 a1 int, 967 a2 int 968); 969INSERT INTO t1 VALUES (1, 20); 970 971CREATE TRIGGER mysqltest_db1.upd_t1 972BEFORE UPDATE ON t1 FOR EACH ROW SET new.a2 = 200; 973 974CREATE TABLE t2 ( 975 a1 int 976); 977 978INSERT INTO t2 VALUES (2); 979 980--connection default 981 982REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost; 983 984--error ER_TABLEACCESS_DENIED_ERROR 985UPDATE IGNORE t1, t2 SET t1.a1 = 2, t2.a1 = 3 WHERE t1.a1 = 1 AND t2.a1 = 2; 986# Cleanup 987 988DROP DATABASE mysqltest_db1; 989DROP USER mysqltest_u1@localhost; 990 991--disconnect con1 992--connection default 993USE test; 994 995--echo End of 5.1 tests. 996