#At file:///Users/cbell/source/bzr/mysql-6.0-bug-46569/ based on revid:rafal.somla@stripped
2884 Chuck Bell 2009-10-23 [merge]
BUG#46569 : Backup fails even when all privileges are assigned
to user on db.*
When an admin issues GRANT ALL on a database and the database
contains objects other than tables (such as stored procedures
and database-level grants), the default privileges granted by
GRANT ALL are not enough to successfully execute backup.
This is because there are additional privileges users need to
read the mysql database tables that contain procedure, event,
triggers, and grant information.
On 29 September, the backup team decided to overcome this
limitation by introducing temporary privilege elevation for
backup. This patch includes code to temporarily give the user
the privileges needed to read all metadata for all objects in
the database. This is granted only during the execution of the
SELECT statements inside si_objects.cc and does not infer any
additional privileges outside of the execution of backup.x
@ mysql-test/suite/backup/r/backup_security.result
Corrected result file.
@ mysql-test/suite/backup/r/backup_security_check.result
Corrected result file.
@ mysql-test/suite/backup/t/backup_security.test
Removed previous step-wise privilege check on backup with new
test cases for the elevated privilege backup.
@ mysql-test/suite/backup/t/backup_security_check.test
Changed tests that failed due to insufficient rights to passed
tests. This is because so long as a user has BACKUP granted on
a database, she can execute backup.
Also added test case to show one only needs BACKUP to execute the backup
database command.
@ sql/si_objects.cc
Implements backup privilege elevation for execution of internal
SELECTs against the mysql and information_schema databases.
The code is designed to hide the elevation and is only used for
elevating SELECT and SHOW CREATE statements.
modified:
mysql-test/suite/backup/r/backup_security.result
mysql-test/suite/backup/r/backup_security_check.result
mysql-test/suite/backup/t/backup_security.test
mysql-test/suite/backup/t/backup_security_check.test
sql/si_objects.cc
=== modified file 'mysql-test/suite/backup/r/backup_security.result'
--- a/mysql-test/suite/backup/r/backup_security.result 2009-10-20 07:42:47 +0000
+++ b/mysql-test/suite/backup/r/backup_security.result 2009-10-23 21:18:10 +0000
@@ -42,21 +42,13 @@ INSERT INTO backup_test_alt.t1 VALUES ("
#
CREATE USER 'bup_no_priv'@'localhost';
CREATE USER 'bup_root_user'@'localhost';
-CREATE USER 'bup_no_bup_priv'@'localhost';
-CREATE USER 'bup_some_priv'@'localhost';
-CREATE USER 'bup_select_priv'@'localhost';
-CREATE USER 'bup_full_priv'@'localhost';
-CREATE USER 'bup_other_priv'@'localhost';
+CREATE USER 'bup_user1'@'localhost';
#
# Grants privileges as follows:
# bup_no_priv - denied everything (poor chap)
# except SELECT ON backup_test_alt.*
# bup_root_user - super user account (like root)
-# bup_no_bup_priv - privileges to a database but no BACKUP or RESTORE
-# bup_some_priv - privileges to some database objects
-# and BACKUP and RESTORE
-# bup_full_priv - user gets database-level specific grants
-# bup_other_priv - user who has privileges other than select
+# bup_user1 - has BACKUP and RESTORE only
#
#
# Setup grants for bup_no_priv
@@ -69,37 +61,10 @@ GRANT SELECT ON backup_test_alt.* TO 'bu
GRANT ALL ON *.* TO 'bup_root_user'@'localhost';
GRANT GRANT OPTION ON *.* TO 'bup_root_user'@'localhost';
#
-# Setup grants for bup_no_bup_priv
+# Setup grants for bup_user1
#
-REVOKE ALL ON *.* FROM 'bup_no_bup_priv'@'localhost';
-GRANT ALL ON backup_test.* TO 'bup_no_bup_priv'@'localhost';
-REVOKE BACKUP, RESTORE ON backup_test.* FROM 'bup_no_bup_priv'@'localhost';
-#
-# Setup grants for bup_some_priv
-#
-REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
-GRANT BACKUP, RESTORE ON *.* TO 'bup_some_priv'@'localhost';
-GRANT ALL ON backup_test.t1 TO 'bup_some_priv'@'localhost';
-#
-# Setup grants for bup_select_priv
-#
-REVOKE ALL ON *.* FROM 'bup_select_priv'@'localhost';
-GRANT SELECT ON backup_test.* TO 'bup_select_priv'@'localhost';
-GRANT BACKUP, RESTORE ON backup_test.* TO 'bup_select_priv'@'localhost';
-#
-# Setup grants for bup_full_priv
-# Note: test case is setup so that backup should fail until
-# all privileges for each object is specified.
-#
-REVOKE ALL ON *.* FROM 'bup_full_priv'@'localhost';
-GRANT BACKUP, RESTORE ON backup_test.* TO 'bup_full_priv'@'localhost';
-#
-# Setup grants for bup_other_priv
-# Note: test case is setup so that backup should fail if user has other
-# privileges but not seleect.
-#
-REVOKE ALL ON *.* FROM 'bup_full_priv'@'localhost';
-GRANT BACKUP, RESTORE, CREATE, DROP ON backup_test.* TO 'bup_other_priv'@'localhost';
+REVOKE ALL ON *.* FROM 'bup_user1'@'localhost';
+GRANT BACKUP, RESTORE ON backup_test.* TO 'bup_user1'@'localhost';
FLUSH PRIVILEGES;
#
# Show grants for users.
@@ -113,30 +78,10 @@ SHOW GRANTS FOR 'bup_root_user'@'localho
Grants for bup_root_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'bup_root_user'@'localhost' WITH GRANT OPTION
#
-SHOW GRANTS FOR 'bup_no_bup_priv'@'localhost';
-Grants for bup_no_bup_priv@localhost
-GRANT USAGE ON *.* TO 'bup_no_bup_priv'@'localhost'
-GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `backup_test`.* TO 'bup_no_bup_priv'@'localhost'
-#
-SHOW GRANTS FOR 'bup_some_priv'@'localhost';
-Grants for bup_some_priv@localhost
-GRANT BACKUP, RESTORE ON *.* TO 'bup_some_priv'@'localhost'
-GRANT ALL PRIVILEGES ON `backup_test`.`t1` TO 'bup_some_priv'@'localhost'
-#
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-Grants for bup_select_priv@localhost
-GRANT USAGE ON *.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT, BACKUP, RESTORE ON `backup_test`.* TO 'bup_select_priv'@'localhost'
-#
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-Grants for bup_full_priv@localhost
-GRANT USAGE ON *.* TO 'bup_full_priv'@'localhost'
-GRANT BACKUP, RESTORE ON `backup_test`.* TO 'bup_full_priv'@'localhost'
-#
-SHOW GRANTS FOR 'bup_other_priv'@'localhost';
-Grants for bup_other_priv@localhost
-GRANT USAGE ON *.* TO 'bup_other_priv'@'localhost'
-GRANT CREATE, DROP, BACKUP, RESTORE ON `backup_test`.* TO 'bup_other_priv'@'localhost'
+SHOW GRANTS FOR 'bup_user1'@'localhost';
+Grants for bup_user1@localhost
+GRANT USAGE ON *.* TO 'bup_user1'@'localhost'
+GRANT BACKUP, RESTORE ON `backup_test`.* TO 'bup_user1'@'localhost'
#
# conn_root_user: Do backup of database with root user for later tests.
#
@@ -162,276 +107,8 @@ SELECT trigger_name FROM INFORMATION_SCH
trigger_name
trg
#
-# Test 1 - Ensure the visibility check works for a basic backup and
-# restore for each type of object. Also show that user must
-# have additional privileges to succesfully backup a database.
-#
-#
-# Step 1, show that root can query IS tables and get correct counts
-# for all objects in the database.
-#
-Get number of tables and views -- should be 3
-SELECT count(*) = 3 FROM INFORMATION_SCHEMA.TABLES
-WHERE table_schema = 'backup_test';
-count(*) = 3
-1
-Get number of triggers -- should be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.TRIGGERS
-WHERE trigger_schema = 'backup_test';
-count(*) = 1
-1
-Get number of procedures and functions -- should be 2
-but will be 0 because user does not have SELECT on
-the mysql tables for routines.
-SELECT count(*) = 2 FROM INFORMATION_SCHEMA.ROUTINES
-WHERE routine_schema = 'backup_test';
-count(*) = 2
-1
-Get number of events -- should be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.EVENTS
-WHERE event_schema = 'backup_test';
-count(*) = 1
-1
-#
-# Connect as user with limited privileges.
-#
-#
-# Step 2, show that a user with only SELECT cannot query IS tables
-# and get correct counts for all objects in the database.
-#
-Get number of tables and views -- should be 3
-SELECT count(*) = 3 FROM INFORMATION_SCHEMA.TABLES
-WHERE table_schema = 'backup_test';
-count(*) = 3
-1
-Get number of triggers -- should be 1 but will be 0
-because user needs TRIGGER privilege.
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.TRIGGERS
-WHERE trigger_schema = 'backup_test';
-count(*) = 1
-0
-Get number of procedures and functions -- should be 2
-but will be 0 because user doesn't have SELECT on
-the mysql routine tables.
-SELECT count(*) = 2 FROM INFORMATION_SCHEMA.ROUTINES
-WHERE routine_schema = 'backup_test';
-count(*) = 2
-0
-Get number of events -- should be 1 but will be 0
-because user needs EVENT privilege.
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.EVENTS
-WHERE event_schema = 'backup_test';
-count(*) = 1
-0
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add privileges.
-#
-#
-# Step 3, give user EVENT privilege and retry backup.
-#
-GRANT EVENT ON backup_test.* TO 'bup_select_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-Grants for bup_select_priv@localhost
-GRANT USAGE ON *.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT, EVENT, BACKUP, RESTORE ON `backup_test`.* TO 'bup_select_priv'@'localhost'
-#
-# Connect as user with upgraded privileges and attempt backup.
-#
-Get number of events -- should now be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.EVENTS
-WHERE event_schema = 'backup_test';
-count(*) = 1
-1
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add privileges.
-#
-#
-# Step 4, give user TRIGGER privilege and retry backup.
-#
-GRANT TRIGGER ON backup_test.* TO 'bup_select_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-Grants for bup_select_priv@localhost
-GRANT USAGE ON *.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT, EVENT, TRIGGER, BACKUP, RESTORE ON `backup_test`.* TO 'bup_select_priv'@'localhost'
-#
-# Connect as user with upgraded privileges and attempt backup.
-#
-Get number of triggers -- should now be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.TRIGGERS
-WHERE trigger_schema = 'backup_test';
-count(*) = 1
-1
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BAD_DB_ERROR
-#
-BACKUP DATABASE not_there_at_all_ever, backup_test to 'backup_test_select.bak';
-ERROR 42000: Unknown database 'not_there_at_all_ever'
-SHOW ERRORS;
-Level Code Message
-Error # Unknown database 'not_there_at_all_ever'
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test, not_there_at_all_ever to 'backup_test_select.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add privileges.
-#
-#
-# Step 5, give user SELECT privilege on the mysql tables
-# and retry backup.
-#
-GRANT SELECT ON mysql.* TO 'bup_select_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-Grants for bup_select_priv@localhost
-GRANT USAGE ON *.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT ON `mysql`.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT, EVENT, TRIGGER, BACKUP, RESTORE ON `backup_test`.* TO 'bup_select_priv'@'localhost'
-#
-# Connect as user with upgraded privileges and attempt backup.
-#
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BACKUP_GET_META_VIEW
-#
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
-ERROR HY000: Failed to obtain metadata for view `backup_test`.`v1`
-SHOW ERRORS;
-Level Code Message
-Error # SHOW VIEW command denied to user 'bup_select_priv'@'localhost' for table 'v1'
-Error # Failed to obtain metadata for view `backup_test`.`v1`
-#
-# Connect as root and add privileges.
-#
-#
-# Step 6, Give user SHOW VIEW privilege and retry backup.
-#
-GRANT SHOW VIEW ON backup_test.* TO 'bup_select_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-Grants for bup_select_priv@localhost
-GRANT USAGE ON *.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT ON `mysql`.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, BACKUP, RESTORE ON `backup_test`.* TO 'bup_select_priv'@'localhost'
-#
-# Connect as user with upgraded privileges and attempt backup.
-#
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
-backup_id
-#
-# Backup now succeeds.
-#
-# conn_select_priv: Attempting backup. Should fail with
-# error ER_BAD_DB_ERROR
-#
-BACKUP DATABASE not_there_at_all_ever to 'backup_test_select1.bak';
-ERROR 42000: Unknown database 'not_there_at_all_ever'
-SHOW ERRORS;
-Level Code Message
-Error # Unknown database 'not_there_at_all_ever'
-#
-# Connect as root and do the restore to ensure everything got
-# restored.
-#
-#
-# Step 7, show that the backup image contains all of the objects
-# and compare that to the original backup made by root.
-#
-RESTORE FROM 'backup_test_select.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
-#
-# 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
-#
-# Test 2 - Only users with BACKUP and RESTORE privilege can run backup
-# and restore.
+# Test 1 - Users without BACKUP or RESTORE cannot backup or restore a
+# database.
#
#
# Connect as user with no privileges and attempt backup.
@@ -692,440 +369,64 @@ SELECT trigger_name FROM INFORMATION_SCH
trigger_name
trg
#
-# Connect as user with no backup privileges and attempt backup.
-#
-#
-# conn_no_bup_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_DENIED_ERROR
-#
-BACKUP DATABASE backup_test to 'bup_no_bup_priv.bak';
-ERROR HY000: Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test'.
-#
-# conn_no_bup_priv: Attempting restore. Should fail with
-# error ER_RESTORE_ACCESS_DENIED_ERROR
-#
-RESTORE FROM 'backup_test_orig.bak';
-ERROR HY000: Insufficient privileges. You must have the RESTORE privilege to restore database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You must have the RESTORE privilege to restore database 'backup_test'.
-#
-# Test 3 - Users must have the ability to read (SELECT) all databases
-# as well as have BACKUP on *.* to execute
-# BACKUP DATABASE * TO <file>.
-#
-#
-# Do restore to ensure it still works with root user.
-#
-#
-# conn_no_priv: Attempting backup *. Should fail with
-# error ER_BACKUP_ACCESS_DBS_INCOMPLETE
-#
-BACKUP DATABASE * to 'bup_no_priv.bak';
-ERROR HY000: Insufficient privileges. You must have access privileges to all databases to execute BACKUP DATABASE *.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You must have access privileges to all databases to execute BACKUP DATABASE *.
-#
-# Connect as user with privileges and attempt backup and restore.
-#
-#
-# conn_root_user: Attempting backup. Should not fail.
-#
-BACKUP DATABASE * to 'bup_root_user_star.bak';
-backup_id
+# Test 2 - Only users with BACKUP privilege can run backup.
#
#
# Connect as user with no backup privileges and attempt backup.
#
#
-# conn_no_bup_priv: Attempting backup *. Should fail with
-# error ER_BACKUP_ACCESS_DBS_INCOMPLETE
-#
-BACKUP DATABASE * to 'bup_no_bup_priv.bak';
-ERROR HY000: Insufficient privileges. You must have access privileges to all databases to execute BACKUP DATABASE *.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You must have access privileges to all databases to execute BACKUP DATABASE *.
-#
-# Connect as root and redo privileges for user with only select.
-#
-DROP DATABASE backup_test_alt;
-REVOKE ALL ON *.* FROM 'bup_select_priv'@'localhost';
-REVOKE ALL ON backup_test.* FROM 'bup_select_priv'@'localhost';
-GRANT SELECT, TRIGGER, EVENT, SHOW VIEW ON *.* TO 'bup_select_priv'@'localhost';
-GRANT BACKUP, RESTORE ON *.* TO 'bup_select_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-Grants for bup_select_priv@localhost
-GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, BACKUP, RESTORE ON *.* TO 'bup_select_priv'@'localhost'
-GRANT SELECT ON `mysql`.* TO 'bup_select_priv'@'localhost'
-#
-# Connect as user with upgraded privileges and attempt backup.
-#
-#
-# conn_select_priv: Attempting backup *. Should succeed.
-#
-BACKUP DATABASE * to 'bup_select_priv.bak';
-backup_id
-#
-#
-# Test 4 - Users must have the ability to read (SELECT) all objects in
-# a databsase as well as have BACKUP to execute
-# BACKUP DATABASE backup_test TO <file>.
-#
-#
-# Connect as user with no privileges and attempt backup.
-#
-#
-# conn_no_priv: Attempting backup. Should fail with
-# error ER_BAD_DB_ERROR
-#
-BACKUP DATABASE backup_test to 'bup_no_priv.bak';
-ERROR 42000: Unknown database 'backup_test'
-SHOW ERRORS;
-Level Code Message
-Error # Unknown database 'backup_test'
-#
-# Connect as user with privileges other than select and attempt backup.
-#
-#
-# conn_other_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'bup_other_priv.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Reconnect as root and change privileges for user.
-#
-#
-# Connect as user with privileges and attempt backup and restore.
-#
-REVOKE ALL ON backup_test.* FROM 'bup_other_priv'@'localhost';
-GRANT SELECT ON mysql.* TO 'bup_other_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_other_priv'@'localhost';
-Grants for bup_other_priv@localhost
-GRANT USAGE ON *.* TO 'bup_other_priv'@'localhost'
-GRANT SELECT ON `mysql`.* TO 'bup_other_priv'@'localhost'
-#
-# Connect as user with SELECT on mysql.* and attempt backup.
-#
-#
-# conn_other_priv: Attempting backup. Should fail with
-# error ER_BAD_DB_ERROR
-#
-BACKUP DATABASE backup_test to 'bup_other_priv.bak';
-ERROR 42000: Unknown database 'backup_test'
-SHOW ERRORS;
-Level Code Message
-Error # Unknown database 'backup_test'
-#
-# Reconnect as root and change privileges for user.
-#
-#
-# Connect as user with privileges and attempt backup and restore.
-#
-GRANT BACKUP ON backup_test.* TO 'bup_other_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_other_priv'@'localhost';
-Grants for bup_other_priv@localhost
-GRANT USAGE ON *.* TO 'bup_other_priv'@'localhost'
-GRANT SELECT ON `mysql`.* TO 'bup_other_priv'@'localhost'
-GRANT BACKUP ON `backup_test`.* TO 'bup_other_priv'@'localhost'
-#
-# Connect as user with SELECT on mysql.* and attempt backup.
-#
-#
-# conn_other_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'bup_other_priv.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Test 5 - Users with partial access (SELECT on some objects) cannot
-# backup the database without errors.
-#
-#
-# Connect as user with some privileges and attempt backup.
-#
-#
-# conn_some_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'bup_some_priv.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
+# conn_user1: Attempting backup. Should succeed
#
-# Reconnect as root to ensure we can restore correctly.
-#
-#
-# Connect as user with privileges and attempt backup and restore.
-#
-RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
+BACKUP DATABASE backup_test to 'bup_user1.bak';
backup_id
#
#
-# Show that all objects were recreated after restore.
-#
-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
-#
-# Test 6 - Users with individual privileges and no database-level except
-# EVENT can successfully backup the database.
-# In this test case, we add one privilege at a time until
-# there is a complete set of database-level privileges
-# and the backup command succeeds.
+# Now show the user cannot SELECT anything from the database.
#
+SHOW TABLES FROM backup_test;
+Tables_in_backup_test
#
-# Connect as user with explicit privileges on specific database objects
-# and attempt backup.
+# Test 3 - Show that granting BACKUP, RESTORE only on a database allows
+# the user to backup the database but not restore it.
#
#
-# conn_conn_full_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
+# conn_user1: Attempting restore. Should fail with one of the restore
+# catalog errors.
#
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add another privilege.
-#
-#
-# Add another database-level grant statement.
-#
-GRANT SELECT, TRIGGER ON backup_test.t2 TO 'bup_full_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-Grants for bup_full_priv@localhost
-GRANT USAGE ON *.* TO 'bup_full_priv'@'localhost'
-GRANT BACKUP, RESTORE ON `backup_test`.* TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t2` TO 'bup_full_priv'@'localhost'
-#
-# Connect as user with explicit on database objects and attempt backup.
-#
-#
-# conn_conn_full_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add another privilege.
-#
-#
-# Add another database-level grant statement.
-#
-GRANT SELECT, TRIGGER ON backup_test.t1 TO 'bup_full_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
+RESTORE FROM 'bup_user1.bak' OVERWRITE;
+Got one of the listed errors
#
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-Grants for bup_full_priv@localhost
-GRANT USAGE ON *.* TO 'bup_full_priv'@'localhost'
-GRANT BACKUP, RESTORE ON `backup_test`.* TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t2` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t1` TO 'bup_full_priv'@'localhost'
+# Test 4 - Show that if a user has BACKUP on one database but not
+# another, the user cannot perform a backup of both databases.
#
-# Connect as user with explicit on database objects and attempt backup.
#
+# conn_user1: Attempting backup. Should fail with
+# error ER_BACKUP_ACCESS_DENIED_ERROR
#
-# conn_conn_full_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add another privilege.
-#
-#
-# Add another database-level grant statement.
-#
-GRANT SELECT, SHOW VIEW ON backup_test.v1 TO 'bup_full_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-Grants for bup_full_priv@localhost
-GRANT USAGE ON *.* TO 'bup_full_priv'@'localhost'
-GRANT BACKUP, RESTORE ON `backup_test`.* TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t2` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t1` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, SHOW VIEW ON `backup_test`.`v1` TO 'bup_full_priv'@'localhost'
-#
-# Connect as user with explicit on database objects and attempt backup.
-#
-#
-# conn_conn_full_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-#
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
+BACKUP DATABASE backup_test_alt to 'bup_no_priv.bak';
+ERROR HY000: Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test_alt'.
SHOW ERRORS;
Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add another privilege.
-#
-#
-# Add another database-level grant statement.
-#
-GRANT EVENT ON backup_test.* TO 'bup_full_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-Grants for bup_full_priv@localhost
-GRANT USAGE ON *.* TO 'bup_full_priv'@'localhost'
-GRANT EVENT, BACKUP, RESTORE ON `backup_test`.* TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t2` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t1` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, SHOW VIEW ON `backup_test`.`v1` TO 'bup_full_priv'@'localhost'
-#
-# Connect as user with explicit on database objects and attempt backup.
-#
+Error # Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test_alt'.
#
-# conn_conn_full_priv: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
+# conn_user1: Attempting backup. Should fail with
+# error ER_BACKUP_ACCESS_DENIED_ERROR
#
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'backup_test'.
+BACKUP DATABASE backup_test, backup_test_alt to 'bup_no_priv.bak';
+ERROR HY000: Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test_alt'.
SHOW ERRORS;
Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
-#
-# Connect as root and add another privilege.
-#
-#
-# Add another database-level grant statement.
-#
-GRANT SELECT ON mysql.* TO 'bup_full_priv'@'localhost';
-FLUSH PRIVILEGES;
-#
-# Show updated privileges.
-#
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-Grants for bup_full_priv@localhost
-GRANT USAGE ON *.* TO 'bup_full_priv'@'localhost'
-GRANT SELECT ON `mysql`.* TO 'bup_full_priv'@'localhost'
-GRANT EVENT, BACKUP, RESTORE ON `backup_test`.* TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t2` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, TRIGGER ON `backup_test`.`t1` TO 'bup_full_priv'@'localhost'
-GRANT SELECT, SHOW VIEW ON `backup_test`.`v1` TO 'bup_full_priv'@'localhost'
-#
-# Connect as user with explicit on database objects and attempt backup.
-#
-#
-# conn_conn_full_priv: Attempting backup. Should succeed.
-#
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-backup_id
-#
-#
-# Connect as root and check restore then cleanup.
-#
-RESTORE FROM 'backup_test_full.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
-#
-# Compare to original backup image file.
-#
-RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
-backup_id
+Error # Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test_alt'.
#
+# Reconnect as root.
#
-# 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_no_priv'@'localhost';
DROP USER 'bup_root_user'@'localhost';
-DROP USER 'bup_no_bup_priv'@'localhost';
-DROP USER 'bup_some_priv'@'localhost';
-DROP USER 'bup_select_priv'@'localhost';
-DROP USER 'bup_full_priv'@'localhost';
-DROP USER 'bup_other_priv'@'localhost';
+DROP USER 'bup_user1'@'localhost';
DROP USER 'joe'@'user';
DROP DATABASE backup_test;
+DROP DATABASE backup_test_alt;
FLUSH PRIVILEGES;
=== modified file 'mysql-test/suite/backup/r/backup_security_check.result'
--- a/mysql-test/suite/backup/r/backup_security_check.result 2009-09-03 11:35:02 +0000
+++ b/mysql-test/suite/backup/r/backup_security_check.result 2009-10-23 21:18:10 +0000
@@ -105,14 +105,13 @@ GRANT BACKUP ON `bup_sec_db2`.* TO 'bup_
GRANT SELECT ON `mysql`.`procs_priv` TO 'bup_sec_user1'@'localhost'
#
# Connect as user bup_sec_user1 and attempt backup.
-# Expected result: Pass for non-patched code, ERROR for patched code.
+# Expected result: Pass for all code now that we have privilege
+# elevation on backup.
#
# connection con_user1
BACKUP DATABASE bup_sec_db1, bup_sec_db2 to 'bup_sec.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'bup_sec_db1'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'bup_sec_db1'.
+backup_id
+#
#
# Drop the user.
#
@@ -308,14 +307,13 @@ GRANT BACKUP ON *.* TO 'bup_sec_user1'@'
GRANT SELECT ON `mysql`.`procs_priv` TO 'bup_sec_user1'@'localhost'
#
# Connect as user bup_sec_user1 and attempt backup.
-# Expected result: Pass for non-patched code, ERROR for patched code.
+# Expected result: Pass for all code now that we have privilege
+# elevation on backup.
#
# connection con_user1
BACKUP DATABASE * to 'bup_sec.bak';
-ERROR HY000: Insufficient privileges. You do not have privileges to backup database 'bup_sec_db1'.
-SHOW ERRORS;
-Level Code Message
-Error # Insufficient privileges. You do not have privileges to backup database 'bup_sec_db1'.
+backup_id
+#
#
# Drop the user.
#
@@ -437,6 +435,24 @@ Catalog details:
Privilege 'mtr'.'<empty>'
Privilege 'mtr'.'<empty>'
Database 'test'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
#
# Revoke all privileges and drop the user.
@@ -549,6 +565,24 @@ Catalog details:
Privilege 'mtr'.'<empty>'
Privilege 'mtr'.'<empty>'
Database 'test'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
+ Privilege 'test'.'<empty>'
# connection default
DROP USER 'bup_sec_user1'@'localhost';
=== modified file 'mysql-test/suite/backup/t/backup_security.test'
--- a/mysql-test/suite/backup/t/backup_security.test 2009-10-12 09:08:34 +0000
+++ b/mysql-test/suite/backup/t/backup_security.test 2009-10-23 21:18:10 +0000
@@ -1,18 +1,17 @@
#
-# This test includes tests for all of the security-related tasks in
+# This test includes test cases for testing backup security tasks in
# MySQL Backup. These include tests for:
#
-# 1) Ensure the visibility check works for a basic backup and restore for
-# each type of object.
-# 2) Only users with BACKUP or RESTORE privilege can run backup or restore.
-# 3) Users must have the ability to read (SELECT) all databases
-# as well as have BACKUP on *.* to execute BACKUP DATABASE * TO <file>.
-# 4) Users must have the ability to read (SELECT) all objects in a database
-# as well as have BACKUP to execute BACKUP DATABASE backup_test TO <file>.
-# 5) Users with partial access (SELECT on some objects) cannot backup
-# the database without errors.
-# 6) Users with individual privileges and no database-level except EVENT
-# can successfully backup the database.
+# 1) Users without BACKUP cannot backup or restore a database.
+# 2) Only users with BACKUP privilege can run backup.
+# 3) Show that granting BACKUP, RESTORE only on a database allows the user
+# to backup the database but not restore it.
+# 4) Show that if a user has BACKUP on one database but not another, the
+# user cannot perform a backup of both databases.
+#
+# Note: This test has been modified to only test backup privilege
+# test cases. A separate test file shall be created to test restore
+# privileges. See BUG#44787.
#
--source include/not_embedded.inc
@@ -43,22 +42,14 @@ INSERT INTO backup_test_alt.t1 VALUES ("
--echo #
CREATE USER 'bup_no_priv'@'localhost';
CREATE USER 'bup_root_user'@'localhost';
-CREATE USER 'bup_no_bup_priv'@'localhost';
-CREATE USER 'bup_some_priv'@'localhost';
-CREATE USER 'bup_select_priv'@'localhost';
-CREATE USER 'bup_full_priv'@'localhost';
-CREATE USER 'bup_other_priv'@'localhost';
+CREATE USER 'bup_user1'@'localhost';
--echo #
--echo # Grants privileges as follows:
--echo # bup_no_priv - denied everything (poor chap)
--echo # except SELECT ON backup_test_alt.*
--echo # bup_root_user - super user account (like root)
---echo # bup_no_bup_priv - privileges to a database but no BACKUP or RESTORE
---echo # bup_some_priv - privileges to some database objects
---echo # and BACKUP and RESTORE
---echo # bup_full_priv - user gets database-level specific grants
---echo # bup_other_priv - user who has privileges other than select
+--echo # bup_user1 - has BACKUP and RESTORE only
--echo #
--echo #
@@ -74,41 +65,10 @@ GRANT ALL ON *.* TO 'bup_root_user'@'loc
GRANT GRANT OPTION ON *.* TO 'bup_root_user'@'localhost';
--echo #
---echo # Setup grants for bup_no_bup_priv
---echo #
-REVOKE ALL ON *.* FROM 'bup_no_bup_priv'@'localhost';
-GRANT ALL ON backup_test.* TO 'bup_no_bup_priv'@'localhost';
-REVOKE BACKUP, RESTORE ON backup_test.* FROM 'bup_no_bup_priv'@'localhost';
-
---echo #
---echo # Setup grants for bup_some_priv
+--echo # Setup grants for bup_user1
--echo #
-REVOKE ALL ON *.* FROM 'bup_some_priv'@'localhost';
-GRANT BACKUP, RESTORE ON *.* TO 'bup_some_priv'@'localhost';
-GRANT ALL ON backup_test.t1 TO 'bup_some_priv'@'localhost';
-
---echo #
---echo # Setup grants for bup_select_priv
---echo #
-REVOKE ALL ON *.* FROM 'bup_select_priv'@'localhost';
-GRANT SELECT ON backup_test.* TO 'bup_select_priv'@'localhost';
-GRANT BACKUP, RESTORE ON backup_test.* TO 'bup_select_priv'@'localhost';
-
---echo #
---echo # Setup grants for bup_full_priv
---echo # Note: test case is setup so that backup should fail until
---echo # all privileges for each object is specified.
---echo #
-REVOKE ALL ON *.* FROM 'bup_full_priv'@'localhost';
-GRANT BACKUP, RESTORE ON backup_test.* TO 'bup_full_priv'@'localhost';
-
---echo #
---echo # Setup grants for bup_other_priv
---echo # Note: test case is setup so that backup should fail if user has other
---echo # privileges but not seleect.
---echo #
-REVOKE ALL ON *.* FROM 'bup_full_priv'@'localhost';
-GRANT BACKUP, RESTORE, CREATE, DROP ON backup_test.* TO 'bup_other_priv'@'localhost';
+REVOKE ALL ON *.* FROM 'bup_user1'@'localhost';
+GRANT BACKUP, RESTORE ON backup_test.* TO 'bup_user1'@'localhost';
FLUSH PRIVILEGES;
@@ -119,15 +79,7 @@ SHOW GRANTS FOR 'bup_no_priv'@'localhost
--echo #
SHOW GRANTS FOR 'bup_root_user'@'localhost';
--echo #
-SHOW GRANTS FOR 'bup_no_bup_priv'@'localhost';
---echo #
-SHOW GRANTS FOR 'bup_some_priv'@'localhost';
---echo #
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
---echo #
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
---echo #
-SHOW GRANTS FOR 'bup_other_priv'@'localhost';
+SHOW GRANTS FOR 'bup_user1'@'localhost';
--echo #
--echo # conn_root_user: Do backup of database with root user for later tests.
@@ -145,286 +97,8 @@ SELECT routine_name FROM INFORMATION_SCH
SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'backup_test';
--echo #
---echo # Test 1 - Ensure the visibility check works for a basic backup and
---echo # restore for each type of object. Also show that user must
---echo # have additional privileges to succesfully backup a database.
---echo #
-
---echo #
---echo # Step 1, show that root can query IS tables and get correct counts
---echo # for all objects in the database.
---echo #
-
---echo Get number of tables and views -- should be 3
-SELECT count(*) = 3 FROM INFORMATION_SCHEMA.TABLES
-WHERE table_schema = 'backup_test';
-
---echo Get number of triggers -- should be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.TRIGGERS
-WHERE trigger_schema = 'backup_test';
-
---echo Get number of procedures and functions -- should be 2
---echo but will be 0 because user does not have SELECT on
---echo the mysql tables for routines.
-SELECT count(*) = 2 FROM INFORMATION_SCHEMA.ROUTINES
-WHERE routine_schema = 'backup_test';
-
---echo Get number of events -- should be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.EVENTS
-WHERE event_schema = 'backup_test';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with limited privileges.
---echo #
-connect (conn_select_priv,localhost,bup_select_priv,,);
-
---echo #
---echo # Step 2, show that a user with only SELECT cannot query IS tables
---echo # and get correct counts for all objects in the database.
---echo #
-
---echo Get number of tables and views -- should be 3
-SELECT count(*) = 3 FROM INFORMATION_SCHEMA.TABLES
-WHERE table_schema = 'backup_test';
-
---echo Get number of triggers -- should be 1 but will be 0
---echo because user needs TRIGGER privilege.
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.TRIGGERS
-WHERE trigger_schema = 'backup_test';
-
---echo Get number of procedures and functions -- should be 2
---echo but will be 0 because user doesn't have SELECT on
---echo the mysql routine tables.
-SELECT count(*) = 2 FROM INFORMATION_SCHEMA.ROUTINES
-WHERE routine_schema = 'backup_test';
-
---echo Get number of events -- should be 1 but will be 0
---echo because user needs EVENT privilege.
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.EVENTS
-WHERE event_schema = 'backup_test';
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_select_priv;
---echo #
---echo # Connect as root and add privileges.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Step 3, give user EVENT privilege and retry backup.
---echo #
-GRANT EVENT ON backup_test.* TO 'bup_select_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with upgraded privileges and attempt backup.
---echo #
-connect (conn_select_priv,localhost,bup_select_priv,,);
-
---echo Get number of events -- should now be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.EVENTS
-WHERE event_schema = 'backup_test';
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_select_priv;
---echo #
---echo # Connect as root and add privileges.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Step 4, give user TRIGGER privilege and retry backup.
---echo #
-GRANT TRIGGER ON backup_test.* TO 'bup_select_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with upgraded privileges and attempt backup.
---echo #
-connect (conn_select_priv,localhost,bup_select_priv,,);
-
---echo Get number of triggers -- should now be 1
-SELECT count(*) = 1 FROM INFORMATION_SCHEMA.TRIGGERS
-WHERE trigger_schema = 'backup_test';
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BAD_DB_ERROR
---echo #
---replace_column 1 #
---error ER_BAD_DB_ERROR
-BACKUP DATABASE not_there_at_all_ever, backup_test to 'backup_test_select.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test, not_there_at_all_ever to 'backup_test_select.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_select_priv;
---echo #
---echo # Connect as root and add privileges.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Step 5, give user SELECT privilege on the mysql tables
---echo # and retry backup.
---echo #
-GRANT SELECT ON mysql.* TO 'bup_select_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with upgraded privileges and attempt backup.
---echo #
-connect (conn_select_priv,localhost,bup_select_priv,,);
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_GET_META_VIEW
---echo #
---replace_column 1 #
---error ER_BACKUP_GET_META_VIEW
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_select_priv;
---echo #
---echo # Connect as root and add privileges.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Step 6, Give user SHOW VIEW privilege and retry backup.
---echo #
-GRANT SHOW VIEW ON backup_test.* TO 'bup_select_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with upgraded privileges and attempt backup.
---echo #
-connect (conn_select_priv,localhost,bup_select_priv,,);
-
---replace_column 1 #
-BACKUP DATABASE backup_test to 'backup_test_select.bak';
-
---echo # Backup now succeeds.
-
---echo #
---echo # conn_select_priv: Attempting backup. Should fail with
---echo # error ER_BAD_DB_ERROR
---echo #
---replace_column 1 #
---error ER_BAD_DB_ERROR
-BACKUP DATABASE not_there_at_all_ever to 'backup_test_select1.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_select_priv;
---echo #
---echo # Connect as root and do the restore to ensure everything got
---echo # restored.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Step 7, show that the backup image contains all of the objects
---echo # and compare that to the original backup made by root.
---echo #
-
---replace_column 1 #
-RESTORE FROM 'backup_test_select.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 # 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 # Test 2 - Only users with BACKUP and RESTORE privilege can run backup
---echo # and restore.
+--echo # Test 1 - Users without BACKUP or RESTORE cannot backup or restore a
+--echo # database.
--echo #
disconnect conn_root;
@@ -483,510 +157,85 @@ SELECT event_name FROM INFORMATION_SCHEM
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_user;
---echo #
---echo # Connect as user with no backup privileges and attempt backup.
---echo #
-connect (conn_no_bup_priv,localhost,bup_no_bup_priv,,);
-
---echo #
---echo # conn_no_bup_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_DENIED_ERROR
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_DENIED_ERROR
-BACKUP DATABASE backup_test to 'bup_no_bup_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
--echo #
---echo # conn_no_bup_priv: Attempting restore. Should fail with
---echo # error ER_RESTORE_ACCESS_DENIED_ERROR
+--echo # Test 2 - Only users with BACKUP privilege can run backup.
--echo #
---replace_column 1 #
---error ER_RESTORE_ACCESS_DENIED_ERROR
-RESTORE FROM 'backup_test_orig.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # Test 3 - Users must have the ability to read (SELECT) all databases
---echo # as well as have BACKUP on *.* to execute
---echo # BACKUP DATABASE * TO <file>.
---echo #
-
-disconnect conn_no_bup_priv;
---echo #
---echo # Do restore to ensure it still works with root user.
---echo #
-connect (conn_no_priv,localhost,bup_no_priv,,);
-
---echo #
---echo # conn_no_priv: Attempting backup *. Should fail with
---echo # error ER_BACKUP_ACCESS_DBS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_DBS_INCOMPLETE
-BACKUP DATABASE * to 'bup_no_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_no_priv;
---echo #
---echo # Connect as user with privileges and attempt backup and restore.
---echo #
-connect (conn_root_user,localhost,bup_root_user,,);
-
---echo #
---echo # conn_root_user: Attempting backup. Should not fail.
---echo #
---replace_column 1 #
-BACKUP DATABASE * to 'bup_root_user_star.bak';
disconnect conn_root_user;
--echo #
--echo # Connect as user with no backup privileges and attempt backup.
--echo #
-connect (conn_no_bup_priv,localhost,bup_no_bup_priv,,);
+connect (conn_user1,localhost,bup_user1,,);
--echo #
---echo # conn_no_bup_priv: Attempting backup *. Should fail with
---echo # error ER_BACKUP_ACCESS_DBS_INCOMPLETE
+--echo # conn_user1: Attempting backup. Should succeed
--echo #
--replace_column 1 #
---error ER_BACKUP_ACCESS_DBS_INCOMPLETE
-BACKUP DATABASE * to 'bup_no_bup_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
+BACKUP DATABASE backup_test to 'bup_user1.bak';
-disconnect conn_no_bup_priv;
---echo #
---echo # Connect as root and redo privileges for user with only select.
--echo #
-connect (conn_root,localhost,root,,);
-
-# Make sure there is only 1 other database that user has select on.
-# This is needed for deterministic result because a list of
-# databases are unordered.
-
-DROP DATABASE backup_test_alt;
-REVOKE ALL ON *.* FROM 'bup_select_priv'@'localhost';
-REVOKE ALL ON backup_test.* FROM 'bup_select_priv'@'localhost';
-GRANT SELECT, TRIGGER, EVENT, SHOW VIEW ON *.* TO 'bup_select_priv'@'localhost';
-GRANT BACKUP, RESTORE ON *.* TO 'bup_select_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
+--echo # Now show the user cannot SELECT anything from the database.
--echo #
-SHOW GRANTS FOR 'bup_select_priv'@'localhost';
+SHOW TABLES FROM backup_test;
-disconnect conn_root;
--echo #
---echo # Connect as user with upgraded privileges and attempt backup.
+--echo # Test 3 - Show that granting BACKUP, RESTORE only on a database allows
+--echo # the user to backup the database but not restore it.
--echo #
-connect (conn_select_priv,localhost,bup_select_priv,,);
--echo #
---echo # conn_select_priv: Attempting backup *. Should succeed.
+--echo # conn_user1: Attempting restore. Should fail with one of the restore
+--echo # catalog errors.
--echo #
--replace_column 1 #
-BACKUP DATABASE * to 'bup_select_priv.bak';
+--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
+eval RESTORE FROM 'bup_user1.bak' OVERWRITE;
--echo #
---echo # Test 4 - Users must have the ability to read (SELECT) all objects in
---echo # a databsase as well as have BACKUP to execute
---echo # BACKUP DATABASE backup_test TO <file>.
+--echo # Test 4 - Show that if a user has BACKUP on one database but not
+--echo # another, the user cannot perform a backup of both databases.
--echo #
-disconnect conn_select_priv;
--echo #
---echo # Connect as user with no privileges and attempt backup.
---echo #
-connect (conn_no_priv,localhost,bup_no_priv,,);
-
---echo #
---echo # conn_no_priv: Attempting backup. Should fail with
---echo # error ER_BAD_DB_ERROR
---echo #
---replace_column 1 #
---error ER_BAD_DB_ERROR
-BACKUP DATABASE backup_test to 'bup_no_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_no_priv;
---echo #
---echo # Connect as user with privileges other than select and attempt backup.
---echo #
-connect (conn_other_priv,localhost,bup_other_priv,,);
-
---echo #
---echo # conn_other_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'bup_other_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # Reconnect as root and change privileges for user.
---echo #
-
-disconnect conn_other_priv;
---echo #
---echo # Connect as user with privileges and attempt backup and restore.
---echo #
-connect (conn_root,localhost,root,,);
-
-REVOKE ALL ON backup_test.* FROM 'bup_other_priv'@'localhost';
-GRANT SELECT ON mysql.* TO 'bup_other_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_other_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with SELECT on mysql.* and attempt backup.
---echo #
-connect (conn_other_priv,localhost,bup_other_priv,,);
-
---echo #
---echo # conn_other_priv: Attempting backup. Should fail with
---echo # error ER_BAD_DB_ERROR
---echo #
---replace_column 1 #
---error ER_BAD_DB_ERROR
-BACKUP DATABASE backup_test to 'bup_other_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # Reconnect as root and change privileges for user.
---echo #
-
-disconnect conn_other_priv;
---echo #
---echo # Connect as user with privileges and attempt backup and restore.
---echo #
-connect (conn_root,localhost,root,,);
-
-GRANT BACKUP ON backup_test.* TO 'bup_other_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_other_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with SELECT on mysql.* and attempt backup.
---echo #
-connect (conn_other_priv,localhost,bup_other_priv,,);
-
---echo #
---echo # conn_other_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'bup_other_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # Test 5 - Users with partial access (SELECT on some objects) cannot
---echo # backup the database without errors.
---echo #
-
-disconnect conn_other_priv;
---echo #
---echo # Connect as user with some privileges and attempt backup.
---echo #
-connect (conn_some_priv,localhost,bup_some_priv,,);
-
---echo #
---echo # conn_some_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'bup_some_priv.bak';
---replace_column 2 #
-SHOW ERRORS;
-
---echo #
---echo # Reconnect as root to ensure we can restore correctly.
---echo #
-
-disconnect conn_some_priv;
---echo #
---echo # Connect as user with privileges and attempt backup and restore.
---echo #
-connect (conn_root,localhost,root,,);
-
---replace_column 1 #
-RESTORE FROM 'backup_test_orig.bak' OVERWRITE;
-
---echo #
---echo # Show that all objects were recreated after restore.
---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 # Test 6 - Users with individual privileges and no database-level except
---echo # EVENT can successfully backup the database.
---echo # In this test case, we add one privilege at a time until
---echo # there is a complete set of database-level privileges
---echo # and the backup command succeeds.
---echo #
-
-disconnect conn_root;
---echo #
---echo # Connect as user with explicit privileges on specific database objects
---echo # and attempt backup.
---echo #
-connect (conn_full_priv,localhost,bup_full_priv,,);
-
---echo #
---echo # conn_conn_full_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_full_priv;
---echo #
---echo # Connect as root and add another privilege.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Add another database-level grant statement.
---echo #
-GRANT SELECT, TRIGGER ON backup_test.t2 TO 'bup_full_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with explicit on database objects and attempt backup.
---echo #
-connect (conn_full_priv,localhost,bup_full_priv,,);
-
---echo #
---echo # conn_conn_full_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_full_priv;
---echo #
---echo # Connect as root and add another privilege.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Add another database-level grant statement.
---echo #
-GRANT SELECT, TRIGGER ON backup_test.t1 TO 'bup_full_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with explicit on database objects and attempt backup.
---echo #
-connect (conn_full_priv,localhost,bup_full_priv,,);
-
---echo #
---echo # conn_conn_full_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
---echo #
---replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
---replace_column 2 #
-SHOW ERRORS;
-
-disconnect conn_full_priv;
---echo #
---echo # Connect as root and add another privilege.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Add another database-level grant statement.
---echo #
-GRANT SELECT, SHOW VIEW ON backup_test.v1 TO 'bup_full_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with explicit on database objects and attempt backup.
---echo #
-connect (conn_full_priv,localhost,bup_full_priv,,);
-
---echo #
---echo # conn_conn_full_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
+--echo # conn_user1: Attempting backup. Should fail with
+--echo # error ER_BACKUP_ACCESS_DENIED_ERROR
--echo #
--replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
+--error ER_BACKUP_ACCESS_DENIED_ERROR
+BACKUP DATABASE backup_test_alt to 'bup_no_priv.bak';
--replace_column 2 #
SHOW ERRORS;
-disconnect conn_full_priv;
---echo #
---echo # Connect as root and add another privilege.
---echo #
-connect (conn_root,localhost,root,,);
-
--echo #
---echo # Add another database-level grant statement.
---echo #
-GRANT EVENT ON backup_test.* TO 'bup_full_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with explicit on database objects and attempt backup.
---echo #
-connect (conn_full_priv,localhost,bup_full_priv,,);
-
---echo #
---echo # conn_conn_full_priv: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
+--echo # conn_user1: Attempting backup. Should fail with
+--echo # error ER_BACKUP_ACCESS_DENIED_ERROR
--echo #
--replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
+--error ER_BACKUP_ACCESS_DENIED_ERROR
+BACKUP DATABASE backup_test, backup_test_alt to 'bup_no_priv.bak';
--replace_column 2 #
SHOW ERRORS;
-disconnect conn_full_priv;
---echo #
---echo # Connect as root and add another privilege.
---echo #
-connect (conn_root,localhost,root,,);
-
---echo #
---echo # Add another database-level grant statement.
---echo #
-GRANT SELECT ON mysql.* TO 'bup_full_priv'@'localhost';
-
-FLUSH PRIVILEGES;
-
---echo #
---echo # Show updated privileges.
---echo #
-SHOW GRANTS FOR 'bup_full_priv'@'localhost';
-
-disconnect conn_root;
---echo #
---echo # Connect as user with explicit on database objects and attempt backup.
---echo #
-connect (conn_full_priv,localhost,bup_full_priv,,);
-
---echo #
---echo # conn_conn_full_priv: Attempting backup. Should succeed.
---echo #
---replace_column 1 #
-BACKUP DATABASE backup_test to 'backup_test_full.bak';
-
-disconnect conn_full_priv;
+disconnect conn_user1;
--echo #
---echo # Connect as root and check restore then cleanup.
+--echo # Reconnect as root.
--echo #
connect (conn_root,localhost,root,,);
---replace_column 1 #
-RESTORE FROM 'backup_test_full.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 # 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_no_priv'@'localhost';
DROP USER 'bup_root_user'@'localhost';
-DROP USER 'bup_no_bup_priv'@'localhost';
-DROP USER 'bup_some_priv'@'localhost';
-DROP USER 'bup_select_priv'@'localhost';
-DROP USER 'bup_full_priv'@'localhost';
-DROP USER 'bup_other_priv'@'localhost';
+DROP USER 'bup_user1'@'localhost';
DROP USER 'joe'@'user';
DROP DATABASE backup_test;
+DROP DATABASE backup_test_alt;
FLUSH PRIVILEGES;
let $MYSQLD_BACKUPDIR= `select @@backupdir`;
remove_file $MYSQLD_BACKUPDIR/bup_root_user.bak;
remove_file $MYSQLD_BACKUPDIR/backup_test_orig.bak;
-remove_file $MYSQLD_BACKUPDIR/bup_root_user_star.bak;
-remove_file $MYSQLD_BACKUPDIR/backup_test_select.bak;
-remove_file $MYSQLD_BACKUPDIR/backup_test_full.bak;
+remove_file $MYSQLD_BACKUPDIR/bup_user1.bak;
=== modified file 'mysql-test/suite/backup/t/backup_security_check.test'
--- a/mysql-test/suite/backup/t/backup_security_check.test 2009-09-03 11:35:02 +0000
+++ b/mysql-test/suite/backup/t/backup_security_check.test 2009-10-23 21:18:10 +0000
@@ -86,17 +86,15 @@ SHOW GRANTS FOR 'bup_sec_user1'@'localho
--echo #
--echo # Connect as user bup_sec_user1 and attempt backup.
---echo # Expected result: Pass for non-patched code, ERROR for patched code.
+--echo # Expected result: Pass for all code now that we have privilege
+--echo # elevation on backup.
--echo #
--echo # connection con_user1
connect (con_user1,localhost,bup_sec_user1,,);
--error 0,1
--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
--replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
BACKUP DATABASE bup_sec_db1, bup_sec_db2 to 'bup_sec.bak';
---replace_column 2 #
-SHOW ERRORS;
--echo #
--echo # Drop the user.
@@ -244,17 +242,15 @@ SHOW GRANTS FOR 'bup_sec_user1'@'localho
--echo #
--echo # Connect as user bup_sec_user1 and attempt backup.
---echo # Expected result: Pass for non-patched code, ERROR for patched code.
+--echo # Expected result: Pass for all code now that we have privilege
+--echo # elevation on backup.
--echo #
--echo # connection con_user1
connect (con_user1,localhost,bup_sec_user1,,);
--error 0,1
--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
--replace_column 1 #
---error ER_BACKUP_ACCESS_OBJS_INCOMPLETE
BACKUP DATABASE * to 'bup_sec.bak';
---replace_column 2 #
-SHOW ERRORS;
--echo #
--echo # Drop the user.
=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc 2009-10-20 07:42:47 +0000
+++ b/sql/si_objects.cc 2009-10-23 21:18:10 +0000
@@ -184,6 +184,9 @@ void Si_session_context::restore_si_ctx(
warning information are stored in the output parameter. Some session
attributes are preserved and reset to predefined values before query
execution (@see Si_session_context).
+
+ @note This method will temporarily invoke additional privileges for
+ all SELECT or SHOW CREATE statements.
@param[in] thd Thread context.
@param[in] query SQL query to be executed.
@@ -200,7 +203,10 @@ run_service_interface_sql(THD *thd, Ed_c
const LEX_STRING *query, bool get_warnings)
{
Si_session_context session_context;
-
+ ulong saved_master_access; // Saved master access ACLs
+ ulong saved_db_access; // Saved db access ACLs
+
+
DBUG_ENTER("run_service_interface_sql");
DBUG_PRINT("run_service_interface_sql",
("query: %.*s",
@@ -209,11 +215,34 @@ run_service_interface_sql(THD *thd, Ed_c
session_context.save_si_ctx(thd);
session_context.reset_si_ctx(thd);
+ /*
+ We only elevate for SELECT or SHOW CREATE queries.
+ */
+ my_bool elevate= strncmp(query->str, "SELECT", 6) == 0 ||
+ strncmp(query->str, "(SELECT", 7) == 0 ||
+ strncmp(query->str, "SHOW CREATE", 11) == 0 ? TRUE : FALSE;
+
+ /*
+ Temporarily give user SELECT privilege so operations on
+ mysql and information_schema can succeed.
+ */
+ if (elevate)
+ {
+ saved_master_access= thd->security_ctx->master_access;
+ saved_db_access= thd->security_ctx->db_access;
+ thd->security_ctx->master_access |= (SELECT_ACL | SHOW_VIEW_ACL |
+ TRIGGER_ACL | PROC_ACLS | EVENT_ACL);
+ }
+
bool rc= ed_connection->execute_direct(*query);
- if (get_warnings) {
- /* Push warnings on the THD error stack. */
- thd->warning_info->append_warnings(thd, ed_connection->get_warn_list());
+ /*
+ Remove elevated privilege.
+ */
+ if (elevate)
+ {
+ thd->security_ctx->master_access= saved_master_access;
+ thd->security_ctx->db_access= saved_db_access;
}
session_context.restore_si_ctx(thd);
Attachment: [text/bzr-bundle] bzr/charles.bell@sun.com-20091023211810-hplg0lwt9m2fxrzo.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0-backup branch (charles.bell:2884) Bug#46569 | Chuck Bell | 23 Oct |
