Fix for the following bugs:

  - BUG#15166: Wrong update permissions required to execute triggers
  - BUG#15196: Wrong select permission required to execute triggers

The idea of the fix is to check necessary privileges
in Item_trigger_field::fix_fields(), instead of having "special variables"
technique. To achieve this, we should pass to an Item_trigger_field instance
a flag, which will indicate the usage/access type of this trigger variable.


mysql-test/r/trigger-grant.result:
  Update the result file.
mysql-test/t/trigger-grant.test:
  Add test cases for BUG#15166 and BUG#15196
sql/item.cc:
  Item_trigger_field: check appropriate (SELECT/UPDATE) privilege in fix_fields().
sql/item.h:
  Add a flag to specify access type for trigger field.
sql/sql_trigger.cc:
  "Special variable" technique of checking privileges for NEW/OLD variables
  was replaced by checking table- and column-level privileges in
  Item_trigger_field::fix_fields().
sql/sql_trigger.h:
  "Special variable" technique of checking privileges for NEW/OLD variables
  was replaced by checking table- and column-level privileges in
  Item_trigger_field::fix_fields().
sql/sql_yacc.yy:
  Specify access type for trigger fields.

2 files changed, 486 insertions, 314 deletions

diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result
index 858cab7a04a..87e5e11d779 100644
--- a/mysql-test/r/trigger-grant.result
+++ b/mysql-test/r/trigger-grant.result
@@ -65,109 +65,6 @@ user_str
 mysqltest_dfn@localhost
 mysqltest_dfn@localhost
 
----> connection: default
-use mysqltest_db1;
-REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-use mysqltest_db1;
-DROP TRIGGER trg1;
-SET @new_sum = 0;
-SET @old_sum = 0;
----> INSERT INTO statement; BEFORE timing
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
-FOR EACH ROW
-SET @new_sum = @new_sum + NEW.num_value;
-INSERT INTO t1 VALUES(4);
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> INSERT INTO statement; AFTER timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-FOR EACH ROW
-SET @new_sum = @new_sum + NEW.num_value;
-INSERT INTO t1 VALUES(5);
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> UPDATE statement; BEFORE timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 BEFORE UPDATE ON t1
-FOR EACH ROW
-SET @old_sum = @old_sum + OLD.num_value;
-UPDATE t1 SET num_value = 10;
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> UPDATE statement; AFTER timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 AFTER UPDATE ON t1
-FOR EACH ROW
-SET @new_sum = @new_sum + NEW.num_value;
-UPDATE t1 SET num_value = 20;
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> DELETE statement; BEFORE timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 BEFORE DELETE ON t1
-FOR EACH ROW
-SET @old_sum = @old_sum + OLD.num_value;
-DELETE FROM t1;
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> DELETE statement; AFTER timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 AFTER DELETE ON t1
-FOR EACH ROW
-SET @old_sum = @old_sum + OLD.num_value;
-DELETE FROM t1;
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
-
----> connection: default
-use mysqltest_db1;
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-use mysqltest_db1;
-DROP TRIGGER trg1;
-SET @new_sum = 0;
-SET @old_sum = 0;
----> INSERT INTO statement; BEFORE timing
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
-FOR EACH ROW
-SET @new_sum = @new_sum + NEW.num_value;
-INSERT INTO t1 VALUES(4);
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> INSERT INTO statement; AFTER timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-FOR EACH ROW
-SET @new_sum = @new_sum + NEW.num_value;
-INSERT INTO t1 VALUES(5);
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> UPDATE statement; BEFORE timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 BEFORE UPDATE ON t1
-FOR EACH ROW
-SET @old_sum = @old_sum + OLD.num_value;
-UPDATE t1 SET num_value = 10;
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> UPDATE statement; AFTER timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 AFTER UPDATE ON t1
-FOR EACH ROW
-SET @new_sum = @new_sum + NEW.num_value;
-UPDATE t1 SET num_value = 20;
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> DELETE statement; BEFORE timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 BEFORE DELETE ON t1
-FOR EACH ROW
-SET @old_sum = @old_sum + OLD.num_value;
-DELETE FROM t1;
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
----> DELETE statement; AFTER timing
-DROP TRIGGER trg1;
-CREATE TRIGGER trg1 AFTER DELETE ON t1
-FOR EACH ROW
-SET @old_sum = @old_sum + OLD.num_value;
-DELETE FROM t1;
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
-
 ---> connection: wl2818_definer_con
 use mysqltest_db1;
 DROP TRIGGER trg1;
@@ -229,3 +126,187 @@ DROP USER mysqltest_inv@localhost;
 DROP DATABASE mysqltest_db1;
 Warnings:
 Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+use mysqltest_db1;
+CREATE TABLE t1(col CHAR(20));
+CREATE TABLE t2(col CHAR(20));
+CREATE TABLE t3(col CHAR(20));
+CREATE TABLE t4(col CHAR(20));
+CREATE USER mysqltest_u1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+GRANT SUPER ON *.* TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+SET @mysqltest_var = NULL;
+
+---> connection: default
+use mysqltest_db1;
+REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT SUPER ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT DELETE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
+FOR EACH ROW
+SET @mysqltest_var = 'Hello, world!';
+
+---> connection: default
+use mysqltest_db1;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+DROP TRIGGER t1_trg_err_1;
+CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+DROP TRIGGER t1_trg_err_2;
+CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
+FOR EACH ROW
+SET NEW.col = 't2_trg_before_insert';
+CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+DROP TRIGGER t3_trg_err_1;
+CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+DROP TRIGGER t3_trg_err_2;
+CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
+FOR EACH ROW
+SET NEW.col = 't4_trg_before_insert';
+
+---> connection: default
+use mysqltest_db1;
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
+FOR EACH ROW
+SET NEW.col = 't2_trg_err_1';
+DROP TRIGGER t2_trg_err_1;
+CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
+FOR EACH ROW
+SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t2_trg_err_2;
+CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
+FOR EACH ROW
+SET NEW.col = 't4_trg_err_1';
+DROP TRIGGER t4_trg_err_1;
+CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
+FOR EACH ROW
+SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t4_trg_err_2;
+
+---> connection: default
+use mysqltest_db1;
+REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+INSERT INTO t1 VALUES('line1');
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1'
+SELECT * FROM t1;
+col
+line1
+SELECT @mysqltest_var;
+@mysqltest_var
+NULL
+INSERT INTO t2 VALUES('line2');
+SELECT * FROM t2;
+col
+t2_trg_before_insert
+INSERT INTO t3 VALUES('t3_line1');
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3'
+SELECT * FROM t3;
+col
+t3_line1
+SELECT @mysqltest_var;
+@mysqltest_var
+NULL
+INSERT INTO t4 VALUES('t4_line2');
+SELECT * FROM t4;
+col
+t4_trg_before_insert
+
+---> connection: default
+use mysqltest_db1;
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+INSERT INTO t1 VALUES('line3');
+SELECT * FROM t1;
+col
+line1
+line3
+SELECT @mysqltest_var;
+@mysqltest_var
+line3
+INSERT INTO t2 VALUES('line4');
+ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2'
+SELECT * FROM t2;
+col
+t2_trg_before_insert
+INSERT INTO t3 VALUES('t3_line2');
+SELECT * FROM t3;
+col
+t3_line1
+t3_line2
+SELECT @mysqltest_var;
+@mysqltest_var
+t3_line2
+INSERT INTO t4 VALUES('t4_line2');
+ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4'
+SELECT * FROM t4;
+col
+t4_trg_before_insert
+DELETE FROM t1;
+SELECT @mysqltest_var;
+@mysqltest_var
+Hello, world!
+DROP USER mysqltest_u1@localhost;
+DROP DATABASE mysqltest_db1;
diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test
index deeaeacbccc..e28bf44a2cc 100644
--- a/mysql-test/t/trigger-grant.test
+++ b/mysql-test/t/trigger-grant.test
@@ -8,8 +8,6 @@
 #
 # Tests for WL#2818:
 #   - Check that triggers are executed under the authorization of the definer.
-#   - Check that if trigger contains NEW/OLD variables, the definer must have
-#     SELECT privilege on the subject table.
 #   - Check DEFINER clause of CREATE TRIGGER statement;
 #     - Check that SUPER privilege required to create a trigger with different
 #       definer.
@@ -18,6 +16,8 @@
 #     - Check that the definer of a trigger does not exist, the trigger will
 #       not be activated.
 #   - Check that SHOW TRIGGERS statement provides "Definer" column.
+#   - Check that if trigger contains NEW/OLD variables, the definer must have
+#     SELECT privilege on the subject table (aka BUG#15166/BUG#15196).
 #
 #  Let's also check that user name part of definer can contain '@' symbol (to
 #  check that triggers are not affected by BUG#13310 "incorrect user parsing
@@ -143,28 +143,28 @@ SELECT * FROM t1;
 SELECT * FROM t2;
 
 #
-# Check that if trigger contains NEW/OLD variables, the definer must have
-# SELECT/UPDATE privilege on the subject table:
-#   - drop the trigger;
-#   - create a new trigger, which will use NEW variable;
-#   - create another new trigger, which will use OLD variable;
-#   - revoke SELECT/UPDATE privilege on the first table from "definer";
-#   - insert a row into the first table;
-#   - analyze error code;
+# Check DEFINER clause of CREATE TRIGGER statement.
 #
-
+# NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement.
+# SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should
+# have SUPER privilege, so this test is meaningless right now.
 #
-# SELECT privilege.
+#   - Check that SUPER privilege required to create a trigger with different
+#     definer:
+#     - try to create a trigger with DEFINER="definer@localhost" under
+#       "invoker";
+#     - analyze error code;
+#   - Check that if the user specified as DEFINER does not exist, a warning is
+#     emitted:
+#     - create a trigger with DEFINER="non_existent_user@localhost" from
+#       "definer";
+#     - check that a warning emitted;
+#   - Check that the definer of a trigger does not exist, the trigger will not
+#     be activated:
+#     - activate just created trigger;
+#     - check error code;
 #
 
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
-
 --connection wl2818_definer_con
 --echo
 --echo ---> connection: wl2818_definer_con
@@ -173,95 +173,137 @@ use mysqltest_db1;
 
 DROP TRIGGER trg1;
 
-SET @new_sum = 0;
-SET @old_sum = 0;
+# Check that SUPER is required to specify different DEFINER.
+# NOTE: meaningless at the moment
 
-# INSERT INTO statement; BEFORE timing
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @new_sum = 0;
 
---echo ---> INSERT INTO statement; BEFORE timing
+# Create with non-existent user.
 
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
+CREATE DEFINER='mysqltest_nonexs'@'localhost'
+  TRIGGER trg2 AFTER INSERT ON t1
   FOR EACH ROW
-    SET @new_sum = @new_sum + NEW.num_value;
+    SET @new_sum = 0;
 
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(4);
+# Check that trg2 will not be activated.
 
-# INSERT INTO statement; AFTER timing
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES(6);
 
---echo ---> INSERT INTO statement; AFTER timing
+#
+# Check that SHOW TRIGGERS statement provides "Definer" column.
+#
+
+SHOW TRIGGERS;
+
+#
+# Check that weird definer values do not break functionality. I.e. check the
+# following definer values:
+#   - '';
+#   - '@';
+#   - '@abc@def@@';
+#   - '@hostname';
+#   - '@abc@def@@@hostname';
+#
 
 DROP TRIGGER trg1;
+DROP TRIGGER trg2;
 
-CREATE TRIGGER trg1 AFTER INSERT ON t1
+CREATE TRIGGER trg1 BEFORE INSERT ON t1
   FOR EACH ROW
-    SET @new_sum = @new_sum + NEW.num_value;
-
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(5);
+    SET @a = 1;
 
-# UPDATE statement; BEFORE timing
+CREATE TRIGGER trg2 AFTER INSERT ON t1
+  FOR EACH ROW
+    SET @a = 2;
 
---echo ---> UPDATE statement; BEFORE timing
+CREATE TRIGGER trg3 BEFORE UPDATE ON t1
+  FOR EACH ROW
+    SET @a = 3;
 
-DROP TRIGGER trg1;
+CREATE TRIGGER trg4 AFTER UPDATE ON t1
+  FOR EACH ROW
+    SET @a = 4;
 
-CREATE TRIGGER trg1 BEFORE UPDATE ON t1
+CREATE TRIGGER trg5 BEFORE DELETE ON t1
   FOR EACH ROW
-    SET @old_sum = @old_sum + OLD.num_value;
+    SET @a = 5;
 
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 10;
+--exec egrep -v '^definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG
+--exec echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQL_TEST_DIR/var/tmp/t1.TRG
+--exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG
 
-# UPDATE statement; AFTER timing
+--echo
 
---echo ---> UPDATE statement; AFTER timing
+SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
 
-DROP TRIGGER trg1;
+--echo
 
-CREATE TRIGGER trg1 AFTER UPDATE ON t1
-  FOR EACH ROW
-    SET @new_sum = @new_sum + NEW.num_value;
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
 
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 20;
+#
+# Cleanup
+#
 
-# DELETE statement; BEFORE timing
+--connection default
+--echo
+--echo ---> connection: default
 
---echo ---> DELETE statement; BEFORE timing
+DROP USER mysqltest_dfn@localhost;
+DROP USER mysqltest_inv@localhost;
 
-DROP TRIGGER trg1;
+DROP DATABASE mysqltest_db1;
 
-CREATE TRIGGER trg1 BEFORE DELETE ON t1
-  FOR EACH ROW
-    SET @old_sum = @old_sum + OLD.num_value;
+###########################################################################
+#
+# BUG#15166: Wrong update [was: select/update] permissions required to execute
+# triggers.
+#
+# BUG#15196: Wrong select permission required to execute triggers.
+#
+###########################################################################
 
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
+#
+# Prepare environment.
+#
 
-# DELETE statement; AFTER timing
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
 
---echo ---> DELETE statement; AFTER timing
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
 
-DROP TRIGGER trg1;
+CREATE DATABASE mysqltest_db1;
 
-CREATE TRIGGER trg1 AFTER DELETE ON t1
-  FOR EACH ROW
-    SET @old_sum = @old_sum + OLD.num_value;
+use mysqltest_db1;
 
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
+# Tables for tesing table-level privileges:
+CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger
+CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger
 
-#
-# UPDATE privilege
-#
-# NOTE: At the moment, UPDATE privilege is required if the trigger contains
-# NEW/OLD variables, whenever the trigger modifies them or not. Moreover,
-# UPDATE privilege is checked for whole table, not for individual columns.
-#
-# The following test cases should be changed when full support of UPDATE
-# privilege will be done.
-#
+# Tables for tesing column-level privileges:
+CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger
+CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger
+
+CREATE USER mysqltest_u1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+GRANT SUPER ON *.* TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost; # to allow connect
+
+SET @mysqltest_var = NULL;
+
+--connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1)
+
+# parsing (CREATE TRIGGER) time:
+#   - check that nor SELECT either UPDATE is required to execute triggger w/o
+#     NEW/OLD variables.
 
 --connection default
 --echo
@@ -269,207 +311,256 @@ DELETE FROM t1;
 
 use mysqltest_db1;
 
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
+REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
 
---connection wl2818_definer_con
+--connection bug15166_u1_con
 --echo
---echo ---> connection: wl2818_definer_con
+--echo ---> connection: bug15166_u1_con
 
 use mysqltest_db1;
 
-DROP TRIGGER trg1;
+CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
+  FOR EACH ROW
+    SET @mysqltest_var = 'Hello, world!';
 
-SET @new_sum = 0;
-SET @old_sum = 0;
+# parsing (CREATE TRIGGER) time:
+#   - check that UPDATE is not enough to read the value;
+#   - check that UPDATE is required to modify the value;
 
-# INSERT INTO statement; BEFORE timing
+--connection default
+--echo
+--echo ---> connection: default
 
---echo ---> INSERT INTO statement; BEFORE timing
+use mysqltest_db1;
 
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @new_sum = @new_sum + NEW.num_value;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
 
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(4);
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
 
-# INSERT INTO statement; AFTER timing
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
 
---echo ---> INSERT INTO statement; AFTER timing
+use mysqltest_db1;
 
-DROP TRIGGER trg1;
+# - table-level privileges
 
-CREATE TRIGGER trg1 AFTER INSERT ON t1
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
   FOR EACH ROW
-    SET @new_sum = @new_sum + NEW.num_value;
+    SET @mysqltest_var = NEW.col;
+DROP TRIGGER t1_trg_err_1;
 
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(5);
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
+  FOR EACH ROW
+    SET @mysqltest_var = OLD.col;
+DROP TRIGGER t1_trg_err_2;
 
-# UPDATE statement; BEFORE timing
+CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
+  FOR EACH ROW
+    SET NEW.col = 't2_trg_before_insert';
 
---echo ---> UPDATE statement; BEFORE timing
+# - column-level privileges
 
-DROP TRIGGER trg1;
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
+  FOR EACH ROW
+    SET @mysqltest_var = NEW.col;
+DROP TRIGGER t3_trg_err_1;
 
-CREATE TRIGGER trg1 BEFORE UPDATE ON t1
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
   FOR EACH ROW
-    SET @old_sum = @old_sum + OLD.num_value;
+    SET @mysqltest_var = OLD.col;
+DROP TRIGGER t3_trg_err_2;
 
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 10;
+CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
+  FOR EACH ROW
+    SET NEW.col = 't4_trg_before_insert';
 
-# UPDATE statement; AFTER timing
+# parsing (CREATE TRIGGER) time:
+#   - check that SELECT is required to read the value;
+#   - check that SELECT is not enough to modify the value;
 
---echo ---> UPDATE statement; AFTER timing
+--connection default
+--echo
+--echo ---> connection: default
 
-DROP TRIGGER trg1;
+use mysqltest_db1;
 
-CREATE TRIGGER trg1 AFTER UPDATE ON t1
-  FOR EACH ROW
-    SET @new_sum = @new_sum + NEW.num_value;
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
 
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 20;
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
 
-# DELETE statement; BEFORE timing
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
 
---echo ---> DELETE statement; BEFORE timing
+use mysqltest_db1;
 
-DROP TRIGGER trg1;
+# - table-level privileges
 
-CREATE TRIGGER trg1 BEFORE DELETE ON t1
-  FOR EACH ROW
-    SET @old_sum = @old_sum + OLD.num_value;
+CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
+ FOR EACH ROW
+  SET @mysqltest_var = NEW.col;
 
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
+CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
+ FOR EACH ROW
+  SET @mysqltest_var = OLD.col;
 
-# DELETE statement; AFTER timing
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
+ FOR EACH ROW
+  SET NEW.col = 't2_trg_err_1';
+DROP TRIGGER t2_trg_err_1;
 
---echo ---> DELETE statement; AFTER timing
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
+ FOR EACH ROW
+  SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t2_trg_err_2;
 
-DROP TRIGGER trg1;
+# - column-level privileges
 
-CREATE TRIGGER trg1 AFTER DELETE ON t1
+CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
   FOR EACH ROW
-    SET @old_sum = @old_sum + OLD.num_value;
-
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
+    SET @mysqltest_var = NEW.col;
 
-#
-# Check DEFINER clause of CREATE TRIGGER statement.
-#
-# NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement.
-# SUPER privilege is used instead. I.e., if one invokes CREATE TRIGGER, it should
-# have SUPER privilege, so this test is meaningless right now.
-#
-#   - Check that SUPER privilege required to create a trigger with different
-#     definer:
-#     - try to create a trigger with DEFINER="definer@localhost" under
-#       "invoker";
-#     - analyze error code;
-#   - Check that if the user specified as DEFINER does not exist, a warning is
-#     emitted:
-#     - create a trigger with DEFINER="non_existent_user@localhost" from
-#       "definer";
-#     - check that a warning emitted;
-#   - Check that the definer of a trigger does not exist, the trigger will not
-#     be activated:
-#     - activate just created trigger;
-#     - check error code;
-#
+CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
+  FOR EACH ROW
+    SET @mysqltest_var = OLD.col;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
+ FOR EACH ROW
+  SET NEW.col = 't4_trg_err_1';
+DROP TRIGGER t4_trg_err_1;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
+ FOR EACH ROW
+  SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t4_trg_err_2;
+
+# execution time:
+#   - check that UPDATE is not enough to read the value;
+#   - check that UPDATE is required to modify the value;
 
---connection wl2818_definer_con
+--connection default
 --echo
---echo ---> connection: wl2818_definer_con
+--echo ---> connection: default
 
 use mysqltest_db1;
 
-DROP TRIGGER trg1;
+REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
 
-# Check that SUPER is required to specify different DEFINER.
-# NOTE: meaningless at the moment
+REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
 
-CREATE DEFINER='mysqltest_inv'@'localhost'
-  TRIGGER trg1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @new_sum = 0;
+# - table-level privileges
 
-# Create with non-existent user.
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES('line1');
 
-CREATE DEFINER='mysqltest_nonexs'@'localhost'
-  TRIGGER trg2 AFTER INSERT ON t1
-  FOR EACH ROW
-    SET @new_sum = 0;
+SELECT * FROM t1;
+SELECT @mysqltest_var;
 
-# Check that trg2 will not be activated.
+INSERT INTO t2 VALUES('line2');
 
---error ER_SPECIFIC_ACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(6);
+SELECT * FROM t2;
 
-#
-# Check that SHOW TRIGGERS statement provides "Definer" column.
-#
+# - column-level privileges
 
-SHOW TRIGGERS;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t3 VALUES('t3_line1');
 
-#
-# Check that weird definer values do not break functionality. I.e. check the
-# following definer values:
-#   - '';
-#   - '@';
-#   - '@abc@def@@';
-#   - '@hostname';
-#   - '@abc@def@@@hostname';
-#
+SELECT * FROM t3;
+SELECT @mysqltest_var;
 
-DROP TRIGGER trg1;
-DROP TRIGGER trg2;
+INSERT INTO t4 VALUES('t4_line2');
 
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @a = 1;
+SELECT * FROM t4;
 
-CREATE TRIGGER trg2 AFTER INSERT ON t1
-  FOR EACH ROW
-    SET @a = 2;
+# execution time:
+#   - check that SELECT is required to read the value;
+#   - check that SELECT is not enough to modify the value;
 
-CREATE TRIGGER trg3 BEFORE UPDATE ON t1
-  FOR EACH ROW
-    SET @a = 3;
+--connection default
+--echo
+--echo ---> connection: default
 
-CREATE TRIGGER trg4 AFTER UPDATE ON t1
-  FOR EACH ROW
-    SET @a = 4;
+use mysqltest_db1;
 
-CREATE TRIGGER trg5 BEFORE DELETE ON t1
-  FOR EACH ROW
-    SET @a = 5;
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
 
---exec egrep -v '^definers=' $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG > $MYSQL_TEST_DIR/var/tmp/t1.TRG
---exec echo "definers='' '@' '@abc@def@@' '@hostname' '@abcdef@@@hostname'" >> $MYSQL_TEST_DIR/var/tmp/t1.TRG
---exec mv $MYSQL_TEST_DIR/var/tmp/t1.TRG $MYSQL_TEST_DIR/var/master-data/mysqltest_db1/t1.TRG
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
 
---echo
+# - table-level privileges
 
-SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+INSERT INTO t1 VALUES('line3');
 
---echo
+SELECT * FROM t1;
+SELECT @mysqltest_var;
 
-SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES('line4');
+
+SELECT * FROM t2;
+
+# - column-level privileges
+
+INSERT INTO t3 VALUES('t3_line2');
+
+SELECT * FROM t3;
+SELECT @mysqltest_var;
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t4 VALUES('t4_line2');
+
+SELECT * FROM t4;
+
+# execution time:
+#   - check that nor SELECT either UPDATE is required to execute triggger w/o
+#     NEW/OLD variables.
+
+DELETE FROM t1;
+
+SELECT @mysqltest_var;
 
 #
-# Cleanup
+# Cleanup.
 #
 
---connection default
---echo
---echo ---> connection: default
-
-DROP USER mysqltest_dfn@localhost;
-DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_u1@localhost;
 
 DROP DATABASE mysqltest_db1;