#
# Bug #17598: privilege checks when trigger is executed.
#
DROP DATABASE IF EXISTS bug17598;
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;
# Scenario: The definer of the trigger has INSERT privileges.
GRANT INSERT ON bug17598.t1 TO u1@localhost;
# Open connection con1 for user u1.
# Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1 
FOR EACH ROW
SET NEW.c1 = 42;
# Open a connection for user u2.
INSERT INTO bug17598.t1 VALUES (1), (2), (3);
# Change to connection default.
SELECT * FROM bug17598.t1;
c1
42
42
42
DROP TRIGGER bug17598.tr1;
REVOKE INSERT on bug17598.t1 FROM u1@localhost;
DELETE FROM bug17598.t1;
# Scenario: The definer of the trigger does not have INSERT privileges.
# Change to connection con1.
# Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE INSERT ON bug17598.t1 
FOR EACH ROW
SET NEW.c1 = 42;
# Change to connection con2.
INSERT INTO bug17598.t1 VALUES (1), (2), (3);
# Change to connection default.
SELECT * FROM bug17598.t1;
c1
42
42
42
DROP TRIGGER bug17598.tr1;
DELETE FROM bug17598.t1;
REVOKE INSERT ON bug17598.t1 FROM u2@localhost;
GRANT UPDATE ON bug17598.t1 TO u2@localhost;
# Scenario: The definer of the trigger has UPDATE privileges.
# Insert some rows to be updated.
INSERT INTO bug17598.t1 values (1), (2), (3);
GRANT UPDATE ON bug17598.t1 TO u1@localhost;
# Change to connection con1.
# Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1 
FOR EACH ROW
SET NEW.c1 = 42;
# Change to connection con2.
UPDATE bug17598.t1 SET c1 = 17;
# Change to connection default.
SELECT * FROM bug17598.t1;
c1
42
42
42
DROP TRIGGER bug17598.tr1;
REVOKE UPDATE ON bug17598.t1 FROM u1@localhost;
DELETE FROM bug17598.t1;
# Scenario: The definer of the trigger does not have UPDATE privileges.
# Insert some rows to be updated.
INSERT INTO bug17598.t1 values (1), (2), (3);
# Change to connection con1.
# Create a trigger that updates the NEW pseudovariable.
CREATE TRIGGER bug17598.tr1 BEFORE UPDATE ON bug17598.t1 
FOR EACH ROW
SET NEW.c1 = 42;
# Change to connection con2.
UPDATE bug17598.t1 SET c1 = 17;
# Change to connection default.
SELECT * FROM bug17598.t1;
c1
42
42
42
DROP TRIGGER bug17598.tr1;
DELETE FROM bug17598.t1;
REVOKE UPDATE ON bug17598.t1 FROM u2@localhost;
REVOKE TRIGGER ON bug17598.t1 FROM u1@localhost;
# Final cleanup
DROP TABLE bug17598.t1;
DROP USER u1, u2;
DROP DATABASE bug17598;