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.


mysql-test/r/sp-error.result:
  Update the result for new message.
mysql-test/r/trigger-grant.result:
  Add result for bug#14635.
mysql-test/r/trigger.result:
  Add result for bug#14635.
mysql-test/t/trigger-grant.test:
  Add test case for bug#14635.
mysql-test/t/trigger.test:
  Add test case for bug#14635.
sql/item.cc:
  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().
sql/item.h:
  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_func.cc:
  Add implementations of set_value() method of Settable_routine_parameter
  interface.
sql/item_func.h:
  Item_func_get_user_var implements Settable_routine_parameter interface.
sql/share/errmsg.txt:
  Update english ER_SP_NOT_VAR_ARG message.
sql/sp_head.cc:
  Use Settable_routine_parameter interface for parameter update.
sql/sql_yacc.yy:
  Set read_only and want_privilege members in Item_trigger_field
  appropriately.  For NEW.x trigger variable used in left-hand-side
  of SET statement the latter is set to UPDATE_ACL, otherwise it is
  set to SELECT_ACL (but see Item_trigger_field::set_required_privilege(),
  where it may be updated to different value).

5 files changed, 389 insertions, 2 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index a49b282ddb7..924963017eb 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -282,9 +282,9 @@ select @tmp_x, @tmp_y, @tmp_z|
 @tmp_x	@tmp_y	@tmp_z
 42	45	87
 call p(42, 43, @tmp_z)|
-ERROR 42000: OUT or INOUT argument 2 for routine test.p is not a variable
+ERROR 42000: OUT or INOUT argument 2 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger
 call p(42, @tmp_y, 43)|
-ERROR 42000: OUT or INOUT argument 3 for routine test.p is not a variable
+ERROR 42000: OUT or INOUT argument 3 for routine test.p is not a variable or NEW pseudo-variable in BEFORE trigger
 drop procedure p|
 create procedure p() begin end|
 lock table t1 read|
diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result
index 87e5e11d779..f6384d479b7 100644
--- a/mysql-test/r/trigger-grant.result
+++ b/mysql-test/r/trigger-grant.result
@@ -310,3 +310,87 @@ SELECT @mysqltest_var;
 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;
+End of 5.0 tests.
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 881933929fe..8b4aba367fb 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1034,3 +1034,59 @@ SET @@sql_mode=@save_sql_mode;
 DROP TRIGGER t1_ai;
 DROP TRIGGER t1_au;
 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 or NEW pseudo-variable in BEFORE trigger
+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 or NEW pseudo-variable in BEFORE trigger
+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 or NEW pseudo-variable in BEFORE trigger
+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 or NEW pseudo-variable in BEFORE trigger
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+SELECT * FROM t1;
+i1
+35
+13
+23
+43
+51
+DROP TABLE t1;
diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test
index 7a3f7a9fa94..12b929898a8 100644
--- a/mysql-test/t/trigger-grant.test
+++ b/mysql-test/t/trigger-grant.test
@@ -564,3 +564,176 @@ SELECT @mysqltest_var;
 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;
+
+--echo End of 5.0 tests.
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 02e4ca5f900..a5bd2ba0b38 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -1213,4 +1213,78 @@ DROP TRIGGER t1_ai;
 DROP TRIGGER t1_au;
 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