MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Chuck Bell Date:November 6 2009 2:47pm
Subject:bzr commit into mysql-6.0-backup branch (charles.bell:2888) Bug#44787
View as plain text  
#At file:///Users/cbell/source/bzr/mysql-6.0-bug-44787-precheck/ based on revid:charles.bell@stripped

 2888 Chuck Bell	2009-11-06
      BUG#44787 : Backup: Check privileges before executing BACKUP/RESTORE
      
      Restore can fail in the middle if the user does not have 
      sufficient privileges to create and populate all of the
      objects.
      
      This patch implements a privilege precheck step to check
      all objects for proper access. If any object fails the
      privilege check, restore halts with an error.
      
      The object-level privileges checked include the following.
        
        RESTORE,CREATE,DROP on db.*
        CREATE              on db.x (if table or view x)
        CREATE_TABLESPACE   on *.*  (if tablespace)
        SUPER               on *.*  (if view, stored routine, event or trigger)
        CREATE_PROC         on db.* (if stored routine)
        EVENT               on db.* (if event)
        GRANT               on db.* (if privilege)
        TRIGGER             on db.* (if trigger but table not found)
        TRIGGER             on db.t (if trigger on t)
      
      Note: This is patch 1 of 3. Patch 2 implements elevation,
      patch 3 implements the options to skip precheck and turn
      elevation off.
     @ mysql-test/suite/backup/include/error_name_to_number.inc
        Added new error code for debug insertion testing.
     @ mysql-test/suite/backup/r/backup_errors_debug_3.result
        Corrected resut file.
     @ mysql-test/suite/backup/r/backup_restore_security.result
        New result file.
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected resut file.
     @ mysql-test/suite/backup/t/backup_errors_debug_3.test
        Added test case for when db is not found in catalog.
     @ mysql-test/suite/backup/t/backup_restore_security.test
        New test for testing restore security prechecks.
     @ mysql-test/suite/backup/t/backup_security.test
        Corrected errors now that restore prechecking is complete.
     @ sql/backup/kernel.cc
        Added call to do object-level privilege checking.
     @ sql/backup/restore_info.h
        Added code to conduct privilege checking for all objects
        prior to executing restore. For tables, views, and triggers
        the check is object-level, for tablespace it is global-level,
        and for all other objects it is database-level.
        
        Moved RESTORE_ACL check to new method in kernel.cc so that
        it occurs prior to prechecking object-level privileges.
     @ sql/share/errmsg-utf8.txt
        New error messages.
     @ sql/share/errmsg.txt
        New error messages.

    added:
      mysql-test/suite/backup/r/backup_restore_security.result
      mysql-test/suite/backup/t/backup_restore_security.test
    modified:
      mysql-test/suite/backup/include/error_name_to_number.inc
      mysql-test/suite/backup/r/backup_errors_debug_3.result
      mysql-test/suite/backup/r/backup_security.result
      mysql-test/suite/backup/t/backup_errors_debug_3.test
      mysql-test/suite/backup/t/backup_security.test
      sql/backup/kernel.cc
      sql/backup/restore_info.h
      sql/share/errmsg-utf8.txt
      sql/share/errmsg.txt
=== modified file 'mysql-test/suite/backup/include/error_name_to_number.inc'
--- a/mysql-test/suite/backup/include/error_name_to_number.inc	2009-09-10 13:31:31 +0000
+++ b/mysql-test/suite/backup/include/error_name_to_number.inc	2009-11-06 14:47:36 +0000
@@ -123,3 +123,4 @@
 --LET $ER_BACKUP_ACCESS_DENIED_ERROR = 1793
 --LET $ER_BACKUP_ACCESS_DBS_INCOMPLETE = 1795
 --LET $ER_BACKUP_ACCESS_OBJS_INCOMPLETE = 1796
+--LET $ER_RESTORE_DB_ERROR = 1800

=== modified file 'mysql-test/suite/backup/r/backup_errors_debug_3.result'
--- a/mysql-test/suite/backup/r/backup_errors_debug_3.result	2009-09-10 13:31:31 +0000
+++ b/mysql-test/suite/backup/r/backup_errors_debug_3.result	2009-11-06 14:47:36 +0000
@@ -1265,7 +1265,45 @@ backup_id
 # Done.
 #
 #
-# Test case 69 - testing error ER_BACKUP_UNEXPECTED_DATA
+# Test case 69 - testing error ER_RESTORE_DB_ERROR
+#
+# Set debug SESSION variable for ER_RESTORE_DB_ERROR
+#
+SET SESSION debug="+d,ER_RESTORE_DB_ERROR";
+#
+# Execute restore
+#
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+ERROR HY000: The database for object 't1' was not found in the catalog.
+#
+# Test case for error ER_RESTORE_DB_ERROR PASSED.
+#
+#
+# Show warning/error from progress and history log
+# if they exist.
+#
+SELECT * FROM mysql.backup_progress WHERE error_num <> 0;
+backup_id	object	error_num	notes
+#		####	The database for object 't1' was not found in the catalog.
+PURGE BACKUP LOGS;
+#
+# Turn off debug SESSION.
+#
+SET SESSION debug="-d";
+#
+# Now demonstrate that the command will work without the
+# debug session tag.
+#
+# Running restore - should not fail.
+#
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+backup_id
+#
+#
+# Done.
+#
+#
+# Test case 70 - testing error ER_BACKUP_UNEXPECTED_DATA
 #
 # Set debug SESSION variable for ER_BACKUP_UNEXPECTED_DATA
 #
@@ -1306,7 +1344,7 @@ DROP DATABASE test_empty;
 # Done.
 #
 #
-# Test case 70 - testing error ER_BACKUP_WRONG_TABLE_BE
+# Test case 71 - testing error ER_BACKUP_WRONG_TABLE_BE
 #
 # Set debug SESSION variable for ER_BACKUP_WRONG_TABLE_BE
 #

=== added file 'mysql-test/suite/backup/r/backup_restore_security.result'
--- a/mysql-test/suite/backup/r/backup_restore_security.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_restore_security.result	2009-11-06 14:47:36 +0000
@@ -0,0 +1,297 @@
+DROP DATABASE IF EXISTS backup_test;
+DROP DATABASE IF EXISTS backup_test_alt;
+#
+# Create users.
+#
+CREATE USER 'bup_some_priv'@'localhost';
+CREATE USER 'joe'@'user';
+#
+# Use the basic data setup in backup_test database.
+#
+#
+# Create database and data to test.
+#
+CREATE DATABASE backup_test;
+CREATE TABLE backup_test.t1 (a char(30)) ENGINE=MEMORY;
+INSERT INTO backup_test.t1 VALUES ("01 Test Basic database example");
+INSERT INTO backup_test.t1 VALUES ("02 Test Basic database example");
+INSERT INTO backup_test.t1 VALUES ("03 Test Basic database example");
+INSERT INTO backup_test.t1 VALUES ("04 Test Basic database example");
+INSERT INTO backup_test.t1 VALUES ("05 Test Basic database example");
+INSERT INTO backup_test.t1 VALUES ("06 Test Basic database example");
+INSERT INTO backup_test.t1 VALUES ("07 Test Basic database example");
+CREATE TABLE backup_test.t2 (a char(30)) ENGINE=MYISAM;
+INSERT INTO backup_test.t2 VALUES ("11 Test Basic database example");
+INSERT INTO backup_test.t2 VALUES ("12 Test Basic database example");
+INSERT INTO backup_test.t2 VALUES ("13 Test Basic database example");
+#
+# Now create more database objects for test.
+#
+CREATE PROCEDURE backup_test.p1(p1 CHAR(20))
+INSERT INTO backup_test.t1 VALUES ("50");
+CREATE TRIGGER backup_test.trg AFTER INSERT ON backup_test.t1 FOR EACH ROW
+INSERT INTO backup_test.t1 VALUES('Test objects count');
+CREATE FUNCTION backup_test.f1() RETURNS INT RETURN (SELECT 1);
+CREATE VIEW backup_test.v1 as SELECT * FROM backup_test.t1;
+CREATE EVENT backup_test.e1 ON SCHEDULE EVERY 1 YEAR DO
+DELETE FROM backup_test.t1 WHERE a = "not there";
+#
+# Now we need some privileges
+#
+GRANT ALL ON backup_test.* TO 'joe'@'user';
+#
+# Revoke grants for bup_some_priv
+#
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+# 
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+Grants for bup_some_priv@localhost
+GRANT USAGE ON *.* TO 'bup_some_priv'@'localhost'
+#
+# conn_root_user: Do backup of database with root user for later tests.
+#
+BACKUP DATABASE backup_test to 'backup_test_orig.bak';
+backup_id
+#
+#
+# Show list of all objects in the database.
+#
+SHOW FULL TABLES FROM backup_test;
+Tables_in_backup_test	Table_type
+t1	BASE TABLE
+t2	BASE TABLE
+v1	VIEW
+SELECT event_name FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'backup_test';
+event_name
+e1
+SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema = 'backup_test';
+routine_name
+f1
+p1
+SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'backup_test';
+trigger_name
+trg
+#
+# conn_root_user: Do backup of database with root user without
+#                 stored procedures for later tests.
+#
+DROP PROCEDURE backup_test.p1;
+DROP FUNCTION backup_test.f1;
+BACKUP DATABASE backup_test to 'backup_test_no_proc.bak';
+backup_id
+#
+# 
+# Test Case 1 : Ensure a user with only RESTORE on db1.* cannot restore 
+#               the database.
+#
+GRANT RESTORE ON backup_test.* TO 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+# 
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+Grants for bup_some_priv@localhost
+GRANT USAGE ON *.* TO 'bup_some_priv'@'localhost'
+GRANT RESTORE ON `backup_test`.* TO 'bup_some_priv'@'localhost'
+#
+# Connect as user with only some privileges.
+#
+#
+# conn_some_priv: Attempting restore. Should fail with 
+# error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+#
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+ERROR HY000: Insufficient privileges. You do not have privileges to restore the object 'backup_test' from this backup image.
+SHOW ERRORS;
+Level	Code	Message
+Error	#	Insufficient privileges. You do not have privileges to restore the object 'backup_test' from this backup image.
+#
+# Test Case 2 : Ensure a user with only SELECT on db1.* cannot restore 
+#               the database.
+#
+#
+# Connect as root and add privileges.
+#
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT SELECT ON backup_test.* TO 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+# 
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+Grants for bup_some_priv@localhost
+GRANT USAGE ON *.* TO 'bup_some_priv'@'localhost'
+GRANT SELECT, RESTORE ON `backup_test`.* TO 'bup_some_priv'@'localhost'
+#
+# Connect as user with only some privileges.
+#
+#
+# conn_some_priv: Attempting restore. Should fail with 
+# error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+#
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+ERROR HY000: Insufficient privileges. You do not have privileges to restore the object 'backup_test' from this backup image.
+SHOW ERRORS;
+Level	Code	Message
+Error	#	Insufficient privileges. You do not have privileges to restore the object 'backup_test' from this backup image.
+#
+# Test Case 3 : Show that SUPER is needed to complete restore 
+#               when there are objects with definer clauses.
+#
+#
+# Connect as root and add privileges.
+#
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT ALL ON backup_test.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION;
+GRANT ALL ON mysql.* TO 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+# 
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+Grants for bup_some_priv@localhost
+GRANT USAGE ON *.* TO 'bup_some_priv'@'localhost'
+GRANT ALL PRIVILEGES ON `mysql`.* TO 'bup_some_priv'@'localhost'
+GRANT ALL PRIVILEGES ON `backup_test`.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION
+#
+# Connect as user with only some privileges.
+#
+#
+# conn_some_priv: Attempting restore. Should fail with
+# ER_RESTORE_ACCESS_DEFINER
+#
+RESTORE FROM 'backup_test_no_proc.bak' OVERWRITE;
+ERROR HY000: Insufficient privileges. You must have the SUPER privilege to restore the object 'backup_test'.'v1'.
+SHOW ERRORS;
+Level	Code	Message
+Error	#	Insufficient privileges. You must have the SUPER privilege to restore the object 'backup_test'.'v1'.
+#
+# Test Case 4 : Ensure a user can only restore the database if and only
+#               if she has all of the required permissions to create and 
+#               populate all objects.
+#
+#
+# Connect as root and add privileges.
+#
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT ALL ON backup_test.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION;
+GRANT SELECT ON mysql.* TO 'bup_some_priv'@'localhost';
+GRANT SUPER ON *.* TO 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+# 
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+Grants for bup_some_priv@localhost
+GRANT SUPER ON *.* TO 'bup_some_priv'@'localhost'
+GRANT ALL PRIVILEGES ON `mysql`.* TO 'bup_some_priv'@'localhost'
+GRANT ALL PRIVILEGES ON `backup_test`.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION
+#
+# Connect as user with only some privileges.
+#
+#
+# conn_some_priv: Attempting restore. Should succeed.
+#
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+backup_id
+#
+#
+# Connect as root and cleanup.
+#
+#
+# Test Case 5 : Show that the privilege check on TRIGGER is required to
+#               restore a table with a trigger.
+#
+# Perform a backup with only a table and a trigger.
+# No grants either.
+#
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+REVOKE ALL ON backup_test.* FROM 'bup_some_priv'@'localhost';
+DROP USER 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+CREATE DATABASE backup_test_trig;
+CREATE TABLE backup_test_trig.t1 (a char(30)) ENGINE=MEMORY;
+CREATE TRIGGER backup_test_trig.trg AFTER INSERT ON backup_test_trig.t1 FOR EACH ROW
+INSERT INTO backup_test_trig.t1 VALUES('Test objects count');
+BACKUP DATABASE backup_test_trig TO 'backup_test_trig.bak';
+backup_id
+#
+CREATE USER 'bup_some_priv'@'localhost';
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT INSERT, UPDATE, DELETE, BACKUP, RESTORE, SELECT, CREATE, DROP 
+ON backup_test_trig.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION;
+GRANT SELECT ON mysql.* TO 'bup_some_priv'@'localhost';
+GRANT SUPER ON *.* TO 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+# 
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+Grants for bup_some_priv@localhost
+GRANT SUPER ON *.* TO 'bup_some_priv'@'localhost'
+GRANT SELECT ON `mysql`.* TO 'bup_some_priv'@'localhost'
+GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, BACKUP, RESTORE ON `backup_test_trig`.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION
+#
+# Connect as user with only some privileges.
+#
+#
+# conn_some_priv: Attempting restore. Should fail with 
+# error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+#
+RESTORE FROM 'backup_test_trig.bak' OVERWRITE;
+ERROR HY000: Insufficient privileges. You do not have privileges to restore the object 'trg' from this backup image.
+SHOW ERRORS;
+Level	Code	Message
+Error	#	Insufficient privileges. You do not have privileges to restore the object 'trg' from this backup image.
+#
+# Connect as root and add privilege for trigger.
+#
+GRANT TRIGGER ON backup_test_trig.t1 TO 'bup_some_priv'@'localhost';
+FLUSH PRIVILEGES;
+#
+# Connect as user with only some privileges.
+#
+#
+# conn_some_priv: Attempting restore. Should succeed.
+#
+RESTORE FROM 'backup_test_trig.bak' OVERWRITE;
+backup_id
+#
+#
+# Connect as root and cleanup.
+#
+DROP DATABASE backup_test_trig;
+#
+# Compare to original backup image file.
+#
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+backup_id
+#
+#
+# Show list of all objects in the database.
+#
+SHOW FULL TABLES FROM backup_test;
+Tables_in_backup_test	Table_type
+t1	BASE TABLE
+t2	BASE TABLE
+v1	VIEW
+SELECT event_name FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'backup_test';
+event_name
+e1
+SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema = 'backup_test';
+routine_name
+f1
+p1
+SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'backup_test';
+trigger_name
+trg
+#
+# Cleanup
+#
+DROP USER 'bup_some_priv'@'localhost';
+DROP USER 'joe'@'user';
+DROP DATABASE backup_test;
+FLUSH PRIVILEGES;

=== modified file 'mysql-test/suite/backup/r/backup_security.result'
--- a/mysql-test/suite/backup/r/backup_security.result	2009-10-23 21:26:11 +0000
+++ b/mysql-test/suite/backup/r/backup_security.result	2009-11-06 14:47:36 +0000
@@ -390,11 +390,11 @@ Tables_in_backup_test
 #          the user to backup the database but not restore it.
 #
 #
-# conn_user1: Attempting restore. Should fail with one of the restore
-# catalog errors.
+# conn_user1: Attempting restore. Should fail with 
+# error ER_RESTORE_ACCESS_OBJS_INCOMPLETE.
 #
 RESTORE FROM 'bup_user1.bak' OVERWRITE;
-Got one of the listed errors
+ERROR HY000: Insufficient privileges. You do not have privileges to restore the object 'backup_test' from this backup image.
 #
 # Test 4 - Show that if a user has BACKUP on one database but not 
 #          another, the user cannot perform a backup of both databases.

=== modified file 'mysql-test/suite/backup/t/backup_errors_debug_3.test'
--- a/mysql-test/suite/backup/t/backup_errors_debug_3.test	2009-09-08 18:48:16 +0000
+++ b/mysql-test/suite/backup/t/backup_errors_debug_3.test	2009-11-06 14:47:36 +0000
@@ -49,11 +49,12 @@
 # 66. ER_BACKUP_LOGGER_INIT
 # 67. ER_BACKUP_READ_LOC
 # 68. ER_BACKUP_PREPARE_DRIVER
+# 69. ER_RESTORE_DB_ERROR
 #
 # Debug Insertion
 # ---------------
-# 69. ER_BACKUP_UNEXPECTED_DATA
-# 70. ER_BACKUP_WRONG_TABLE_BE
+# 70. ER_BACKUP_UNEXPECTED_DATA
+# 71. ER_BACKUP_WRONG_TABLE_BE
 #
 
 --source include/not_embedded.inc
@@ -350,9 +351,16 @@ LET $errname = ER_BACKUP_PREPARE_DRIVER;
 LET $operation = BACKUP;
 --source suite/backup/include/test_for_error.inc
 
+# Test for error ER_RESTORE_DB_ERROR.
+LET $caseno = 69;
+LET $errno = $ER_RESTORE_DB_ERROR;
+LET $errname = ER_RESTORE_DB_ERROR;
+LET $operation = RESTORE;
+--source suite/backup/include/test_for_error.inc
+
 # Test for error ER_BACKUP_UNEXPECTED_DATA.
 --echo #
---echo # Test case 69 - testing error ER_BACKUP_UNEXPECTED_DATA
+--echo # Test case 70 - testing error ER_BACKUP_UNEXPECTED_DATA
 --echo #
 --echo # Set debug SESSION variable for ER_BACKUP_UNEXPECTED_DATA
 --echo #
@@ -402,7 +410,7 @@ DROP DATABASE test_empty;
 --echo #
 
 # Test for error ER_BACKUP_WRONG_TABLE_BE.
-LET $caseno = 70;
+LET $caseno = 71;
 LET $errno = $ER_BACKUP_WRONG_TABLE_BE;
 LET $errname = ER_BACKUP_WRONG_TABLE_BE;
 LET $operation = RESTORE;

=== added file 'mysql-test/suite/backup/t/backup_restore_security.test'
--- a/mysql-test/suite/backup/t/backup_restore_security.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_restore_security.test	2009-11-06 14:47:36 +0000
@@ -0,0 +1,338 @@
+#
+# This test includes test cases for testing privilege checking on restore.
+#
+# Test Cases
+# ----------
+#  1. Ensure a user with only RESTORE on db1.* cannot restore the database.
+#  2. Ensure a user with only SELECT on db1.* cannot restore the database.
+#  3. Show that SUPER is needed when there are object that use a definer
+#     clause are in the backup image.
+#  4. Ensure a user can only restore the database if and only if she has
+#     all of the required permissions to create and populate all objects.
+#  5. Show that having TRIGGER on a specific table is required to restore
+#     a table with a trigger. 
+#
+
+--source include/not_embedded.inc
+
+disable_query_log;
+call mtr.add_suppression("Restore: Insufficient privileges");
+enable_query_log;
+
+connect (conn_root,localhost,root,,);
+
+--disable_warnings
+DROP DATABASE IF EXISTS backup_test;
+DROP DATABASE IF EXISTS backup_test_alt;
+--enable_warnings
+
+--echo #
+--echo # Create users.
+--echo #
+CREATE USER 'bup_some_priv'@'localhost';
+CREATE USER 'joe'@'user';
+
+--echo #
+--echo # Use the basic data setup in backup_test database.
+--echo #
+--source suite/backup/include/basic_data.inc
+
+--echo #
+--echo # Revoke grants for bup_some_priv
+--echo #
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+--echo # 
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+
+--echo #
+--echo # conn_root_user: Do backup of database with root user for later tests.
+--echo #
+
+--replace_column 1 #
+BACKUP DATABASE backup_test to 'backup_test_orig.bak';
+
+--echo #
+--echo # Show list of all objects in the database.
+--echo #
+SHOW FULL TABLES FROM backup_test;
+SELECT event_name FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'backup_test';
+SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema = 'backup_test';
+SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'backup_test';
+
+--echo #
+--echo # conn_root_user: Do backup of database with root user without
+--echo #                 stored procedures for later tests.
+--echo #
+
+DROP PROCEDURE backup_test.p1;
+DROP FUNCTION backup_test.f1;
+
+--replace_column 1 #
+BACKUP DATABASE backup_test to 'backup_test_no_proc.bak';
+
+--echo # 
+--echo # Test Case 1 : Ensure a user with only RESTORE on db1.* cannot restore 
+--echo #               the database.
+--echo #
+
+GRANT RESTORE ON backup_test.* TO 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+--echo # 
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+
+disconnect conn_root;
+--echo #
+--echo # Connect as user with only some privileges.
+--echo #
+connect (conn_some_priv,localhost,bup_some_priv,,);
+
+--echo #
+--echo # conn_some_priv: Attempting restore. Should fail with 
+--echo # error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+--echo #
+--replace_column 1 #
+--error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+--replace_column 2 #
+SHOW ERRORS;
+
+--echo #
+--echo # Test Case 2 : Ensure a user with only SELECT on db1.* cannot restore 
+--echo #               the database.
+--echo #
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and add privileges.
+--echo #
+connect (conn_root,localhost,root,,);
+
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT SELECT ON backup_test.* TO 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+--echo # 
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+
+disconnect conn_root;
+--echo #
+--echo # Connect as user with only some privileges.
+--echo #
+connect (conn_some_priv,localhost,bup_some_priv,,);
+
+--echo #
+--echo # conn_some_priv: Attempting restore. Should fail with 
+--echo # error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+--echo #
+--replace_column 1 #
+--error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+--replace_column 2 #
+SHOW ERRORS;
+
+--echo #
+--echo # Test Case 3 : Show that SUPER is needed to complete restore 
+--echo #               when there are objects with definer clauses.
+--echo #
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and add privileges.
+--echo #
+connect (conn_root,localhost,root,,);
+
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT ALL ON backup_test.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION;
+GRANT ALL ON mysql.* TO 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+--echo # 
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+
+disconnect conn_root;
+--echo #
+--echo # Connect as user with only some privileges.
+--echo #
+connect (conn_some_priv,localhost,bup_some_priv,,);
+
+--echo #
+--echo # conn_some_priv: Attempting restore. Should fail with
+--echo # ER_RESTORE_ACCESS_DEFINER
+--echo #
+--replace_column 1 #
+--error ER_RESTORE_ACCESS_DEFINER
+RESTORE FROM 'backup_test_no_proc.bak' OVERWRITE;
+--replace_column 2 #
+SHOW ERRORS;
+
+--echo #
+--echo # Test Case 4 : Ensure a user can only restore the database if and only
+--echo #               if she has all of the required permissions to create and 
+--echo #               populate all objects.
+--echo #
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and add privileges.
+--echo #
+connect (conn_root,localhost,root,,);
+
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+GRANT ALL ON backup_test.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION;
+GRANT SELECT ON mysql.* TO 'bup_some_priv'@'localhost';
+GRANT SUPER ON *.* TO 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+--echo # 
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+
+disconnect conn_root;
+--echo #
+--echo # Connect as user with only some privileges.
+--echo #
+connect (conn_some_priv,localhost,bup_some_priv,,);
+
+--echo #
+--echo # conn_some_priv: Attempting restore. Should succeed.
+--echo #
+--replace_column 1 #
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and cleanup.
+--echo #
+connect (conn_root,localhost,root,,);
+
+--echo #
+--echo # Test Case 5 : Show that the privilege check on TRIGGER is required to
+--echo #               restore a table with a trigger.
+
+--echo #
+--echo # Perform a backup with only a table and a trigger.
+--echo # No grants either.
+--echo #
+
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+REVOKE ALL ON backup_test.* FROM 'bup_some_priv'@'localhost';
+DROP USER 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+CREATE DATABASE backup_test_trig;
+CREATE TABLE backup_test_trig.t1 (a char(30)) ENGINE=MEMORY;
+CREATE TRIGGER backup_test_trig.trg AFTER INSERT ON backup_test_trig.t1 FOR EACH ROW
+ INSERT INTO backup_test_trig.t1 VALUES('Test objects count');
+
+--replace_column 1 #
+BACKUP DATABASE backup_test_trig TO 'backup_test_trig.bak';
+
+CREATE USER 'bup_some_priv'@'localhost';
+
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+
+GRANT INSERT, UPDATE, DELETE, BACKUP, RESTORE, SELECT, CREATE, DROP 
+ ON backup_test_trig.* TO 'bup_some_priv'@'localhost' WITH GRANT OPTION;
+GRANT SELECT ON mysql.* TO 'bup_some_priv'@'localhost';
+GRANT SUPER ON *.* TO 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+--echo # 
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_some_priv'@'localhost';
+
+disconnect conn_root;
+--echo #
+--echo # Connect as user with only some privileges.
+--echo #
+connect (conn_some_priv,localhost,bup_some_priv,,);
+
+--echo #
+--echo # conn_some_priv: Attempting restore. Should fail with 
+--echo # error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+--echo #
+--replace_column 1 #
+--error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+RESTORE FROM 'backup_test_trig.bak' OVERWRITE;
+--replace_column 2 #
+SHOW ERRORS;
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and add privilege for trigger.
+--echo #
+connect (conn_root,localhost,root,,);
+
+GRANT TRIGGER ON backup_test_trig.t1 TO 'bup_some_priv'@'localhost';
+
+FLUSH PRIVILEGES;
+
+disconnect conn_root;
+--echo #
+--echo # Connect as user with only some privileges.
+--echo #
+connect (conn_some_priv,localhost,bup_some_priv,,);
+
+--echo #
+--echo # conn_some_priv: Attempting restore. Should succeed.
+--echo #
+--replace_column 1 #
+RESTORE FROM 'backup_test_trig.bak' OVERWRITE;
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and cleanup.
+--echo #
+connect (conn_root,localhost,root,,);
+
+DROP DATABASE backup_test_trig;
+
+--echo #
+--echo # Compare to original backup image file.
+--echo #
+
+--replace_column 1 #
+RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+
+--echo #
+--echo # Show list of all objects in the database.
+--echo #
+SHOW FULL TABLES FROM backup_test;
+SELECT event_name FROM INFORMATION_SCHEMA.EVENTS WHERE event_schema = 'backup_test';
+SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_schema = 'backup_test';
+SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'backup_test';
+
+--echo #
+--echo # Cleanup
+--echo #
+
+DROP USER 'bup_some_priv'@'localhost';
+DROP USER 'joe'@'user';
+
+DROP DATABASE backup_test;
+FLUSH PRIVILEGES;
+
+let $MYSQLD_BACKUPDIR= `select @@backupdir`;
+remove_file $MYSQLD_BACKUPDIR/backup_test_orig.bak;
+remove_file $MYSQLD_BACKUPDIR/backup_test_trig.bak;
+remove_file $MYSQLD_BACKUPDIR/backup_test_no_proc.bak;

=== modified file 'mysql-test/suite/backup/t/backup_security.test'
--- a/mysql-test/suite/backup/t/backup_security.test	2009-10-23 21:26:11 +0000
+++ b/mysql-test/suite/backup/t/backup_security.test	2009-11-06 14:47:36 +0000
@@ -17,8 +17,8 @@
 --source include/not_embedded.inc
 
 disable_query_log;
-call mtr.add_suppression("Backup:");
-call mtr.add_suppression("Restore:");
+call mtr.add_suppression("Backup: Insufficient privileges");
+call mtr.add_suppression("Restore: Insufficient privileges");
 enable_query_log;
 
 connect (conn_root,localhost,root,,);
@@ -184,11 +184,11 @@ SHOW TABLES FROM backup_test;
 --echo #
 
 --echo #
---echo # conn_user1: Attempting restore. Should fail with one of the restore
---echo # catalog errors.
+--echo # conn_user1: Attempting restore. Should fail with 
+--echo # error ER_RESTORE_ACCESS_OBJS_INCOMPLETE.
 --echo #
 --replace_column 1 #
---error ER_BACKUP_CANT_RESTORE_DB, ER_BACKUP_CANT_RESTORE_TABLE, ER_BACKUP_CANT_RESTORE_VIEW, ER_BACKUP_CANT_RESTORE_SROUT, ER_BACKUP_CANT_RESTORE_EVENT, ER_BACKUP_CANT_RESTORE_TRIGGER
+--error ER_RESTORE_ACCESS_OBJS_INCOMPLETE
 eval RESTORE FROM 'bup_user1.bak' OVERWRITE;
 
 --echo #

=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc	2009-10-23 15:41:56 +0000
+++ b/sql/backup/kernel.cc	2009-11-06 14:47:36 +0000
@@ -1984,7 +1984,20 @@ int bcat_add_item(st_bstream_image_heade
                         item->name.begin,
                         item->type,
                         item->pos));
+  
+  /*
+    Do object-level privilege checking for all objects being restored.
+    The user must have, at a minimum, the ability to create each object 
+    plus any specific privileges for the object type (e.g. functions,
+    triggers).
+   
+    An error is returned if the user does not have the correct privileges
+    to create the object.
+  */
 
+  if (info->check_restore_privileges(item))
+    return BSTREAM_ERROR; 
+  
   switch (item->type) {
 
   case BSTREAM_IT_TABLESPACE:

=== modified file 'sql/backup/restore_info.h'
--- a/sql/backup/restore_info.h	2009-10-21 13:32:24 +0000
+++ b/sql/backup/restore_info.h	2009-11-06 14:47:36 +0000
@@ -45,6 +45,9 @@ public:
   Image_info::Db* add_db(const ::String&, uint);
   Image_info::Table* add_table(Image_info::Db&, const ::String&,
                                backup::Snapshot_info&, ulong);
+                               
+  bool check_restore_privileges(struct st_bstream_item_info *item);
+  
 private:
 
   /*
@@ -117,17 +120,6 @@ inline
 backup::Image_info::Db*
 Restore_info::add_db(const ::String &name, uint pos)
 {
-  /*
-    Check privileges for this database. User must have RESTORE
-    privilege in order to execute a restore.
-  */
-  DEBUG_SYNC(m_thd, "before_restore_privileges");
-  if (check_access(m_thd, RESTORE_ACL, name.ptr(), 0, 1, 1, 0))
-  {
-    m_log.report_error(ER_RESTORE_ACCESS_DENIED_ERROR, name.ptr());
-    return NULL;
-  }
-
   Db *db= Image_info::add_db(name, pos);
 
   if (!db)
@@ -153,4 +145,196 @@ Restore_info::add_table(Image_info::Db &
   return t;
 }
 
+/**
+  Perform privilege checking for restore.
+ 
+  This method checks the known minimal privileges needed to restore each
+  object in the backup image. This method is called once for every object in 
+  the catalog. It ensures there are no privilege-based restrictions that would 
+  prohibit a successful restore. 
+  
+  It begins by checking that the user has RESTORE for the database. If the user 
+  does not have RESTORE on any database in the backup image, an error is 
+  generated. Next, specific object-level privilege checking is performed for 
+  all of the objects in the database. The object-level privileges checked 
+  include the following.
+  
+  RESTORE,CREATE,DROP on db.*
+  CREATE              on db.x (if table or view x)
+  CREATE_TABLESPACE   on *.*  (if tablespace)
+  SUPER               on *.*  (if view, stored routine, event or trigger)
+  CREATE_PROC         on db.* (if stored routine)
+  EVENT               on db.* (if event)
+  GRANT               on db.* (if privilege)
+  TRIGGER             on db.* (if trigger but table not found)
+  TRIGGER             on db.t (if trigger on t)
+ 
+  @param[in] item     The item to check.
+ 
+  @returns 
+    @retval  FALSE    User has privileges for the object.
+    @retval  TRUE     User does not have privileges for object.
+*/
+inline
+bool 
+Restore_info::check_restore_privileges(struct st_bstream_item_info *item)
+{
+  using namespace backup;
+  int result= 0;
+  THD *thd= ::current_thd;          
+  Image_info::Db *db= NULL;         // Database object
+  st_bstream_table_info *tbl_info;  // Table information
+  st_bstream_dbitem_info *db_item;  // Database item information
+  TABLE_LIST tbl_list;
+  
+  backup::String item_name(item->name.begin, item->name.end);
+  const char *name_str= item_name.c_ptr_safe();
+
+  DBUG_ASSERT(item);
+  
+  /*
+    Check privileges for this database. User must have RESTORE
+    privilege in order to execute a restore.
+  */
+  DEBUG_SYNC(thd, "before_restore_privileges");
+  
+  if (item->type == BSTREAM_IT_DB)
+  {
+    if (check_access(thd, RESTORE_ACL, name_str, 0, 1, 1, 0))
+    {
+      m_log.report_error(ER_RESTORE_ACCESS_DENIED_ERROR, name_str);
+      return TRUE;
+    }
+  }
+
+  /*
+    Get the database for objects that have a database element.
+  */
+  switch (item->type) 
+  {
+  case BSTREAM_IT_TABLE:
+  case BSTREAM_IT_VIEW:
+  case BSTREAM_IT_SPROC:
+  case BSTREAM_IT_SFUNC:
+  case BSTREAM_IT_EVENT:
+  case BSTREAM_IT_TRIGGER:
+  case BSTREAM_IT_PRIVILEGE:
+    {
+      db_item= (st_bstream_dbitem_info*) item;
+
+      db= get_db(db_item->db->base.pos);
+      DBUG_EXECUTE_IF("ER_RESTORE_DB_ERROR", db= NULL;);
+      if (!db)
+      {
+        m_log.report_error(ER_RESTORE_DB_ERROR, name_str);
+        return TRUE;
+      }
+    }
+  default:
+    break;
+  } // switch (item->type)
+  
+  /*
+    Check privileges for each object based on type of object.
+  */
+  switch (item->type) 
+  {
+  case BSTREAM_IT_TABLESPACE:
+    {
+      result= !(thd->security_ctx->master_access & CREATE_TABLESPACE_ACL);
+      break;        
+    }
+  case BSTREAM_IT_DB:
+    {
+      result= check_access(thd, CREATE_ACL + DROP_ACL, name_str, 0, 1, 1, 0);
+      break;
+    }
+  case BSTREAM_IT_TABLE:
+  case BSTREAM_IT_VIEW:
+    {
+      tbl_list.init_one_table(db->name().ptr(), db->name().length(),
+                              name_str, strlen(name_str),
+                              name_str, TL_READ);
+      
+      result= check_table_access(thd, CREATE_ACL, &tbl_list, TRUE, TRUE, 1);
+      break;
+    }
+  case BSTREAM_IT_SPROC:
+  case BSTREAM_IT_SFUNC:
+    {
+      result= check_access(thd, CREATE_PROC_ACL, db->name().ptr(), 0, 1, 1, 0); 
+      break;
+    }
+  case BSTREAM_IT_EVENT:
+    {
+      result= check_access(thd, EVENT_ACL, db->name().ptr(), 0, 1, 1, 0); 
+      break;
+    }
+  case BSTREAM_IT_TRIGGER:
+    {
+      /*
+        Get the table reference for this trigger and check access.
+      */
+      Image_info::Table *tbl= get_table(db_item->snap_num, db_item->pos);
+      
+      /*
+        If the table is not found, revert to checking at the database
+        level.
+      */
+      if (!tbl)
+      {
+       result= check_access(thd, TRIGGER_ACL, db->name().ptr(), 0, 1, 1, 0); 
+      }
+      else 
+      {
+        tbl_list.init_one_table(db->name().ptr(), db->name().length(),
+                                tbl->name().ptr(), tbl->name().length(),
+                                tbl->name().ptr(), TL_READ);
+        result= check_table_access(thd, TRIGGER_ACL, &tbl_list, TRUE, TRUE, 1);
+      }
+      break;
+    }
+  case BSTREAM_IT_PRIVILEGE:
+    {
+      result= check_access(thd, GRANT_ACL, db->name().ptr(), 0, 1, 1, 0); 
+      break;
+    }
+  default:
+    break;
+  } // switch (item->type)
+    
+  // Return error if privilege check fails.
+  if (result)
+  {
+    m_log.report_error(ER_RESTORE_ACCESS_OBJS_INCOMPLETE, name_str);
+    return TRUE;
+  }
+
+  /*
+    Check for SUPER_ACL if any objects exist that have a definer clause.
+  */
+  switch (item->type) 
+  {
+  case BSTREAM_IT_VIEW:
+  case BSTREAM_IT_SPROC:
+  case BSTREAM_IT_SFUNC:
+  case BSTREAM_IT_EVENT:
+  case BSTREAM_IT_TRIGGER:
+    {
+      result= !(thd->security_ctx->master_access & SUPER_ACL);
+      break;
+    }
+  default:
+    break;
+  } // switch (item->type)
+  
+  // Return error if privilege check fails.
+  if (result)
+  {
+    m_log.report_error(ER_RESTORE_ACCESS_DEFINER, db->name().ptr(), name_str);
+    return TRUE;
+  }
+  return FALSE;
+}
+
 #endif /*RESTORE_INFO_H_*/

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2009-10-12 09:08:34 +0000
+++ b/sql/share/errmsg-utf8.txt	2009-11-06 14:47:36 +0000
@@ -6562,3 +6562,9 @@ ER_BACKUP_ACCESS_OBJS_INCOMPLETE
   eng "Insufficient privileges. You do not have privileges to backup database '%s'."
 ER_WARN_I_S_SKIPPED_TABLE
   eng "Table '%s'.'%s' was skipped since its definition is being modified by concurrent DDL statement."
+ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+  eng "Insufficient privileges. You do not have privileges to restore the object '%s' from this backup image."
+ER_RESTORE_ACCESS_DEFINER
+  eng "Insufficient privileges. You must have the SUPER privilege to restore the object '%s'.'%s'."
+ER_RESTORE_DB_ERROR
+  eng "The database for object '%s' was not found in the catalog."

=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt	2009-10-26 14:02:26 +0000
+++ b/sql/share/errmsg.txt	2009-11-06 14:47:36 +0000
@@ -6562,3 +6562,9 @@ ER_BACKUP_ACCESS_OBJS_INCOMPLETE
   eng "Insufficient privileges. You do not have privileges to backup database '%s'."
 ER_WARN_I_S_SKIPPED_TABLE
   eng "Table '%s'.'%s' was skipped since its definition is being modified by concurrent DDL statement."
+ER_RESTORE_ACCESS_OBJS_INCOMPLETE
+  eng "Insufficient privileges. You do not have privileges to restore the object '%s' from this backup image."
+ER_RESTORE_ACCESS_DEFINER
+  eng "Insufficient privileges. You must have the SUPER privilege to restore the object '%s'.'%s'."
+ER_RESTORE_DB_ERROR
+  eng "The database for object '%s' was not found in the catalog."


Attachment: [text/bzr-bundle] bzr/charles.bell@sun.com-20091106144736-fehm3o2px9fmqqss.bundle
Thread
bzr commit into mysql-6.0-backup branch (charles.bell:2888) Bug#44787Chuck Bell6 Nov
  • Re: bzr commit into mysql-6.0-backup branch (charles.bell:2888)Bug#44787Ingo Strüwing9 Nov
    • Re: bzr commit into mysql-6.0-backup branch (charles.bell:2888)Bug#44787Ingo Strüwing11 Nov
  • Re: bzr commit into mysql-6.0-backup branch (charles.bell:2888)Bug#44787Rafal Somla10 Nov