#At file:///home/ngb/mysql/bzr/bug42217-5.1-bugteam/
2827 Guangbao Ni 2009-03-06
Bug #42217 mysql.procs_priv does not get replicated
mysql.procs_priv table itself does not get replicated.
Inserting routine privilege record into mysql.procs_priv table
is triggered by creating function/procedure statements
according to current user's privileges.
Because the current user of SQL thread has GLOBAL_ACL,
which doesn't need any check mysql.procs_priv privilege
when create/alter/execute routines.
Corresponding GLOBAL_ACL privilege user
doesn't insert routine privilege record into
mysql.procs_priv when creating a routine.
Fixed by switching the current user of SQL thread to definer user if
the definer user exists on slave.
That populates procs_priv, otherwise to keep the SQL thread
user and procs_priv remains unchanged.
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 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-03-06 09:57:48 +0000
@@ -89,3 +89,81 @@ 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 bug42217_db;
+CREATE DATABASE bug42217_db;
+GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost'
+ IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
+USE bug42217_db;
+CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
+BEGIN
+RETURN "INSIDE upgrade_del_func()";
+END//
+USE bug42217_db;
+SELECT * FROM mysql.procs_priv;
+Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
+localhost bug42217_db 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 bug42217_db 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 `bug42217_db`.* TO 'create_rout_db'@'localhost' WITH GRANT OPTION
+GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO 'create_rout_db'@'localhost'
+USE bug42217_db;
+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()
+"Check whether the definer user will be able to execute the replicated routine on slave"
+USE bug42217_db;
+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()
+DELETE FROM mysql.procs_priv;
+FLUSH PRIVILEGES;
+USE bug42217_db;
+"Can't execute the replicated routine on slave like before after procs privilege is deleted "
+SELECT upgrade_del_func();
+ERROR 42000: execute command denied to user 'create_rout_db'@'localhost' for routine 'bug42217_db.upgrade_del_func'
+"Test the user who creates a function on master doesn't exist on slave."
+"Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted"
+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
+"Should no privilege record for upgrade_alter_func in mysql.procs_priv"
+SELECT * FROM mysql.procs_priv;
+Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
+SELECT upgrade_alter_func();
+ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not exist
+USE bug42217_db;
+DROP FUNCTION upgrade_del_func;
+DROP FUNCTION upgrade_alter_func;
+DROP DATABASE bug42217_db;
+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-03-06 09:57:48 +0000
@@ -112,3 +112,100 @@ 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;
+
+--disable_warnings
+DROP DATABASE IF EXISTS bug42217_db;
+--enable_warnings
+CREATE DATABASE bug42217_db;
+
+GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost'
+ IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
+
+connect (create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,);
+connect (create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,);
+
+connection create_rout_db_master;
+
+
+USE bug42217_db;
+
+DELIMITER //;
+CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
+BEGIN
+ RETURN "INSIDE upgrade_del_func()";
+END//
+
+DELIMITER ;//
+
+connection master;
+
+USE bug42217_db;
+--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 bug42217_db;
+SHOW CREATE FUNCTION upgrade_del_func;
+SELECT upgrade_del_func();
+
+--echo "Check whether the definer user will be able to execute the replicated routine on slave"
+connection create_rout_db_slave;
+USE bug42217_db;
+SHOW CREATE FUNCTION upgrade_del_func;
+SELECT upgrade_del_func();
+
+connection slave;
+DELETE FROM mysql.procs_priv;
+FLUSH PRIVILEGES;
+USE bug42217_db;
+--echo "Can't execute the replicated routine on slave like before after procs privilege is deleted "
+--error 1370
+SELECT upgrade_del_func();
+
+--echo "Test the user who creates a function on master doesn't exist on slave."
+--echo "Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted"
+DROP USER 'create_rout_db'@'localhost';
+
+connection create_rout_db_master;
+DELIMITER //;
+CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
+BEGIN
+ RETURN "INSIDE upgrade_alter_func()";
+END//
+DELIMITER ;//
+
+connection master;
+SELECT upgrade_alter_func();
+
+sync_slave_with_master;
+SHOW CREATE FUNCTION upgrade_alter_func;
+--echo "Should no privilege record for upgrade_alter_func in mysql.procs_priv"
+--replace_column 8 #
+SELECT * FROM mysql.procs_priv;
+--error 1449
+SELECT upgrade_alter_func();
+
+###### CLEAN UP SECTION ##############
+disconnect create_rout_db_master;
+disconnect create_rout_db_slave;
+connection master;
+USE bug42217_db;
+DROP FUNCTION upgrade_del_func;
+DROP FUNCTION upgrade_alter_func;
+DROP DATABASE bug42217_db;
+DROP USER 'create_rout_db'@'localhost';
+
+--echo "End of test"
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-02-16 11:38:15 +0000
+++ b/sql/sql_parse.cc 2009-03-06 09:57:48 +0000
@@ -4129,9 +4129,32 @@ end_with_restore_list:
res= (sp_result= lex->sphead->create(thd));
switch (sp_result) {
- case SP_OK:
+ case SP_OK: {
#ifndef NO_EMBEDDED_ACCESS_CHECKS
/* only add privileges if really neccessary */
+
+ Security_context security_context;
+ bool restore_backup_context= false;
+ Security_context *backup= NULL;
+ LEX_USER *definer= thd->lex->definer;
+ /*
+ Check if the definer exists on slave,
+ then use definer privilege to insert routine privileges to mysql.procs_priv.
+
+ For current user of SQL thread has GLOBAL_ACL privilege,
+ which doesn't any check routine privileges,
+ so no routine privilege record will insert into mysql.procs_priv.
+ */
+ if (thd->slave_thread && is_acl_user(definer->host.str, definer->user.str))
+ {
+ security_context.change_security_context(thd,
+ &thd->lex->definer->user,
+ &thd->lex->definer->host,
+ &thd->lex->sphead->m_db,
+ &backup);
+ restore_backup_context= true;
+ }
+
if (sp_automatic_privileges && !opt_noacl &&
check_routine_access(thd, DEFAULT_CREATE_PROC_ACLS,
lex->sphead->m_db.str, name,
@@ -4143,8 +4166,19 @@ end_with_restore_list:
ER_PROC_AUTO_GRANT_FAIL,
ER(ER_PROC_AUTO_GRANT_FAIL));
}
+
+ /*
+ Restore current user with GLOBAL_ACL privilege of SQL thread
+ */
+ if (restore_backup_context)
+ {
+ DBUG_ASSERT(thd->slave_thread == 1);
+ thd->security_ctx->restore_security_context(thd, backup);
+ }
+
#endif
break;
+ }
case SP_WRITE_ROW_FAILED:
my_error(ER_SP_ALREADY_EXISTS, MYF(0), SP_TYPE_STRING(lex), name);
break;
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217 | Guangbao Ni | 6 Mar |
