Bug #39580 BACKUP/RESTORE should not use SUPER
Submitted: 22 Sep 2008 9:15 Modified: 13 Jul 2009 18:10
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S4 (Feature request)
Version:6.0 OS:Any
Assigned to: Chuck Bell
Tags: Security
Triage: Triaged: D2 (Serious)

[22 Sep 2008 9:15] Domas Mituzas
Description:
Backup needs to be decoupled because of multiple reasons, like hitting the max_connections limit and actually getting access to the data, rather than just executing backup or restore

How to repeat:
BACKUP

Suggested fix:
Decouple privileges, check if SELECT privilege for databases exists, etc.
[2 Mar 2009 21:25] Chuck Bell
Preliminary design concept
--------------------------
Create two new privileges: (BACKUP, RESTORE).

BACKUP allows a user to execute the backup command IFF he has the appropriate permissions to read the objects in the database. This is very restrictive and is intended to ensure no one can make a backup of a database that they otherwise cannot read.

RESTORE allows a user to execute the restore command IFF he has permissions to create a database (and possibly other create related feats). This permission is more powerful and therefore separate from BACKUP to allow database administrators to restrict its use more so than BACKUP.

Question: What about the mysqlbackup utility? Shouldn't these be implemented there too?
[17 Mar 2009 16:01] Chuck Bell
A problem has been found that requires restructuring of the server code in order to complete this work.

Adding even a single new privilege to the ACL's in sql_acl.h is not possible. This is because the privileges are defined using shifts:

#define SELECT_ACL	(1L << 0)
#define INSERT_ACL	(1L << 1)
#define UPDATE_ACL	(1L << 2)
#define DELETE_ACL	(1L << 3)
...
#define EXTRA_ACL	(1L << 29)
#define NO_ACCESS	(1L << 30)

<out of space>

Furthermore, loops that traverse the user table are using this shift thereby complicating the issue:

  for (tmp_field= table->field+3, priv = SELECT_ACL;
       *tmp_field && (*tmp_field)->real_type() == MYSQL_TYPE_ENUM &&
	 ((Field_enum*) (*tmp_field))->typelib->count == 2 ;
       tmp_field++, priv <<= 1)
  {
    if (priv & rights)				 // set requested privileges
      (*tmp_field)->store(&what, 1, &my_charset_latin1);
  }

What needs to be done is someone needs to refactor the sql_acl.h/.cc code as well as the code that iterates over the user table and the ACLs in general to allow for more privileges to be defined. This is a seemingly complicated task as there are several places in the code where this can go horribly wrong if done improperly. Recommend open a bug report to allow more privileges to be defined.
[18 Mar 2009 15:12] Chuck Bell
This bug requires patch from BUG#43730 before it can be solved.
[26 May 2009 15:12] Chuck Bell
It may be necessary to add the following special permissions for the new ACLs:

BACKUP - SELECT on mysql.procs_priv (and other priv tables)
RESTORE - SELECT on mysql database
[5 Jun 2009 9:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75640

2822 Chuck Bell	2009-06-04
      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_columns_mysql.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
[5 Jun 2009 14:52] Chuck Bell
It has been decided we will use the following new privileges for backup:

BACKUP - a database-level privilege that enables a user to backup data
RESTORE - a database-level privilege that enables a user to restore data

These privileges are implemented so that the user is temporarily given access to the objects internally. That is, the user's privileges are temporarily elevated for the duration of the operation. Note that at no time is the user capable of using those rights -- they are only valid for the backup or restore operation and only in the context of the thread executing inside the server. 

These privileges can be used by a DBA to assign tasks to other users. For example, a set of users could be given the BACKUP privilege to backup data (but not restore). This could be useful for automation or scripts that require a user account. Similarly, the RESTORE privilege could be restricted to fewer individuals.
[5 Jun 2009 15:32] Chuck Bell
The following is a FAQ concerning the decision to use only BACKUP and RESTORE as privileges for backup and restore. The information in this list was taken from an email conversation between the developer and an architecture advisor.

Q: What will happen to WL#4090 "Online backup: User rights to perform backup and restore operations"? 
A: The worklog will be annotated then made obsolete by BUG#39580.

Q: What will happen to WL#4768 "Test backup security"? 
A: It will be revised to include the new privileges.

Q: Are BACKUP and RESTORE what the manual calls "database privileges"? 
A: Yes, at the database-level. 

Q: Does "GRANT ALL ON mydb.* TO user1;" include BACKUP, RESTORE? 
A: Yes.

Q: Is "GRANT BACKUP ON mydb.table1 TO user1;" legal? 
A: No. Error 1144 "Illegal GRANT/REVOKE..." 

Q: Is "GRANT BACKUP, RESTORE ON `my%b`.* TO user1;" legal? 
A: Yes

Q: Are there new columns in mysql.db? If so, what? 
A: Yes, Backup_priv and Restore_priv with a default of 'N'.

Q: Are there new columns in mysql.host? If so, what? 
A: No. 

Q: Who has BACKUP and RESTORE privileges initially? 
A: Only root.

Q: Will a GRANT BACKUP, RESTORE occur if I run mysql_install_db? 
A: No.

Q: Will a GRANT BACKUP, RESTORE occur if I run mysql_upgrade? 
A: No. 

Q: Does GRANT ALL PRIVILEGES ON test.* cause grant of BACKUP and RESTORE? 
A: Yes. 

Q: What is going to happen to --secure-file-priv and --secure-backup-file-priv? 
A: They are still enforced. 

Q: Assuming I do not have the SHOW DATABASES privilege, and I have only the RESTORE privilege on database mydb, is "SHOW DATABASES;" going to show me database mydb? 
A: No. 

Q: I've seen the comments on Bug#43730 "There is no way to add new privileges to sql_acl code", and seen that you proposed to use 29 + 31. Is that still correct? 
A: No, Sergei G. has helped me solve that problem.

Q: Can I grant multiple BACKUP or RESTORE privileges to a user for a list of databases?
A: Yes.
[5 Jun 2009 15:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75723

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
[11 Jun 2009 0:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76080

2822 Chuck Bell	2009-06-10
      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.
      added:
        mysql-test/suite/backup/include/backup_check_privileges.inc
      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/si_objects.h
        sql/sql_acl.cc
        sql/sql_acl.h
        sql/sql_show.cc
        sql/sql_yacc.yy
[18 Jun 2009 0:30] Chuck Bell
Patch withdrawn. Design cannot overcome vulnerabilities in backup image. Must consider alternative solutions.
[24 Jun 2009 15:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77055

2832 Chuck Bell	2009-06-24
      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.
      
      This patch does not infer any additional rights to the user during
      the operation. The user must still have sufficient rights to read
      all objects for backup and create all objects for restore.
      added:
        mysql-test/suite/backup/include/backup_check_privileges.inc
      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/sql_acl.cc
        sql/sql_acl.h
        sql/sql_show.cc
        sql/sql_yacc.yy
[24 Jun 2009 16:05] Chuck Bell
Decided at 22 June Backup Meeting:

We will replace SUPER with BACKUP and RESTORE for backup and restore operations.

BACKUP permits a user to execute the BACKUP DATABASE command.

RESTORE permits a user to execute the RESTORE command.

These are database-level privileges and may be granted as follows:

GRANT BACKUP, RESTORE ON db1.* TO 'joe'@'user';
GRANT BACKUP ON *.* TO 'joe'@'user';
GRANT RESTORE ON db2.* TO 'joe'@'user';
GRANT ALL ON db3.* TO 'joe'@'user';

In addition, the root user has BACKUP, RESTORE by default.

Note: There will be no elevation taking place and no object-level privilege checking. The user must have sufficient privileges to access all objects in the database along with BACKUP to perform a backup. Likewise, the user must have sufficient privileges to create and populate all objects in the backup image file along with RESTORE to perform a restore.

Note: It is still possible for a restore to fail in the middle if the user does not have sufficient privileges. For example, if the user does not have privileges to create procedures, the restore will fail when the procedures are being created.
[25 Jun 2009 23:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77264
[26 Jun 2009 14:11] Chuck Bell
Note: The user must have SELECT on the database(s) in the backup.
      This check is done before the BACKUP privilege check.
[26 Jun 2009 14:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77345
[30 Jun 2009 18:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77566
[1 Jul 2009 14:54] Rafal Somla
Good to push.
[1 Jul 2009 20:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77706
[1 Jul 2009 21:20] Chuck Bell
Chuck checked the box for Jorgen since his reply stated he approved of the patch but wanted answers to some questions -- which were sent.
[2 Jul 2009 11:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77758

2844 Rafal Somla	2009-07-02
      Bug#39580 - BACKUP/RESTORE should not use SUPER
      
      This is a follow-up fix of test results which have changed
      due to the new backup/restore privileges.
      
      This patch also contains an unrelated fix for backup_client
      test which produced non-deterministic results.
     @ mysql-test/r/information_schema.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/backup/r/backup_client.result
        Wrong order of events, if test is repeated.
        Fixed result.
     @ mysql-test/suite/backup/t/backup_client.test
        Wrong order of events, if test is repeated.
        Drop events in a certain order, so that they are re-used in the
        old order if test is repeated.
     @ mysql-test/suite/funcs_1/r/innodb_trig_03.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/innodb_trig_03e.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/is_columns_mysql.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/memory_trig_03.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/memory_trig_03e.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/myisam_trig_03.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/myisam_trig_03e.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/ndb_trig_03.result
        Update results because of new backup/restore privileges.
     @ mysql-test/suite/funcs_1/r/ndb_trig_03e.result
        Update results because of new backup/restore privileges.
[2 Jul 2009 13:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77775

2845 Chuck Bell	2009-07-02
      BUG#39580 : BACKUP/RESTORE should not use SUPER
      
      The backup_security test fails on non-debug compiled machines.
      The SHOW PROCEDURE CODE requires debug compile.
      This patch removes these statements.
     @ mysql-test/suite/backup/include/backup_check_privileges.inc
        Removed SHOW PROCEDURE CODE statements.
     @ mysql-test/suite/backup/r/backup_security.result
        Fixed result file.
[7 Jul 2009 14:09] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090707140519-svplog8kcfejbzbe) (version source revid:ingo.struewing@sun.com-20090702190828-8iscrkt20e8g5dtt) (merge vers: 5.4.4-alpha) (pib:11)
[9 Jul 2009 7:36] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090707140519-svplog8kcfejbzbe) (version source revid:ingo.struewing@sun.com-20090702190828-8iscrkt20e8g5dtt) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 18:10] Paul Dubois
No changelog entry needed. Not in any released version.