MySQL Bugs: #46569: Backup fails even when all privileges are assigned to user on db.*

Bug #46569	Backup fails even when all privileges are assigned to user on db.*
Submitted:	5 Aug 2009 16:03	Modified:	21 Apr 2010 17:33
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-5.4.4	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
Login as root user and create database db1.
Create table db1.t1 (id int);
Create user joe@'%';
Grant all on db1.* to joe@'%';
Show grants for joe@'%';
Flush privileges;

Now login as user=joe and perform backup

show grants for joe;
backup database db1 to 'db1.bak';

Backup fails by giving error "Can't enumerate grants in database 'db1'"

Ideally backup should not fail. BUG#39580 mentions that when GRANT ALL ON mydb.* TO user1, then user should be able to perform Backup operation. This statement contradicts as seen above.

How to repeat:
Login as root user and perform the following:

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> Create table db1.t1 (id int);
Query OK, 0 rows affected (0.07 sec)

mysql> Create user joe@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db1.* to joe@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for joe@'%';
+----------------------------------------------+
| Grants for joe@%                             |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'joe'@'%'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'joe'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)

Now login as user joe and perform backup

mysql> backup database db1 to 'db2.bak';
ERROR 1748 (HY000): Can't enumerate grants in database 'db1'.

Thank you for the bug report. Verified as described:

c:\temp>mysql\bin\mysql -ujoe
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.4.4-alpha-Win X64-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> backup database db1 to 'db2.bak';
ERROR 1748 (HY000): Can't enumerate grants in database 'db1'.
mysql>

This is not a bug. The code is expected to behave this way if the user does not have appropriate permissions to backup all of the objects.

BUG#44787 is designed to change this behavior but it is currently part of a larger discussion regarding how to handle elevation and/or privilege checking in general.

Note: Restore can also fail in the middle for similar privilege issues.

To me the last reply is completely non-understandable

1) it contradicts what documentation says
2) all privleges were granted to that database

If details of things are stil discussed then leave this open as a verified bug for as long.

Re-opening this bug.

The manual (for 6.0) and the code do not seem to 
correspond to each other.

We need to fix either the privs, the manual, or both.

When one executes a GRANT ALL to a user for a given database (or all databases), it gives the user all privileges on all objects in the database including BACKUP and RESTORE.

However, it does *not* grant the user SELECT or any semblance of access to the mysql database. Currently, a user needs access to certain tables in the mysql database in order to do a complete backup. This is how the system works today.

Thus, this is not a bug because GRANT ALL does *not* give the user all of the rights for all of the metadata (such as grants) for a given database and can therefore fail if the user does not have sufficient rights to the mysql database.

There is a similar case for restore.

Per 8 September backup meeting:

 - Close this bug as not a bug

Please disregard last post. I was a bit premature in marking it as not a bug. There are still those who object to that decision. Asked that this be added to next backup meeting agenda.

;-)

.. but I'd like to add comment the comment that current situation is inconsistent with mysqldump and all 3rd party tools generating backups as SQL statements.

It was decided on 29 September that we should do a discrete privilege elevation for backup that allows anyone with the BACKUP_PRIV on a given database to backup that database.

Thus, this case of granting ALL to a user will give the user all of the privileges needed to do backup.

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/85097

2875 Chuck Bell	2009-09-29
      BUG#46569 : Backup fails even when all privileges are assigned to user on db.*
      
      When an admin issues GRANT ALL on a database and the database contains objects
      other than tables (such as stored procedures and database-level grants), the 
      default privileges granted by GRANT ALL are not enough to successfully execute
      backup.
      
      This is because there are additional privileges users need to read the mysql
      database tables that contain procedure, event, triggers, and grant information.
      
      On 29 September, the backup team decided to overcome this limitation by 
      introducing temporary privilege elevation for backup. This patch includes code
      to temporarily give the user the privileges needed to read all metadata for all
      objects in the database. This is granted only during the execution of the 
      SELECT statements inside si_objects.cc and does not infer any additional privileges
      outside of the execution of backup.
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_security_check.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_security.test
        Changed previously failing tests to passed due to new privilege elevation
        for backup.
     @ mysql-test/suite/backup/t/backup_security_check.test
        Changed tests that failed due to insufficient rights to passed tests. This
        is because so long as a user has BACKUP granted on a database, she can 
        execute backup.
        
        Also added test case to show one only needs BACKUP to execute the backup
        database command.
     @ sql/si_objects.cc
        Implements backup privilege elevation for execution of internal SELECTs
        against the mysql and information_schema databases.
     @ sql/si_objects.h
        Primitives for new elevation methods.

Please see questions by email.

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/85610

2875 Chuck Bell	2009-10-02
      BUG#46569 : Backup fails even when all privileges are assigned 
                  to user on db.*
      
      When an admin issues GRANT ALL on a database and the database 
      contains objects other than tables (such as stored procedures 
      and database-level grants), the default privileges granted by 
      GRANT ALL are not enough to successfully execute backup.
      
      This is because there are additional privileges users need to
      read the mysql database tables that contain procedure, event, 
      triggers, and grant information.
      
      On 29 September, the backup team decided to overcome this 
      limitation by introducing temporary privilege elevation for 
      backup. This patch includes code to temporarily give the user 
      the privileges needed to read all metadata for all objects in 
      the database. This is granted only during the execution of the 
      SELECT statements inside si_objects.cc and does not infer any 
      additional privileges outside of the execution of backup.
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_security_check.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_security.test
        Removed previous step-wise privilege check on backup with new
        test cases for the elevated privilege backup.
     @ mysql-test/suite/backup/t/backup_security_check.test
        Changed tests that failed due to insufficient rights to passed 
        tests. This is because so long as a user has BACKUP granted on 
        a database, she can execute backup.
        
        Also added test case to show one only needs BACKUP to execute the backup
        database command.
     @ sql/si_objects.cc
        Implements backup privilege elevation for execution of internal 
        SELECTs against the mysql and information_schema databases.
     @ sql/si_objects.h
        Primitives for new elevation methods.

Updated reviewers.

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/85959

2875 Chuck Bell	2009-10-06
      BUG#46569 : Backup fails even when all privileges are assigned 
                  to user on db.*
      
      When an admin issues GRANT ALL on a database and the database 
      contains objects other than tables (such as stored procedures 
      and database-level grants), the default privileges granted by 
      GRANT ALL are not enough to successfully execute backup.
      
      This is because there are additional privileges users need to
      read the mysql database tables that contain procedure, event, 
      triggers, and grant information.
      
      On 29 September, the backup team decided to overcome this 
      limitation by introducing temporary privilege elevation for 
      backup. This patch includes code to temporarily give the user 
      the privileges needed to read all metadata for all objects in 
      the database. This is granted only during the execution of the 
      SELECT statements inside si_objects.cc and does not infer any 
      additional privileges outside of the execution of backup.
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_security_check.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_security.test
        Removed previous step-wise privilege check on backup with new
        test cases for the elevated privilege backup.
     @ mysql-test/suite/backup/t/backup_security_check.test
        Changed tests that failed due to insufficient rights to passed 
        tests. This is because so long as a user has BACKUP granted on 
        a database, she can execute backup.
        
        Also added test case to show one only needs BACKUP to execute the backup
        database command.
     @ sql/si_objects.cc
        Implements backup privilege elevation for execution of internal 
        SELECTs against the mysql and information_schema databases.
        
        The code is designed to hide the elevation and is only used for
        elevating SELECT and SHOW CREATE statements.

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/86999

2875 Chuck Bell	2009-10-15
      BUG#46569 : Backup fails even when all privileges are assigned 
                  to user on db.*
      
      When an admin issues GRANT ALL on a database and the database 
      contains objects other than tables (such as stored procedures 
      and database-level grants), the default privileges granted by 
      GRANT ALL are not enough to successfully execute backup.
      
      This is because there are additional privileges users need to
      read the mysql database tables that contain procedure, event, 
      triggers, and grant information.
      
      On 29 September, the backup team decided to overcome this 
      limitation by introducing temporary privilege elevation for 
      backup. This patch includes code to temporarily give the user 
      the privileges needed to read all metadata for all objects in 
      the database. This is granted only during the execution of the 
      SELECT statements inside si_objects.cc and does not infer any 
      additional privileges outside of the execution of backup.
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_security_check.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_security.test
        Removed previous step-wise privilege check on backup with new
        test cases for the elevated privilege backup.
     @ mysql-test/suite/backup/t/backup_security_check.test
        Changed tests that failed due to insufficient rights to passed 
        tests. This is because so long as a user has BACKUP granted on 
        a database, she can execute backup.
        
        Also added test case to show one only needs BACKUP to execute the backup
        database command.
     @ sql/si_objects.cc
        Implements backup privilege elevation for execution of internal 
        SELECTs against the mysql and information_schema databases.
        
        The code is designed to hide the elevation and is only used for
        elevating SELECT and SHOW CREATE statements.

Approved.

Patch approved.

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/88057

2884 Chuck Bell	2009-10-23 [merge]
      BUG#46569 : Backup fails even when all privileges are assigned 
                  to user on db.*
      
      When an admin issues GRANT ALL on a database and the database 
      contains objects other than tables (such as stored procedures 
      and database-level grants), the default privileges granted by 
      GRANT ALL are not enough to successfully execute backup.
      
      This is because there are additional privileges users need to
      read the mysql database tables that contain procedure, event, 
      triggers, and grant information.
      
      On 29 September, the backup team decided to overcome this 
      limitation by introducing temporary privilege elevation for 
      backup. This patch includes code to temporarily give the user 
      the privileges needed to read all metadata for all objects in 
      the database. This is granted only during the execution of the 
      SELECT statements inside si_objects.cc and does not infer any 
      additional privileges outside of the execution of backup.x
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_security_check.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_security.test
        Removed previous step-wise privilege check on backup with new
        test cases for the elevated privilege backup.
     @ mysql-test/suite/backup/t/backup_security_check.test
        Changed tests that failed due to insufficient rights to passed 
        tests. This is because so long as a user has BACKUP granted on 
        a database, she can execute backup.
        
        Also added test case to show one only needs BACKUP to execute the backup
        database command.
     @ sql/si_objects.cc
        Implements backup privilege elevation for execution of internal 
        SELECTs against the mysql and information_schema databases.
        
        The code is designed to hide the elevation and is only used for
        elevating SELECT and SHOW CREATE statements.

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/88058

2884 Chuck Bell	2009-10-23 [merge]
      BUG#46569 : Backup fails even when all privileges are assigned 
                  to user on db.*
      
      When an admin issues GRANT ALL on a database and the database 
      contains objects other than tables (such as stored procedures 
      and database-level grants), the default privileges granted by 
      GRANT ALL are not enough to successfully execute backup.
      
      This is because there are additional privileges users need to
      read the mysql database tables that contain procedure, event, 
      triggers, and grant information.
      
      On 29 September, the backup team decided to overcome this 
      limitation by introducing temporary privilege elevation for 
      backup. This patch includes code to temporarily give the user 
      the privileges needed to read all metadata for all objects in 
      the database. This is granted only during the execution of the 
      SELECT statements inside si_objects.cc and does not infer any 
      additional privileges outside of the execution of backup.x
     @ mysql-test/suite/backup/r/backup_security.result
        Corrected result file.
     @ mysql-test/suite/backup/r/backup_security_check.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_security.test
        Removed previous step-wise privilege check on backup with new
        test cases for the elevated privilege backup.
     @ mysql-test/suite/backup/t/backup_security_check.test
        Changed tests that failed due to insufficient rights to passed 
        tests. This is because so long as a user has BACKUP granted on 
        a database, she can execute backup.
        
        Also added test case to show one only needs BACKUP to execute the backup
        database command.
     @ sql/si_objects.cc
        Implements backup privilege elevation for execution of internal 
        SELECTs against the mysql and information_schema databases.
        
        The code is designed to hide the elevation and is only used for
        elevating SELECT and SHOW CREATE statements.

Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)

Noted in 6.0.14 changelog.

For changelog entry, see Bug#44787.