List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:March 25 2009 7:33am
Subject:Re: bzr commit into mysql-5.1-bugteam branch (alfranio.correia:2823)
Bug#39489
View as plain text  
Hi Alfranio,

Nice work, Approved!

Alfranio Correia wrote:
> #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-22
>       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-22 14:07:32 +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-22 14:07:32 +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-22 14:07:32 +0000
> @@ -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-22 14:07:32
> +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-22 14:07:32 +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-22 14:07:32 +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-22 14:07:32 +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-22 14:07:32 +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);
> 
> 
> -- 
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:    http://lists.mysql.com/commits?unsub=1

Thread
bzr commit into mysql-5.1-bugteam branch (alfranio.correia:2823)Bug#39489Alfranio Correia22 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (alfranio.correia:2823)Bug#39489He Zhenxing25 Mar