List:Commits« Previous MessageNext Message »
From:Guangbao Ni Date:February 27 2009 9:17pm
Subject:bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217
View as plain text  
#At file:///home/ngb/mysql/bzr/bug42217-5.1-bugteam/

 2827 Guangbao Ni	2009-02-27
      Bug #42217  	mysql.procs_priv does not get replicated
      
      
      mysql.procs_priv does not get replicated.Yet mysql.procs_priv table operations don't have correspoding 
      statement replicated to slave. the operations for mysql.procs_priv is triggered by creating 
      function/procedure statements according to current user's privileges.
      Because the current user of SQL thread has GLOBAL_ACL, so needn't check mysql.procs_priv privilege 
      when create routines. Corresponding GLOBAL_ACL privilege doesn't need to insert routine privilege 
      into mysql.procs_priv.
      
      Fxied by 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:
  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();
+
+--echo "Test the user who create a function on master doesn't exist on slave."
+DROP USER 'create_rout_db'@'localhost';
+
+connection create_rout_db;
+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;
+--error 1449
+SELECT upgrade_alter_func();
+
+###### CLEAN UP SECTION ##############
+connection master;
+USE privdb;
+DROP FUNCTION upgrade_del_func;
+DROP FUNCTION upgrade_alter_func;
+DROP DATABASE privdb;
+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-02-27 21:16:12 +0000
@@ -4079,6 +4079,8 @@ end_with_restore_list:
     uint namelen;
     char *name;
     int sp_result= SP_INTERNAL_ERROR;
+    Security_context security_context;
+    bool restore_backup_context= false;
 
     DBUG_ASSERT(lex->sphead != 0);
     DBUG_ASSERT(lex->sphead->m_db.str); /* Must be initialized in the parser */
@@ -4132,6 +4134,26 @@ end_with_restore_list:
     case SP_OK:
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
       /* only add privileges if really neccessary */
+
+      Security_context *backup;
+      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 needn't check 
+        routine privileges, so no routine privileges 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,6 +4165,13 @@ 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 (thd->slave_thread && restore_backup_context)
+        thd->security_ctx->restore_security_context(thd, backup);
+
 #endif
     break;
     case SP_WRITE_ROW_FAILED:

Thread
bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Guangbao Ni27 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Andrei Elkin2 Mar
    • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Guangbao Ni4 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Andrei Elkin2 Mar
    • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Guangbao Ni4 Mar
      • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Andrei Elkin4 Mar
        • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Guangbao Ni5 Mar
          • Re: bzr commit into mysql-5.1-bugteam branch (gni:2827) Bug#42217Andrei Elkin5 Mar