bk commit into 5.0 tree (kroki:1.2116) BUG#14635

[kroki]

Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash 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.2116 06/05/09 13:28:10 kroki@stripped +10 -0
  Bug#14635: Accept NEW.x as INOUT parameters to stored procedures
  from within triggers
  
  Add support for passing NEW.x as INOUT and OUT parameters to stored
  procedures.  Passing NEW.x as INOUT parameter requires SELECT and
  UPDATE privileges on that column, and passing it as OUT parameter
  requires only UPDATE privilege.

  sql/sql_yacc.yy
    1.467 06/05/09 13:28:02 kroki@stripped +11 -3
    Set read_only flag in Item_trigger_field.  For NEW.x trigger variable
    used in left-hand-side of SET statement we set it to UPDATE_ACL,
    otherwise we set it to SELECT_ACL (but see
    Item_trigger_field::set_required_privilege(), where it may be updated
    to different level).

  sql/sp_head.cc
    1.208 06/05/09 13:28:02 kroki@stripped +17 -49
    Use Settable_routine_parameter interface for parameter update.

  sql/item_func.h
    1.138 06/05/09 13:28:02 kroki@stripped +11 -1
    Item_func_get_user_var implements Settable_routine_parameter interface.

  sql/item_func.cc
    1.283 06/05/09 13:28:02 kroki@stripped +12 -0
    Add implementations of set_value() method of Settable_routine_parameter
    interface.

  sql/item.h
    1.194 06/05/09 13:28:02 kroki@stripped +94 -8
    Add interface class Settable_routine_parameter and interface query
    method to Item class.  Item_splocal and Item_trigger_field implement
    this interface.
    
    For Item_trigger_field:
     - add read_only attribute and is_read_only() method.
     - remove access_type and add original_privilege and want_privilege
       instead.
     - add set_value() method.
     - add reset_privilege() method.

  sql/item.cc
    1.219 06/05/09 13:28:01 kroki@stripped +27 -2
    Add implementations of set_value() and set_required_privilege() methods
    of Settable_routine_parameter interface.
    
    Use Item_trigger_field::want_privilege instead of
    Item_trigger_field::access_type.
    
    Reset privileges on Item_trigger_field::cleanup().

  mysql-test/t/trigger.test
    1.44 06/05/09 13:28:01 kroki@stripped +74 -0
    Add test case for bug#14635.

  mysql-test/t/trigger-grant.test
    1.7 06/05/09 13:28:00 kroki@stripped +173 -0
    Add test case for bug#14635.

  mysql-test/r/trigger.result
    1.39 06/05/09 13:28:00 kroki@stripped +56 -0
    Add result for bug#14635.

  mysql-test/r/trigger-grant.result
    1.4 06/05/09 13:28:00 kroki@stripped +83 -0
    Add result for bug#14635.

# 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:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug14635

--- 1.218/sql/item.cc	2006-04-28 14:06:51 +04:00
+++ 1.219/sql/item.cc	2006-05-09 13:28:01 +04:00
@@ -958,6 +958,12 @@
 }
 
 
+bool Item_splocal::set_value(THD *thd, sp_rcontext *ctx, Item *it)
+{
+  return ctx->set_variable(thd, get_var_idx(), it);
+}
+
+
 /*****************************************************************************
   Item_case_expr methods
 *****************************************************************************/
@@ -5365,6 +5371,25 @@
 }
 
 
+void Item_trigger_field::set_required_privilege(const bool rw)
+{
+  /*
+    Require SELECT and UPDATE privilege if this field will be read and
+    set, and only UPDATE privilege for setting the field.
+  */
+  want_privilege= (rw ? SELECT_ACL | UPDATE_ACL : UPDATE_ACL);
+}
+
+
+bool Item_trigger_field::set_value(THD *thd, sp_rcontext */*ctx*/, Item *it)
+{
+  Item *item= sp_prepare_func_item(thd, &it);
+
+  return (!item || (!fixed && fix_fields(thd, 0)) ||
+          (item->save_in_field(field, 0) < 0));
+}
+
+
 bool Item_trigger_field::fix_fields(THD *thd, Item **items)
 {
   /*
@@ -5387,8 +5412,7 @@
 
     if (table_grants)
     {
-      table_grants->want_privilege=
-        access_type == AT_READ ? SELECT_ACL : UPDATE_ACL;
+      table_grants->want_privilege= want_privilege;
 
       if (check_grant_column(thd, table_grants, triggers->table->s->db,
                              triggers->table->s->table_name, field_name,
@@ -5420,6 +5444,7 @@
 
 void Item_trigger_field::cleanup()
 {
+  reset_privilege();
   /*
     Since special nature of Item_trigger_field we should not do most of
     things from Item_field::cleanup() or Item_ident::cleanup() here.

--- 1.193/sql/item.h	2006-04-28 14:06:51 +04:00
+++ 1.194/sql/item.h	2006-05-09 13:28:02 +04:00
@@ -372,6 +372,41 @@
 
 /*************************************************************************/
 
+class sp_rcontext;
+
+
+class Settable_routine_parameter
+{
+public:
+  /*
+    Set required privileges for accessing the parameter.
+
+    SYNOPSIS
+      set_required_privilege()
+        rw        if 'rw' is true then we are going to read and set the
+                  parameter, so SELECT and UPDATE privileges might be
+                  required, otherwise we only reading it and SELECT
+                  privilege might be required.
+  */
+  virtual void set_required_privilege(const bool rw) {};
+
+  /*
+    Set parameter value.
+
+    SYNOPSIS
+      set_value()
+        thd       thread handle
+        ctx       context of the local variable
+        it        item which represents new value
+
+    RETURN
+      true if parameter value has been set,
+      false if error has occured.
+  */
+  virtual bool set_value(THD *thd, sp_rcontext *ctx, Item *it)= 0;
+};
+
+
 typedef bool (Item::*Item_processor)(byte *arg);
 typedef Item* (Item::*Item_transformer) (byte *arg);
 typedef void (*Cond_traverser) (const Item *item, void *arg);
@@ -744,6 +779,14 @@
   }
 
   virtual bool is_splocal() { return 0; } /* Needed for error checking */
+
+  /*
+    Return Settable_routine_parameter interface of the Item.
+  */
+  virtual Settable_routine_parameter *get_settable_routine_parameter()
+  {
+    return 0;
+  }
 };
 
 
@@ -842,7 +885,8 @@
   runtime.
 *****************************************************************************/
 
-class Item_splocal :public Item_sp_variable
+class Item_splocal :public Item_sp_variable,
+                    private Settable_routine_parameter
 {
   uint m_var_idx;
 
@@ -880,6 +924,15 @@
 
   inline enum Type type() const;
   inline Item_result result_type() const;
+
+private:
+  bool set_value(THD *thd, sp_rcontext *ctx, Item *it);
+
+public:
+  Settable_routine_parameter *get_settable_routine_parameter()
+  {
+    return this;
+  }
 };
 
 /*****************************************************************************
@@ -2100,14 +2153,13 @@
         two Field instances representing either OLD or NEW version of this
         field.
 */
-class Item_trigger_field : public Item_field
+class Item_trigger_field : public Item_field,
+                           private Settable_routine_parameter
 {
 public:
   /* 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 */
@@ -2118,11 +2170,11 @@
   Item_trigger_field(Name_resolution_context *context_arg,
                      row_version_type row_ver_arg,
                      const char *field_name_arg,
-                     access_types access_type_arg)
+                     ulong priv, const bool ro)
     :Item_field(context_arg,
                (const char *)NULL, (const char *)NULL, field_name_arg),
-     row_version(row_ver_arg), field_idx((uint)-1),
-     access_type(access_type_arg), table_grants(NULL)
+     row_version(row_ver_arg), field_idx((uint)-1), original_privilege(priv),
+     want_privilege(priv), table_grants(NULL), read_only (ro)
   {}
   void setup_field(THD *thd, TABLE *table, GRANT_INFO *table_grant_info);
   enum Type type() const { return TRIGGER_FIELD_ITEM; }
@@ -2131,10 +2183,44 @@
   void print(String *str);
   table_map used_tables() const { return (table_map)0L; }
   void cleanup();
+  bool is_read_only() const { return read_only; }
 
 private:
-  access_types access_type;
+  void set_required_privilege(const bool rw);
+  bool set_value(THD *thd, sp_rcontext *ctx, Item *it);
+
+public:
+  Settable_routine_parameter *get_settable_routine_parameter()
+  {
+    return (!is_read_only() ? this : 0);
+  }
+
+  bool set_value(THD *thd, Item *it)
+  {
+    return set_value(thd, NULL, it);
+  }
+
+  void reset_privilege()
+  {
+    want_privilege= original_privilege;
+  }
+
+private:
+  /*
+    At parse time we can't tell if this trigger field will be used as
+    OUT or INOUT parameter of stored routine.  Call to
+    set_required_privilege() will update want_privilege to required
+    level, and call to reset_privilege() will reset them to
+    original_privilege after value update.
+  */
+  ulong original_privilege;
+  ulong want_privilege;
   GRANT_INFO *table_grants;
+  /*
+    Trigger field is read-only unless it belongs to the NEW row in a
+    BEFORE INSERT of BEFORE UPDATE trigger.
+  */
+  bool read_only;
 };
 
 

--- 1.282/sql/item_func.cc	2006-04-26 22:38:46 +04:00
+++ 1.283/sql/item_func.cc	2006-05-09 13:28:02 +04:00
@@ -4103,6 +4103,18 @@
 }
 
 
+bool Item_func_get_user_var::set_value(THD *thd,
+                                       sp_rcontext */*ctx*/, Item *it)
+{
+  Item_func_set_user_var *suv= new Item_func_set_user_var(get_name(), it);
+  /*
+    Item_func_set_user_var is not fixed after construction, call
+    fix_fields().
+  */
+  return (suv->fix_fields(thd, &it) || suv->check() || suv->update());
+}
+
+
 bool Item_user_var_as_out_param::fix_fields(THD *thd, Item **ref)
 {
   DBUG_ASSERT(fixed == 0);

--- 1.137/sql/item_func.h	2006-04-12 19:30:52 +04:00
+++ 1.138/sql/item_func.h	2006-05-09 13:28:02 +04:00
@@ -1178,7 +1178,8 @@
 };
 
 
-class Item_func_get_user_var :public Item_func
+class Item_func_get_user_var :public Item_func,
+                              private Settable_routine_parameter
 {
   user_var_entry *var_entry;
 
@@ -1205,6 +1206,15 @@
   table_map used_tables() const
   { return const_item() ? 0 : RAND_TABLE_BIT; }
   bool eq(const Item *item, bool binary_cmp) const;
+
+private:
+  bool set_value(THD *thd, sp_rcontext *ctx, Item *it);
+
+public:
+  Settable_routine_parameter *get_settable_routine_parameter()
+  {
+    return this;
+  }
 };
 
 

--- 1.466/sql/sql_yacc.yy	2006-04-23 08:11:24 +04:00
+++ 1.467/sql/sql_yacc.yy	2006-05-09 13:28:02 +04:00
@@ -7206,12 +7206,18 @@
               YYABORT;
             }
 
+            DBUG_ASSERT(!new_row ||
+                        (lex->trg_chistics.event == TRG_EVENT_INSERT ||
+                         lex->trg_chistics.event == TRG_EVENT_UPDATE));
+            const bool read_only=
+              !(new_row && lex->trg_chistics.action_time ==
TRG_ACTION_BEFORE);
             if (!(trg_fld= new Item_trigger_field(Lex->current_context(),
                                                   new_row ?
                                                   Item_trigger_field::NEW_ROW:
                                                   Item_trigger_field::OLD_ROW,
                                                   $3.str,
-                                                  Item_trigger_field::AT_READ)))
+                                                  SELECT_ACL,
+                                                  read_only)))
               YYABORT;
 
             /*
@@ -7847,11 +7853,13 @@
               it= new Item_null();
             }
 
+            DBUG_ASSERT(lex->trg_chistics.action_time == TRG_ACTION_BEFORE &&
+                        (lex->trg_chistics.event == TRG_EVENT_INSERT ||
+                         lex->trg_chistics.event == TRG_EVENT_UPDATE));
             if (!(trg_fld= new Item_trigger_field(Lex->current_context(),
                                                   Item_trigger_field::NEW_ROW,
                                                   $2.base_name.str,
-                                                  Item_trigger_field::AT_UPDATE)
-                                                  ) ||
+                                                  UPDATE_ACL, false)) ||
                 !(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-05-09 13:28:00 +04:00
@@ -310,3 +310,86 @@
 Hello, world!
 DROP USER mysqltest_u1@localhost;
 DROP DATABASE mysqltest_db1;
+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 (i1 INT);
+CREATE TABLE t2 (i1 INT);
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+GRANT EXECUTE, CREATE ROUTINE, SUPER ON *.* TO mysqltest_dfn@localhost;
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
+CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (7);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+INSERT INTO t2 VALUES (11);
+ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column
'i1' in table 't2'
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (13);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+INSERT INTO t2 VALUES (17);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't2'
+REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t2 VALUES (23);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't2'
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (29);
+INSERT INTO t2 VALUES (31);
+REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+INSERT INTO t1 VALUES (41);
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
+INSERT INTO t1 VALUES (47);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
+INSERT INTO t1 VALUES (53);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+DROP PROCEDURE p1;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t1_bi;
+DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_dfn@localhost;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP DATABASE mysqltest_db1;
+USE test;

--- 1.6/mysql-test/t/trigger-grant.test	2006-03-06 23:45:06 +03:00
+++ 1.7/mysql-test/t/trigger-grant.test	2006-05-09 13:28:00 +04:00
@@ -564,3 +564,176 @@
 DROP USER mysqltest_u1@localhost;
 
 DROP DATABASE mysqltest_db1;
+
+
+#
+# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
+# procedures from within triggers
+#
+# We require UPDATE privilege when NEW.x passed as OUT parameter, and
+# SELECT and UPDATE when NEW.x passed as INOUT parameter.
+#
+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 (i1 INT);
+CREATE TABLE t2 (i1 INT);
+
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+
+GRANT EXECUTE, CREATE ROUTINE, SUPER ON *.* TO mysqltest_dfn@localhost;
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
+
+connect (definer,localhost,mysqltest_dfn,,mysqltest_db1);
+connect (invoker,localhost,mysqltest_inv,,mysqltest_db1);
+
+connection definer;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
+CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
+
+# Check that having no privilege won't work.
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (7);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (11);
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having only SELECT privilege is not enough.
+connection default;
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (13);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (17);
+
+connection default;
+REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having only UPDATE privilege is enough for OUT parameter,
+# but not for INOUT parameter.
+connection default;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (19);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (23);
+
+connection default;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having SELECT and UPDATE privileges is enough.
+connection default;
+GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (29);
+INSERT INTO t2 VALUES (31);
+
+connection default;
+REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+connection default;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+# Check that late procedure redefining won't open a security hole.
+connection default;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (41);
+
+connection definer;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (47);
+
+connection definer;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (53);
+
+connection default;
+DROP PROCEDURE p1;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t1_bi;
+
+# Cleanup.
+disconnect definer;
+disconnect invoker;
+connection default;
+DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_dfn@localhost;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP DATABASE mysqltest_db1;
+USE test;
+
+# End of 5.0 tests.

--- 1.38/mysql-test/r/trigger.result	2006-04-28 10:30:43 +04:00
+++ 1.39/mysql-test/r/trigger.result	2006-05-09 13:28:00 +04:00
@@ -998,3 +998,59 @@
 conn_id	trigger_conn_id
 DROP TRIGGER t1_bi;
 DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+CREATE TABLE t1 (i1 INT);
+INSERT INTO t1 VALUES (3);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
+CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+CALL p1(NEW.i1);
+CALL p2(NEW.i1);
+END//
+UPDATE t1 SET i1 = 11 WHERE i1 = 3;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (13);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+CALL p1(OLD.i1);
+UPDATE t1 SET i1 = 19 WHERE i1 = 13;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (23);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+CALL p1(OLD.i1);
+UPDATE t1 SET i1 = 31 WHERE i1 = 23;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (37);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+UPDATE t1 SET i1 = 43 WHERE i1 = 37;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (47);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+UPDATE t1 SET i1 = 51 WHERE i1 = 47;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+SELECT * FROM t1;
+i1
+35
+13
+23
+43
+51
+DROP TABLE t1;

--- 1.43/mysql-test/t/trigger.test	2006-04-28 10:30:43 +04:00
+++ 1.44/mysql-test/t/trigger.test	2006-05-09 13:28:01 +04:00
@@ -1165,4 +1165,78 @@
 DROP TRIGGER t1_bi;
 DROP TABLE t1;
 
+
+#
+# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
+# procedures from within triggers
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+--enable_warnings
+
+CREATE TABLE t1 (i1 INT);
+
+# Check that NEW.x pseudo variable is accepted as INOUT and OUT
+# parameter to stored routine.
+INSERT INTO t1 VALUES (3);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
+CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
+delimiter //;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+  CALL p1(NEW.i1);
+  CALL p2(NEW.i1);
+END//
+delimiter ;//
+UPDATE t1 SET i1 = 11 WHERE i1 = 3;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+# Check that OLD.x pseudo variable is not accepted as INOUT and OUT
+# parameter to stored routine.
+INSERT INTO t1 VALUES (13);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+  CALL p1(OLD.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 19 WHERE i1 = 13;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+
+INSERT INTO t1 VALUES (23);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+  CALL p1(OLD.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 31 WHERE i1 = 23;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+
+# Check that NEW.x pseudo variable is read-only in the AFTER TRIGGER.
+INSERT INTO t1 VALUES (37);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 43 WHERE i1 = 37;
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+
+INSERT INTO t1 VALUES (47);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 51 WHERE i1 = 47;
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+
+# Post requisite.
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
 # End of 5.0 tests

--- 1.207/sql/sp_head.cc	2006-04-18 13:07:30 +04:00
+++ 1.208/sql/sp_head.cc	2006-05-09 13:28:02 +04:00
@@ -1355,19 +1355,6 @@
 }
 
 
-static Item_func_get_user_var *item_is_user_var(Item *it)
-{
-  if (it->type() == Item::FUNC_ITEM)
-  {
-    Item_func *fi= static_cast<Item_func*>(it);
-
-    if (fi->functype() == Item_func::GUSERVAR_FUNC)
-      return static_cast<Item_func_get_user_var*>(fi);
-  }
-  return NULL;
-}
-
-
 /*
   Execute a procedure. 
   SYNOPSIS
@@ -1443,22 +1430,28 @@
     for (uint i= 0 ; i < params ; i++)
     {
       Item *arg_item= it_args++;
-      sp_variable_t *spvar= m_pcont->find_variable(i);
 
       if (!arg_item)
         break;
 
+      sp_variable_t *spvar= m_pcont->find_variable(i);
+
       if (!spvar)
         continue;
 
       if (spvar->mode != sp_param_in)
       {
-        if (!arg_item->is_splocal() && !item_is_user_var(arg_item))
+        Settable_routine_parameter *srp=
+          arg_item->get_settable_routine_parameter();
+
+        if (!srp)
         {
           my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, m_qname.str);
           err_status= TRUE;
           break;
         }
+
+        srp->set_required_privilege(spvar->mode == sp_param_inout);
       }
 
       if (spvar->mode == sp_param_out)
@@ -1526,36 +1519,16 @@
       if (spvar->mode == sp_param_in)
         continue;
 
-      if (arg_item->is_splocal())
-      {
-        if (octx->set_variable(thd,
-                               ((Item_splocal*) arg_item)->get_var_idx(),
-                               nctx->get_item(i)))
-        {
-          err_status= TRUE;
-          break;
-        }
-      }
-      else
-      {
-        Item_func_get_user_var *guv= item_is_user_var(arg_item);
+      Settable_routine_parameter *srp=
+        arg_item->get_settable_routine_parameter();
 
-	if (guv)
-	{
-	  Item *item= nctx->get_item(i);
-	  Item_func_set_user_var *suv;
-
-	  suv= new Item_func_set_user_var(guv->get_name(), item);
-	  /*
-            Item_func_set_user_var is not fixed after construction,
-            call fix_fields().
-	  */
-          if ((err_status= test(!suv || suv->fix_fields(thd, &item) ||
-                                suv->check() || suv->update())))
-            break;
-	}
-      }
+      DBUG_ASSERT(srp);
 
+      if (srp->set_value(thd, octx, nctx->get_item(i)))
+      {
+        err_status= TRUE;
+        break;
+      }
     }
   }
 
@@ -2389,12 +2362,7 @@
 int
 sp_instr_set_trigger_field::exec_core(THD *thd, uint *nextp)
 {
-  int res= 0;
-  Item *it= sp_prepare_func_item(thd, &value);
-  if (!it ||
-      !trigger_field->fixed && trigger_field->fix_fields(thd, 0) ||
-      (it->save_in_field(trigger_field->field, 0) < 0))
-    res= -1;
+  const int res= (!trigger_field->set_value(thd, value) ? 0 : -1);
   *nextp = m_ip+1;
   return res;
 }