MySQL Bugs: #49446: Backup / Restore of test DB passes even when no privileges are assigned to user

Bug #49446	Backup / Restore of test DB passes even when no privileges are assigned to user
Submitted:	4 Dec 2009 4:47	Modified:	4 Dec 2009 17:06
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0-backup	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
Login as root user and create user_nopriv@localhost.
Do not assign any privileges to user_nopriv@localhost
Now login as user_nopriv@localhost and check the grants for this user
Perform backup of test database. Backup succeeds.

As per latest fix of MySQL backup privileges(BUG#44787 and backup_security.test), user with backup privilege can only backup database. 
The issue noticed here contradicts above fix. 

Note that backup_elevation ON in this case.

How to repeat:
Login as root user

mysql> create user nopriv@localhost;
Query OK, 0 rows affected (0.00 sec)

Login as nopriv user

mysql> show grants for nopriv@localhost;
+--------------------------------------------+
| Grants for nopriv@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'nopriv'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> backup database test to 'test.bak';
+-----------+
| backup_id |
+-----------+
| 276       |
+-----------+
1 row in set (0.31 sec)

Suggested fix:
User should not be able to backup test database if appropriate privileges are 
not assigned.

I can't repeat this with recent mysql-6.0-codebase:

77-52-7-73:6.0-codebase openxs$ bin/mysql -unopriv test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.14-alpha-debug Source distribution

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

mysql> select current_user();
+------------------+
| current_user()   |
+------------------+
| nopriv@localhost |
+------------------+
1 row in set (0.00 sec)

mysql>  show grants;
+--------------------------------------------+
| Grants for nopriv@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'nopriv'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> backup database test to 'test.bak';
ERROR 1750 (HY000): Can't enumerate grants in database 'test'.

So, is it a regression in your team tree?

This is a problem in the check_access() and privilege checking code in the server. If you add any privilege other than BACKUP_ACL, the code works and the operation will fail correctly with:

ERROR 1793 (HY000): Insufficient privileges. You must have the BACKUP privilege to backup database 'test'.

However, having no other privileges causes the system to incorrectly process the user's privileges. I have not dug into this deep enough but it appears to be an issue related to when the BACKUP_ACL and RESTORE_ACL were added to the privilege checking code in the server. 

It is not, in fact, a backup code issue and the code for BUG#44787 is correctly calling check_access(). Unfortunately, check_access() is returning the wrong result.

This seems isolated to the test database. If you try the test case presented with any other database, the code works like it is supposed to (the operation fails). Indeed, creating a database requires adding some privilege for user nopriv@localhost in order for the user to see the database.

The test database is readable by everyone. It is hard coded into the privilege tables. You can override this behavior but it requires removing these rows from the mysql.db table.

mysql> select * from db \G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
          Backup_priv: Y
         Restore_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
          Backup_priv: Y
         Restore_priv: Y
2 rows in set (0.00 sec)

Once you remove these rows and issue a FLUSH PRIVILEGES (or restart the server), the test case above will fail because the user has no privileges on the test database.

Recommend close as 'not a bug'.

Indeed. The test database is treated specially. On server installation, it is populated with privileges for everyone. Remember our discussion about the anonymous user ''@'%'?

See also
http://dev.mysql.com/doc/refman/5.1/en/database-use.html
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html

Taking courage to set this as "Not a bug".

When said that test database is special, I see different behaviour of backup test db when backup_elevation is OFF

Set the backup_elevation to OFF
Login as nopriv@localhost and perform backup of test database

mysql> backup database test to 'test.bak';
ERROR 1750 (HY000): Can't enumerate grants in database 'test'.

The backup fails in this case even when all privileges are assigned. 

mysql> select * from mysql.db\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
          Backup_priv: Y
         Restore_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
          Backup_priv: Y
         Restore_priv: Y
2 rows in set (0.00 sec)

The error message may indicate that the user has no SELECT on mysql.*, which is required without elevation.

The safe comparison between "test" and other databases can be done after removal of all privileges for the anonymous user from the test database. The steps "Anonymous Account Removal" in the quoted manual page might do that, but I'm not sure about this.