#At file:///Users/cbell/source/bzr/mysql-6.0-bug-44787/ based on revid:ingo.struewing@stripped
2875 Chuck Bell 2009-09-30
BUG#44787 :
This patch implements a prototype for the detailed privilege checking on
restore.
NOTICE: This is a prototype patch committed for the benefit of the backup team
to evaluate the solution proposed. A formal patch will be issued later once
all input is considered.
@ mysql-test/suite/backup/r/backup_restore_security.result
New result file.
@ mysql-test/suite/backup/t/backup_restore_security.test
New test for testing detailed privilege checking on restore.
NOTE: This is incomplete.
@ sql/backup/kernel.cc
Adds detailed privilege checking to the bcat_add_item() retore method.
@ sql/share/errmsg-utf8.txt
New error message.
@ sql/share/errmsg.txt
New error message.
added:
mysql-test/suite/backup/r/backup_restore_security.result
mysql-test/suite/backup/t/backup_restore_security.test
modified:
sql/backup/kernel.cc
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-10-01 01:23:27 +0000
@@ -0,0 +1,173 @@
+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';
+#
+# Setup grants for bup_some_priv
+#
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+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'
+#
+# 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
+#
+# 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 this backup image.
+SHOW ERRORS;
+Level Code Message
+Error # Insufficient privileges. You do not have privileges to restore this backup image.
+#
+# Connect as root and add privileges.
+#
+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 this backup image.
+SHOW ERRORS;
+Level Code Message
+Error # Insufficient privileges. You do not have privileges to restore this backup image.
+#
+# 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 SELECT 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.
+#
+#
+# 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;
=== 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-10-01 01:23:27 +0000
@@ -0,0 +1,168 @@
+#
+# This test includes tests for all of the security-related tasks for
+# detailed privilege checking on restore.
+#
+
+--source include/not_embedded.inc
+
+disable_query_log;
+call mtr.add_suppression("Backup:");
+call mtr.add_suppression("Restore:");
+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 # Setup grants for bup_some_priv
+--echo #
+REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
+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';
+
+--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';
+
+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;
+
+disconnect conn_some_priv;
+--echo #
+--echo # Connect as root and add privileges.
+--echo #
+connect (conn_root,localhost,root,,);
+
+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;
+
+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 # 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;
=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc 2009-09-21 11:39:02 +0000
+++ b/sql/backup/kernel.cc 2009-10-01 01:23:27 +0000
@@ -1923,6 +1923,143 @@ int bcat_close(st_bstream_image_header *
}
/**
+ Check access to a database-level object.
+
+ This method gets the database name for an object and checks
+ the privilege requested for the object.
+
+ @param[in] thd Current thread.
+ @param[in] item The item to check.
+ @param[in] info The restore info object.
+ @param[in] priv The privileged being checked.
+
+ @returns bool
+ @retval TRUE the user has the privilege
+ @retval FALSE the user does not have the privilege
+*/
+bool check_catalog_item_access(THD *thd,
+ struct st_bstream_item_info *item,
+ Restore_info *info,
+ ulong priv)
+{
+ using namespace backup;
+
+ st_bstream_dbitem_info *it= (st_bstream_dbitem_info*)item;
+ if (!it)
+ return BSTREAM_ERROR;
+
+ Image_info::Db *db= (Image_info::Db*) info->get_db(it->db->base.pos);
+ if (!db)
+ return BSTREAM_ERROR;
+
+ return check_access(thd, priv, db->name().ptr(), 0, 1, 1, 0);
+}
+
+/**
+ Perform detailed privilege checking for restore.
+
+ This method checks the known minimal privileges needed to restore each
+ object in the backup image.
+
+ @param[in] item The item to check.
+ @param[in] info The restore info object.
+ @param[in] name_str The name of the object.
+
+ @returns
+ @retval BSTREAM_OK User has privileges for the object.
+ @retval BSTREAM_ERROR User does not have privileges for object.
+*/
+int check_restore_privileges(struct st_bstream_item_info *item,
+ Restore_info *info,
+ const char *name_str)
+{
+ using namespace backup;
+ int result= 0;
+ THD *thd= ::current_thd;
+
+ Logger &log= info->m_log;
+
+ /*
+ 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:
+ {
+ st_bstream_table_info *it= (st_bstream_table_info*)item;
+ if (!it)
+ return BSTREAM_ERROR;
+
+ Image_info::Db *db= info->get_db(it->base.db->base.pos); // reports errors
+ if (!db)
+ return BSTREAM_ERROR;
+
+ TABLE_LIST *ptr= (TABLE_LIST*)alloc_root(thd->mem_root,
+ sizeof(TABLE_LIST));
+ if (!ptr)
+ return BSTREAM_ERROR;
+
+ ptr->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, ptr, TRUE, TRUE, 1);
+ break;
+ }
+ case BSTREAM_IT_SPROC:
+ case BSTREAM_IT_SFUNC:
+ {
+ result= check_catalog_item_access(thd, item, info, CREATE_PROC_ACL);
+ // If binary log is turned on we also have to have SUPER.
+ if (!result && obs::is_binlog_engaged())
+ result= check_global_access(thd, SUPER_ACL);
+ break;
+ }
+ case BSTREAM_IT_EVENT:
+ {
+ result= check_catalog_item_access(thd, item, info, EVENT_ACL);
+ break;
+ }
+ /*
+ Due to a limitation in how the catalog stores triggers (triggers
+ are per-table objects WRT privileges), trigger access can only
+ be checked on the database level.
+ */
+ case BSTREAM_IT_TRIGGER:
+ {
+ result= check_catalog_item_access(thd, item, info, TRIGGER_ACL);
+ break;
+ }
+ case BSTREAM_IT_PRIVILEGE:
+ {
+ result= check_catalog_item_access(thd, item, info, GRANT_ACL);
+ break;
+ }
+ default:
+ break;
+ } // switch (item->type)
+
+ // Return error if privilege check fails.
+ if (result)
+ {
+ log.report_error(ER_RESTORE_ACCESS_OBJS_INCOMPLETE);
+ return BSTREAM_ERROR;
+ }
+ return BSTREAM_OK;
+}
+
+/**
Add item to restore catalogue.
@todo Report errors.
@@ -1945,6 +2082,26 @@ int bcat_add_item(st_bstream_image_heade
item->name.begin,
item->type,
item->pos));
+
+ /*
+ Do detailed 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. events).
+
+ An error is returned if the user does not have the correct privileges
+ to create the object.
+ */
+
+ int result= check_restore_privileges(item, info, name_str.c_ptr_safe());
+ if (result != BSTREAM_OK)
+ return result;
+
+ // Return error if privilege check fails.
+ if (result)
+ {
+ log.report_error(ER_RESTORE_ACCESS_OBJS_INCOMPLETE);
+ return BSTREAM_ERROR;
+ }
switch (item->type) {
=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt 2009-09-10 14:06:46 +0000
+++ b/sql/share/errmsg-utf8.txt 2009-10-01 01:23:27 +0000
@@ -6562,3 +6562,5 @@ 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 this backup image."
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2009-09-10 14:06:46 +0000
+++ b/sql/share/errmsg.txt 2009-10-01 01:23:27 +0000
@@ -6562,3 +6562,5 @@ 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 this backup image."
Attachment: [text/bzr-bundle] bzr/charles.bell@sun.com-20091001012327-dhig12uoijsg390w.bundle
