List:Commits« Previous MessageNext Message »
From:Alexander Nozdrin Date:December 22 2005 9:44pm
Subject:bk commit into 5.0 tree (anozdrin:1.1995) BUG#15196
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 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.1995 05/12/23 00:44:02 anozdrin@stripped +7 -0
  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.
  
  As a side effect of this fix, SELECT/UPDATE privileges are checked also
  at parsing time (not only at execution time). This happens because fix_fields()
  is called at parsing time in order to check that correct column reference
  is used.

  sql/sql_yacc.yy
    1.446 05/12/23 00:43:54 anozdrin@stripped +5 -2
    Specify access type for trigger fields.

  sql/sql_trigger.h
    1.18 05/12/23 00:43:54 anozdrin@stripped +0 -11
    Remove "special variable" technique.

  sql/sql_trigger.cc
    1.37 05/12/23 00:43:54 anozdrin@stripped +0 -27
    Remove "special variable" technique.

  sql/item.h
    1.184 05/12/23 00:43:54 anozdrin@stripped +15 -2
    Add a flag to specify access type for trigger field.

  sql/item.cc
    1.201 05/12/23 00:43:54 anozdrin@stripped +25 -3
    Check appropriate (SELECT/UPDATE) privilege in fix_fields().

  mysql-test/t/trigger-grant.test
    1.3 05/12/23 00:43:54 anozdrin@stripped +156 -217
    Add test cases for BUG#15166 and BUG#15196

  mysql-test/r/trigger-grant.result
    1.2 05/12/23 00:43:53 anozdrin@stripped +87 -103
    Update the result file.

# 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:	booka.home
# Root:	/home/alik/MySQL/devel/5.0-bug15166

--- 1.200/sql/item.cc	2005-12-12 09:19:41 +03:00
+++ 1.201/sql/item.cc	2005-12-23 00:43:54 +03:00
@@ -5254,22 +5254,44 @@
     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);
 
+  /* Check access privileges for the subject table. */
+
+  TABLE_LIST table_list;
+  bzero((char *) &table_list, sizeof (table_list));
+  table_list.db= (char *) triggers->table->s->db;
+  table_list.db_length= strlen(table_list.db);
+  table_list.table_name= (char *) triggers->table->s->table_name;
+  table_list.table_name_length= strlen(table_list.table_name);
+  table_list.alias= (char *) triggers->table->alias;
+  table_list.table= triggers->table;
+
+  ulong needed_access = 0;
+  if (m_access & AT_READ)
+    needed_access|= SELECT_ACL;
+  if (m_access & AT_UPDATE)
+    needed_access|= UPDATE_ACL;
+
+  if (check_table_access(thd, needed_access, &table_list, 0))
+    return TRUE;
+
+  /* Set field. */
+
   if (field_idx != (uint)-1)
   {
     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.183/sql/item.h	2005-12-11 10:30:51 +03:00
+++ 1.184/sql/item.h	2005-12-23 00:43:54 +03:00
@@ -2130,6 +2130,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 */
@@ -2139,10 +2141,11 @@
 
   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_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), m_access(access_arg)
   {}
   void setup_field(THD *thd, TABLE *table);
   enum Type type() const { return TRIGGER_FIELD_ITEM; }
@@ -2151,7 +2154,17 @@
   void print(String *str);
   table_map used_tables() const { return (table_map)0L; }
   void cleanup();
+  inline access_types access() const;
+
+private:
+  access_types m_access;
 };
+
+
+inline Item_trigger_field::access_types Item_trigger_field::access() const
+{
+  return m_access;
+}
 
 
 class Item_cache: public Item

--- 1.445/sql/sql_yacc.yy	2005-12-11 10:30:53 +03:00
+++ 1.446/sql/sql_yacc.yy	2005-12-23 00:43:54 +03:00
@@ -7274,7 +7274,8 @@
                                                   new_row ?
                                                   Item_trigger_field::NEW_ROW:
                                                   Item_trigger_field::OLD_ROW,
-                                                  $3.str)))
+                                                  $3.str,
+                                                  Item_trigger_field::AT_READ)))
               YYABORT;
 
             /*
@@ -7900,7 +7901,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.1/mysql-test/r/trigger-grant.result	2005-11-10 22:24:55 +03:00
+++ 1.2/mysql-test/r/trigger-grant.result	2005-12-23 00:43:53 +03:00
@@ -65,109 +65,6 @@
 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;
@@ -236,3 +133,90 @@
 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 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 UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER trg_err_1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+CREATE TRIGGER trg2 BEFORE INSERT ON t2
+FOR EACH ROW
+SET NEW.col = 'trg2';
+
+---> 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;
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER trg1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+CREATE TRIGGER trg_err_2 BEFORE UPDATE ON t2
+FOR EACH ROW
+SET NEW.col = 'trg_err_2';
+ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for table 't2'
+
+---> 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;
+INSERT INTO t1 VALUES('line1');
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+SELECT * FROM t1;
+col
+SELECT @mysqltest_var;
+@mysqltest_var
+NULL
+INSERT INTO t2 VALUES('line2');
+SELECT * FROM t2;
+col
+trg2
+
+---> 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;
+INSERT INTO t1 VALUES('line3');
+SELECT * FROM t1;
+col
+line3
+SELECT @mysqltest_var;
+@mysqltest_var
+line3
+INSERT INTO t2 VALUES('line4');
+ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for table 't2'
+SELECT * FROM t2;
+col
+trg2
+DROP USER mysqltest_u1@localhost;
+DROP DATABASE mysqltest_db1;

--- 1.2/mysql-test/t/trigger-grant.test	2005-12-08 23:38:30 +03:00
+++ 1.3/mysql-test/t/trigger-grant.test	2005-12-23 00:43:54 +03:00
@@ -143,223 +143,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.
 #
 # NOTE: there is no dedicated TRIGGER privilege for CREATE TRIGGER statement.
@@ -471,5 +254,161 @@
 
 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;
+
+CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger
+CREATE TABLE t2(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 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;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CREATE TRIGGER trg_err_1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @mysqltest_var = NEW.col;
+
+CREATE TRIGGER trg2 BEFORE INSERT ON t2
+  FOR EACH ROW
+    SET NEW.col = 'trg2';
+
+# parsing 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;
+
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+CREATE TRIGGER trg1 BEFORE INSERT ON t1
+ FOR EACH ROW
+  SET @mysqltest_var = NEW.col;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CREATE TRIGGER trg_err_2 BEFORE UPDATE ON t2
+ FOR EACH ROW
+  SET NEW.col = '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;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES('line1');
+
+SELECT * FROM t1;
+SELECT @mysqltest_var;
+
+INSERT INTO t2 VALUES('line2');
+
+SELECT * FROM t2;
+
+# 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;
+
+INSERT INTO t1 VALUES('line3');
+
+SELECT * FROM t1;
+SELECT @mysqltest_var;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES('line4');
+
+SELECT * FROM t2;
+
+#
+# Cleanup.
+#
+
+DROP USER mysqltest_u1@localhost;
 
 DROP DATABASE mysqltest_db1;

--- 1.36/sql/sql_trigger.cc	2005-12-07 17:01:08 +03:00
+++ 1.37/sql/sql_trigger.cc	2005-12-23 00:43:54 +03:00
@@ -904,10 +904,6 @@
              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;
-
         lex_end(&lex);
       }
       thd->db= save_db.str;
@@ -1157,29 +1153,6 @@
       return TRUE;
     }
 
-    /*
-      If the trigger uses special variables (NEW/OLD), check that we have
-      SELECT and UPDATE privileges on the subject table.
-    */
-    
-    if (is_special_var_used(event, time_type))
-    {
-      TABLE_LIST table_list;
-      bzero((char *) &table_list, sizeof (table_list));
-      table_list.db= (char *) table->s->db;
-      table_list.db_length= strlen(table_list.db);
-      table_list.table_name= (char *) table->s->table_name;
-      table_list.table_name_length= strlen(table_list.table_name);
-      table_list.alias= (char *) table->alias;
-      table_list.table= table;
-
-      if (check_table_access(thd, SELECT_ACL | UPDATE_ACL, &table_list, 0))
-      {
-        sp_restore_security_context(thd, save_ctx);
-        return TRUE;
-      }
-    }
-    
 #endif // NO_EMBEDDED_ACCESS_CHECKS
 
     thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER);

--- 1.17/sql/sql_trigger.h	2005-12-07 12:36:24 +03:00
+++ 1.18/sql/sql_trigger.h	2005-12-23 00:43:54 +03:00
@@ -55,12 +55,6 @@
   */
   LEX_STRING        sroutines_key;
 
-  /*
-    is_special_var_used specifies whether trigger body contains special
-    variables (NEW/OLD).
-  */
-  bool m_spec_var_used[TRG_EVENT_MAX][TRG_ACTION_MAX];
-
 public:
   /*
     Field responsible for storing triggers definitions in file.
@@ -107,11 +101,6 @@
   bool has_before_update_triggers()
   {
     return test(bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE]);
-  }
-
-  inline bool is_special_var_used(int event, int action_time) const
-  {
-    return m_spec_var_used[event][action_time];
   }
 
   void set_table(TABLE *new_table);
Thread
bk commit into 5.0 tree (anozdrin:1.1995) BUG#15196Alexander Nozdrin22 Dec