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
