List:Commits« Previous MessageNext Message »
From:Ingo Struewing Date:March 4 2009 10:20am
Subject:bzr commit into mysql-6.0-backup branch (ingo.struewing:2783) Bug#41979
View as plain text  
#At file:///home2/mydev/bzrroot/mysql-6.0-bug41979-1/ based on revid:ingo.struewing@stripped

 2783 Ingo Struewing	2009-03-04
      Bug#41979 - Routine level grants not restored when user is dropped,
                  recreated before restore
      
      BACKUP/RESTORE did not implement backup and restore of privileges
      for stored procedures and stored functions.
      
      On BACKUP, we do now select routine privileges from mysql.proc_priv.
      This is a preliminary solution until Bug 26886 / WL 2646 are implementd.
      
      Another BACKUP change is in the GRANT statement creation. Grants for
      routines have the additional keyword PROCEDURE or FUNCTION after ON.
      
      On RESTORE, we need to be aware of the additional keyword, when we
      search for the database name in the GRANT statement.
     @ mysql-test/suite/backup/r/backup_db_grants.result
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Added test result.
     @ mysql-test/suite/backup/r/backup_db_grants_extra.result
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Fixed test result.
     @ mysql-test/suite/backup/t/backup_db_grants.test
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Added test.
     @ mysql-test/suite/backup/t/backup_db_grants_extra.test
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Fixed test comments. RESTORE no longer fails on routine priiviliges.
     @ sql/backup/kernel.cc
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Changed GRANT statement parser to find database name also in
        routine privilege grants.
     @ sql/si_objects.cc
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Fixed a compiler warning.
        Changed GRANT statement creation to handle routine privileges.
        Added routine privileges to privilege selection.

    modified:
      mysql-test/suite/backup/r/backup_db_grants.result
      mysql-test/suite/backup/r/backup_db_grants_extra.result
      mysql-test/suite/backup/t/backup_db_grants.test
      mysql-test/suite/backup/t/backup_db_grants_extra.test
      sql/backup/kernel.cc
      sql/si_objects.cc
=== modified file 'mysql-test/suite/backup/r/backup_db_grants.result'
--- a/mysql-test/suite/backup/r/backup_db_grants.result	2008-12-13 19:55:44 +0000
+++ b/mysql-test/suite/backup/r/backup_db_grants.result	2009-03-04 10:20:21 +0000
@@ -224,3 +224,79 @@ EXECUTE stmt5;
 SET NAMES latin1;
 DROP DATABASE bup_db_grants;
 DROP DATABASE db2;
+#
+# Bug#41979 - Routine level grants not restored when user is dropped,
+#             recreated before restore
+#
+# Create database, table, and users.
+CREATE DATABASE bup_db_grants;
+CREATE TABLE bup_db_grants.t1(c1 INT, c2 CHAR(20));
+CREATE USER 'bup_user1'@'%';
+CREATE USER 'bup_user2'@'doodle';
+# Create stored procedure.
+CREATE PROCEDURE bup_db_grants.p1(p1 CHAR(20))
+BEGIN
+INSERT INTO bup_db_grants.t1 VALUES(50,'e');
+INSERT INTO bup_db_grants.t1 VALUES(50,'e');
+END||
+# Grant privileges on the procedure.
+GRANT EXECUTE ON PROCEDURE bup_db_grants.p1 TO 'bup_user1'@'%';
+GRANT ALTER ROUTINE ON PROCEDURE bup_db_grants.p1 TO 'bup_user2'@'doodle';
+# Create stored function.
+CREATE FUNCTION bup_db_grants.f1(p1 CHAR(20)) RETURNS INT
+BEGIN
+RETURN (SELECT c1 FROM bup_db_grants.t1 WHERE c2 = p1 LIMIT 1);
+END||
+# Grant privileges on the function.
+GRANT EXECUTE ON FUNCTION bup_db_grants.f1 TO 'bup_user1'@'%';
+GRANT ALTER ROUTINE ON FUNCTION bup_db_grants.f1 TO 'bup_user2'@'doodle';
+FLUSH PRIVILEGES;
+# Demonstrate rights of the users.
+SHOW GRANTS FOR 'bup_user1'@'%';
+Grants for bup_user1@%
+GRANT USAGE ON *.* TO 'bup_user1'@'%'
+GRANT EXECUTE ON PROCEDURE `bup_db_grants`.`p1` TO 'bup_user1'@'%'
+GRANT EXECUTE ON FUNCTION `bup_db_grants`.`f1` TO 'bup_user1'@'%'
+SHOW GRANTS FOR 'bup_user2'@'doodle';
+Grants for bup_user2@doodle
+GRANT USAGE ON *.* TO 'bup_user2'@'doodle'
+GRANT ALTER ROUTINE ON PROCEDURE `bup_db_grants`.`p1` TO 'bup_user2'@'doodle'
+GRANT ALTER ROUTINE ON FUNCTION `bup_db_grants`.`f1` TO 'bup_user2'@'doodle'
+# Run backup
+BACKUP DATABASE bup_db_grants TO 'bup_db_grants.bak';
+backup_id
+#
+# Drop users and recreate them (removes grants completely).
+DROP USER 'bup_user1'@'%';
+DROP USER 'bup_user2'@'doodle';
+FLUSH PRIVILEGES;
+CREATE USER 'bup_user1'@'%';
+CREATE USER 'bup_user2'@'doodle';
+FLUSH PRIVILEGES;
+# Demonstrate rights of the users.
+SHOW GRANTS FOR 'bup_user1'@'%';
+Grants for bup_user1@%
+GRANT USAGE ON *.* TO 'bup_user1'@'%'
+SHOW GRANTS FOR 'bup_user2'@'doodle';
+Grants for bup_user2@doodle
+GRANT USAGE ON *.* TO 'bup_user2'@'doodle'
+# Run Restore
+RESTORE FROM 'bup_db_grants.bak' OVERWRITE;
+backup_id
+#
+FLUSH PRIVILEGES;
+# Demonstrate rights of the users.
+SHOW GRANTS FOR 'bup_user1'@'%';
+Grants for bup_user1@%
+GRANT USAGE ON *.* TO 'bup_user1'@'%'
+GRANT EXECUTE ON PROCEDURE `bup_db_grants`.`p1` TO 'bup_user1'@'%'
+GRANT EXECUTE ON FUNCTION `bup_db_grants`.`f1` TO 'bup_user1'@'%'
+SHOW GRANTS FOR 'bup_user2'@'doodle';
+Grants for bup_user2@doodle
+GRANT USAGE ON *.* TO 'bup_user2'@'doodle'
+GRANT ALTER ROUTINE ON PROCEDURE `bup_db_grants`.`p1` TO 'bup_user2'@'doodle'
+GRANT ALTER ROUTINE ON FUNCTION `bup_db_grants`.`f1` TO 'bup_user2'@'doodle'
+# Cleanup
+DROP USER 'bup_user1'@'%';
+DROP USER 'bup_user2'@'doodle';
+DROP DATABASE bup_db_grants;

=== modified file 'mysql-test/suite/backup/r/backup_db_grants_extra.result'
--- a/mysql-test/suite/backup/r/backup_db_grants_extra.result	2009-02-26 13:53:17 +0000
+++ b/mysql-test/suite/backup/r/backup_db_grants_extra.result	2009-03-04 10:20:21 +0000
@@ -1,11 +1,11 @@
-Test - 4
-Test to ensure routine-level GRANT statements are recorded in the
-backup image and restore reapplies the GRANT statements.
+# Test - 4
+# Test to ensure routine-level GRANT statements are recorded in the
+# backup image and restore reapplies the GRANT statements.
 DROP DATABASE IF EXISTS db1;
 DROP DATABASE IF EXISTS db2;
-
-create databases and tables
-
+#
+# create databases and tables
+#
 CREATE DATABASE db1;
 CREATE DATABASE db2;
 CREATE TABLE db1.t1(a INT, b CHAR(20), years YEAR);
@@ -17,14 +17,14 @@ INSERT INTO db1.t1 VALUES
 INSERT INTO db1.t2 VALUES(10,'a'),(20,'b'),(30,'c'),(40,'d');
 INSERT INTO db2.t1 VALUES(10,'a1'),(20,'b1'),(30,'c1'),(40,'d1');
 INSERT INTO db2.t2 VALUES(100,'2008-09-09'),(200,'1996-06-13');
-
-create users
-
+#
+# create users
+#
 CREATE USER 'jim'@'%';
 CREATE USER 'carry'@'%';
-
-create procedures and functions.
-
+#
+# create procedures and functions.
+#
 CREATE PROCEDURE db1.p11(xy INT)
 SQL SECURITY DEFINER SELECT 1;||
 CREATE PROCEDURE db2.p21(a CHAR(20))
@@ -37,9 +37,9 @@ CREATE FUNCTION db2.f21() RETURNS INT
 BEGIN 
 RETURN(SELECT COUNT(*) FROM db1.t2);
 END;||
-
-Grant Routine level privileges to users
-
+#
+# Grant Routine level privileges to users
+#
 GRANT EXECUTE ON PROCEDURE db2.p21 TO jim@'%';
 GRANT EXECUTE ON FUNCTION db1.f11 TO jim@'%';
 GRANT SELECT on db1.* TO jim@'%';
@@ -47,8 +47,8 @@ GRANT SELECT on db2.* TO jim@'%';
 GRANT EXECUTE ON PROCEDURE db1.p11 TO carry@'%';
 GRANT EXECUTE ON FUNCTION db2.f21 TO carry@'%';
 FLUSH PRIVILEGES;
-
-Verify the grant privileges for users
+#
+# Verify the grant privileges for users
 SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA 
 FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES 
 WHERE TABLE_SCHEMA='db1' OR TABLE_SCHEMA='db2' 
@@ -64,19 +64,18 @@ Host	Db	User	Routine_name	Routine_type	G
 %	db1	jim	f11	FUNCTION	root@localhost	Execute	#
 %	db2	carry	f21	FUNCTION	root@localhost	Execute	#
 %	db2	jim	p21	PROCEDURE	root@localhost	Execute	#
-Perform backup
+# Perform backup
 BACKUP DATABASE db1, db2 TO 'db12.bak';
 backup_id
 #
-Drop database and users
+# Drop databases but not users
 DROP DATABASE db1;
 DROP DATABASE db2;
-Perform restore and verify that restore properly reapplies all 
-grants for users.
+# Perform restore and verify that all grants are retained.
 RESTORE FROM 'db12.bak';
 backup_id
 #
-Verify the objects and grants
+# Verify the objects and grants
 SHOW TABLES FROM db1;
 Tables_in_db1
 t1
@@ -108,8 +107,8 @@ Host	Db	User	Routine_name	Routine_type	G
 %	db1	jim	f11	FUNCTION	root@localhost	Execute	#
 %	db2	carry	f21	FUNCTION	root@localhost	Execute	#
 %	db2	jim	p21	PROCEDURE	root@localhost	Execute	#
-We notice that all the grants are restored intact.
-Excercise objects
+# We notice that all the grants are intact.
+# Excercise objects
 CALL db1.p11(2);
 1
 1
@@ -143,17 +142,17 @@ SELECT * FROM db2.t2;
 d1	e1
 100	2008-09-09
 200	1996-06-13
-
-Drop database and users
+#
+# Drop databases and users
 DROP DATABASE db1;
 DROP DATABASE db2;
 DROP USER jim@'%';
 DROP USER carry@'%';
-
-Recreate users
+#
+# Recreate users
 CREATE USER 'jim'@'%';
 CREATE USER 'carry'@'%';
-Verify grants for users
+# Verify grants for users
 FLUSH PRIVILEGES;
 SHOW GRANTS FOR jim@'%';
 Grants for jim@%
@@ -161,11 +160,11 @@ GRANT USAGE ON *.* TO 'jim'@'%'
 SHOW GRANTS FOR carry@'%';
 Grants for carry@%
 GRANT USAGE ON *.* TO 'carry'@'%'
-Ensure that restore properly reapplies all grants for users.
+# Ensure that restore properly reapplies all grants.
 RESTORE FROM 'db12.bak';
 backup_id
 #
-Verify the objects and grants
+# Verify the objects and grants
 FLUSH PRIVILEGES;
 SHOW TABLES FROM db1;
 Tables_in_db1
@@ -194,10 +193,12 @@ SELECT * FROM mysql.procs_priv 
 WHERE Db='db1' OR Db='db2' 
 ORDER BY Db, User, Routine_name;
 Host	Db	User	Routine_name	Routine_type	Grantor	Proc_priv	Timestamp
-From the above table, We can notice that grants for stored routines will
-not be restored. Bug#41979
-Drop users, then restore and verify the grants.
-Restore will throw a warning message
+%	db1	carry	p11	PROCEDURE	root@localhost	Execute	#
+%	db1	jim	f11	FUNCTION	root@localhost	Execute	#
+%	db2	carry	f21	FUNCTION	root@localhost	Execute	#
+%	db2	jim	p21	PROCEDURE	root@localhost	Execute	#
+# Drop databases and users, then restore and verify the grants.
+# Restore will throw warning messages
 DROP DATABASE db1;
 DROP DATABASE db2;
 DROP USER jim@'%';
@@ -207,9 +208,13 @@ backup_id
 #
 SHOW WARNINGS;
 Level	Code	Message
+Warning	#	The grant 'Execute ON PROCEDURE db1.p11' for the user 'carry'@'%' was skipped because the user does not exist.
+Warning	#	The grant 'Execute ON FUNCTION db1.f11' for the user 'jim'@'%' was skipped because the user does not exist.
 Warning	#	The grant 'SELECT ON db1.*' for the user 'jim'@'%' was skipped because the user does not exist.
+Warning	#	The grant 'Execute ON FUNCTION db2.f21' for the user 'carry'@'%' was skipped because the user does not exist.
+Warning	#	The grant 'Execute ON PROCEDURE db2.p21' for the user 'jim'@'%' was skipped because the user does not exist.
 Warning	#	The grant 'SELECT ON db2.*' for the user 'jim'@'%' was skipped because the user does not exist.
-Verify grants after restore
+# Verify grants after restore
 SHOW GRANTS FOR jim@'%';
 ERROR 42000: There is no such grant defined for user 'jim' on host '%'
 SHOW GRANTS FOR carry@'%';

=== modified file 'mysql-test/suite/backup/t/backup_db_grants.test'
--- a/mysql-test/suite/backup/t/backup_db_grants.test	2009-02-24 20:57:21 +0000
+++ b/mysql-test/suite/backup/t/backup_db_grants.test	2009-03-04 10:20:21 +0000
@@ -47,7 +47,6 @@ SHOW GRANTS FOR 'no_user'@'%';
 --echo Run backup
 --replace_column 1 #
 BACKUP DATABASE bup_db_grants TO 'bup_db_grants.bak';
-
 --echo Drop users and recreate them (removes grants completely).
 DROP USER 'bup_user1'@'%';
 DROP USER bup_user2;
@@ -201,6 +200,79 @@ SET NAMES latin1;
 
 DROP DATABASE bup_db_grants;
 DROP DATABASE db2;
+--remove_file $MYSQLD_BACKUPDIR/bup_db_grants.bak
 
---error 0,1
+--echo #
+--echo # Bug#41979 - Routine level grants not restored when user is dropped,
+--echo #             recreated before restore
+--echo #
+--echo # Create database, table, and users.
+CREATE DATABASE bup_db_grants;
+CREATE TABLE bup_db_grants.t1(c1 INT, c2 CHAR(20));
+CREATE USER 'bup_user1'@'%';
+CREATE USER 'bup_user2'@'doodle';
+#
+--echo # Create stored procedure.
+--delimiter ||
+CREATE PROCEDURE bup_db_grants.p1(p1 CHAR(20))
+  BEGIN
+    INSERT INTO bup_db_grants.t1 VALUES(50,'e');
+    INSERT INTO bup_db_grants.t1 VALUES(50,'e');
+  END||
+--delimiter ;
+--echo # Grant privileges on the procedure.
+GRANT EXECUTE ON PROCEDURE bup_db_grants.p1 TO 'bup_user1'@'%';
+GRANT ALTER ROUTINE ON PROCEDURE bup_db_grants.p1 TO 'bup_user2'@'doodle';
+#
+--echo # Create stored function.
+--delimiter ||
+CREATE FUNCTION bup_db_grants.f1(p1 CHAR(20)) RETURNS INT
+  BEGIN
+    RETURN (SELECT c1 FROM bup_db_grants.t1 WHERE c2 = p1 LIMIT 1);
+  END||
+--delimiter ;
+--echo # Grant privileges on the function.
+GRANT EXECUTE ON FUNCTION bup_db_grants.f1 TO 'bup_user1'@'%';
+GRANT ALTER ROUTINE ON FUNCTION bup_db_grants.f1 TO 'bup_user2'@'doodle';
+#
+FLUSH PRIVILEGES;
+#
+--echo # Demonstrate rights of the users.
+SHOW GRANTS FOR 'bup_user1'@'%';
+SHOW GRANTS FOR 'bup_user2'@'doodle';
+#
+--echo # Run backup
+--replace_column 1 #
+BACKUP DATABASE bup_db_grants TO 'bup_db_grants.bak';
+#
+--echo # Drop users and recreate them (removes grants completely).
+DROP USER 'bup_user1'@'%';
+DROP USER 'bup_user2'@'doodle';
+#
+FLUSH PRIVILEGES;
+#
+CREATE USER 'bup_user1'@'%';
+CREATE USER 'bup_user2'@'doodle';
+#
+FLUSH PRIVILEGES;
+#
+--echo # Demonstrate rights of the users.
+SHOW GRANTS FOR 'bup_user1'@'%';
+SHOW GRANTS FOR 'bup_user2'@'doodle';
+#
+--echo # Run Restore
+--replace_column 1 #
+RESTORE FROM 'bup_db_grants.bak' OVERWRITE;
+#
+FLUSH PRIVILEGES;
+#
+--echo # Demonstrate rights of the users.
+SHOW GRANTS FOR 'bup_user1'@'%';
+SHOW GRANTS FOR 'bup_user2'@'doodle';
+#
+--echo # Cleanup
+DROP USER 'bup_user1'@'%';
+DROP USER 'bup_user2'@'doodle';
+DROP DATABASE bup_db_grants;
 --remove_file $MYSQLD_BACKUPDIR/bup_db_grants.bak
+

=== modified file 'mysql-test/suite/backup/t/backup_db_grants_extra.test'
--- a/mysql-test/suite/backup/t/backup_db_grants_extra.test	2009-02-26 13:53:17 +0000
+++ b/mysql-test/suite/backup/t/backup_db_grants_extra.test	2009-03-04 10:20:21 +0000
@@ -1,6 +1,6 @@
---echo Test - 4
---echo Test to ensure routine-level GRANT statements are recorded in the
---echo backup image and restore reapplies the GRANT statements.
+--echo # Test - 4
+--echo # Test to ensure routine-level GRANT statements are recorded in the
+--echo # backup image and restore reapplies the GRANT statements.
 
 --source include/not_embedded.inc
 
@@ -11,9 +11,9 @@ DROP DATABASE IF EXISTS db1;
 DROP DATABASE IF EXISTS db2;
 --enable_warnings
 
---echo
---echo create databases and tables
---echo
+--echo #
+--echo # create databases and tables
+--echo #
 
 CREATE DATABASE db1;
 CREATE DATABASE db2;
@@ -30,16 +30,16 @@ INSERT INTO db1.t2 VALUES(10,'a'),(20,'b
 INSERT INTO db2.t1 VALUES(10,'a1'),(20,'b1'),(30,'c1'),(40,'d1');
 INSERT INTO db2.t2 VALUES(100,'2008-09-09'),(200,'1996-06-13');
 
---echo
---echo create users
---echo
+--echo #
+--echo # create users
+--echo #
 
 CREATE USER 'jim'@'%';
 CREATE USER 'carry'@'%';
 
---echo
---echo create procedures and functions.
---echo
+--echo #
+--echo # create procedures and functions.
+--echo #
 
 DELIMITER ||;
 
@@ -61,9 +61,9 @@ CREATE FUNCTION db2.f21() RETURNS INT
 
 DELIMITER ;||
 
---echo
---echo Grant Routine level privileges to users
---echo
+--echo #
+--echo # Grant Routine level privileges to users
+--echo #
 
 GRANT EXECUTE ON PROCEDURE db2.p21 TO jim@'%';
 GRANT EXECUTE ON FUNCTION db1.f11 TO jim@'%';
@@ -74,8 +74,8 @@ GRANT EXECUTE ON PROCEDURE db1.p11 TO ca
 GRANT EXECUTE ON FUNCTION db2.f21 TO carry@'%';
 FLUSH PRIVILEGES;
 
---echo
---echo Verify the grant privileges for users
+--echo #
+--echo # Verify the grant privileges for users
 
 SELECT GRANTEE, PRIVILEGE_TYPE, TABLE_SCHEMA 
 FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES 
@@ -86,22 +86,21 @@ SELECT * FROM mysql.procs_priv 
  WHERE Db='db1' OR Db='db2' 
  ORDER BY Db, User, Routine_name;
 
---echo Perform backup
+--echo # Perform backup
 --replace_column 1 #
 BACKUP DATABASE db1, db2 TO 'db12.bak';
 
---echo Drop database and users
+--echo # Drop databases but not users
 
 DROP DATABASE db1;
 DROP DATABASE db2;
 
---echo Perform restore and verify that restore properly reapplies all 
---echo grants for users.
+--echo # Perform restore and verify that all grants are retained.
 
 --replace_column 1 #
 RESTORE FROM 'db12.bak';
 
---echo Verify the objects and grants
+--echo # Verify the objects and grants
 
 SHOW TABLES FROM db1;
 SHOW TABLES FROM db2;
@@ -119,8 +118,8 @@ SELECT * FROM mysql.procs_priv 
  WHERE Db='db1' OR Db='db2' 
  ORDER BY Db, User, Routine_name;
 
---echo We notice that all the grants are restored intact.
---echo Excercise objects
+--echo # We notice that all the grants are intact.
+--echo # Excercise objects
 
 CALL db1.p11(2);
 CALL db2.p21('TEST');
@@ -132,31 +131,31 @@ SELECT * FROM db2.t1;
 SELECT * FROM db1.t2;
 SELECT * FROM db2.t2;
 
---echo
---echo Drop database and users
+--echo #
+--echo # Drop databases and users
 
 DROP DATABASE db1;
 DROP DATABASE db2;
 DROP USER jim@'%';
 DROP USER carry@'%';
 
---echo
---echo Recreate users
+--echo #
+--echo # Recreate users
 
 CREATE USER 'jim'@'%';
 CREATE USER 'carry'@'%';
 
---echo Verify grants for users
+--echo # Verify grants for users
 FLUSH PRIVILEGES;
 SHOW GRANTS FOR jim@'%';
 SHOW GRANTS FOR carry@'%';
 
---echo Ensure that restore properly reapplies all grants for users.
+--echo # Ensure that restore properly reapplies all grants.
 
 --replace_column 1 #
 RESTORE FROM 'db12.bak';
 
---echo Verify the objects and grants
+--echo # Verify the objects and grants
 
 FLUSH PRIVILEGES;
 SHOW TABLES FROM db1;
@@ -175,11 +174,8 @@ SELECT * FROM mysql.procs_priv 
  WHERE Db='db1' OR Db='db2' 
  ORDER BY Db, User, Routine_name;
 
---echo From the above table, We can notice that grants for stored routines will
---echo not be restored. Bug#41979
-
---echo Drop users, then restore and verify the grants.
---echo Restore will throw a warning message
+--echo # Drop databases and users, then restore and verify the grants.
+--echo # Restore will throw warning messages
 
 DROP DATABASE db1;
 DROP DATABASE db2;
@@ -193,7 +189,7 @@ RESTORE FROM 'db12.bak';
 --replace_column 2 #
 SHOW WARNINGS;
 
---echo Verify grants after restore
+--echo # Verify grants after restore
 # If the users are dropped, grants will not be restored.
 
 --error ER_NONEXISTING_GRANT

=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc	2009-02-27 11:16:44 +0000
+++ b/sql/backup/kernel.cc	2009-03-04 10:20:21 +0000
@@ -2091,6 +2091,19 @@ int bcat_create_item(st_bstream_image_he
     /*
       We need to check the grant against the database list to ensure the
       grants have not been altered to apply to another database.
+      At the time of fixing Bug#41979 (Routine level grants not restored
+      when user is dropped, recreated before restore), a GRANT "statement"
+      looks like so:
+          15 'bup_user2'@'%'
+          29 SELECT(b) ON bup_db_grants.s1
+          32 SET character_set_client= binary
+          54 GRANT SELECT(b) ON bup_db_grants.s1 TO 'bup_user2'@'%'
+
+      For procedures and functions:
+          15 'bup_user1'@'%'
+          37 Execute ON PROCEDURE bup_db_grants.p1
+          32 SET character_set_client= binary
+          62 GRANT Execute ON PROCEDURE bup_db_grants.p1 TO 'bup_user1'@'%'
     */
     ::String db_name;  // db name extracted from grant statement
     char *start;
@@ -2098,6 +2111,10 @@ int bcat_create_item(st_bstream_image_he
     int size= 0;
 
     start= strstr((char *)create_stmt.begin, "ON ") + 3;
+    if (!strncmp(start, "PROCEDURE ", 10))
+      start+= 10;
+    if (!strncmp(start, "FUNCTION ", 9))
+      start+= 9;
     end= strstr(start, ".");
     size= end - start;
     db_name.alloc(size);

=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc	2009-02-26 13:53:17 +0000
+++ b/sql/si_objects.cc	2009-03-04 10:20:21 +0000
@@ -2175,7 +2175,7 @@ const String *Tablespace_obj::get_descri
 
   /* Either description or id and data file name must be not empty. */
   DBUG_ASSERT(m_description.length() ||
-              m_id.length() && m_data_file_name.length());
+              (m_id.length() && m_data_file_name.length()));
 
   if (m_description.length())
     DBUG_RETURN(&m_description);
@@ -2246,6 +2246,7 @@ Grant_obj::Grant_obj(const Ed_row &row)
   const LEX_STRING *db_name= row.get_column(2);
   const LEX_STRING *tbl_name= row.get_column(3);
   const LEX_STRING *col_name= row.get_column(4);
+  const LEX_STRING *routine_type= row.get_column(5);
 
   LEX_STRING table_name= { C_STRING_WITH_LEN("") };
   LEX_STRING column_name= { C_STRING_WITH_LEN("") };
@@ -2263,10 +2264,21 @@ Grant_obj::Grant_obj(const Ed_row &row)
   String_stream s_stream(&m_grant_info);
   s_stream << privilege_type;
 
+  /*
+    Either column_name or routine_type or both are NULL.
+    They are never both non-NULL.
+  */
+  DBUG_ASSERT(!column_name.length || !routine_type->length);
+
   if (column_name.length)
     s_stream << "(" << &column_name << ")";
 
-  s_stream << " ON " << db_name << ".";
+  s_stream << " ON ";
+
+  if (routine_type->length)
+    s_stream << routine_type << " ";
+
+  s_stream << db_name << ".";
 
   if (table_name.length)
     s_stream << &table_name;
@@ -2463,7 +2475,8 @@ Obj_iterator *get_all_db_grants(THD *thd
     "privilege_type AS c2, "
     "table_schema AS c3, "
     "NULL AS c4, "
-    "NULL AS c5 "
+    "NULL AS c5, "
+    "NULL AS c6 "
     "FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES "
     "WHERE table_schema = '" << db_name << "') "
     "UNION "
@@ -2471,6 +2484,7 @@ Obj_iterator *get_all_db_grants(THD *thd
     "privilege_type, "
     "table_schema, "
     "table_name, "
+    "NULL, "
     "NULL "
     "FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES "
     "WHERE table_schema = '" << db_name << "') "
@@ -2479,10 +2493,20 @@ Obj_iterator *get_all_db_grants(THD *thd
     "privilege_type, "
     "table_schema, "
     "table_name, "
-    "column_name "
+    "column_name, "
+    "NULL "
     "FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES "
     "WHERE table_schema = '" << db_name << "') "
-    "ORDER BY c1 ASC, c2 ASC, c3 ASC, c4 ASC, c5 ASC";
+    "UNION "
+    "(SELECT CONCAT('''', User, '''@''', Host, ''''), "
+    "Proc_priv, "
+    "Db, "
+    "Routine_name, "
+    "NULL, "
+    "Routine_type "
+    "FROM mysql.procs_priv "
+    "WHERE Db = '" << db_name << "') "
+    "ORDER BY c1 ASC, c2 ASC, c3 ASC, c4 ASC, c5 ASC, c6 ASC";
 
   return create_row_set_iterator<Grant_iterator>(thd, s_stream.lex_string());
 }


Attachment: [text/bzr-bundle] bzr/ingo.struewing@sun.com-20090304102021-3htrw472lvjpesuk.bundle
Thread
bzr commit into mysql-6.0-backup branch (ingo.struewing:2783) Bug#41979Ingo Struewing4 Mar
  • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2783)Bug#41979Jørgen Løland5 Mar
  • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2783)Bug#41979Rafal Somla6 Mar