bk commit into 5.1 tree (anozdrin:1.2134)

[Alexander Nozdrin]

Below is the list of changes that have just been committed into a local
5.1 repository of alik. When alik does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2134 06/02/06 15:23:17 anozdrin@stripped +6 -0
  Merge mysql.com:/home/alik/Documents/AllProgs/MySQL/devel/5.0-rev-1.2004.2.1
  into  mysql.com:/home/alik/Documents/AllProgs/MySQL/devel/5.1-tree

  sql/sql_trigger.cc
    1.45 06/02/06 15:23:14 anozdrin@stripped +21 -13
    Merged.

  mysql-test/t/trigger-grant.test
    1.5 06/02/06 15:23:14 anozdrin@stripped +26 -33
    Merged.

  sql/sql_yacc.yy
    1.451 06/02/06 15:18:27 anozdrin@stripped +0 -0
    Auto merged

  sql/item.h
    1.190 06/02/06 15:18:27 anozdrin@stripped +0 -0
    Auto merged

  sql/item.cc
    1.166 06/02/06 15:18:27 anozdrin@stripped +0 -0
    Auto merged

  mysql-test/r/trigger-grant.result
    1.4 06/02/06 15:18:27 anozdrin@stripped +0 -0
    Auto merged

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	anozdrin
# Host:	station.home
# Root:	/home/alik/Documents/AllProgs/MySQL/devel/5.1-tree/RESYNC

--- 1.165/sql/item.cc	2006-02-02 16:55:22 +03:00
+++ 1.166/sql/item.cc	2006-02-06 15:18:27 +03:00
@@ -5257,6 +5257,7 @@
     setup_field()
       thd   - current thread context
       table - table of trigger (and where we looking for fields)
+      table_grant_info - GRANT_INFO of the subject table
 
   NOTE
     This function does almost the same as fix_fields() for Item_field
@@ -5270,7 +5271,8 @@
     table of trigger which uses this item.
 */
 
-void Item_trigger_field::setup_field(THD *thd, TABLE *table)
+void Item_trigger_field::setup_field(THD *thd, TABLE *table,
+                                     GRANT_INFO *table_grant_info)
 {
   bool save_set_query_id= thd->set_query_id;
 
@@ -5284,6 +5286,7 @@
                             0, &field_idx);
   thd->set_query_id= save_set_query_id;
   triggers= table->triggers;
+  table_grants= table_grant_info;
 }
 
 
@@ -5302,22 +5305,42 @@
     Since trigger is object tightly associated with TABLE object most
     of its set up can be performed during trigger loading i.e. trigger
     parsing! So we have little to do in fix_fields. :)
-    FIXME may be we still should bother about permissions here.
   */
+
   DBUG_ASSERT(fixed == 0);
 
+  /* Set field. */
+
   if (field_idx != (uint)-1)
   {
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    /*
+      Check access privileges for the subject table. We check privileges only
+      in runtime.
+    */
+
+    if (table_grants)
+    {
+      table_grants->want_privilege=
+        access_type == AT_READ ? SELECT_ACL : UPDATE_ACL;
+
+      if (check_grant_column(thd, table_grants, triggers->table->s->db,
+                             triggers->table->s->table_name, field_name,
+                             strlen(field_name), thd->security_ctx))
+        return TRUE;
+    }
+#endif // NO_EMBEDDED_ACCESS_CHECKS
+
     field= (row_version == OLD_ROW) ? triggers->old_field[field_idx] :
                                       triggers->new_field[field_idx];
     set_field(field);
     fixed= 1;
-    return 0;
+    return FALSE;
   }
 
   my_error(ER_BAD_FIELD_ERROR, MYF(0), field_name,
            (row_version == NEW_ROW) ? "NEW" : "OLD");
-  return 1;
+  return TRUE;
 }
 
 

--- 1.189/sql/item.h	2006-01-20 19:38:06 +03:00
+++ 1.190/sql/item.h	2006-02-06 15:18:27 +03:00
@@ -2172,6 +2172,8 @@
   /* Is this item represents row from NEW or OLD row ? */
   enum row_version_type {OLD_ROW, NEW_ROW};
   row_version_type row_version;
+  /* Is this item used for reading or updating the value? */
+  enum access_types { AT_READ = 0x1, AT_UPDATE = 0x2 };
   /* Next in list of all Item_trigger_field's in trigger */
   Item_trigger_field *next_trg_field;
   /* Index of the field in the TABLE::field array */
@@ -2181,18 +2183,24 @@
 
   Item_trigger_field(Name_resolution_context *context_arg,
                      row_version_type row_ver_arg,
-                     const char *field_name_arg)
+                     const char *field_name_arg,
+                     access_types access_type_arg)
     :Item_field(context_arg,
                (const char *)NULL, (const char *)NULL, field_name_arg),
-     row_version(row_ver_arg), field_idx((uint)-1)
+     row_version(row_ver_arg), field_idx((uint)-1),
+     access_type(access_type_arg), table_grants(NULL)
   {}
-  void setup_field(THD *thd, TABLE *table);
+  void setup_field(THD *thd, TABLE *table, GRANT_INFO *table_grant_info);
   enum Type type() const { return TRIGGER_FIELD_ITEM; }
   bool eq(const Item *item, bool binary_cmp) const;
   bool fix_fields(THD *, Item **);
   void print(String *str);
   table_map used_tables() const { return (table_map)0L; }
   void cleanup();
+
+private:
+  access_types access_type;
+  GRANT_INFO *table_grants;
 };
 
 

--- 1.450/sql/sql_yacc.yy	2006-02-02 23:20:20 +03:00
+++ 1.451/sql/sql_yacc.yy	2006-02-06 15:18:27 +03:00
@@ -9043,7 +9043,8 @@
                                                   new_row ?
                                                   Item_trigger_field::NEW_ROW:
                                                   Item_trigger_field::OLD_ROW,
-                                                  $3.str)))
+                                                  $3.str,
+                                                  Item_trigger_field::AT_READ)))
               YYABORT;
 
             /*
@@ -9727,7 +9728,9 @@
 
             if (!(trg_fld= new Item_trigger_field(Lex->current_context(),
                                                   Item_trigger_field::NEW_ROW,
-                                                  $2.base_name.str)) ||
+                                                  $2.base_name.str,
+                                                  Item_trigger_field::AT_UPDATE)
+                                                  ) ||
                 !(sp_fld= new sp_instr_set_trigger_field(lex->sphead->
                           	                         instructions(),
                                 	                 lex->spcont,

--- 1.3/mysql-test/r/trigger-grant.result	2006-01-24 20:15:08 +03:00
+++ 1.4/mysql-test/r/trigger-grant.result	2006-02-06 15:18:27 +03:00
@@ -7,12 +7,57 @@
 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;
 
 ---> connection: wl2818_definer_con
 CREATE TABLE t1(num_value INT);
 CREATE TABLE t2(user_str TEXT);
+
+---> connection: default
+GRANT INSERT, DELETE ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT INSERT, DELETE ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+
+---> connection: default
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(CURRENT_USER());
+ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
+
+---> connection: default
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(CURRENT_USER());
+
+---> connection: default
+REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+DROP TRIGGER trg1;
+ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
+
+---> connection: wl2818_definer_con
+INSERT INTO t1 VALUES(0);
+ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
+
+---> connection: default
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+INSERT INTO t1 VALUES(0);
+DROP TRIGGER trg1;
+DELETE FROM t1;
+DELETE FROM t2;
+
+---> connection: default
+REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
 CREATE TRIGGER trg1 AFTER INSERT ON t1
 FOR EACH ROW
 INSERT INTO t2 VALUES(CURRENT_USER());
@@ -72,6 +117,17 @@
   TRIGGER trg1 BEFORE INSERT ON t1
 FOR EACH ROW
 SET @new_sum = 0;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+
+---> connection: default
+use mysqltest_db1;
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @new_sum = 0;
 CREATE DEFINER='mysqltest_nonexs'@'localhost'
   TRIGGER trg2 AFTER INSERT ON t1
 FOR EACH ROW
@@ -79,7 +135,6 @@
 Warnings:
 Note	1449	There is no 'mysqltest_nonexs'@'localhost' registered
 INSERT INTO t1 VALUES(6);
-ERROR 42000: Access denied; you need the SUPER privilege for this operation
 SHOW TRIGGERS;
 Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer
 trg1	INSERT	t1	SET @new_sum = 0	BEFORE	NULL		mysqltest_inv@localhost

--- 1.4/mysql-test/t/trigger-grant.test	2006-02-01 13:28:40 +03:00
+++ 1.5/mysql-test/t/trigger-grant.test	2006-02-06 15:23:14 +03:00
@@ -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
@@ -255,223 +255,6 @@
 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;
-#
-
-#
-# SELECT privilege.
-#
-
---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
-
-use mysqltest_db1;
-
-DROP TRIGGER trg1;
-
-SET @new_sum = 0;
-SET @old_sum = 0;
-
-# INSERT INTO statement; BEFORE timing
-
---echo ---> INSERT INTO statement; BEFORE timing
-
-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(4);
-
-# INSERT INTO statement; AFTER timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(5);
-
-# UPDATE statement; BEFORE timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 10;
-
-# UPDATE statement; AFTER timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 20;
-
-# DELETE statement; BEFORE timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
-
-# DELETE statement; AFTER timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
-
-#
-# 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.
-#
-
---connection default
---echo
---echo ---> 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
---echo
---echo ---> connection: wl2818_definer_con
-
-use mysqltest_db1;
-
-DROP TRIGGER trg1;
-
-SET @new_sum = 0;
-SET @old_sum = 0;
-
-# INSERT INTO statement; BEFORE timing
-
---echo ---> INSERT INTO statement; BEFORE timing
-
-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(4);
-
-# INSERT INTO statement; AFTER timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(5);
-
-# UPDATE statement; BEFORE timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 10;
-
-# UPDATE statement; AFTER timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-UPDATE t1 SET num_value = 20;
-
-# DELETE statement; BEFORE timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
-
-# DELETE statement; AFTER timing
-
---echo ---> 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;
-
---error ER_TABLEACCESS_DENIED_ERROR
-DELETE FROM t1;
-
-#
 # Check DEFINER clause of CREATE TRIGGER statement.
 #
 #   - Check that SUPER privilege required to create a trigger with different
@@ -601,5 +384,312 @@
 
 DROP USER mysqltest_dfn@localhost;
 DROP USER mysqltest_inv@localhost;
+
+DROP DATABASE mysqltest_db1;
+
+
+###########################################################################
+#
+# BUG#15166: Wrong update [was: select/update] permissions required to execute
+# triggers.
+#
+# BUG#15196: Wrong select permission required to execute triggers.
+#
+###########################################################################
+
+#
+# Prepare environment.
+#
+
+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;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+use mysqltest_db1;
+
+# 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
+
+# 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 TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+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
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+
+--connection bug15166_u1_con
+--echo
+--echo ---> 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!';
+
+# parsing (CREATE TRIGGER) time:
+#   - check that UPDATE is not enough to read the value;
+#   - check that UPDATE is required to modify the value;
+
+--connection default
+--echo
+--echo ---> 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
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+# - table-level privileges
+
+# 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 @mysqltest_var = NEW.col;
+DROP TRIGGER t1_trg_err_1;
+
+# 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;
+
+CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
+  FOR EACH ROW
+    SET NEW.col = 't2_trg_before_insert';
+
+# - column-level privileges
+
+# 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;
+
+# 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 @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';
+
+# parsing (CREATE TRIGGER) time:
+#   - check that SELECT is required to read the value;
+#   - check that SELECT is not enough to modify the value;
+
+--connection default
+--echo
+--echo ---> 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
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+# - table-level privileges
+
+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;
+
+# 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;
+
+# 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;
+
+# - column-level privileges
+
+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;
+
+# 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 default
+--echo
+--echo ---> 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;
+
+# - table-level privileges
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES('line1');
+
+SELECT * FROM t1;
+SELECT @mysqltest_var;
+
+INSERT INTO t2 VALUES('line2');
+
+SELECT * FROM t2;
+
+# - column-level privileges
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t3 VALUES('t3_line1');
+
+SELECT * FROM t3;
+SELECT @mysqltest_var;
+
+INSERT INTO t4 VALUES('t4_line2');
+
+SELECT * FROM t4;
+
+# execution time:
+#   - check that SELECT is required to read the value;
+#   - check that SELECT is not enough to modify the value;
+
+--connection default
+--echo
+--echo ---> 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;
+
+# - table-level privileges
+
+INSERT INTO t1 VALUES('line3');
+
+SELECT * FROM t1;
+SELECT @mysqltest_var;
+
+--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.
+#
+
+DROP USER mysqltest_u1@localhost;
 
 DROP DATABASE mysqltest_db1;

--- 1.44/sql/sql_trigger.cc	2006-02-01 13:28:40 +03:00
+++ 1.45/sql/sql_trigger.cc	2006-02-06 15:23:14 +03:00
@@ -390,7 +390,12 @@
   for (trg_field= (Item_trigger_field *)(lex->trg_table_fields.first);
        trg_field; trg_field= trg_field->next_trg_field)
   {
-    trg_field->setup_field(thd, table);
+    /*
+      NOTE: now we do not check privileges at CREATE TRIGGER time. This will
+      be changed in the future.
+    */
+    trg_field->setup_field(thd, table, NULL);
+
     if (!trg_field->fixed &&
         trg_field->fix_fields(thd, (Item **)0))
       return 1;
@@ -826,8 +831,7 @@
 
       char *trg_name_buff;
       List_iterator_fast<ulonglong> itm(triggers->definition_modes_list);
-      List_iterator_fast<LEX_STRING> it_definer(triggers->
-                                                definers_list);
+      List_iterator_fast<LEX_STRING> it_definer(triggers->definers_list);
       LEX *old_lex= thd->lex, lex;
       sp_rcontext *save_spcont= thd->spcont;
       ulong save_sql_mode= thd->variables.sql_mode;
@@ -842,6 +846,7 @@
       {
         trg_sql_mode= itm++;
         LEX_STRING *trg_definer= it_definer++;
+
         thd->variables.sql_mode= (ulong)*trg_sql_mode;
         lex_start(thd, (uchar*)trg_create_str->str, trg_create_str->length);
 
@@ -915,11 +920,11 @@
                (Item_trigger_field *)(lex.trg_table_fields.first);
              trg_field;
              trg_field= trg_field->next_trg_field)
-          trg_field->setup_field(thd, table);
-
-        triggers->m_spec_var_used[lex.trg_chistics.event]
-          [lex.trg_chistics.action_time]=
-          lex.trg_table_fields.first ? TRUE : FALSE;
+        {
+          trg_field->setup_field(thd, table, 
+            &triggers->subject_table_grants[lex.trg_chistics.event]
+                                           [lex.trg_chistics.action_time]);
+        }
 
         lex_end(&lex);
       }
@@ -1159,38 +1164,30 @@
     if (sp_change_security_context(thd, sp_trigger, &save_ctx))
       return TRUE;
 
-    {
-      TABLE_LIST table_list, **save_query_tables_own_last;
-      ulong wanted_access = TRIGGER_ACL;
-      
-      bzero((char *) &table_list, sizeof (table_list));
-      table_list.db= (char *) table->s->db.str;
-      table_list.db_length= table->s->db.length;
-      table_list.table_name= table->s->table_name.str;
-      table_list.table_name_length= table->s->table_name.length;
-      table_list.alias= (char *) table->alias;
-      table_list.table= table;
-      save_query_tables_own_last= thd->lex->query_tables_own_last;
-      thd->lex->query_tables_own_last= 0;
-      
-      /*
-         If the trigger uses special variables (NEW/OLD), check that we have
-         SELECT and UPDATE privileges on the subject table.
-       */
+    /*
+      Fetch information about table-level privileges to GRANT_INFO structure for
+      subject table. Check of privileges that will use it and information about
+      column-level privileges will happen in Item_trigger_field::fix_fields().
+    */
 
-      if (is_special_var_used(event, time_type))
-        wanted_access|= SELECT_ACL | UPDATE_ACL;
+    fill_effective_table_privileges(thd,
+                                    &subject_table_grants[event][time_type],
+                                    table->s->db.str,
table->s->table_name.str);
 
-      err_status= check_table_access(thd, wanted_access, &table_list, 0);
+    /* Check that the definer has TRIGGER privilege on the subject table. */
 
-      thd->lex->query_tables_own_last= save_query_tables_own_last;
-      if (err_status)
-      {
-        sp_restore_security_context(thd, save_ctx);
-        return TRUE;
-      }
+    if (!(subject_table_grants[event][time_type].privilege & TRIGGER_ACL))
+    {
+      char priv_desc[128];
+      get_privilege_desc(priv_desc, sizeof(priv_desc), TRIGGER_ACL);
+
+      my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0), priv_desc,
+               thd->security_ctx->priv_user, thd->security_ctx->host_or_ip,
+               table->s->table_name);
+
+      sp_restore_security_context(thd, save_ctx);
+      return TRUE;
     }
-    
 #endif // NO_EMBEDDED_ACCESS_CHECKS
 
     thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER);