bzr commit into mysql-5.1-bugteam branch (alfranio.correia:2823) Bug#39489

[Alfranio Correia]

#At
file:///home/acorreia/workspace.sun/repository.mysql/bzrwork/bug-39489/mysql-5.1-bugteam/
based on revid:joro@stripped

 2823 Alfranio Correia	2009-03-27
      BUG#39489 Cannot create or drop triggers without SUPER privilege
            
      If the binlog was active, a user should define a function as
      deterministic to create it. On the other hand, a trigger don't 
      have this option and a user should have the SUPER privilige to 
      do so. To easy the creation, the replica could be configured
      with the option log-bin-trust-function-creators thus eliminating
      the need for a special option and privilege.
            
      These requirements were used to avoid database inconsistencies
      between the master and slave. With row and mixed-based format 
      these requirements are not necessary as in the former case, DML 
      operations are logged as rows and in latter, any unsafe operation 
      is switched to row format.
            
      To fix this bug, the deterministic option, SUPER privilige or 
      log-bin-trust-function-creators are not checked when the row or
      mixed-based is set.
added:
  mysql-test/suite/rpl/r/rpl_tg_sp_permission.result
  mysql-test/suite/rpl/t/rpl_tg_sp_permission-master.opt
  mysql-test/suite/rpl/t/rpl_tg_sp_permission.test
modified:
  mysql-test/suite/rpl/r/rpl_sp.result
  mysql-test/suite/rpl/t/rpl_sp.test
  sql/sp.cc
  sql/sql_parse.cc
  sql/sql_trigger.cc

=== modified file 'mysql-test/suite/rpl/r/rpl_sp.result'
--- a/mysql-test/suite/rpl/r/rpl_sp.result	2009-02-16 11:38:15 +0000
+++ b/mysql-test/suite/rpl/r/rpl_sp.result	2009-03-27 15:36:34 +0000
@@ -145,7 +145,7 @@ begin
 insert into t1 values (x);
 return x+2;
 end|
-ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its
declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
+drop function fn1;
 create function fn1(x int)
 returns int
 deterministic
@@ -180,7 +180,7 @@ begin
 return unix_timestamp();
 end|
 alter function fn1 contains sql;
-ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its
declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
+alter function fn1 no sql;
 delete from t1;
 set timestamp=1000000000;
 insert into t1 values(fn1());
@@ -190,7 +190,7 @@ no sql
 begin
 return unix_timestamp();
 end|
-ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you
*might* want to use the less safe log_bin_trust_function_creators variable)
+drop function fn2;
 set @old_log_bin_trust_routine_creators= @@global.log_bin_trust_routine_creators;
 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;
 set global log_bin_trust_routine_creators=1;
@@ -462,6 +462,12 @@ master-bin.000001	#	Query	1	#	use `mysql
 master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo2
 master-bin.000001	#	Query	1	#	use `mysqltest1`; drop procedure foo3
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost`
FUNCTION `fn1`(x int) RETURNS int(11)
+begin
+insert into t1 values (x);
+return x+2;
+end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function fn1
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost`
FUNCTION `fn1`(x int) RETURNS int(11)
     DETERMINISTIC
 begin
 insert into t1 values (x);
@@ -476,6 +482,8 @@ master-bin.000001	#	Query	1	#	use `mysql
 begin
 return unix_timestamp();
 end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; alter function fn1 contains sql
+master-bin.000001	#	Query	1	#	use `mysqltest1`; alter function fn1 no sql
 master-bin.000001	#	Query	1	#	use `mysqltest1`; delete from t1
 master-bin.000001	#	Query	1	#	use `mysqltest1`; insert into t1 values(fn1())
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1`
FUNCTION `fn2`() RETURNS int(11)
@@ -483,6 +491,12 @@ master-bin.000001	#	Query	1	#	use `mysql
 begin
 return unix_timestamp();
 end
+master-bin.000001	#	Query	1	#	use `mysqltest1`; drop function fn2
+master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1`
FUNCTION `fn2`() RETURNS int(11)
+    NO SQL
+begin
+return unix_timestamp();
+end
 master-bin.000001	#	Query	1	#	use `mysqltest1`; CREATE DEFINER=`root`@`localhost`
FUNCTION `fn3`() RETURNS int(11)
     READS SQL DATA
 begin
@@ -742,6 +756,16 @@ drop procedure foo3
 /*!*/;
 SET TIMESTAMP=t/*!*/;
 CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
+begin
+insert into t1 values (x);
+return x+2;
+end
+/*!*/;
+SET TIMESTAMP=t/*!*/;
+drop function fn1
+/*!*/;
+SET TIMESTAMP=t/*!*/;
+CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(x int) RETURNS int(11)
     DETERMINISTIC
 begin
 insert into t1 values (x);
@@ -768,6 +792,12 @@ return unix_timestamp();
 end
 /*!*/;
 SET TIMESTAMP=t/*!*/;
+alter function fn1 contains sql
+/*!*/;
+SET TIMESTAMP=t/*!*/;
+alter function fn1 no sql
+/*!*/;
+SET TIMESTAMP=t/*!*/;
 delete from t1
 /*!*/;
 SET TIMESTAMP=t/*!*/;
@@ -781,6 +811,16 @@ return unix_timestamp();
 end
 /*!*/;
 SET TIMESTAMP=t/*!*/;
+drop function fn2
+/*!*/;
+SET TIMESTAMP=t/*!*/;
+CREATE DEFINER=`zedjzlcsjhd`@`127.0.0.1` FUNCTION `fn2`() RETURNS int(11)
+    NO SQL
+begin
+return unix_timestamp();
+end
+/*!*/;
+SET TIMESTAMP=t/*!*/;
 CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS int(11)
     READS SQL DATA
 begin

=== added file 'mysql-test/suite/rpl/r/rpl_tg_sp_permission.result'
--- a/mysql-test/suite/rpl/r/rpl_tg_sp_permission.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_tg_sp_permission.result	2009-03-27 15:36:34 +0000
@@ -0,0 +1,21 @@
+GRANT ALL PRIVILEGES ON *.* TO 'user_priv'@'localhost';
+REVOKE SUPER ON *.* FROM 'user_priv'@'localhost';
+CREATE TABLE t1 ( a int );
+"Testing functions"
+CREATE FUNCTION f1(a INT) RETURNS INT
+BEGIN
+RETURN 1;
+END|
+ALTER FUNCTION f1 MODIFIES SQL DATA COMMENT 'Function f1';
+DROP FUNCTION f1;
+CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC
+BEGIN
+RETURN 1;
+END|
+ALTER FUNCTION f1 MODIFIES SQL DATA COMMENT 'Function f1';
+DROP FUNCTION f1;
+"Testing Triggers"
+CREATE TRIGGER g1 BEFORE INSERT ON t1 FOR EACH ROW SET new.a=new.a+1;
+DROP TRIGGER g1;
+DROP TABLE t1;
+DROP USER 'user_priv'@'localhost';

=== modified file 'mysql-test/suite/rpl/t/rpl_sp.test'
--- a/mysql-test/suite/rpl/t/rpl_sp.test	2008-07-10 16:09:39 +0000
+++ b/mysql-test/suite/rpl/t/rpl_sp.test	2009-03-27 15:36:34 +0000
@@ -115,14 +115,14 @@ delimiter ;|
 
 # I add ,0 so that it does not print the error in the test output,
 # because this error is hostname-dependent
---error 1142,0
+--error ER_TABLEACCESS_DENIED_ERROR,0
 call foo4(); # invoker has no INSERT grant on table t1 => failure
 
 connection master;
 call foo3(); # success (definer == root)
 show warnings;
 
---error 1142,0
+--error ER_TABLEACCESS_DENIED_ERROR,0
 call foo4(); # definer's rights => failure
 
 # we test replication of ALTER PROCEDURE
@@ -182,14 +182,19 @@ drop procedure foo2;
 drop procedure foo3;
 
 delimiter |;
-# check that needs "deterministic"
---error 1418
+# In row and mixed mode there is no need for "deterministic"
 create function fn1(x int)
        returns int
 begin
        insert into t1 values (x);
        return x+2;
 end|
+
+delimiter ;|
+
+drop function fn1;
+
+delimiter |;
 create function fn1(x int)
        returns int
        deterministic
@@ -221,9 +226,9 @@ begin
 end|
 
 delimiter ;|
-# check that needs "deterministic"
---error 1418
+# In row and mixed mode there is no need for "deterministic"
 alter function fn1 contains sql;
+alter function fn1 no sql;
 
 delete from t1;
 set timestamp=1000000000;
@@ -232,7 +237,6 @@ insert into t1 values(fn1()); 
 connection con1;
 
 delimiter |;
---error 1419 # only full-global-privs user can create a function
 create function fn2()
        returns int
        no sql
@@ -240,6 +244,9 @@ begin
        return unix_timestamp();
 end|
 delimiter ;|
+
+drop function fn2;
+
 connection master;
 set @old_log_bin_trust_routine_creators= @@global.log_bin_trust_routine_creators;
 set @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators;

=== added file 'mysql-test/suite/rpl/t/rpl_tg_sp_permission-master.opt'
--- a/mysql-test/suite/rpl/t/rpl_tg_sp_permission-master.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_tg_sp_permission-master.opt	2009-03-27 15:36:34 +0000
@@ -0,0 +1 @@
+--log-bin-trust-function-creators=0

=== added file 'mysql-test/suite/rpl/t/rpl_tg_sp_permission.test'
--- a/mysql-test/suite/rpl/t/rpl_tg_sp_permission.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_tg_sp_permission.test	2009-03-27 15:36:34 +0000
@@ -0,0 +1,66 @@
+##########################################################################
+#   This test verifies if it is possible to create functions and triggers 
+#   without super privileges and the determinic option when  the 
+#   binlog is on.
+#
+#   1 - Creates and alters functions
+#   3 - Create triggers
+##########################################################################
+-- source include/not_embedded.inc
+-- source include/have_binlog_format_mixed_or_row.inc
+
+GRANT ALL PRIVILEGES ON *.* TO 'user_priv'@'localhost';
+REVOKE SUPER ON *.* FROM 'user_priv'@'localhost';
+connect (conn_user_priv, LOCALHOST, user_priv,, test,$MASTER_MYPORT);
+
+CREATE TABLE t1 ( a int );
+
+--connection conn_user_priv
+
+###########################################################################
+#                                 Function
+###########################################################################
+--connection conn_user_priv
+
+echo "Testing functions";
+##  Creating new function f1 that does not fail because no DETERMINISTIC ##
+delimiter |;
+CREATE FUNCTION f1(a INT) RETURNS INT
+BEGIN
+  RETURN 1;
+END|
+delimiter ;|
+
+ALTER FUNCTION f1 MODIFIES SQL DATA COMMENT 'Function f1';
+
+DROP FUNCTION f1;
+
+## Creating new function f1 that does not fail because non-super user ##
+delimiter |;
+CREATE FUNCTION f1(a INT) RETURNS INT DETERMINISTIC
+BEGIN
+  RETURN 1;
+END|
+delimiter ;|
+
+ALTER FUNCTION f1 MODIFIES SQL DATA COMMENT 'Function f1';
+
+DROP FUNCTION f1;
+
+###########################################################################
+#                                 Triggers
+###########################################################################
+--connection conn_user_priv
+
+echo "Testing Triggers";
+## Creating new trigger g1 that does not fail because non-super user ##
+CREATE TRIGGER g1 BEFORE INSERT ON t1 FOR EACH ROW SET new.a=new.a+1;
+DROP TRIGGER g1;
+
+###########################################################################
+#                             Clean up
+###########################################################################
+connection default;
+
+DROP TABLE t1;
+DROP USER 'user_priv'@'localhost';

=== modified file 'sql/sp.cc'
--- a/sql/sp.cc	2009-03-17 20:29:24 +0000
+++ b/sql/sp.cc	2009-03-27 15:36:34 +0000
@@ -853,7 +853,8 @@ sp_create_routine(THD *thd, int type, sp
     }
 
     if ((sp->m_type == TYPE_ENUM_FUNCTION) &&
-        !trust_function_creators && mysql_bin_log.is_open())
+        !trust_function_creators && mysql_bin_log.is_open() &&
+        thd->variables.binlog_format == BINLOG_FORMAT_STMT)
     {
       if (!sp->m_chistics->detistic)
       {

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-03-19 13:59:10 +0000
+++ b/sql/sql_parse.cc	2009-03-27 15:36:34 +0000
@@ -4343,6 +4343,7 @@ create_sp_error:
 	memcpy(&lex->sp_chistics, &chistics, sizeof(lex->sp_chistics));
         if ((sp->m_type == TYPE_ENUM_FUNCTION) &&
             !trust_function_creators &&  mysql_bin_log.is_open() &&
+            thd->variables.binlog_format == BINLOG_FORMAT_STMT &&
             !sp->m_chistics->detistic &&
             (chistics.daccess == SP_CONTAINS_SQL ||
              chistics.daccess == SP_MODIFIES_SQL_DATA))

=== modified file 'sql/sql_trigger.cc'
--- a/sql/sql_trigger.cc	2009-02-13 16:41:47 +0000
+++ b/sql/sql_trigger.cc	2009-03-27 15:36:34 +0000
@@ -370,7 +370,8 @@ bool mysql_create_or_drop_trigger(THD *t
     applies to them too.
   */
   if (!trust_function_creators && mysql_bin_log.is_open() &&
-      !(thd->security_ctx->master_access & SUPER_ACL))
+      !(thd->security_ctx->master_access & SUPER_ACL) &&
+      thd->variables.binlog_format == BINLOG_FORMAT_STMT)
   {
     my_error(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER, MYF(0));
     DBUG_RETURN(TRUE);