--echo #
--echo # Bug #17598: privilege checks when trigger is executed.
--echo #
--disable_warnings
DROP DATABASE IF EXISTS bug17598;
--enable_warnings
CREATE DATABASE bug17598;
CREATE USER u1;
CREATE USER u2;
GRANT SELECT ON bug17598.* TO u1@localhost;
CREATE TABLE bug17598.t1 (c1 int);

GRANT TRIGGER ON bug17598.t1 TO u1@localhost;
GRANT INSERT ON bug17598.t1 TO u2@localhost;

--echo # Scenario: The definer of the trigger has INSERT privileges.
GRANT INSERT ON bug17598.t1 TO u1@localhost;
--echo # Open connection con1 for user u1.
connect (con1,localhost,u1,,);
--echo # Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1 
  FOR EACH ROW
    SET NEW.c1 = 42;
--echo # Open a connection for user u2.
connect (con2,localhost,u2,,);
INSERT INTO bug17598.t1 VALUES (1), (2), (3);
--echo # Change to connection default.
connection default;
SELECT * FROM bug17598.t1;
DROP TRIGGER bug17598.tr1;
REVOKE INSERT on bug17598.t1 FROM u1@localhost;
DELETE FROM bug17598.t1;

--echo # Scenario: The definer of the trigger does not have INSERT privileges.
--echo # Change to connection con1.
connection con1;
--echo # Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1 
  FOR EACH ROW
    SET NEW.c1 = 42;
--echo # Change to connection con2.
connection con2;
INSERT INTO bug17598.t1 VALUES (1), (2), (3);
--echo # Change to connection default.
connection default;
SELECT * FROM bug17598.t1;
DROP TRIGGER bug17598.tr1;
DELETE FROM bug17598.t1;

REVOKE INSERT ON bug17598.t1 FROM u2@localhost;
GRANT UPDATE ON bug17598.t1 TO u2@localhost;

--echo # Scenario: The definer of the trigger has UPDATE privileges.
--echo # Insert some rows to be updated.
INSERT INTO bug17598.t1 values (1), (2), (3);
GRANT UPDATE ON bug17598.t1 TO u1@localhost;
--echo # Change to connection con1.
connection con1;
--echo # Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1 
  FOR EACH ROW
    SET NEW.c1 = 42;
--echo # Change to connection con2.
connection con2;
UPDATE bug17598.t1 SET c1 = 17;
--echo # Change to connection default.
connection default;
SELECT * FROM bug17598.t1;
DROP TRIGGER bug17598.tr1;
REVOKE UPDATE ON bug17598.t1 FROM u1@localhost;
DELETE FROM bug17598.t1;

--echo # Scenario: The definer of the trigger does not have UPDATE privileges.
--echo # Insert some rows to be updated.
INSERT INTO bug17598.t1 values (1), (2), (3);
--echo # Change to connection con1.
connection con1;
--echo # Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1 
  FOR EACH ROW
    SET NEW.c1 = 42;
--echo # Change to connection con2.
connection con2;
UPDATE bug17598.t1 SET c1 = 17;
--echo # Change to connection default.
connection default;
SELECT * FROM bug17598.t1;
DROP TRIGGER bug17598.tr1;
DELETE FROM bug17598.t1;

REVOKE UPDATE ON bug17598.t1 FROM u2@localhost;
REVOKE TRIGGER ON bug17598.t1 FROM u1@localhost;

--echo # Final cleanup
disconnect con1;
disconnect con2;
DROP TABLE bug17598.t1;
DROP USER u1, u2;
DROP DATABASE bug17598;