Guang Bao,
And one more thing about testing.
>
> Fxied by switch current user of SQL thread to definer user (if
> the definer user existes on slave,
A consequence of that should be that the definer user will be able to
execute the replicated routine?
And this is something that we should make clear.
Could you please make a test showing that change in the slave side behaviour?
In other words, your test user create_rout_db should also test if it
is able to execute the routine (the new behaviour) as well as that it
can't execute a routine that does not have `creator' set in procs_priv
(partly the former behaviour that remains).
cheers,
Andrei
> or current user of SQL thread is still used) when checking whether the routine
> privilege is needed
> to insert mysql.procs_priv table or not.
> modified:
> mysql-test/suite/rpl/r/rpl_do_grant.result
> mysql-test/suite/rpl/t/rpl_do_grant.test
> sql/sql_parse.cc
>
> per-file messages:
> mysql-test/suite/rpl/r/rpl_do_grant.result
> Test case result for routine privilege when definer user exist or not on slave
> mysql-test/suite/rpl/t/rpl_do_grant.test
> Test case result for routine privilege when definer user exist or not on slave
> sql/sql_parse.cc
> Switch current user of SQL thread to definer user (if the definer user existes on
> slave,
> or current user of SQL thread is still used) when checking whether the routine
> privilege is needed
> to insert mysql.procs_priv table or not.
> === modified file 'mysql-test/suite/rpl/r/rpl_do_grant.result'
> --- a/mysql-test/suite/rpl/r/rpl_do_grant.result 2007-06-27 12:28:02 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_do_grant.result 2009-02-27 21:16:12 +0000
> @@ -89,3 +89,62 @@ show grants for rpl_do_grant2@localhost;
> ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host
> 'localhost'
> show grants for rpl_do_grant2@localhost;
> ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host
> 'localhost'
> +DROP DATABASE IF EXISTS privdb;
> +Warnings:
> +Note 1008 Can't drop database 'privdb'; database doesn't exist
> +CREATE DATABASE privdb;
> +GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost'
> + IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
> +USE privdb;
> +CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
> +BEGIN
> +RETURN "INSIDE upgrade_del_func()";
> +END//
> +USE privdb;
> +SELECT * FROM mysql.procs_priv;
> +Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
>
> +localhost privdb create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter
> Routine #
> +SELECT upgrade_del_func();
> +upgrade_del_func()
> +INSIDE upgrade_del_func()
> +SELECT * FROM mysql.procs_priv;
> +Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
>
> +localhost privdb create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter
> Routine #
> +SHOW GRANTS FOR 'create_rout_db'@'localhost';
> +Grants for create_rout_db@localhost
> +GRANT USAGE ON *.* TO 'create_rout_db'@'localhost' IDENTIFIED BY PASSWORD
> '*08792480350CBA057BDE781B9DF183B263934601'
> +GRANT CREATE ROUTINE ON `privdb`.* TO 'create_rout_db'@'localhost' WITH GRANT
> OPTION
> +GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `privdb`.`upgrade_del_func` TO
> 'create_rout_db'@'localhost'
> +USE privdb;
> +SHOW CREATE FUNCTION upgrade_del_func;
> +Function sql_mode Create Function character_set_client collation_connection Database
> Collation
> +upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION
> `upgrade_del_func`() RETURNS char(30) CHARSET latin1
> +BEGIN
> +RETURN "INSIDE upgrade_del_func()";
> +END latin1 latin1_swedish_ci latin1_swedish_ci
> +SELECT upgrade_del_func();
> +upgrade_del_func()
> +INSIDE upgrade_del_func()
> +"Test the user who create a function on master doesn't exist on slave."
> +DROP USER 'create_rout_db'@'localhost';
> +CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
> +BEGIN
> +RETURN "INSIDE upgrade_alter_func()";
> +END//
> +SELECT upgrade_alter_func();
> +upgrade_alter_func()
> +INSIDE upgrade_alter_func()
> +SHOW CREATE FUNCTION upgrade_alter_func;
> +Function sql_mode Create Function character_set_client collation_connection Database
> Collation
> +upgrade_alter_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION
> `upgrade_alter_func`() RETURNS char(30) CHARSET latin1
> +BEGIN
> +RETURN "INSIDE upgrade_alter_func()";
> +END latin1 latin1_swedish_ci latin1_swedish_ci
> +SELECT upgrade_alter_func();
> +ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not
> exist
> +USE privdb;
> +DROP FUNCTION upgrade_del_func;
> +DROP FUNCTION upgrade_alter_func;
> +DROP DATABASE privdb;
> +DROP USER 'create_rout_db'@'localhost';
> +"End of test"
>
> === modified file 'mysql-test/suite/rpl/t/rpl_do_grant.test'
> --- a/mysql-test/suite/rpl/t/rpl_do_grant.test 2007-06-27 12:28:02 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_do_grant.test 2009-02-27 21:16:12 +0000
> @@ -112,3 +112,76 @@ show grants for rpl_do_grant2@localhost;
> sync_slave_with_master;
> --error 1141
> show grants for rpl_do_grant2@localhost;
> +
> +#####################################################
> +# Purpose
> +# Test whether mysql.procs_priv get replicated
> +# Related bugs:
> +# BUG42217 mysql.procs_priv does not get replicated
> +#####################################################
> +connection master;
> +
> +DROP DATABASE IF EXISTS privdb;
> +CREATE DATABASE privdb;
> +
> +GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost'
> + IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
> +
> +connect (create_rout_db, localhost, create_rout_db, create_rout_db, privdb);
> +connection create_rout_db;
> +
> +USE privdb;
> +
> +DELIMITER //;
> +CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
> +BEGIN
> + RETURN "INSIDE upgrade_del_func()";
> +END//
> +
> +DELIMITER ;//
> +
> +connection master;
> +
> +USE privdb;
> +--replace_column 8 #
> +SELECT * FROM mysql.procs_priv;
> +SELECT upgrade_del_func();
> +
> +sync_slave_with_master;
> +--replace_column 8 #
> +SELECT * FROM mysql.procs_priv;
> +SHOW GRANTS FOR 'create_rout_db'@'localhost';
> +
> +USE privdb;
> +SHOW CREATE FUNCTION upgrade_del_func;
> +
> +SELECT upgrade_del_func();
> +
