#At file:///Users/cbell/source/bzr/mysql-6.0-bug-44787-precheck/ based on revid:charles.bell@stripped
2888 Chuck Bell 2009-11-04
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.
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/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_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/r/backup_security.result
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
=== 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-04 19:34:44 +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 5 : 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 6 : 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 7 : 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-04 19:34:44 +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.
=== 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-04 19:34:44 +0000
@@ -0,0 +1,340 @@
+#
+# 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. Demonstrate that the user must have all privileges to run restore.
+# 4. Show that SUPER is needed to complete restore with binlog on.
+# 5. Show that SUPER is needed when there are object that use a definer
+# clause are in the backup image.
+# 6. 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.
+# 7. 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 5 : 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 6 : 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 7 : 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-04 19:34:44 +0000
@@ -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-04 19:34:44 +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-04 19:34:44 +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,199 @@ 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();
+
+ /*
+ 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;
+ }
+ }
+
+ /*
+ Check privileges for each object based on type of object.
+ */
+ switch (item->type)
+ {
+ case BSTREAM_IT_TABLESPACE:
+ {
+ result= check_global_access(thd, 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_info= (st_bstream_table_info*) item;
+ DBUG_ASSERT (tbl_info);
+
+ db= get_db(tbl_info->base.db->base.pos); // reports errors
+ DBUG_ASSERT(db);
+
+ 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:
+ {
+ st_bstream_dbitem_info *db_item= (st_bstream_dbitem_info*) item;
+ DBUG_ASSERT(db_item);
+
+ db= (Image_info::Db*) get_db(db_item->db->base.pos);
+ DBUG_ASSERT(db);
+
+ result= check_access(thd, CREATE_PROC_ACL, db->name().ptr(), 0, 1, 1, 0);
+ break;
+ }
+ case BSTREAM_IT_EVENT:
+ {
+ st_bstream_dbitem_info *db_item= (st_bstream_dbitem_info*) item;
+ DBUG_ASSERT(db_item);
+
+ db= (Image_info::Db*) get_db(db_item->db->base.pos);
+ DBUG_ASSERT(db);
+
+ result= check_access(thd, EVENT_ACL, db->name().ptr(), 0, 1, 1, 0);
+ break;
+ }
+ case BSTREAM_IT_TRIGGER:
+ {
+ st_bstream_dbitem_info *db_item= (st_bstream_dbitem_info*) item;
+ DBUG_ASSERT(db_item);
+
+ db= (Image_info::Db*) get_db(db_item->db->base.pos);
+ DBUG_ASSERT(db);
+
+ /*
+ 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:
+ {
+ st_bstream_dbitem_info *db_item= (st_bstream_dbitem_info*) item;
+ DBUG_ASSERT(db_item);
+
+ db= (Image_info::Db*) get_db(db_item->db->base.pos);
+ DBUG_ASSERT(db);
+
+ 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-04 19:34:44 +0000
@@ -6562,3 +6562,7 @@ 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'."
=== 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-04 19:34:44 +0000
@@ -6562,3 +6562,7 @@ 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'."
Attachment: [text/bzr-bundle] bzr/charles.bell@sun.com-20091104193444-19otr9uxmvwevgb3.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-backup branch (charles.bell:2888) Bug#44787 | Chuck Bell | 4 Nov |
