#At file:///Users/cbell/source/bzr/mysql-6.0-bug-45889/ based on revid:rafal.somla@stripped
2844 Chuck Bell 2009-07-15 [merge]
BUG#45889 : BACKUP DATABASE command should include all objects
PROBLEM: The code currently silently succeeds if a user is not able to read
all of the objects and their metadata in the database and, in the case of
BACKUP DATABASE *, is not able to read all of the databases.
This is an error because the BACKUP DATABASE command has been clarified
to error if a user cannot read all of the objects and their metadata
all all of the databases listed.
This patch implements a solution to produce an error if either of the
following are true:
* The user cannot read (SELECT) all of the databases when issuing a
BACKUP DATABASE * command.
* The user cannot read (SELECT) all of the objects in a database
and read their metadata when issuing a BACKUP DATABASE <list> command.
@ mysql-test/suite/backup/r/backup_security.result
Updated result file with additional test cases.
@ mysql-test/suite/backup/r/backup_security_check.result
New result file.
@ mysql-test/suite/backup/t/backup_security.test
Reorganized test file and added new test cases.
@ mysql-test/suite/backup/t/backup_security_check.test
New test to check that backup images contain all objects in the backup
image or fail. This test can detect if a regression has occurred in
the object checking (counting) mechanism introduced in this patch.
@ sql/backup/backup_info.cc
Added call to check user access for each database to ensure the
user has visibility to all objects.
Added call to check user access for all databases in the event a
BACKUP DATABASE * command is issued.
@ sql/share/errmsg.txt
New error messages.
@ sql/si_objects.cc
Added helper methods to check to see if user has visibility on all objects
for a given database and has visibility for all databases except
mysql and information_schema.
@ sql/si_objects.h
Declaration for new methods.
Clarification in comments.
added:
mysql-test/suite/backup/r/backup_security_check.result
mysql-test/suite/backup/t/backup_security_check.test
modified:
mysql-test/suite/backup/r/backup_security.result
mysql-test/suite/backup/t/backup_security.test
sql/backup/backup_info.cc
sql/share/errmsg.txt
sql/si_objects.cc
sql/si_objects.h
=== modified file 'mysql-test/suite/backup/r/backup_security.result'
--- a/mysql-test/suite/backup/r/backup_security.result 2009-07-02 13:47:21 +0000
+++ b/mysql-test/suite/backup/r/backup_security.result 2009-07-15 14:18:59 +0000
@@ -1,11 +1,7 @@
DROP DATABASE IF EXISTS backup_test;
+DROP DATABASE IF EXISTS backup_test_alt;
#
# Create database and data to test.
-# Create 3 users and grants rights as follows:
-# bup_no_rights - denied everything (poor chap)
-# except SELECT ON backup_test_alt.*
-# bup_with_rights - super user account (like root)
-# bup_some_rights - rights to a database but no BACKUP or RESTORE
#
CREATE DATABASE backup_test;
CREATE TABLE backup_test.t1 (a char(30)) ENGINE=MEMORY;
@@ -16,6 +12,10 @@ INSERT INTO backup_test.t1 VALUES ("04 T
INSERT INTO backup_test.t1 VALUES ("05 Test #1 - privilege");
INSERT INTO backup_test.t1 VALUES ("06 Test #1 - privilege");
INSERT INTO backup_test.t1 VALUES ("07 Test #1 - privilege");
+CREATE TABLE backup_test.t2 (a char(30)) ENGINE=MEMORY;
+INSERT INTO backup_test.t2 VALUES ("01 Test #2 - privilege");
+INSERT INTO backup_test.t2 VALUES ("02 Test #2 - privilege");
+INSERT INTO backup_test.t2 VALUES ("03 Test #2 - privilege");
CREATE DATABASE backup_test_alt;
CREATE TABLE backup_test_alt.t1 (a char(30)) ENGINE=MEMORY;
INSERT INTO backup_test_alt.t1 VALUES ("01 Test #2 - privilege");
@@ -30,21 +30,108 @@ CREATE FUNCTION backup_test.f1() RETURNS
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";
-CREATE USER 'bup_no_rights'@'localhost';
-CREATE USER 'bup_with_rights'@'localhost';
-CREATE USER 'bup_some_rights'@'localhost';
-REVOKE ALL ON *.* FROM 'bup_no_rights'@'localhost';
-REVOKE ALL ON *.* FROM 'bup_with_rights'@'localhost';
-REVOKE ALL ON *.* FROM 'bup_some_rights'@'localhost';
-GRANT SELECT ON backup_test_alt.* TO 'bup_no_rights'@'localhost';
-GRANT ALL ON *.* TO 'bup_with_rights'@'localhost';
-GRANT GRANT OPTION ON *.* TO 'bup_with_rights'@'localhost';
-GRANT ALL ON backup_test.* TO 'bup_some_rights'@'localhost';
-REVOKE BACKUP, RESTORE ON backup_test.* FROM 'bup_some_rights'@'localhost';
-GRANT SELECT ON mysql.* TO 'bup_with_rights'@'localhost';
+#
+# Create users.
+#
+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';
+#
+# 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
+#
+#
+# Setup grants for bup_no_priv
+#
+REVOKE ALL ON *.* FROM 'bup_no_priv'@'localhost';
+GRANT SELECT ON backup_test_alt.* TO 'bup_no_priv'@'localhost';
+#
+# Setup grants for bup_root_user
+#
+GRANT ALL ON *.* TO 'bup_root_user'@'localhost';
+GRANT GRANT OPTION ON *.* TO 'bup_root_user'@'localhost';
+#
+# Setup grants for bup_no_bup_priv
+#
+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';
FLUSH PRIVILEGES;
+#
+# Show grants for users.
#
-# root_user: Do backup of database with root user for later tests.
+SHOW GRANTS FOR 'bup_no_priv'@'localhost';
+Grants for bup_no_priv@localhost
+GRANT USAGE ON *.* TO 'bup_no_priv'@'localhost'
+GRANT SELECT ON `backup_test_alt`.* TO 'bup_no_priv'@'localhost'
+#
+SHOW GRANTS FOR 'bup_root_user'@'localhost';
+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'
+#
+# conn_root_user: Do backup of database with root user for later tests.
#
BACKUP DATABASE backup_test to 'backup_test_orig.bak';
backup_id
@@ -55,6 +142,7 @@ backup_id
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
@@ -67,38 +155,288 @@ SELECT trigger_name FROM INFORMATION_SCH
trigger_name
trg
#
-# Connect as user with some rights and attempt backup.
+# 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
#
-# some_rights: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_DENIED_ERROR
+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'.
#
-BACKUP DATABASE backup_test to 'bup_some_rights.bak';
-ERROR HY000: Insufficient privileges. You must have the BACKUP privilege 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 must have the BACKUP privilege to backup database 'backup_test'.
+Error # Insufficient privileges. You do not have privileges to backup database 'backup_test'.
#
-# some_rights: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_DENIED_ERROR
+# Connect as root and add privileges.
#
-BACKUP DATABASE * to 'bup_some_rights.bak';
-ERROR HY000: Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test'.
+#
+# 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 #### Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test'.
+Error # Unknown database 'not_there_at_all_ever'
#
-# Connect as user with no rights and attempt backup.
+# 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
#
-# no_rights: Attempting backup. Should fail with
+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 meta-data for view `backup_test`.`v1`
+SHOW ERRORS;
+Level Code Message
+Error # Failed to obtain meta-data 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 backup_test to 'bup_no_rights.bak';
-ERROR 42000: Unknown database 'backup_test'
+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 'backup_test'
+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.
+#
+#
+# Connect as user with no privileges and attempt backup.
+#
+#
+# conn_no_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'.
#
# Show user has not gained rights.
#
@@ -106,89 +444,89 @@ Error #### Unknown database 'backup_test
# Checking privileges for a table.
#
SELECT * FROM backup_test.t1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 't1'
INSERT INTO backup_test.t1 VALUES ("900");
-ERROR 42000: INSERT command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: INSERT command denied to user 'bup_no_priv'@'localhost' for table 't1'
UPDATE backup_test.t1 SET a = "gotcha!";
-ERROR 42000: UPDATE command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: UPDATE command denied to user 'bup_no_priv'@'localhost' for table 't1'
DELETE FROM backup_test.t1;
-ERROR 42000: DELETE command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: DELETE command denied to user 'bup_no_priv'@'localhost' for table 't1'
TRUNCATE TABLE backup_test.t1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 't1'
ALTER TABLE backup_test.t1 ENGINE=MEMORY;
-ERROR 42000: ALTER command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: ALTER command denied to user 'bup_no_priv'@'localhost' for table 't1'
DROP TABLE backup_test.t1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 't1'
CREATE TABLE backup_test.t2 (a int);
-ERROR 42000: CREATE command denied to user 'bup_no_rights'@'localhost' for table 't2'
+ERROR 42000: CREATE command denied to user 'bup_no_priv'@'localhost' for table 't2'
DESCRIBE backup_test.t1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 't1'
SHOW CREATE TABLE backup_test.t1;
-ERROR 42000: SHOW command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: SHOW command denied to user 'bup_no_priv'@'localhost' for table 't1'
#
# Checking privileges for a view.
#
SELECT * FROM backup_test.v1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
INSERT INTO backup_test.v1 VALUES ("800");
-ERROR 42000: INSERT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: INSERT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
UPDATE backup_test.v1 SET a = "gotcha again!";
-ERROR 42000: UPDATE command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: UPDATE command denied to user 'bup_no_priv'@'localhost' for table 'v1'
DELETE FROM backup_test.v1;
-ERROR 42000: DELETE command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: DELETE command denied to user 'bup_no_priv'@'localhost' for table 'v1'
TRUNCATE TABLE backup_test.v1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 'v1'
ALTER VIEW backup_test.v1 AS SELECT 1;
-ERROR 42000: CREATE VIEW command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: CREATE VIEW command denied to user 'bup_no_priv'@'localhost' for table 'v1'
DROP VIEW backup_test.v1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 'v1'
CREATE VIEW backup_test.v2 AS SELECT 0;
-ERROR 42000: CREATE VIEW command denied to user 'bup_no_rights'@'localhost' for table 'v2'
+ERROR 42000: CREATE VIEW command denied to user 'bup_no_priv'@'localhost' for table 'v2'
DESCRIBE backup_test.v1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
SHOW CREATE VIEW backup_test.v1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
#
# Checking privileges for a trigger.
#
DROP TRIGGER backup_test.trg;
-ERROR 42000: TRIGGER command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: TRIGGER command denied to user 'bup_no_priv'@'localhost' for table 't1'
CREATE TRIGGER backup_test.trg2 BEFORE INSERT ON backup_test.t1 FOR EACH ROW
INSERT INTO backup_test.t1 VALUES('not there at all');
-ERROR 42000: TRIGGER command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: TRIGGER command denied to user 'bup_no_priv'@'localhost' for table 't1'
#
# Checking privileges for an event.
#
DROP EVENT backup_test.e1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
ALTER EVENT backup_test.e1 ENABLE;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
CREATE EVENT backup_test.e2 ON SCHEDULE EVERY 1 YEAR DO
DELETE FROM backup_test.t1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
SHOW CREATE EVENT backup_test.e1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
#
# Checking privileges for a function.
#
DROP FUNCTION backup_test.f1;
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.f1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.f1'
ALTER FUNCTION backup_test.f1 COMMENT "Tricky, eh?";
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.f1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.f1'
CREATE FUNCTION backup_test.f2() RETURNS INT RETURN (SELECT 11);
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
SHOW CREATE FUNCTION backup_test.f1;
ERROR 42000: FUNCTION f1 does not exist
#
# Checking privileges for a procedure.
#
DROP PROCEDURE backup_test.p1;
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.p1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.p1'
ALTER PROCEDURE backup_test.p1 COMMENT "Tricky, eh?";
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.p1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.p1'
CREATE PROCEDURE backup_test.p1(p1 CHAR(20))
INSERT INTO backup_test.t1 VALUES ("100");
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
SHOW CREATE PROCEDURE backup_test.p1;
ERROR 42000: PROCEDURE p1 does not exist
#
@@ -196,38 +534,20 @@ ERROR 42000: PROCEDURE p1 does not exist
# existing database.
#
DROP DATABASE backup_test;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
CREATE DATABASE x1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'x1'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'x1'
ALTER DATABASE backup_test UPGRADE DATA DIRECTORY NAME;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
-#
-# no_rights: Attempting backup. Should fail with
-# error ER_BACKUP_ACCESS_DENIED_ERROR
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
#
-BACKUP DATABASE backup_test_alt to 'bup_no_rights.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 must have the BACKUP privilege to backup database 'backup_test_alt'.
-#
-# no_rights: Attempting backup. Should fail with
+# conn_no_priv: Attempting backup. Should fail with
# error ER_BACKUP_ACCESS_DENIED_ERROR
#
-BACKUP DATABASE * to 'bup_no_rights.bak';
+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 must have the BACKUP privilege to backup database 'backup_test_alt'.
-#
-# no_rights: 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'.
+Error # Insufficient privileges. You must have the BACKUP privilege to backup database 'backup_test_alt'.
#
# Show user has not gained rights.
#
@@ -235,89 +555,89 @@ Error #### Insufficient privileges. You
# Checking privileges for a table.
#
SELECT * FROM backup_test.t1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 't1'
INSERT INTO backup_test.t1 VALUES ("900");
-ERROR 42000: INSERT command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: INSERT command denied to user 'bup_no_priv'@'localhost' for table 't1'
UPDATE backup_test.t1 SET a = "gotcha!";
-ERROR 42000: UPDATE command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: UPDATE command denied to user 'bup_no_priv'@'localhost' for table 't1'
DELETE FROM backup_test.t1;
-ERROR 42000: DELETE command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: DELETE command denied to user 'bup_no_priv'@'localhost' for table 't1'
TRUNCATE TABLE backup_test.t1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 't1'
ALTER TABLE backup_test.t1 ENGINE=MEMORY;
-ERROR 42000: ALTER command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: ALTER command denied to user 'bup_no_priv'@'localhost' for table 't1'
DROP TABLE backup_test.t1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 't1'
CREATE TABLE backup_test.t2 (a int);
-ERROR 42000: CREATE command denied to user 'bup_no_rights'@'localhost' for table 't2'
+ERROR 42000: CREATE command denied to user 'bup_no_priv'@'localhost' for table 't2'
DESCRIBE backup_test.t1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 't1'
SHOW CREATE TABLE backup_test.t1;
-ERROR 42000: SHOW command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: SHOW command denied to user 'bup_no_priv'@'localhost' for table 't1'
#
# Checking privileges for a view.
#
SELECT * FROM backup_test.v1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
INSERT INTO backup_test.v1 VALUES ("800");
-ERROR 42000: INSERT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: INSERT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
UPDATE backup_test.v1 SET a = "gotcha again!";
-ERROR 42000: UPDATE command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: UPDATE command denied to user 'bup_no_priv'@'localhost' for table 'v1'
DELETE FROM backup_test.v1;
-ERROR 42000: DELETE command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: DELETE command denied to user 'bup_no_priv'@'localhost' for table 'v1'
TRUNCATE TABLE backup_test.v1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 'v1'
ALTER VIEW backup_test.v1 AS SELECT 1;
-ERROR 42000: CREATE VIEW command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: CREATE VIEW command denied to user 'bup_no_priv'@'localhost' for table 'v1'
DROP VIEW backup_test.v1;
-ERROR 42000: DROP command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: DROP command denied to user 'bup_no_priv'@'localhost' for table 'v1'
CREATE VIEW backup_test.v2 AS SELECT 0;
-ERROR 42000: CREATE VIEW command denied to user 'bup_no_rights'@'localhost' for table 'v2'
+ERROR 42000: CREATE VIEW command denied to user 'bup_no_priv'@'localhost' for table 'v2'
DESCRIBE backup_test.v1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
SHOW CREATE VIEW backup_test.v1;
-ERROR 42000: SELECT command denied to user 'bup_no_rights'@'localhost' for table 'v1'
+ERROR 42000: SELECT command denied to user 'bup_no_priv'@'localhost' for table 'v1'
#
# Checking privileges for a trigger.
#
DROP TRIGGER backup_test.trg;
-ERROR 42000: TRIGGER command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: TRIGGER command denied to user 'bup_no_priv'@'localhost' for table 't1'
CREATE TRIGGER backup_test.trg2 BEFORE INSERT ON backup_test.t1 FOR EACH ROW
INSERT INTO backup_test.t1 VALUES('not there at all');
-ERROR 42000: TRIGGER command denied to user 'bup_no_rights'@'localhost' for table 't1'
+ERROR 42000: TRIGGER command denied to user 'bup_no_priv'@'localhost' for table 't1'
#
# Checking privileges for an event.
#
DROP EVENT backup_test.e1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
ALTER EVENT backup_test.e1 ENABLE;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
CREATE EVENT backup_test.e2 ON SCHEDULE EVERY 1 YEAR DO
DELETE FROM backup_test.t1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
SHOW CREATE EVENT backup_test.e1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
#
# Checking privileges for a function.
#
DROP FUNCTION backup_test.f1;
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.f1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.f1'
ALTER FUNCTION backup_test.f1 COMMENT "Tricky, eh?";
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.f1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.f1'
CREATE FUNCTION backup_test.f2() RETURNS INT RETURN (SELECT 11);
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
SHOW CREATE FUNCTION backup_test.f1;
ERROR 42000: FUNCTION f1 does not exist
#
# Checking privileges for a procedure.
#
DROP PROCEDURE backup_test.p1;
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.p1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.p1'
ALTER PROCEDURE backup_test.p1 COMMENT "Tricky, eh?";
-ERROR 42000: alter routine command denied to user 'bup_no_rights'@'localhost' for routine 'backup_test.p1'
+ERROR 42000: alter routine command denied to user 'bup_no_priv'@'localhost' for routine 'backup_test.p1'
CREATE PROCEDURE backup_test.p1(p1 CHAR(20))
INSERT INTO backup_test.t1 VALUES ("100");
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
SHOW CREATE PROCEDURE backup_test.p1;
ERROR 42000: PROCEDURE p1 does not exist
#
@@ -325,41 +645,245 @@ ERROR 42000: PROCEDURE p1 does not exist
# existing database.
#
DROP DATABASE backup_test;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
CREATE DATABASE x1;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'x1'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'x1'
ALTER DATABASE backup_test UPGRADE DATA DIRECTORY NAME;
-ERROR 42000: Access denied for user 'bup_no_rights'@'localhost' to database 'backup_test'
+ERROR 42000: Access denied for user 'bup_no_priv'@'localhost' to database 'backup_test'
#
-# Connect as user with rights and attempt backup and restore.
+# Connect as user with privileges and attempt backup and restore.
#
#
-# with_rights: Attempting backup. Should succeed
+# conn_root_user: Attempting backup. Should succeed
#
-BACKUP DATABASE backup_test to 'bup_with_rights.bak';
+BACKUP DATABASE backup_test to 'bup_root_user.bak';
backup_id
#
#
-# with_rights: Attempting backup. Should not fail.
+# conn_root_user: Attempting restore. Should succeed
#
-BACKUP DATABASE * to 'bup_with_rights_star.bak';
+RESTORE FROM 'bup_root_user.bak' OVERWRITE;
backup_id
#
#
-# with_rights: Attempting restore. Should succeed
+# Show that all objects were recreated after restore.
#
-RESTORE FROM 'bup_with_rights.bak' OVERWRITE;
-backup_id
+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 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 the SELECT privilege on all databases to execute BACKUP DATABASE *.
+SHOW ERRORS;
+Level Code Message
+Error # Insufficient privileges. You must have the SELECT privilege on 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
+#
+#
+# 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 the SELECT privilege on all databases to execute BACKUP DATABASE *.
+SHOW ERRORS;
+Level Code Message
+Error # Insufficient privileges. You must have the SELECT privilege on 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'.
+#
+# 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_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
@@ -371,15 +895,208 @@ 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.
+#
+#
+# Connect as user with explicit privileges on specific 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.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.
+#
+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'
+#
+# 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, 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'.
+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.
+#
+#
+# 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 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
#
#
-# Show that all objects were recreated after restore.
+# 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
@@ -394,9 +1111,12 @@ trg
#
# Cleanup
#
-DROP USER 'bup_no_rights'@'localhost';
-DROP USER 'bup_with_rights'@'localhost';
-DROP USER 'bup_some_rights'@'localhost';
+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 DATABASE backup_test;
-DROP DATABASE backup_test_alt;
FLUSH PRIVILEGES;
=== added file 'mysql-test/suite/backup/r/backup_security_check.result'
--- a/mysql-test/suite/backup/r/backup_security_check.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_security_check.result 2009-07-15 14:18:59 +0000
@@ -0,0 +1,559 @@
+DROP DATABASE IF EXISTS bup_sec_db1;
+DROP DATABASE IF EXISTS bup_sec_db2;
+#
+# Create some data to test. Includes one of each type of object
+# and two databases.
+#
+CREATE DATABASE bup_sec_db1;
+CREATE TABLE bup_sec_db1.t1 (a char(30)) ENGINE=MEMORY;
+CREATE TABLE bup_sec_db1.t2 (a char(30)) ENGINE=MEMORY;
+CREATE VIEW bup_sec_db1.v1 as SELECT * FROM bup_sec_db1.t1;
+CREATE TRIGGER bup_sec_db1.t1_ai AFTER INSERT ON bup_sec_db1.t1 FOR EACH ROW
+INSERT INTO bup_sec_db1.t1 VALUES('Test objects count');
+CREATE PROCEDURE bup_sec_db1.p1(p1 CHAR(20))
+INSERT INTO bup_sec_db1.t1 VALUES ("50");
+CREATE FUNCTION bup_sec_db1.f1() RETURNS INT RETURN (SELECT 1);
+CREATE EVENT bup_sec_db1.e1 ON SCHEDULE EVERY 1 YEAR DO
+DELETE FROM bup_sec_db1.t1 WHERE a = "not there";
+CREATE DATABASE bup_sec_db2;
+CREATE TABLE bup_sec_db2.t1 (a char(30)) ENGINE=MEMORY;
+CREATE TRIGGER bup_sec_db2.t1_ai AFTER INSERT ON bup_sec_db2.t1 FOR EACH ROW
+INSERT INTO bup_sec_db2.t1 VALUES('Test objects count');
+#
+# Attempt backup as root to capture backup image with all objects.
+#
+BACKUP DATABASE * to 'bup_sec.bak';
+backup_id
+#
+#
+# Dump the backup image to show the metadata using mysqlbackup.
+#
+# exec $MYSQL_BACKUP
+
+Image path: #
+Image size: #
+Image compression: none
+Image version: 1
+Creation time: #
+Server version: #
+Server byte order: #
+Server charset: 'utf8'
+
+Catalog details:
+
+ Database 'bup_sec_db1'
+ Table 'bup_sec_db1'.'t1'
+ Table 'bup_sec_db1'.'t2'
+ Sproc 'bup_sec_db1'.'p1'
+ Sfunc 'bup_sec_db1'.'f1'
+ View 'bup_sec_db1'.'v1'
+ Event 'bup_sec_db1'.'e1'
+ Trigger 'bup_sec_db1'.'t1_ai'
+ Database 'bup_sec_db2'
+ Table 'bup_sec_db2'.'t1'
+ Trigger 'bup_sec_db2'.'t1_ai'
+ Database 'mtr'
+ Table 'mtr'.'global_suppressions'
+ Table 'mtr'.'test_suppressions'
+ Sproc 'mtr'.'add_suppression'
+ Sproc 'mtr'.'check_testcase'
+ Sproc 'mtr'.'check_warnings'
+ Sproc 'mtr'.'force_restart'
+ Trigger 'mtr'.'gs_insert'
+ Trigger 'mtr'.'ts_insert'
+ 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>'
+
+#
+# Test 1 - Minimum set of privileges for "empty" backup of list
+#
+#
+# Create a new user named bup_sec_user1 and grant the user
+# a minimum set of privileges needed to run a complete or full backup
+# using the BACKUP DATABASE <list> command on an empty list.
+#
+CREATE USER 'bup_sec_user1'@'localhost';
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+#
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+Grants for bup_sec_user1@localhost
+GRANT USAGE ON *.* TO 'bup_sec_user1'@'localhost'
+GRANT BACKUP ON `bup_sec_db1`.* TO 'bup_sec_user1'@'localhost'
+GRANT BACKUP ON `bup_sec_db2`.* TO 'bup_sec_user1'@'localhost'
+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.
+#
+# 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'.
+#
+# Drop the user.
+#
+# connection default
+DROP USER 'bup_sec_user1'@'localhost';
+#
+# Test 2 - Minimum set of specific privileges for "full" backup of list
+#
+#
+# Create a new user named bup_sec_user1 and grant the user
+# a minimum set of the object-specific privileges needed to run a
+# complete or full backup using the BACKUP DATABASE <list> command.
+#
+CREATE USER 'bup_sec_user1'@'localhost';
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t2 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON PROCEDURE bup_sec_db1.p1 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON FUNCTION bup_sec_db1.f1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+#
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+Grants for bup_sec_user1@localhost
+GRANT USAGE ON *.* TO 'bup_sec_user1'@'localhost'
+GRANT EVENT, BACKUP ON `bup_sec_db1`.* TO 'bup_sec_user1'@'localhost'
+GRANT BACKUP ON `bup_sec_db2`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT ON `bup_sec_db1`.`t2` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT ON `mysql`.`procs_priv` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, TRIGGER ON `bup_sec_db2`.`t1` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, TRIGGER ON `bup_sec_db1`.`t1` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, SHOW VIEW ON `bup_sec_db1`.`v1` TO 'bup_sec_user1'@'localhost'
+GRANT EXECUTE ON PROCEDURE `bup_sec_db1`.`p1` TO 'bup_sec_user1'@'localhost'
+GRANT EXECUTE ON FUNCTION `bup_sec_db1`.`f1` TO 'bup_sec_user1'@'localhost'
+#
+# Connect as user bup_sec_user1 and attempt backup.
+# Expected result: success.
+#
+# connection con_user1
+BACKUP DATABASE bup_sec_db1, bup_sec_db2 to 'bup_sec.bak';
+backup_id
+#
+#
+# Run mysqlbackup to dump the image and show all objects included.
+#
+# exec $MYSQL_BACKUP
+
+Image path: #
+Image size: #
+Image compression: none
+Image version: 1
+Creation time: #
+Server version: #
+Server byte order: #
+Server charset: 'utf8'
+
+Catalog details:
+
+ Database 'bup_sec_db1'
+ Table 'bup_sec_db1'.'t1'
+ Table 'bup_sec_db1'.'t2'
+ Sproc 'bup_sec_db1'.'p1'
+ Sfunc 'bup_sec_db1'.'f1'
+ View 'bup_sec_db1'.'v1'
+ Event 'bup_sec_db1'.'e1'
+ Trigger 'bup_sec_db1'.'t1_ai'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Database 'bup_sec_db2'
+ Table 'bup_sec_db2'.'t1'
+ Trigger 'bup_sec_db2'.'t1_ai'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+
+#
+# Revoke all privileges and drop the user.
+#
+# connection default
+# Explicit revoke due to Bug#36544
+REVOKE EXECUTE ON FUNCTION bup_sec_db1.f1 FROM 'bup_sec_user1'@'localhost';
+DROP USER 'bup_sec_user1'@'localhost';
+#
+# Test 3 - Minimum set of db-level privileges for "full" backup of list
+#
+#
+# Create a new user named bup_sec_user1 and grant the user
+# a minimum set of privileges needed to run a complete or full backup
+# using the BACKUP DATABASE <list> command.
+#
+CREATE USER 'bup_sec_user1'@'localhost';
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+#
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+Grants for bup_sec_user1@localhost
+GRANT USAGE ON *.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, EXECUTE, SHOW VIEW, EVENT, TRIGGER, BACKUP ON `bup_sec_db1`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, TRIGGER, BACKUP ON `bup_sec_db2`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT ON `mysql`.`procs_priv` TO 'bup_sec_user1'@'localhost'
+#
+# Connect as user bup_sec_user1 and attempt backup.
+# Expected result: success.
+#
+# connection con_user1
+BACKUP DATABASE bup_sec_db1, bup_sec_db2 to 'bup_sec.bak';
+backup_id
+#
+#
+# Run mysqlbackup to dump the image and show all objects included.
+#
+# exec $MYSQL_BACKUP
+
+Image path: #
+Image size: #
+Image compression: none
+Image version: 1
+Creation time: #
+Server version: #
+Server byte order: #
+Server charset: 'utf8'
+
+Catalog details:
+
+ Database 'bup_sec_db1'
+ Table 'bup_sec_db1'.'t1'
+ Table 'bup_sec_db1'.'t2'
+ Sproc 'bup_sec_db1'.'p1'
+ Sfunc 'bup_sec_db1'.'f1'
+ View 'bup_sec_db1'.'v1'
+ Event 'bup_sec_db1'.'e1'
+ Trigger 'bup_sec_db1'.'t1_ai'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Database 'bup_sec_db2'
+ Table 'bup_sec_db2'.'t1'
+ Trigger 'bup_sec_db2'.'t1_ai'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+
+#
+# Drop the user.
+#
+# connection default
+DROP USER 'bup_sec_user1'@'localhost';
+#
+# Test 4 - Minimum set of privileges for "empty" backup of *
+#
+#
+# Create a new user named bup_sec_user1 and grant the user
+# a minimum set of privileges needed to run a complete or full backup
+# using the BACKUP DATABASE * command.
+#
+CREATE USER 'bup_sec_user1'@'localhost';
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+#
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+Grants for bup_sec_user1@localhost
+GRANT USAGE ON *.* TO 'bup_sec_user1'@'localhost'
+GRANT BACKUP ON `bup_sec_db1`.* TO 'bup_sec_user1'@'localhost'
+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.
+#
+# connection con_user1
+BACKUP DATABASE * to 'bup_sec.bak';
+ERROR HY000: Insufficient privileges. You must have the SELECT privilege on all databases to execute BACKUP DATABASE *.
+SHOW ERRORS;
+Level Code Message
+Error # Insufficient privileges. You must have the SELECT privilege on all databases to execute BACKUP DATABASE *.
+#
+# Drop the user.
+#
+# connection default
+DROP USER 'bup_sec_user1'@'localhost';
+#
+# Test 5 - Minimum set of specific privileges for "full" backup of *
+#
+#
+# Create a new user named bup_sec_user1 and grant the user
+# a minimum set of object-specific privileges needed to run a complete
+# or full backup using the BACKUP DATABASE * command.
+#
+CREATE USER 'bup_sec_user1'@'localhost';
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t2 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON PROCEDURE bup_sec_db1.p1 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON FUNCTION bup_sec_db1.f1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+GRANT ALL ON mtr.* TO 'bup_sec_user1'@'localhost';
+#
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+Grants for bup_sec_user1@localhost
+GRANT USAGE ON *.* TO 'bup_sec_user1'@'localhost'
+GRANT ALL PRIVILEGES ON `mtr`.* TO 'bup_sec_user1'@'localhost'
+GRANT EVENT, BACKUP ON `bup_sec_db1`.* TO 'bup_sec_user1'@'localhost'
+GRANT BACKUP ON `bup_sec_db2`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT ON `bup_sec_db1`.`t2` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT ON `mysql`.`procs_priv` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, TRIGGER ON `bup_sec_db2`.`t1` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, TRIGGER ON `bup_sec_db1`.`t1` TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, SHOW VIEW ON `bup_sec_db1`.`v1` TO 'bup_sec_user1'@'localhost'
+GRANT EXECUTE ON PROCEDURE `bup_sec_db1`.`p1` TO 'bup_sec_user1'@'localhost'
+GRANT EXECUTE ON FUNCTION `bup_sec_db1`.`f1` TO 'bup_sec_user1'@'localhost'
+#
+# Run backup again as user bup_sec_user1 and reattempt backup.
+# Expected result: success.
+#
+# connection con_user1
+BACKUP DATABASE * to 'bup_sec.bak';
+backup_id
+#
+#
+# Run mysqlbackup to dump the image and show all objects included.
+#
+# exec $MYSQL_BACKUP
+
+Image path: #
+Image size: #
+Image compression: none
+Image version: 1
+Creation time: #
+Server version: #
+Server byte order: #
+Server charset: 'utf8'
+
+Catalog details:
+
+ Database 'bup_sec_db1'
+ Table 'bup_sec_db1'.'t1'
+ Table 'bup_sec_db1'.'t2'
+ Sproc 'bup_sec_db1'.'p1'
+ Sfunc 'bup_sec_db1'.'f1'
+ View 'bup_sec_db1'.'v1'
+ Event 'bup_sec_db1'.'e1'
+ Trigger 'bup_sec_db1'.'t1_ai'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Database 'bup_sec_db2'
+ Table 'bup_sec_db2'.'t1'
+ Trigger 'bup_sec_db2'.'t1_ai'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Database 'mtr'
+ Table 'mtr'.'global_suppressions'
+ Table 'mtr'.'test_suppressions'
+ Sproc 'mtr'.'add_suppression'
+ Sproc 'mtr'.'check_testcase'
+ Sproc 'mtr'.'check_warnings'
+ Sproc 'mtr'.'force_restart'
+ Trigger 'mtr'.'gs_insert'
+ Trigger 'mtr'.'ts_insert'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Database 'test'
+
+#
+# Revoke all privileges and drop the user.
+#
+# connection default
+# Explicit revoke due to Bug#36544
+REVOKE EXECUTE ON FUNCTION bup_sec_db1.f1 FROM 'bup_sec_user1'@'localhost';
+DROP USER 'bup_sec_user1'@'localhost';
+#
+# Test 6 - Minimum set of db-level privileges for "full" backup of *
+#
+#
+# Create a new user named bup_sec_user1 and grant the user
+# a minimum set of privileges needed to run a complete or full backup
+# using the BACKUP DATABASE * command.
+#
+CREATE USER 'bup_sec_user1'@'localhost';
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT ALL ON mtr.* TO 'bup_sec_user1'@'localhost';
+#
+# Show grants for user.
+#
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+Grants for bup_sec_user1@localhost
+GRANT USAGE ON *.* TO 'bup_sec_user1'@'localhost'
+GRANT ALL PRIVILEGES ON `mtr`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, EXECUTE, SHOW VIEW, EVENT, TRIGGER, BACKUP ON `bup_sec_db1`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT, TRIGGER, BACKUP ON `bup_sec_db2`.* TO 'bup_sec_user1'@'localhost'
+GRANT SELECT ON `mysql`.`procs_priv` TO 'bup_sec_user1'@'localhost'
+#
+# Run backup again as user bup_sec_user1 and reattempt backup.
+# Expected result: success.
+#
+# connection con_user1
+BACKUP DATABASE * to 'bup_sec.bak';
+backup_id
+#
+#
+# Run mysqlbackup to dump the image and show all objects included.
+#
+# exec $MYSQL_BACKUP
+
+Image path: #
+Image size: #
+Image compression: none
+Image version: 1
+Creation time: #
+Server version: #
+Server byte order: #
+Server charset: 'utf8'
+
+Catalog details:
+
+ Database 'bup_sec_db1'
+ Table 'bup_sec_db1'.'t1'
+ Table 'bup_sec_db1'.'t2'
+ Sproc 'bup_sec_db1'.'p1'
+ Sfunc 'bup_sec_db1'.'f1'
+ View 'bup_sec_db1'.'v1'
+ Event 'bup_sec_db1'.'e1'
+ Trigger 'bup_sec_db1'.'t1_ai'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Privilege 'bup_sec_db1'.'<empty>'
+ Database 'bup_sec_db2'
+ Table 'bup_sec_db2'.'t1'
+ Trigger 'bup_sec_db2'.'t1_ai'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Privilege 'bup_sec_db2'.'<empty>'
+ Database 'mtr'
+ Table 'mtr'.'global_suppressions'
+ Table 'mtr'.'test_suppressions'
+ Sproc 'mtr'.'add_suppression'
+ Sproc 'mtr'.'check_testcase'
+ Sproc 'mtr'.'check_warnings'
+ Sproc 'mtr'.'force_restart'
+ Trigger 'mtr'.'gs_insert'
+ Trigger 'mtr'.'ts_insert'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Privilege 'mtr'.'<empty>'
+ Database 'test'
+
+# connection default
+DROP USER 'bup_sec_user1'@'localhost';
+#
+# Cleanup
+#
+DROP DATABASE bup_sec_db1;
+DROP DATABASE bup_sec_db2;
=== modified file 'mysql-test/suite/backup/t/backup_security.test'
--- a/mysql-test/suite/backup/t/backup_security.test 2009-07-01 20:42:26 +0000
+++ b/mysql-test/suite/backup/t/backup_security.test 2009-07-15 14:18:59 +0000
@@ -1,8 +1,18 @@
#
# This test includes tests for all of the security-related tasks in
-# Online Backup. These include tests for:
+# MySQL Backup. These include tests for:
#
-# 1) Only users with BACKUP or RESTORE privilege can run backup or restore.
+# 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.
#
--source include/not_embedded.inc
@@ -12,24 +22,15 @@ call mtr.add_suppression("Backup:");
call mtr.add_suppression("Restore:");
enable_query_log;
-connect (root_user,localhost,root,,);
+connect (conn_root,localhost,root,,);
--disable_warnings
DROP DATABASE IF EXISTS backup_test;
+DROP DATABASE IF EXISTS backup_test_alt;
--enable_warnings
-#
-# Test 1 - Only users with BACKUP and RESTORE privilege can run backup
-# and restore.
-#
-
--echo #
--echo # Create database and data to test.
---echo # Create 3 users and grants rights as follows:
---echo # bup_no_rights - denied everything (poor chap)
---echo # except SELECT ON backup_test_alt.*
---echo # bup_with_rights - super user account (like root)
---echo # bup_some_rights - rights to a database but no BACKUP or RESTORE
--echo #
CREATE DATABASE backup_test;
CREATE TABLE backup_test.t1 (a char(30)) ENGINE=MEMORY;
@@ -41,6 +42,11 @@ INSERT INTO backup_test.t1 VALUES ("05 T
INSERT INTO backup_test.t1 VALUES ("06 Test #1 - privilege");
INSERT INTO backup_test.t1 VALUES ("07 Test #1 - privilege");
+CREATE TABLE backup_test.t2 (a char(30)) ENGINE=MEMORY;
+INSERT INTO backup_test.t2 VALUES ("01 Test #2 - privilege");
+INSERT INTO backup_test.t2 VALUES ("02 Test #2 - privilege");
+INSERT INTO backup_test.t2 VALUES ("03 Test #2 - privilege");
+
CREATE DATABASE backup_test_alt;
CREATE TABLE backup_test_alt.t1 (a char(30)) ENGINE=MEMORY;
INSERT INTO backup_test_alt.t1 VALUES ("01 Test #2 - privilege");
@@ -61,23 +67,99 @@ CREATE VIEW backup_test.v1 as SELECT * F
CREATE EVENT backup_test.e1 ON SCHEDULE EVERY 1 YEAR DO
DELETE FROM backup_test.t1 WHERE a = "not there";
-CREATE USER 'bup_no_rights'@'localhost';
-CREATE USER 'bup_with_rights'@'localhost';
-CREATE USER 'bup_some_rights'@'localhost';
-
-REVOKE ALL ON *.* FROM 'bup_no_rights'@'localhost';
-REVOKE ALL ON *.* FROM 'bup_with_rights'@'localhost';
-REVOKE ALL ON *.* FROM 'bup_some_rights'@'localhost';
-GRANT SELECT ON backup_test_alt.* TO 'bup_no_rights'@'localhost';
-GRANT ALL ON *.* TO 'bup_with_rights'@'localhost';
-GRANT GRANT OPTION ON *.* TO 'bup_with_rights'@'localhost';
-GRANT ALL ON backup_test.* TO 'bup_some_rights'@'localhost';
-REVOKE BACKUP, RESTORE ON backup_test.* FROM 'bup_some_rights'@'localhost';
-GRANT SELECT ON mysql.* TO 'bup_with_rights'@'localhost';
+--echo #
+--echo # Create users.
+--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';
+
+--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 #
+
+--echo #
+--echo # Setup grants for bup_no_priv
+--echo #
+REVOKE ALL ON *.* FROM 'bup_no_priv'@'localhost';
+GRANT SELECT ON backup_test_alt.* TO 'bup_no_priv'@'localhost';
+
+--echo #
+--echo # Setup grants for bup_root_user
+--echo #
+GRANT ALL ON *.* TO 'bup_root_user'@'localhost';
+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 #
+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';
+
FLUSH PRIVILEGES;
+--echo #
+--echo # Show grants for users.
--echo #
---echo # root_user: Do backup of database with root user for later tests.
+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';
+
+--echo #
+--echo # conn_root_user: Do backup of database with root user for later tests.
--echo #
--replace_column 1 #
@@ -91,111 +173,598 @@ 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 root_user;
--echo #
---echo # Connect as user with some rights and attempt backup.
+--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 #
-connect (some_rights,localhost,bup_some_rights,,);
--echo #
---echo # some_rights: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_DENIED_ERROR
+--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_DENIED_ERROR
-BACKUP DATABASE backup_test to 'bup_some_rights.bak';
---replace_regex /[0-9]/#/
+--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 # some_rights: Attempting backup. Should fail with
---echo # error ER_BACKUP_ACCESS_DENIED_ERROR
+--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_DENIED_ERROR
-BACKUP DATABASE * to 'bup_some_rights.bak';
---replace_regex /[0-9]/#/
+--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 some_rights;
+disconnect conn_select_priv;
--echo #
---echo # Connect as user with no rights and attempt backup.
+--echo # Connect as root and add privileges.
--echo #
-connect (no_rights,localhost,bup_no_rights,,);
+connect (conn_root,localhost,root,,);
--echo #
---echo # no_rights: Attempting backup. Should fail with
+--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 backup_test to 'bup_no_rights.bak';
---replace_regex /[0-9]/#/
+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 #
+
+disconnect conn_root;
+--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 restore. Should fail with
+--echo # error ER_RESTORE_ACCESS_DENIED_ERROR
+--echo #
+--replace_column 1 #
+--error ER_RESTORE_ACCESS_DENIED_ERROR
+RESTORE FROM 'backup_test_orig.bak';
+--replace_column 2 #
SHOW ERRORS;
source suite/backup/include/backup_check_privileges.inc;
--echo #
---echo # no_rights: Attempting backup. Should fail with
+--echo # conn_no_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_alt to 'bup_no_rights.bak';
---replace_regex /[0-9]/#/
+BACKUP DATABASE backup_test_alt to 'bup_no_priv.bak';
+--replace_column 2 #
SHOW ERRORS;
+source suite/backup/include/backup_check_privileges.inc;
+
+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 succeed
+--echo #
+--replace_column 1 #
+BACKUP DATABASE backup_test to 'bup_root_user.bak';
+
+--echo #
+--echo # conn_root_user: Attempting restore. Should succeed
+--echo #
+--replace_column 1 #
+RESTORE FROM 'bup_root_user.bak' OVERWRITE;
+
--echo #
---echo # no_rights: Attempting backup. Should fail with
+--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';
+
+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 * to 'bup_no_rights.bak';
---replace_regex /[0-9]/#/
+BACKUP DATABASE backup_test to 'bup_no_bup_priv.bak';
+--replace_column 2 #
SHOW ERRORS;
--echo #
---echo # no_rights: Attempting restore. Should fail with
+--echo # conn_no_bup_priv: Attempting restore. Should fail with
--echo # error ER_RESTORE_ACCESS_DENIED_ERROR
--echo #
--replace_column 1 #
--error ER_RESTORE_ACCESS_DENIED_ERROR
RESTORE FROM 'backup_test_orig.bak';
---replace_regex /[0-9]/#/
+--replace_column 2 #
SHOW ERRORS;
-source suite/backup/include/backup_check_privileges.inc;
+--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 no_rights;
+disconnect conn_no_bup_priv;
--echo #
---echo # Connect as user with rights and attempt backup and restore.
+--echo # Do restore to ensure it still works with root user.
--echo #
-connect (with_rights,localhost,bup_with_rights,,);
+connect (conn_no_priv,localhost,bup_no_priv,,);
--echo #
---echo # with_rights: Attempting backup. Should succeed
+--echo # conn_no_priv: Attempting backup *. Should fail with
+--echo # error ER_BACKUP_ACCESS_DBS_INCOMPLETE
--echo #
--replace_column 1 #
-BACKUP DATABASE backup_test to 'bup_with_rights.bak';
+--error ER_BACKUP_ACCESS_DBS_INCOMPLETE
+BACKUP DATABASE * to 'bup_no_priv.bak';
+--replace_column 2 #
+SHOW ERRORS;
+disconnect conn_no_priv;
--echo #
---echo # with_rights: Attempting backup. Should not fail.
+--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_with_rights_star.bak';
+BACKUP DATABASE * to 'bup_root_user_star.bak';
+disconnect conn_root_user;
--echo #
---echo # with_rights: Attempting restore. Should succeed
+--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_DBS_INCOMPLETE
--echo #
--replace_column 1 #
-RESTORE FROM 'bup_with_rights.bak' OVERWRITE;
+--error ER_BACKUP_ACCESS_DBS_INCOMPLETE
+BACKUP DATABASE * to 'bup_no_bup_priv.bak';
+--replace_column 2 #
+SHOW ERRORS;
-disconnect with_rights;
+disconnect conn_no_bup_priv;
--echo #
---echo # Do restore to ensure it still works with root user.
+--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 #
+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 succeed.
+--echo #
+--replace_column 1 #
+BACKUP DATABASE * to 'bup_select_priv.bak';
+
+--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 #
+
+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 (root_user,localhost,root,,);
+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.
@@ -205,11 +774,223 @@ 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';
+--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 #
+--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 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 #
+--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 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;
+--echo #
+--echo # Connect as root and check restore then cleanup.
+--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 that all objects were recreated after restore.
+--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';
@@ -220,14 +1001,20 @@ SELECT trigger_name FROM INFORMATION_SCH
--echo # Cleanup
--echo #
-DROP USER 'bup_no_rights'@'localhost';
-DROP USER 'bup_with_rights'@'localhost';
-DROP USER 'bup_some_rights'@'localhost';
+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 DATABASE backup_test;
-DROP DATABASE backup_test_alt;
FLUSH PRIVILEGES;
let $MYSQLD_BACKUPDIR= `select @@backupdir`;
-remove_file $MYSQLD_BACKUPDIR/bup_with_rights.bak;
+remove_file $MYSQLD_BACKUPDIR/bup_root_user.bak;
remove_file $MYSQLD_BACKUPDIR/backup_test_orig.bak;
-remove_file $MYSQLD_BACKUPDIR/bup_with_rights_star.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;
=== added file 'mysql-test/suite/backup/t/backup_security_check.test'
--- a/mysql-test/suite/backup/t/backup_security_check.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_security_check.test 2009-07-15 14:18:59 +0000
@@ -0,0 +1,388 @@
+#
+# This test includes tests for all of the security-related tasks in
+# MySQL Backup. This test was created to demonstrate that the problem
+# of skipped objects has been solved by BUG#45889. Specifically, the
+# BACKUP DATABASE command should include all objects or fail with an error.
+# This file includes the following test cases:
+#
+# 1) Minimum set of privileges for "empty" backup of list
+# 2) Minimum set of specific privileges for "full" backup of list
+# 3) Minimum set of db-level privileges for "full" backup of list
+# 4) Minimum set of privileges for "empty" backup of *
+# 5) Minimum set of specific privileges for "full" backup of *
+# 6) Minimum set of db-level privileges for "full" backup of *
+#
+
+--source include/not_embedded.inc
+
+--let $MYSQLD_DATADIR= `SELECT @@datadir`
+--let $MYSQLD_BACKUPDIR= `SELECT @@backupdir`
+
+--disable_warnings
+DROP DATABASE IF EXISTS bup_sec_db1;
+DROP DATABASE IF EXISTS bup_sec_db2;
+--enable_warnings
+
+--echo #
+--echo # Create some data to test. Includes one of each type of object
+--echo # and two databases.
+--echo #
+
+CREATE DATABASE bup_sec_db1;
+CREATE TABLE bup_sec_db1.t1 (a char(30)) ENGINE=MEMORY;
+CREATE TABLE bup_sec_db1.t2 (a char(30)) ENGINE=MEMORY;
+CREATE VIEW bup_sec_db1.v1 as SELECT * FROM bup_sec_db1.t1;
+CREATE TRIGGER bup_sec_db1.t1_ai AFTER INSERT ON bup_sec_db1.t1 FOR EACH ROW
+ INSERT INTO bup_sec_db1.t1 VALUES('Test objects count');
+CREATE PROCEDURE bup_sec_db1.p1(p1 CHAR(20))
+ INSERT INTO bup_sec_db1.t1 VALUES ("50");
+CREATE FUNCTION bup_sec_db1.f1() RETURNS INT RETURN (SELECT 1);
+CREATE EVENT bup_sec_db1.e1 ON SCHEDULE EVERY 1 YEAR DO
+ DELETE FROM bup_sec_db1.t1 WHERE a = "not there";
+
+CREATE DATABASE bup_sec_db2;
+CREATE TABLE bup_sec_db2.t1 (a char(30)) ENGINE=MEMORY;
+CREATE TRIGGER bup_sec_db2.t1_ai AFTER INSERT ON bup_sec_db2.t1 FOR EACH ROW
+ INSERT INTO bup_sec_db2.t1 VALUES('Test objects count');
+
+--echo #
+--echo # Attempt backup as root to capture backup image with all objects.
+--echo #
+--error 0,1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+--replace_column 1 #
+BACKUP DATABASE * to 'bup_sec.bak';
+
+--echo #
+--echo # Dump the backup image to show the metadata using mysqlbackup.
+--echo #
+--echo # exec \$MYSQL_BACKUP
+--source suite/backup/include/backup_client_regex_output.inc
+exec $MYSQL_BACKUP --catalog-details $MYSQLD_BACKUPDIR/bup_sec.bak 2>&1;
+--echo
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+
+--echo #
+--echo # Test 1 - Minimum set of privileges for "empty" backup of list
+--echo #
+
+--echo #
+--echo # Create a new user named bup_sec_user1 and grant the user
+--echo # a minimum set of privileges needed to run a complete or full backup
+--echo # using the BACKUP DATABASE <list> command on an empty list.
+--echo #
+CREATE USER 'bup_sec_user1'@'localhost';
+
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Connect as user bup_sec_user1 and attempt backup.
+--echo # Expected result: Pass for non-patched code, ERROR for patched code.
+--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.
+--echo #
+--echo # connection default
+--connection default
+--disconnect con_user1
+--error 0,1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+DROP USER 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Test 2 - Minimum set of specific privileges for "full" backup of list
+--echo #
+
+--echo #
+--echo # Create a new user named bup_sec_user1 and grant the user
+--echo # a minimum set of the object-specific privileges needed to run a
+--echo # complete or full backup using the BACKUP DATABASE <list> command.
+--echo #
+CREATE USER 'bup_sec_user1'@'localhost';
+
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t2 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON PROCEDURE bup_sec_db1.p1 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON FUNCTION bup_sec_db1.f1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Connect as user bup_sec_user1 and attempt backup.
+--echo # Expected result: success.
+--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 #
+BACKUP DATABASE bup_sec_db1, bup_sec_db2 to 'bup_sec.bak';
+
+--echo #
+--echo # Run mysqlbackup to dump the image and show all objects included.
+--echo #
+--echo # exec \$MYSQL_BACKUP
+--source suite/backup/include/backup_client_regex_output.inc
+exec $MYSQL_BACKUP --catalog-details $MYSQLD_BACKUPDIR/bup_sec.bak 2>&1;
+--echo
+
+--echo #
+--echo # Revoke all privileges and drop the user.
+--echo #
+--echo # connection default
+--connection default
+--disconnect con_user1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+--echo # Explicit revoke due to Bug#36544
+REVOKE EXECUTE ON FUNCTION bup_sec_db1.f1 FROM 'bup_sec_user1'@'localhost';
+DROP USER 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Test 3 - Minimum set of db-level privileges for "full" backup of list
+--echo #
+
+--echo #
+--echo # Create a new user named bup_sec_user1 and grant the user
+--echo # a minimum set of privileges needed to run a complete or full backup
+--echo # using the BACKUP DATABASE <list> command.
+--echo #
+CREATE USER 'bup_sec_user1'@'localhost';
+
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Connect as user bup_sec_user1 and attempt backup.
+--echo # Expected result: success.
+--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 #
+BACKUP DATABASE bup_sec_db1, bup_sec_db2 to 'bup_sec.bak';
+
+--echo #
+--echo # Run mysqlbackup to dump the image and show all objects included.
+--echo #
+--echo # exec \$MYSQL_BACKUP
+--source suite/backup/include/backup_client_regex_output.inc
+exec $MYSQL_BACKUP --catalog-details $MYSQLD_BACKUPDIR/bup_sec.bak 2>&1;
+--echo
+
+--echo #
+--echo # Drop the user.
+--echo #
+--echo # connection default
+--connection default
+--disconnect con_user1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+DROP USER 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Test 4 - Minimum set of privileges for "empty" backup of *
+--echo #
+
+--echo #
+--echo # Create a new user named bup_sec_user1 and grant the user
+--echo # a minimum set of privileges needed to run a complete or full backup
+--echo # using the BACKUP DATABASE * command.
+--echo #
+CREATE USER 'bup_sec_user1'@'localhost';
+
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Connect as user bup_sec_user1 and attempt backup.
+--echo # Expected result: Pass for non-patched code, ERROR for patched code.
+--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_DBS_INCOMPLETE
+BACKUP DATABASE * to 'bup_sec.bak';
+--replace_column 2 #
+SHOW ERRORS;
+
+--echo #
+--echo # Drop the user.
+--echo #
+--echo # connection default
+--connection default
+--disconnect con_user1
+--error 0,1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+DROP USER 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Test 5 - Minimum set of specific privileges for "full" backup of *
+--echo #
+
+--echo #
+--echo # Create a new user named bup_sec_user1 and grant the user
+--echo # a minimum set of object-specific privileges needed to run a complete
+--echo # or full backup using the BACKUP DATABASE * command.
+--echo #
+CREATE USER 'bup_sec_user1'@'localhost';
+
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.t2 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON PROCEDURE bup_sec_db1.p1 TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON FUNCTION bup_sec_db1.f1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.t1 TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.v1 TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.t1 TO 'bup_sec_user1'@'localhost';
+# Don't care about mtr.
+GRANT ALL ON mtr.* TO 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Run backup again as user bup_sec_user1 and reattempt backup.
+--echo # Expected result: success.
+--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 #
+BACKUP DATABASE * to 'bup_sec.bak';
+
+--echo #
+--echo # Run mysqlbackup to dump the image and show all objects included.
+--echo #
+--echo # exec \$MYSQL_BACKUP
+--source suite/backup/include/backup_client_regex_output.inc
+exec $MYSQL_BACKUP --catalog-details $MYSQLD_BACKUPDIR/bup_sec.bak 2>&1;
+--echo
+
+--echo #
+--echo # Revoke all privileges and drop the user.
+--echo #
+--echo # connection default
+--connection default
+--disconnect con_user1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+--echo # Explicit revoke due to Bug#36544
+REVOKE EXECUTE ON FUNCTION bup_sec_db1.f1 FROM 'bup_sec_user1'@'localhost';
+DROP USER 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Test 6 - Minimum set of db-level privileges for "full" backup of *
+--echo #
+
+--echo #
+--echo # Create a new user named bup_sec_user1 and grant the user
+--echo # a minimum set of privileges needed to run a complete or full backup
+--echo # using the BACKUP DATABASE * command.
+--echo #
+CREATE USER 'bup_sec_user1'@'localhost';
+
+GRANT SELECT ON mysql.procs_priv TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT SHOW VIEW ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EXECUTE ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT EVENT ON bup_sec_db1.* TO 'bup_sec_user1'@'localhost';
+GRANT BACKUP ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT SELECT ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+GRANT TRIGGER ON bup_sec_db2.* TO 'bup_sec_user1'@'localhost';
+# Don't care about mtr.
+GRANT ALL ON mtr.* TO 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Show grants for user.
+--echo #
+SHOW GRANTS FOR 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Run backup again as user bup_sec_user1 and reattempt backup.
+--echo # Expected result: success.
+--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 #
+BACKUP DATABASE * to 'bup_sec.bak';
+
+--echo #
+--echo # Run mysqlbackup to dump the image and show all objects included.
+--echo #
+--echo # exec \$MYSQL_BACKUP
+--source suite/backup/include/backup_client_regex_output.inc
+exec $MYSQL_BACKUP --catalog-details $MYSQLD_BACKUPDIR/bup_sec.bak 2>&1;
+--echo
+
+--echo # connection default
+--connection default
+--disconnect con_user1
+--remove_file $MYSQLD_BACKUPDIR/bup_sec.bak
+DROP USER 'bup_sec_user1'@'localhost';
+
+--echo #
+--echo # Cleanup
+--echo #
+DROP DATABASE bup_sec_db1;
+DROP DATABASE bup_sec_db2;
+
=== modified file 'sql/backup/backup_info.cc'
--- a/sql/backup/backup_info.cc 2009-07-06 07:29:06 +0000
+++ b/sql/backup/backup_info.cc 2009-07-15 14:18:59 +0000
@@ -621,6 +621,15 @@ backup::Image_info::Db* Backup_info::add
return NULL;
}
+ /*
+ Check to see if the user can see all of the objects in the database.
+ */
+ if (obs::check_user_access(m_thd, name))
+ {
+ m_log.report_error(ER_BACKUP_ACCESS_OBJS_INCOMPLETE, name->ptr());
+ return NULL;
+ }
+
Db *db= Image_info::add_db(*name, pos);
if (!db)
@@ -739,6 +748,16 @@ int Backup_info::add_all_dbs()
using namespace obs;
int res= 0;
+
+ /*
+ Check to see if the user can see all of the databases.
+ */
+ if (check_user_access(m_thd, 0))
+ {
+ m_log.report_error(ER_BACKUP_ACCESS_DBS_INCOMPLETE);
+ return ERROR;
+ }
+
Obj_iterator *dbit= get_databases(m_thd);
if (!dbit)
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2009-07-06 07:29:06 +0000
+++ b/sql/share/errmsg.txt 2009-07-15 14:18:59 +0000
@@ -6550,3 +6550,7 @@ ER_BACKUP_ACCESS_DENIED_ERROR
eng "Insufficient privileges. You must have the BACKUP privilege to backup database '%s'."
ER_RESTORE_ACCESS_DENIED_ERROR
eng "Insufficient privileges. You must have the RESTORE privilege to restore database '%s'."
+ER_BACKUP_ACCESS_DBS_INCOMPLETE
+ eng "Insufficient privileges. You must have the SELECT privilege on all databases to execute BACKUP DATABASE *."
+ER_BACKUP_ACCESS_OBJS_INCOMPLETE
+ eng "Insufficient privileges. You do not have privileges to backup database '%s'."
=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc 2009-06-24 08:05:34 +0000
+++ b/sql/si_objects.cc 2009-07-15 14:18:59 +0000
@@ -2501,6 +2501,144 @@ Obj_iterator *get_databases(THD *thd)
///////////////////////////////////////////////////////////////////////////
+///////////////////////////////////////////////////////////////////////////
+/**
+ Return a count of databases.
+
+ This method returns the number of databases excluding the internal
+ databases 'mysql' and 'information_schema'.
+
+ @Note This method executes based on the current context of the THD
+ and thus returns a value based on the accessibility of objects
+ by the user context.
+
+ @param[in] THD Current thread
+
+ @return uint Number of databases.
+*/
+uint get_num_dbs(THD *thd)
+{
+ Ed_connection ed_connection(thd);
+ Ed_result_set *ed_result_set;
+ String_stream s_stream;
+
+ s_stream <<
+ "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA " <<
+ "WHERE LCASE(schema_name) != 'mysql' AND " <<
+ "LCASE(schema_name) != 'information_schema'";
+
+ if (run_service_interface_sql(thd, &ed_connection, s_stream.lex_string()))
+ /* Query failed with an error */
+ return NULL;
+ else if(ed_connection.get_warn_count())
+ /* Push warnings to BACKUP's error stack. */
+ thd->warning_info->append_warnings(thd, ed_connection.get_warn_list());
+
+ DBUG_ASSERT(ed_connection.get_field_count());
+
+ /* Use store_result to get ownership of result memory */
+ ed_result_set= ed_connection.store_result_set();
+ uint count= ed_result_set->size();
+ delete ed_result_set;
+ return count;
+}
+
+///////////////////////////////////////////////////////////////////////////
+
+/**
+ Return a count of objects belonging to a given database.
+
+ This method returns the number of objects that match the database name
+ specified as @c db_name.
+
+ @Note This method executes based on the current context of the THD
+ and thus returns a value based on the accessibility of objects
+ by the user context.
+
+ @param[in] THD Current thread
+ @param[in] String Database name.
+
+ @return uint Number of objects in the database.
+*/
+uint get_num_objects(THD *thd, const String *db_name)
+{
+ Ed_connection ed_connection(thd);
+ Ed_result_set *ed_result_set;
+ String_stream s_stream;
+
+ /*
+ Build query to select all objects from IS tables.
+ */
+ s_stream <<
+ "SELECT TABLE_NAME "
+ "FROM INFORMATION_SCHEMA.TABLES "
+ "WHERE table_schema = '" << db_name << "' UNION "
+ "SELECT TRIGGER_NAME "
+ "FROM INFORMATION_SCHEMA.TRIGGERS "
+ "WHERE trigger_schema = '" << db_name << "' UNION "
+ "SELECT ROUTINE_NAME "
+ "FROM INFORMATION_SCHEMA.ROUTINES "
+ "WHERE routine_schema = '" << db_name << "' UNION "
+ "SELECT EVENT_NAME "
+ "FROM INFORMATION_SCHEMA.EVENTS "
+ "WHERE event_schema = '" << db_name << "'";
+
+ if (run_service_interface_sql(thd, &ed_connection, s_stream.lex_string()))
+ /* Query failed with an error */
+ return NULL;
+ else if(ed_connection.get_warn_count())
+ /* Push warnings to BACKUP's error stack. */
+ thd->warning_info->append_warnings(thd, ed_connection.get_warn_list());
+
+ DBUG_ASSERT(ed_connection.get_field_count());
+
+ /* Use store_result to get ownership of result memory */
+ ed_result_set= ed_connection.store_result_set();
+ uint count= ed_result_set->size();
+ delete ed_result_set;
+ return count;
+}
+
+///////////////////////////////////////////////////////////////////////////
+bool check_user_access(THD *thd, const String *db_name)
+{
+ uint elev_count;
+ uint user_count;
+
+ /*
+ Get value from user context without elevation.
+ */
+ if (!db_name)
+ user_count= get_num_dbs(thd);
+ else
+ user_count= get_num_objects(thd, db_name);
+
+ /*
+ Peform global elevation with SELECT to get value of
+ someone with global SELECT (e.g. root).
+ */
+ ulong saved_master_access= thd->security_ctx->master_access;
+ ulong saved_db_access= thd->security_ctx->db_access;
+ thd->security_ctx->master_access |= (SELECT_ACL | TRIGGER_ACL | EVENT_ACL);
+
+ if (!db_name)
+ elev_count= get_num_dbs(thd);
+ else
+ elev_count= get_num_objects(thd, db_name);
+
+ /*
+ Turn elevation off and restore user access.
+ */
+ thd->security_ctx->master_access= saved_master_access;
+ thd->security_ctx->db_access= saved_db_access;
+
+ /*
+ Compare results and return.
+ */
+ return !(user_count == elev_count);
+}
+
+///////////////////////////////////////////////////////////////////////////
Obj_iterator *get_db_tables(THD *thd, const String *db_name)
{
String_stream s_stream;
=== modified file 'sql/si_objects.h'
--- a/sql/si_objects.h 2009-05-05 17:42:58 +0000
+++ b/sql/si_objects.h 2009-07-15 14:18:59 +0000
@@ -164,7 +164,9 @@ Obj *get_database_stub(THD *thd, const S
///////////////////////////////////////////////////////////////////////////
/**
- Create an iterator over all databases in the server.
+ Create an iterator over the databases in the server that the user
+ can read with his current permissions.
+
Includes system databases, such as "mysql" and "information_schema".
The client is responsible for destruction of the returned iterator.
@@ -174,6 +176,29 @@ Obj *get_database_stub(THD *thd, const S
Obj_iterator *get_databases(THD *thd);
/**
+ Check user access to see all databases or objects in a given database.
+
+ This method can be used in two ways.
+
+ 1) If db_name = 0, the method shall check the user's visibility for all
+ databases excluding mysql and information_schema.
+ 2) If db_name != 0, the method shall check the user's visibility for all
+ objects in the database specified.
+
+ @param[in] THD The current thread.
+ @param[in] String The database name to check or 0
+
+ @returns Information whether user can see all specified objects
+ @retval TRUE
+ if db_name != 0 : user does not have visibility to the database specified
+ if db_name == 0 : user does not have visibility to all databases
+ @retval FALSE
+ if db_name != 0 : user does have visibility to the database specified
+ if db_name == 0 : user does have visibility to all databases
+*/
+bool check_user_access(THD *thd, const String *db_name);
+
+/**
Create an iterator over all base tables in a particular database.
Temporary tables are not included.
Attachment: [text/bzr-bundle] bzr/charles.bell@sun.com-20090715141859-rnz1iv2kr9rd14uo.bundle
