Bug #41979 Routine level grants not restored when user is dropped, recreated before restore
Submitted: 8 Jan 2009 20:04 Modified: 30 Mar 2009 1:59
Reporter: Hema Sridharan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:mysql-6.0-backup OS:Linux
Assigned to: Ingo Strüwing CPU Architecture:Any

[8 Jan 2009 20:04] Hema Sridharan
Description:
Create database(db1), table(t1) and load some values in table.
Create user jim@'%';
Create stored procedures db1.p1.
Assign routine level execute privileges to users for stored procedures.
Verify grants for user jim@'%';
Execute backup operation(db1.bak).
Drop database and user. Recreate user jim@'%'
Restore from db1.bak and verify grants for jim@'%';
We can notice that grants for procedures is not restored.

CREATE DATABASE db1;
CREATE TABLE db1.t1(a INT, b CHAR(20));
INSERT INTO db1.t1 VALUES
 (1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4');
CREATE USER 'jim'@'%';
DELIMITER ||
CREATE PROCEDURE db1.p1(a CHAR(20))
  BEGIN
     INSERT INTO db1.t1 VALUES(50,'e');
  END;||
DELIMITER ;
GRANT EXECUTE ON PROCEDURE db1.p1 TO jim@'%';
SHOW GRANTS FOR jim@'%';
BACKUP DATABASE db1 to 'db1.bak';
DROP DATABASE db1;
DROP USER jim@'%';
CREATE USER 'jim'@'%';
RESTORE FROM 'db1.bak';
SHOW TABLES FROM db1;
SHOW GRANTS FOR jim@'%';

The grants for jim@'%' fails to show execute privilege on procedure db1.p1. 

Workaround: Restore without dropping and recreating user jim@'%'; 

How to repeat:
mysql> CREATE DATABASE db1;

mysql> CREATE TABLE db1.t1(a INT, b CHAR(20));

mysql> INSERT INTO db1.t1 VALUES
    ->  (1,'TEST1'),(2,'TEST2'),(3,'TEST3'),(4,'TEST4');

mysql> CREATE USER 'jim'@'%';

mysql> DELIMITER ||
mysql>  CREATE PROCEDURE db1.p1(a CHAR(20))
    ->   BEGIN
     INSERT INTO db1.t1 VALUES(50,'e');
    ->      INSERT INTO db1.t1 VALUES(50,'e');
    ->   END;||
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>  GRANT EXECUTE ON PROCEDURE db1.p1 TO jim@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR jim@'%';
+----------------------------------------------------+
| Grants for jim@%                                   |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%'                    |
| GRANT EXECUTE ON PROCEDURE `db1`.`p1` TO 'jim'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.procs_priv;
+------+-----+------+--------------+--------------+----------------+-----------+---------------------+
| Host | Db  | User | Routine_name | Routine_type | Grantor        | Proc_priv | Timestamp           |
+------+-----+------+--------------+--------------+----------------+-----------+---------------------+
| %    | db1 | jim  | p1           | PROCEDURE    | root@localhost | Execute   | 2009-01-08 22:14:16 |
+------+-----+------+--------------+--------------+----------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> BACKUP DATABASE db1 to 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 270       |
+-----------+
1 row in set (0.19 sec)

mysql> DROP DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql> DROP USER jim@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'jim'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR jim@'%';
+---------------------------------+
| Grants for jim@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> RESTORE FROM 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 271       |
+-----------+
1 row in set (0.01 sec)

mysql> SHOW TABLES FROM db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR jim@'%';
+---------------------------------+
| Grants for jim@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.procs_priv;
Empty set (0.00 sec)

From the above, we can notice that grant privileges on procedure is not restored. To avoid this problem, execute restore operation without dropping user.

Suggested fix:
Privileges on stored routines should be restored.
[9 Jan 2009 7:55] Valeriy Kravchuk
Thank you for a problem report. Verified just as described, also - with 6.0.8.
[21 Jan 2009 21:04] Chuck Bell
May be related to BUG#41578 (same solution may fix both bugs).
[10 Feb 2009 13:37] Ingo Strüwing
This has to wait for:

  - WL#2646: INFORMATION_SCHEMA.ROUTINE_PRIVILEGES view
  - Bug#26886: No ROUTINE_PRIVILEGES table in INFORMATION_SCHEMA
[4 Mar 2009 10:21] 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/68198

2783 Ingo Struewing	2009-03-04
      Bug#41979 - Routine level grants not restored when user is dropped,
                  recreated before restore
      
      BACKUP/RESTORE did not implement backup and restore of privileges
      for stored procedures and stored functions.
      
      On BACKUP, we do now select routine privileges from mysql.proc_priv.
      This is a preliminary solution until Bug 26886 / WL 2646 are implementd.
      
      Another BACKUP change is in the GRANT statement creation. Grants for
      routines have the additional keyword PROCEDURE or FUNCTION after ON.
      
      On RESTORE, we need to be aware of the additional keyword, when we
      search for the database name in the GRANT statement.
     @ mysql-test/suite/backup/r/backup_db_grants.result
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Added test result.
     @ mysql-test/suite/backup/r/backup_db_grants_extra.result
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Fixed test result.
     @ mysql-test/suite/backup/t/backup_db_grants.test
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Added test.
     @ mysql-test/suite/backup/t/backup_db_grants_extra.test
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Fixed test comments. RESTORE no longer fails on routine priiviliges.
     @ sql/backup/kernel.cc
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Changed GRANT statement parser to find database name also in
        routine privilege grants.
     @ sql/si_objects.cc
        Bug#41979 - Routine level grants not restored when user is dropped,
                    recreated before restore
        Fixed a compiler warning.
        Changed GRANT statement creation to handle routine privileges.
        Added routine privileges to privilege selection.
[5 Mar 2009 12:29] Jørgen Løland
Patch approved.
[6 Mar 2009 8:58] Rafal Somla
Good to push.
[6 Mar 2009 15:45] Ingo Strüwing
Queued to mysql-6.0-backup.
[26 Mar 2009 12:33] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:charles.bell@sun.com-20090309134019-vr480npdp30f2gvh) (merge vers: 6.0.11-alpha) (pib:6)
[30 Mar 2009 1:59] Paul DuBois
Noted in 6.0.11 changelog.

BACKUP DATABASE and RESTORE did not implement backup and restore of
privileges for stored procedures and stored functions.