#At file:///C:/source/bzr/mysql-6.0-bug-39580/ based on revid:charles.bell@stripped1-10hssymlbgfmnjr4
2822 Chuck Bell 2009-06-05
BUG#39580 : BACKUP/RESTORE should not use SUPER
The backup system uses a privilege (SUPER) that is much more powerful
than what is necessary to allow backup and restore.
This patch changes the requirement of SUPER to two new privileges:
BACKUP - required for backup operations
RESTORE - required for restore operaions
These new privileges are set at the database-level thereby giving the
ability to grant BACKUP or RESTORE to one or more users for a given
database.
modified:
mysql-test/r/events_grant.result
mysql-test/r/grant.result
mysql-test/r/lowercase_table_grant.result
mysql-test/r/ps.result
mysql-test/r/system_mysql_db.result
mysql-test/suite/backup/r/backup_db_grants.result
mysql-test/suite/backup/r/backup_security.result
mysql-test/suite/backup/t/backup_security.test
mysql-test/suite/funcs_1/r/is_column_privileges.result
mysql-test/suite/funcs_1/r/is_schema_privileges.result
mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result
mysql-test/suite/funcs_1/r/is_user_privileges.result
scripts/mysql_system_tables.sql
scripts/mysql_system_tables_data.sql
scripts/mysql_system_tables_fix.sql
sql/backup/backup_kernel.h
sql/backup/kernel.cc
sql/si_objects.cc
sql/sql_acl.cc
sql/sql_acl.h
sql/sql_show.cc
sql/sql_yacc.yy
per-file messages:
mysql-test/r/events_grant.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/r/grant.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/r/lowercase_table_grant.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/r/ps.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/r/system_mysql_db.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/suite/backup/r/backup_db_grants.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/suite/backup/r/backup_security.result
New result file.
mysql-test/suite/backup/t/backup_security.test
Test reworked to explictly deny all rights for one user and grant only
the new privileges to the other.
mysql-test/suite/funcs_1/r/is_column_privileges.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/suite/funcs_1/r/is_schema_privileges.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result
New result file as a consequence of adding columns to the mysql tables.
mysql-test/suite/funcs_1/r/is_user_privileges.result
New result file as a consequence of adding columns to the mysql tables.
scripts/mysql_system_tables.sql
Added new columns to user and db tables in mysql database.
scripts/mysql_system_tables_data.sql
Added new data for the new columns to user and db tables in mysql database
to give root BACKUP and RESTORE privileges.
scripts/mysql_system_tables_fix.sql
Added code to fix the mysql tables.
sql/backup/backup_kernel.h
Added new method header.
sql/backup/kernel.cc
Added new method to check privileges to localize changes.
Moved privilege checking to new location for restore because the list
of databases is required for the check_privileges() method.
sql/si_objects.cc
Added code to elevate privileges temporarily while backup or
restore is running.
sql/sql_acl.cc
Added new column definitions for db table.
sql/sql_acl.h
Added new privilege declarations and associations.
sql/sql_show.cc
Added explanation of new columns for show.
sql/sql_yacc.yy
Added parser tags for grant recognition.
=== modified file 'mysql-test/r/events_grant.result'
--- a/mysql-test/r/events_grant.result 2009-02-16 14:47:53 +0000
+++ b/mysql-test/r/events_grant.result 2009-06-05 15:49:36 +0000
@@ -22,7 +22,7 @@ SHOW GRANTS;
Grants for ev_test@localhost
GRANT USAGE ON *.* TO 'ev_test'@'localhost'
GRANT ALL PRIVILEGES ON `events_test`.* TO 'ev_test'@'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, TRIGGER ON `events_test2`.* TO 'ev_test'@'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, TRIGGER, BACKUP, RESTORE ON `events_test2`.* TO 'ev_test'@'localhost'
"Here comes an error:";
SHOW EVENTS;
ERROR 42000: Access denied for user 'ev_test'@'localhost' to database 'events_test2'
=== modified file 'mysql-test/r/grant.result'
--- a/mysql-test/r/grant.result 2009-03-27 22:06:26 +0000
+++ b/mysql-test/r/grant.result 2009-06-05 15:49:36 +0000
@@ -13,8 +13,8 @@ GRANT USAGE ON *.* TO 'mysqltest_1'@'loc
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant delete on mysqltest.* to mysqltest_1@localhost;
select * from mysql.user where user="mysqltest_1";
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
@@ -44,15 +44,15 @@ delete from mysql.user where user='mysql
flush privileges;
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10;
select * from mysql.user where user="mysqltest_1";
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 10 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 10 0 0 0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30;
select * from mysql.user where user="mysqltest_1";
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 10 20 30 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 10 20 30 0
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
@@ -87,7 +87,7 @@ revoke LOCK TABLES, ALTER on mysqltest.*
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
-GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
+GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, BACKUP, RESTORE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
flush privileges;
@@ -486,6 +486,8 @@ Trigger Tables To use triggers
Create tablespace Server Admin To create/alter/drop tablespaces
Update Tables To update existing rows
Usage Server Admin No privileges - allow connect only
+Backup Server Admin To execute BACKUP commands.
+Restore Server Admin To execute RESTORE commands.
create database mysqltest;
create table mysqltest.t1 (a int,b int,c int);
grant all on mysqltest.t1 to mysqltest_1@localhost;
@@ -614,7 +616,7 @@ flush privileges;
use test;
set @user123="non-existent";
select * from mysql.db where user=@user123;
-Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
+Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Backup_priv Restore_priv
set names koi8r;
create database ┬─;
grant select on ┬─.* to root@localhost;
=== modified file 'mysql-test/r/lowercase_table_grant.result'
--- a/mysql-test/r/lowercase_table_grant.result 2006-02-01 10:28:45 +0000
+++ b/mysql-test/r/lowercase_table_grant.result 2009-06-05 15:49:36 +0000
@@ -6,8 +6,8 @@ Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
select * from db where user = 'mysqltest_1';
-Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
-localhost mysqltest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y
+Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Backup_priv Restore_priv
+localhost mysqltest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y Y Y
update db set db = 'MYSQLtest' where db = 'mysqltest' and user = 'mysqltest_1' and host = 'localhost';
flush privileges;
show grants for mysqltest_1@localhost;
@@ -15,8 +15,8 @@ Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
select * from db where user = 'mysqltest_1';
-Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
-localhost MYSQLtest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y
+Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Backup_priv Restore_priv
+localhost MYSQLtest mysqltest_1 Y Y Y Y Y Y N Y Y Y Y Y Y Y Y Y Y Y Y Y Y
delete from db where db = 'MYSQLtest' and user = 'mysqltest_1' and host = 'localhost';
flush privileges;
drop user mysqltest_1@localhost;
=== modified file 'mysql-test/r/ps.result'
--- a/mysql-test/r/ps.result 2009-03-27 22:06:26 +0000
+++ b/mysql-test/r/ps.result 2009-06-05 15:49:36 +0000
@@ -1194,13 +1194,13 @@ SET @aux= "SELECT COUNT(*)
prepare my_stmt from @aux;
execute my_stmt;
COUNT(*)
-40
+42
execute my_stmt;
COUNT(*)
-40
+42
execute my_stmt;
COUNT(*)
-40
+42
deallocate prepare my_stmt;
drop procedure if exists p1|
drop table if exists t1|
=== modified file 'mysql-test/r/system_mysql_db.result'
--- a/mysql-test/r/system_mysql_db.result 2009-03-11 21:22:33 +0000
+++ b/mysql-test/r/system_mysql_db.result 2009-06-05 15:49:36 +0000
@@ -50,6 +50,8 @@ db CREATE TABLE `db` (
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
+ `Backup_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
+ `Restore_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
@@ -113,6 +115,8 @@ user CREATE TABLE `user` (
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
+ `Backup_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
+ `Restore_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
=== modified file 'mysql-test/suite/backup/r/backup_db_grants.result'
--- a/mysql-test/suite/backup/r/backup_db_grants.result 2009-03-18 21:09:40 +0000
+++ b/mysql-test/suite/backup/r/backup_db_grants.result 2009-06-05 15:49:36 +0000
@@ -72,6 +72,7 @@ backup_id
Warnings:
# 1745 The grant 'ALTER ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'ALTER ROUTINE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
+# 1745 The grant 'BACKUP ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'CREATE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'CREATE ROUTINE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'CREATE TEMPORARY TABLES ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
@@ -84,6 +85,7 @@ Warnings:
# 1745 The grant 'INSERT ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'LOCK TABLES ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'REFERENCES ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
+# 1745 The grant 'RESTORE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'SELECT ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'SHOW VIEW ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'TRIGGER ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
@@ -126,6 +128,7 @@ Warnings:
# 1745 The grant 'SELECT(b) ON bup_db_grants.s1' for the user 'bup_user2'@'%' was skipped because the user does not exist.
# 1745 The grant 'ALTER ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'ALTER ROUTINE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
+# 1745 The grant 'BACKUP ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'CREATE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'CREATE ROUTINE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'CREATE TEMPORARY TABLES ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
@@ -138,6 +141,7 @@ Warnings:
# 1745 The grant 'INSERT ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'LOCK TABLES ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'REFERENCES ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
+# 1745 The grant 'RESTORE ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'SELECT ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'SHOW VIEW ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
# 1745 The grant 'TRIGGER ON bup_db_grants.*' for the user 'no_user'@'%' was skipped because the user does not exist.
=== modified file 'mysql-test/suite/backup/r/backup_security.result'
--- a/mysql-test/suite/backup/r/backup_security.result 2008-12-10 23:50:10 +0000
+++ b/mysql-test/suite/backup/r/backup_security.result 2009-06-05 15:49:36 +0000
@@ -14,8 +14,11 @@ CREATE USER bup_with_rights;
default: Grant user rights to run backup. Revoke SUPER from one user.
GRANT ALL ON *.* TO 'bup_no_rights'@'%';
GRANT ALL ON *.* TO 'bup_with_rights'@'%';
-REVOKE SUPER ON *.* FROM 'bup_no_rights'@'%';
-GRANT SUPER ON *.* TO 'bup_with_rights'@'%';
+default: Explicitly remove rights from both users.
+REVOKE BACKUP, RESTORE, SUPER ON *.* FROM 'bup_with_rights'@'%';
+REVOKE BACKUP, RESTORE, SUPER ON *.* FROM 'bup_no_rights'@'%';
+default: Grant only BACKUP and RESTORE to one user.
+GRANT BACKUP,RESTORE ON *.* TO 'bup_with_rights'@'%';
FLUSH PRIVILEGES;
default: Do backup of database with default test user for later tests.
BACKUP DATABASE backup_test to 'backup_test_orig.bak';
@@ -24,16 +27,21 @@ backup_id
default: Connect as user with no rights and attempt backup and restore.
no_rights: Attempting backup. Should fail with error 1227
BACKUP DATABASE backup_test to 'bup_no_rights.bak';
-ERROR 42000: Access denied; you need the SUPER privilege for this operation
+ERROR 42000: Access denied; you need the BACKUP privilege for this operation
SHOW ERRORS;
Level Code Message
-Error 1227 Access denied; you need the SUPER privilege for this operation
+Error 1227 Access denied; you need the BACKUP privilege for this operation
+default: Connect as user with rights to create file for testing restore.
+BACKUP DATABASE backup_test to 'bup_no_rights.bak';
+backup_id
+#
+no_rights: Connect as user with no rights and attempt backup and restore.
no_rights: Attempting restore. Should fail with error 1227
RESTORE FROM 'bup_no_rights.bak';
-ERROR 42000: Access denied; you need the SUPER privilege for this operation
+ERROR 42000: Access denied; you need the RESTORE privilege for this operation
SHOW ERRORS;
Level Code Message
-Error 1227 Access denied; you need the SUPER privilege for this operation
+Error 1227 Access denied; you need the RESTORE privilege for this operation
SELECT * FROM backup_test.t1;
a
01 Test #1 - super privilege
=== modified file 'mysql-test/suite/backup/t/backup_security.test'
--- a/mysql-test/suite/backup/t/backup_security.test 2009-02-24 20:57:21 +0000
+++ b/mysql-test/suite/backup/t/backup_security.test 2009-06-05 15:49:36 +0000
@@ -40,8 +40,11 @@ CREATE USER bup_with_rights;
--echo default: Grant user rights to run backup. Revoke SUPER from one user.
GRANT ALL ON *.* TO 'bup_no_rights'@'%';
GRANT ALL ON *.* TO 'bup_with_rights'@'%';
-REVOKE SUPER ON *.* FROM 'bup_no_rights'@'%';
-GRANT SUPER ON *.* TO 'bup_with_rights'@'%';
+--echo default: Explicitly remove rights from both users.
+REVOKE BACKUP, RESTORE, SUPER ON *.* FROM 'bup_with_rights'@'%';
+REVOKE BACKUP, RESTORE, SUPER ON *.* FROM 'bup_no_rights'@'%';
+--echo default: Grant only BACKUP and RESTORE to one user.
+GRANT BACKUP,RESTORE ON *.* TO 'bup_with_rights'@'%';
FLUSH PRIVILEGES;
--echo default: Do backup of database with default test user for later tests.
@@ -58,6 +61,14 @@ connect (no_rights,localhost,bup_no_righ
BACKUP DATABASE backup_test to 'bup_no_rights.bak';
SHOW ERRORS;
+--echo default: Connect as user with rights to create file for testing restore.
+connection default;
+--replace_column 1 #
+BACKUP DATABASE backup_test to 'bup_no_rights.bak';
+
+--echo no_rights: Connect as user with no rights and attempt backup and restore.
+connection no_rights;
+
--echo no_rights: Attempting restore. Should fail with error 1227
--replace_column 1 #
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
@@ -98,6 +109,7 @@ DROP USER bup_with_rights;
DROP DATABASE backup_test;
let $MYSQLD_BACKUPDIR= `select @@backupdir`;
+remove_file $MYSQLD_BACKUPDIR/bup_no_rights.bak;
remove_file $MYSQLD_BACKUPDIR/bup_with_rights.bak;
remove_file $MYSQLD_BACKUPDIR/backup_test_orig.bak;
=== modified file 'mysql-test/suite/funcs_1/r/is_column_privileges.result'
--- a/mysql-test/suite/funcs_1/r/is_column_privileges.result 2009-02-16 14:47:53 +0000
+++ b/mysql-test/suite/funcs_1/r/is_column_privileges.result 2009-06-05 15:49:36 +0000
@@ -135,6 +135,7 @@ ORDER BY grantee,table_schema,privilege_
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser3'@'localhost' def db_datadict ALTER NO
'testuser3'@'localhost' def db_datadict ALTER ROUTINE NO
+'testuser3'@'localhost' def db_datadict BACKUP NO
'testuser3'@'localhost' def db_datadict CREATE NO
'testuser3'@'localhost' def db_datadict CREATE ROUTINE NO
'testuser3'@'localhost' def db_datadict CREATE TEMPORARY TABLES NO
@@ -147,6 +148,7 @@ GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVI
'testuser3'@'localhost' def db_datadict INSERT NO
'testuser3'@'localhost' def db_datadict LOCK TABLES NO
'testuser3'@'localhost' def db_datadict REFERENCES NO
+'testuser3'@'localhost' def db_datadict RESTORE NO
'testuser3'@'localhost' def db_datadict SELECT NO
'testuser3'@'localhost' def db_datadict SHOW VIEW NO
'testuser3'@'localhost' def db_datadict TRIGGER NO
=== modified file 'mysql-test/suite/funcs_1/r/is_schema_privileges.result'
--- a/mysql-test/suite/funcs_1/r/is_schema_privileges.result 2009-02-16 14:47:53 +0000
+++ b/mysql-test/suite/funcs_1/r/is_schema_privileges.result 2009-06-05 15:49:36 +0000
@@ -68,6 +68,8 @@ GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVI
''@'%' def test CREATE ROUTINE
''@'%' def test EVENT
''@'%' def test TRIGGER
+''@'%' def test BACKUP
+''@'%' def test RESTORE
''@'%' def test\_% SELECT
''@'%' def test\_% INSERT
''@'%' def test\_% UPDATE
@@ -84,6 +86,8 @@ GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVI
''@'%' def test\_% CREATE ROUTINE
''@'%' def test\_% EVENT
''@'%' def test\_% TRIGGER
+''@'%' def test\_% BACKUP
+''@'%' def test\_% RESTORE
###############################################################################
# Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility
###############################################################################
=== modified file 'mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result'
--- a/mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result 2009-02-16 14:47:53 +0000
+++ b/mysql-test/suite/funcs_1/r/is_schema_privileges_is_mysql_test.result 2009-06-05 15:49:36 +0000
@@ -11,6 +11,7 @@ WHERE table_schema IN ('information_sche
ORDER BY grantee, table_schema, privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
''@'%' def test ALTER NO
+''@'%' def test BACKUP NO
''@'%' def test CREATE NO
''@'%' def test CREATE ROUTINE NO
''@'%' def test CREATE TEMPORARY TABLES NO
@@ -22,6 +23,7 @@ GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVI
''@'%' def test INSERT NO
''@'%' def test LOCK TABLES NO
''@'%' def test REFERENCES NO
+''@'%' def test RESTORE NO
''@'%' def test SELECT NO
''@'%' def test SHOW VIEW NO
''@'%' def test TRIGGER NO
=== modified file 'mysql-test/suite/funcs_1/r/is_user_privileges.result'
--- a/mysql-test/suite/funcs_1/r/is_user_privileges.result 2009-02-17 11:16:05 +0000
+++ b/mysql-test/suite/funcs_1/r/is_user_privileges.result 2009-06-05 15:49:36 +0000
@@ -76,10 +76,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
#
# Add GRANT OPTION db_datadict.* to testuser1;
GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
@@ -93,10 +93,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.user_privileges
WHERE grantee LIKE '''testuser%'''
@@ -105,10 +105,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser1'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
SHOW GRANTS;
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
@@ -130,10 +130,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
#
# Here <SELECT YES> is shown correctly for testuser1;
@@ -147,10 +147,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 Y N N N N N N N N N Y N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 Y N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
# Switch to connection testuser1
SELECT * FROM information_schema.user_privileges
WHERE grantee LIKE '''testuser%'''
@@ -159,10 +159,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser1'@'localhost' def SELECT YES
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 Y N N N N N N N N N Y N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 Y N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
SHOW GRANTS;
Grants for testuser1@localhost
GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION
@@ -207,10 +207,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
# Switch to connection testuser1
SELECT * FROM information_schema.user_privileges
WHERE grantee LIKE '''testuser%'''
@@ -253,10 +253,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
# Switch to connection testuser1
SELECT * FROM information_schema.user_privileges
WHERE grantee LIKE '''testuser%'''
@@ -265,10 +265,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser1'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
SHOW GRANTS;
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
@@ -284,10 +284,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser1'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
SHOW GRANTS;
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
@@ -309,10 +309,10 @@ GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_
'testuser3'@'localhost' def USAGE NO
SELECT * FROM mysql.user
WHERE user LIKE 'testuser%' ORDER BY host, user;
-Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
-localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
-localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Backup_priv Restore_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
+localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
+localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
# Switch to connection testuser1
SELECT * FROM information_schema.user_privileges
WHERE grantee LIKE '''testuser%'''
=== modified file 'scripts/mysql_system_tables.sql'
--- a/scripts/mysql_system_tables.sql 2009-06-02 18:19:27 +0000
+++ b/scripts/mysql_system_tables.sql 2009-06-05 15:49:36 +0000
@@ -5,7 +5,7 @@
set sql_mode='';
set storage_engine=myisam;
-CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges';
+CREATE TABLE IF NOT EXISTS db ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Backup_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Restore_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db,User), KEY User (User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Database privileges';
-- Remember for later if db table already existed
set @had_db_table= @@warning_count != 0;
@@ -13,7 +13,7 @@ set @had_db_table= @@warning_count != 0;
CREATE TABLE IF NOT EXISTS host ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,Db) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Host privileges; Merged with database privileges';
-CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges';
+CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Password char(41) character set latin1 collate latin1_bin DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Backup_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Restore_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges';
-- Remember for later if user table already existed
set @had_user_table= @@warning_count != 0;
=== modified file 'scripts/mysql_system_tables_data.sql'
--- a/scripts/mysql_system_tables_data.sql 2008-10-03 16:41:23 +0000
+++ b/scripts/mysql_system_tables_data.sql 2009-06-05 15:49:36 +0000
@@ -11,8 +11,8 @@ set @current_hostname= @@hostname;
-- Fill "db" table with default grants for anyone to
-- access database 'test' and 'test_%' if "db" table didn't exist
CREATE TEMPORARY TABLE tmp_db LIKE db;
-INSERT INTO tmp_db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y');
-INSERT INTO tmp_db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y');
+INSERT INTO tmp_db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y','Y');
+INSERT INTO tmp_db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y','Y');
INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0;
DROP TABLE tmp_db;
@@ -21,9 +21,9 @@ DROP TABLE tmp_db;
-- from local machine if "users" table didn't exist before
CREATE TEMPORARY TABLE tmp_user LIKE user;
set @current_hostname= @@hostname;
-INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
-REPLACE INTO tmp_user SELECT @current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0 FROM dual WHERE LOWER( @current_hostname) != 'localhost';
-REPLACE INTO tmp_user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
+INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
+REPLACE INTO tmp_user SELECT @current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0 FROM dual WHERE LOWER( @current_hostname) != 'localhost';
+REPLACE INTO tmp_user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
INSERT INTO tmp_user (host,user) VALUES ('localhost','');
INSERT INTO tmp_user (host,user) SELECT @current_hostname,'' FROM dual WHERE LOWER(@current_hostname ) != 'localhost';
INSERT INTO user SELECT * FROM tmp_user WHERE @had_user_table=0;
=== modified file 'scripts/mysql_system_tables_fix.sql'
--- a/scripts/mysql_system_tables_fix.sql 2009-05-21 13:17:37 +0000
+++ b/scripts/mysql_system_tables_fix.sql 2009-06-05 15:49:36 +0000
@@ -600,6 +600,44 @@ ALTER TABLE user MODIFY Create_tablespac
UPDATE user SET Create_tablespace_priv = Super_priv WHERE @hadCreateTablespacePriv = 0;
+#
+# user.Backup_priv and user.Restore.priv
+#
+SET @hadBackupPriv := 0;
+SELECT @hadBackupPriv :=1 FROM db WHERE Backup_priv LIKE '%';
+
+ALTER TABLE db ADD Backup_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Trigger_priv;
+ALTER TABLE db MODIFY Backup_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Trigger_priv;
+
+UPDATE user SET Backup_priv = Super_priv WHERE @hadBackupPriv = 0;
+
+SET @hadRestorePriv := 0;
+SELECT @hadRestorePriv :=1 FROM user WHERE Restore_priv LIKE '%';
+
+ALTER TABLE db ADD Restore_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Backup_priv;
+ALTER TABLE db MODIFY Restore_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Backup_priv;
+
+UPDATE db SET Restore_priv = Super_priv WHERE @hadRestorePriv = 0;
+
+#
+# user.Backup_priv and user.Restore.priv
+#
+SET @hadBackupPriv := 0;
+SELECT @hadBackupPriv :=1 FROM user WHERE Backup_priv LIKE '%';
+
+ALTER TABLE user ADD Backup_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_tablespace_priv;
+ALTER TABLE user MODIFY Backup_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_tablespace_priv;
+
+UPDATE user SET Backup_priv = Super_priv WHERE @hadBackupPriv = 0;
+
+SET @hadRestorePriv := 0;
+SELECT @hadRestorePriv :=1 FROM user WHERE Restore_priv LIKE '%';
+
+ALTER TABLE user ADD Restore_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Backup_priv;
+ALTER TABLE user MODIFY Restore_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Backup_priv;
+
+UPDATE user SET Restore_priv = Super_priv WHERE @hadRestorePriv = 0;
+
# Activate the new, possible modified privilege tables
# This should not be needed, but gives us some extra testing that the above
# changes was correct
=== modified file 'sql/backup/backup_kernel.h'
--- a/sql/backup/backup_kernel.h 2009-03-16 14:38:05 +0000
+++ b/sql/backup/backup_kernel.h 2009-06-05 15:49:36 +0000
@@ -167,7 +167,8 @@ private:
int prepare_path(::String *backupdir,
LEX_STRING orig_loc);
- int prepare(::String *backupdir, LEX_STRING location);
+ int prepare(::String *backupdir, LEX_STRING location);
+ int check_privileges(enum_type type);
void disable_fkey_constraints();
int restore_triggers_and_events();
=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc 2009-05-25 07:11:29 +0000
+++ b/sql/backup/kernel.cc 2009-06-05 15:49:36 +0000
@@ -535,6 +535,61 @@ int Backup_restore_ctx::prepare_path(::S
}
/**
+ Perform privilege checks for backup and restore operations.
+
+ This method checks to see if the current user has the correct privileges.
+ For backup, the user must have the BACKUP privilege for all databases.
+ For restore, the yuser must have the RESTORE privilege for all databases.
+
+ @param[in] type of operation.
+ @returns
+ @retval false if privilege check passed.
+ @retval error if privilege check fails.
+*/
+int Backup_restore_ctx::check_privileges(enum_type type)
+{
+ int ret= 0;
+
+ if (type == BACKUP)
+ {
+ List_iterator< ::LEX_STRING > it(m_thd->lex->db_list);
+ ::LEX_STRING *s;
+
+ ret= 0;
+ while ((s= it++) && !ret)
+ {
+ backup::String db_name(*s);
+ ret= check_access(m_thd, BACKUP_ACL,
+ db_name.c_ptr_safe(), 0, 1, 1, 0);
+ }
+ }
+ else
+ {
+ for (uint i= 0; i < m_catalog->db_count(); i++)
+ {
+ backup::Image_info::Db *db= m_catalog->get_db(i);
+ backup::String str= db->name();
+ ret= check_access(m_thd, RESTORE_ACL,
+ str.c_ptr_safe(), 0, 1, 1, 0);
+ if (ret || is_killed())
+ break;
+ }
+ }
+ if (ret)
+ {
+ /*
+ We generate the error here since check_access() has error
+ generation turned off.
+ */
+ my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0),
+ (type == BACKUP) ? "BACKUP" : "RESTORE");
+ return fatal_error(log_error(ER_SPECIFIC_ACCESS_DENIED_ERROR,
+ (type == BACKUP) ? "BACKUP" : "RESTORE"));
+ }
+ return false;
+}
+
+/**
Do preparations common to backup and restore operations.
It is checked if another operation is in progress and if yes then
@@ -545,23 +600,12 @@ int Backup_restore_ctx::prepare_path(::S
@returns 0 on success, error code otherwise.
*/
-int Backup_restore_ctx::prepare(::String *backupdir, LEX_STRING location)
+int Backup_restore_ctx::prepare(::String *backupdir, LEX_STRING location)
{
if (m_error)
return m_error;
- int ret= 0;
-
- /*
- Check access for SUPER rights. If user does not have SUPER, fail with error.
-
- In case of error, we write only to backup logs, because check_global_access()
- pushes the same error on the error stack.
- */
- DEBUG_SYNC(m_thd, "before_backup_privileges");
- ret= check_global_access(m_thd, SUPER_ACL);
- if (ret || is_killed())
- return fatal_error(log_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, "SUPER"));
+ uint ret= 0;
/*
Check if another BACKUP/RESTORE is running and if not, register
@@ -681,6 +725,13 @@ Backup_restore_ctx::prepare_for_backup(S
time_t when= my_time(0);
report_start(when);
+ /*
+ Check privileges before backup.
+ */
+ DEBUG_SYNC(m_thd, "before_backup_privileges");
+ if (ret= check_privileges(BACKUP))
+ return NULL;
+
/*
Do preparations common to backup and restore operations. After call
to prepare() all meta-data changes are blocked.
@@ -1406,6 +1457,7 @@ int Backup_restore_ctx::do_restore(bool
using namespace backup;
+ uint ret= 0;
int err;
Input_stream &s= *static_cast<Input_stream*>(m_stream);
Restore_info &info= *static_cast<Restore_info*>(m_catalog);
@@ -1419,6 +1471,16 @@ int Backup_restore_ctx::do_restore(bool
DBUG_PRINT("restore", ("Restoring meta-data"));
+ /*
+ Check access for operation. If insufficient rights, fail with error.
+
+ In case of error, we write only to backup logs, because check_access()
+ pushes the same error on the error stack.
+ */
+ DEBUG_SYNC(m_thd, "before_restore_privileges");
+ if (ret= check_privileges(RESTORE))
+ DBUG_RETURN(ret);
+
// unless RESTORE... OVERWRITE: return error if database already exists
if (!overwrite)
{
@@ -2273,7 +2335,14 @@ int bcat_create_item(st_bstream_image_he
// Mark that data is being changed.
info->m_data_changed= TRUE;
- if (sobj->create(thd))
+
+ uint ret= 0;
+ ulong saved_master_access= thd->security_ctx->master_access;
+ if (item->type == BSTREAM_IT_PRIVILEGE)
+ thd->security_ctx->master_access|= GRANT_ACL;
+ ret= sobj->create(thd);
+ thd->security_ctx->master_access= saved_master_access;
+ if (ret)
{
log.report_error(create_err, desc);
return BSTREAM_ERROR;
=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc 2009-05-22 13:46:54 +0000
+++ b/sql/si_objects.cc 2009-06-05 15:49:36 +0000
@@ -207,8 +207,20 @@ run_service_interface_sql(THD *thd, Ed_c
session_context.save_si_ctx(thd);
session_context.reset_si_ctx(thd);
+ /*
+ Temporarily give user SELECT privilege so operations on
+ mysql and information_schema can succeed.
+ */
+ ulong saved_master_access= thd->security_ctx->master_access;
+ thd->security_ctx->master_access|= SELECT_ACL;
+
bool rc= ed_connection->execute_direct(*query);
+ /*
+ Remove elevated privilege.
+ */
+ thd->security_ctx->master_access= saved_master_access;
+
session_context.restore_si_ctx(thd);
DBUG_RETURN(rc);
=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc 2009-05-07 20:48:24 +0000
+++ b/sql/sql_acl.cc 2009-06-05 15:49:36 +0000
@@ -144,6 +144,16 @@ TABLE_FIELD_W_TYPE mysql_db_table_fields
{ C_STRING_WITH_LEN("Trigger_priv") },
{ C_STRING_WITH_LEN("enum('N','Y')") },
{ C_STRING_WITH_LEN("utf8") }
+ },
+ {
+ { C_STRING_WITH_LEN("Backup_priv") },
+ { C_STRING_WITH_LEN("enum('N','Y')") },
+ { C_STRING_WITH_LEN("utf8") }
+ },
+ {
+ { C_STRING_WITH_LEN("Restore_priv") },
+ { C_STRING_WITH_LEN("enum('N','Y')") },
+ { C_STRING_WITH_LEN("utf8") }
}
};
@@ -4595,13 +4605,13 @@ static const char *command_array[]=
"ALTER", "SHOW DATABASES", "SUPER", "CREATE TEMPORARY TABLES",
"LOCK TABLES", "EXECUTE", "REPLICATION SLAVE", "REPLICATION CLIENT",
"CREATE VIEW", "SHOW VIEW", "CREATE ROUTINE", "ALTER ROUTINE",
- "CREATE USER", "EVENT", "TRIGGER", "CREATE TABLESPACE"
+ "CREATE USER", "EVENT", "TRIGGER", "CREATE TABLESPACE", "BACKUP", "RESTORE"
};
static uint command_lengths[]=
{
6, 6, 6, 6, 6, 4, 6, 8, 7, 4, 5, 10, 5, 5, 14, 5, 23, 11, 7, 17, 18, 11, 9,
- 14, 13, 11, 5, 7, 17
+ 14, 13, 11, 5, 7, 17, 6, 7
};
@@ -4620,7 +4630,7 @@ static int show_routine_grants(THD *thd,
bool mysql_show_grants(THD *thd,LEX_USER *lex_user)
{
ulong want_access;
- uint counter,index;
+ ulong counter,index;
int error = 0;
ACL_USER *acl_user;
ACL_DB *acl_db;
=== modified file 'sql/sql_acl.h'
--- a/sql/sql_acl.h 2009-04-02 08:50:24 +0000
+++ b/sql/sql_acl.h 2009-06-05 15:49:36 +0000
@@ -52,13 +52,16 @@
4. acl_init() or whatever - to define behaviour for old privilege tables
5. sql_yacc.yy - for GRANT/REVOKE to work
*/
-#define EXTRA_ACL (1L << 29)
-#define NO_ACCESS (1L << 30)
+#define BACKUP_ACL (1L << 29)
+#define RESTORE_ACL (1L << 30)
+#define NO_ACCESS (1L << 31)
+
#define DB_ACLS \
(UPDATE_ACL | SELECT_ACL | INSERT_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \
GRANT_ACL | REFERENCES_ACL | INDEX_ACL | ALTER_ACL | CREATE_TMP_ACL | \
LOCK_TABLES_ACL | EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | \
- CREATE_PROC_ACL | ALTER_PROC_ACL | EVENT_ACL | TRIGGER_ACL)
+ CREATE_PROC_ACL | ALTER_PROC_ACL | EVENT_ACL | TRIGGER_ACL | \
+ BACKUP_ACL | RESTORE_ACL )
#define TABLE_ACLS \
(SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \
@@ -81,7 +84,7 @@
CREATE_TMP_ACL | LOCK_TABLES_ACL | REPL_SLAVE_ACL | REPL_CLIENT_ACL | \
EXECUTE_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL | CREATE_PROC_ACL | \
ALTER_PROC_ACL | CREATE_USER_ACL | EVENT_ACL | TRIGGER_ACL | \
- CREATE_TABLESPACE_ACL)
+ CREATE_TABLESPACE_ACL | BACKUP_ACL | RESTORE_ACL )
#define DEFAULT_CREATE_PROC_ACLS \
(ALTER_PROC_ACL | EXECUTE_ACL)
@@ -105,19 +108,22 @@
CREATE_PROC_ACL | ALTER_PROC_ACL )
#define DB_CHUNK4 (EXECUTE_ACL)
#define DB_CHUNK5 (EVENT_ACL | TRIGGER_ACL)
+#define DB_CHUNK6 (BACKUP_ACL | RESTORE_ACL)
#define fix_rights_for_db(A) (((A) & DB_CHUNK0) | \
(((A) << 4) & DB_CHUNK1) | \
(((A) << 6) & DB_CHUNK2) | \
(((A) << 9) & DB_CHUNK3) | \
(((A) << 2) & DB_CHUNK4))| \
- (((A) << 9) & DB_CHUNK5)
+ (((A) << 9) & DB_CHUNK5) | \
+ (((A) << 10) & DB_CHUNK6)
#define get_rights_for_db(A) (((A) & DB_CHUNK0) | \
(((A) & DB_CHUNK1) >> 4) | \
(((A) & DB_CHUNK2) >> 6) | \
(((A) & DB_CHUNK3) >> 9) | \
(((A) & DB_CHUNK4) >> 2))| \
- (((A) & DB_CHUNK5) >> 9)
+ (((A) & DB_CHUNK5) >> 9) | \
+ (((A) & DB_CHUNK6) >> 10)
#define TBL_CHUNK0 DB_CHUNK0
#define TBL_CHUNK1 DB_CHUNK1
#define TBL_CHUNK2 (CREATE_VIEW_ACL | SHOW_VIEW_ACL)
@@ -163,6 +169,8 @@ enum mysql_db_table_field
MYSQL_DB_FIELD_EXECUTE_PRIV,
MYSQL_DB_FIELD_EVENT_PRIV,
MYSQL_DB_FIELD_TRIGGER_PRIV,
+ MYSQL_DB_FIELD_BACKUP_PRIV,
+ MYSQL_DB_FIELD_RESTORE_PRIV,
MYSQL_DB_FIELD_COUNT
};
=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc 2009-05-15 08:00:35 +0000
+++ b/sql/sql_show.cc 2009-06-05 15:49:36 +0000
@@ -317,6 +317,8 @@ static struct show_privileges_st sys_pri
{"Create tablespace", "Server Admin", "To create/alter/drop tablespaces"},
{"Update", "Tables", "To update existing rows"},
{"Usage","Server Admin","No privileges - allow connect only"},
+ {"Backup", "Server Admin", "To execute BACKUP commands."},
+ {"Restore", "Server Admin", "To execute RESTORE commands."},
{NullS, NullS, NullS}
};
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-05-11 17:58:07 +0000
+++ b/sql/sql_yacc.yy 2009-06-05 15:49:36 +0000
@@ -13348,6 +13348,8 @@ object_privilege:
| EVENT_SYM { Lex->grant |= EVENT_ACL;}
| TRIGGER_SYM { Lex->grant |= TRIGGER_ACL; }
| CREATE TABLESPACE { Lex->grant |= CREATE_TABLESPACE_ACL; }
+ | BACKUP_SYM { Lex->grant |= BACKUP_ACL; }
+ | RESTORE_SYM { Lex->grant |= RESTORE_ACL; }
;
opt_and:
