| 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: | |
| Category: | MySQL Server: Backup | Severity: | S2 (Serious) |
| Version: | mysql-6.0-backup | OS: | Linux |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
[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.
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.