Fix for BUG#9412: Triggers: should have trigger privilege.

Implement table-level TRIGGER privilege to control access to triggers.
Before this path global SUPER privilege was used for this purpose, that
was the big security problem.

In details, before this patch SUPER privilege was required:
  - for the user at CREATE TRIGGER time to create a new trigger;
  - for the user at DROP TRIGGER time to drop the existing trigger;
  - for the definer at trigger activation time to execute the trigger (if the
    definer loses SUPER privilege, all its triggers become unavailable);

This patch changes the behaviour in the following way:
  - TRIGGER privilege on the subject table for trigger is required:
    - for the user at CREATE TRIGGER time to create a new trigger;
    - for the user at DROP TRIGGER time to drop the existing trigger;
    - for the definer at trigger activation time to execute the trigger
      (if the definer loses TRIGGER privilege on the subject table, all its
      triggers on this table become unavailable).
  - SUPER privilege is still required:
    - for the user at CREATE TRIGGER time to explicitly set the trigger
      definer to the user other than CURRENT_USER().

When the server works with database of the previous version (w/o TRIGGER
privilege), or if the database is being upgraded from the previous versions,
TRIGGER privilege is granted to whose users, who have CREATE privilege.


mysql-test/r/grant.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/r/information_schema.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/r/lowercase_table_grant.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/r/ps.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/r/sp.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/r/trigger-compat.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/r/trigger-grant.result:
  Updated the result file after adding TRIGGER privilege.
mysql-test/t/trigger-compat.test:
  Grant table-level TRIGGER privilege instead of global SUPER one.
mysql-test/t/trigger-grant.test:
  1. Grant table-level TRIGGER privilege instead of global SUPER one.
  2. Updated the test case to check that SUPER is required to specify
  the user other than the current as a definer.
scripts/mysql_create_system_tables.sh:
  Added TRIGGER privilege.
scripts/mysql_fix_privilege_tables.sql:
  Added TRIGGER privilege.
sql/sql_acl.cc:
  Added TRIGGER privilege.
sql/sql_acl.h:
  Added TRIGGER privilege.
sql/sql_show.cc:
  Added TRIGGER privilege.
sql/sql_trigger.cc:
  Check TRIGGER privilege instead of SUPER.
sql/sql_yacc.yy:
  Added TRIGGER privilege.

2 files changed, 141 insertions, 12 deletions

diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test
index c2acc235135..5b2483cc3ea 100644
--- a/mysql-test/t/trigger-compat.test
+++ b/mysql-test/t/trigger-compat.test
@@ -35,8 +35,7 @@ CREATE DATABASE mysqltest_db1;
 CREATE USER mysqltest_dfn@localhost;
 CREATE USER mysqltest_inv@localhost;
 
-GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
-GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost;
 
 #
 # Create a table and the first trigger.
diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test
index deeaeacbccc..dfa3c3687f5 100644
--- a/mysql-test/t/trigger-grant.test
+++ b/mysql-test/t/trigger-grant.test
@@ -44,9 +44,124 @@ CREATE DATABASE mysqltest_db1;
 CREATE USER mysqltest_dfn@localhost;
 CREATE USER mysqltest_inv@localhost;
 
-GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
 GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
 
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+CREATE TABLE t1(num_value INT);
+CREATE TABLE t2(user_str TEXT);
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT INSERT, DELETE ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT INSERT, DELETE ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+
+#
+# Check that the user must have TRIGGER privilege to create a trigger.
+#
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2 VALUES(CURRENT_USER());
+
+--disconnect wl2818_definer_con
+
+#
+# Check that the user must have TRIGGER privilege to drop a trigger.
+#
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2 VALUES(CURRENT_USER());
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+--error ER_TABLEACCESS_DENIED_ERROR
+DROP TRIGGER trg1;
+
+--disconnect wl2818_definer_con
+
+#
+# Check that the definer must have TRIGGER privilege to activate a trigger.
+#
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES(0);
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+INSERT INTO t1 VALUES(0);
+
+# Cleanup for further tests.
+DROP TRIGGER trg1;
+DELETE FROM t1;
+DELETE FROM t2;
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
+
 #
 # Check that triggers are executed under the authorization of the definer:
 #   - create two tables under "definer";
@@ -64,9 +179,6 @@ GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
 --echo
 --echo ---> connection: wl2818_definer_con
 
-CREATE TABLE t1(num_value INT);
-CREATE TABLE t2(user_str TEXT);
-
 CREATE TRIGGER trg1 AFTER INSERT ON t1
   FOR EACH ROW
     INSERT INTO t2 VALUES(CURRENT_USER());
@@ -362,10 +474,6 @@ DELETE FROM t1;
 #
 # 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
@@ -391,7 +499,26 @@ use mysqltest_db1;
 DROP TRIGGER trg1;
 
 # Check that SUPER is required to specify different DEFINER.
-# NOTE: meaningless at the moment
+
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @new_sum = 0;
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+--disconnect wl2818_definer_con
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
 
 CREATE DEFINER='mysqltest_inv'@'localhost'
   TRIGGER trg1 BEFORE INSERT ON t1
@@ -407,7 +534,10 @@ CREATE DEFINER='mysqltest_nonexs'@'localhost'
 
 # Check that trg2 will not be activated.
 
---error ER_SPECIFIC_ACCESS_DENIED_ERROR
+# --error ER_SPECIFIC_ACCESS_DENIED_ERROR
+#
+# TODO: Due to the BUG#13198(SP executes if definer does not exist) the
+# following statement does not fail as it should.
 INSERT INTO t1 VALUES(6);
 
 #