Bug #22394 Restore from Backup using MySQL Adm does not restore stored procedures correctly
Submitted: 15 Sep 2006 15:13 Modified: 28 Sep 2006 17:41
Reporter: Scott Hoellrich Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Administrator Severity:S4 (Feature request)
Version:1.2.3 rc OS:Windows (Windows 2003)
Assigned to: CPU Architecture:Any
Tags: 1.2.3 rc, 5.0.24, administrator, Backup, restore, server

[15 Sep 2006 15:13] Scott Hoellrich
Description:
OS: Windows 2003
Engine version: 5.0.24-community-nt 
Engine Running: InnoDB
Administrator Utility Version: 1.2.3 rc

This issue involves 2 systems: System A and System B.
System A is the source system from which a backup of the data was created.
System B is the target system.

A backup of the entire InnoDB database was created from the a schema on System A.
This backup was restored to System B using the option to rename the schema.

1.) A DEFINER clause exists in the CREATE PROCEDURE commands in the generated backup script that references a non-existent account on System B.
2.) When the catalog of the schema is viewed using the Administrator utility, the account indicated in the Definer column is the account that existed on System A, with %@ appended to the end of the username.
This account does not exist on System B.

How to repeat:
Create 2 installations on separate systems: System A (source) and System B(target). Each installation should consist of the Server and Administrator utility.

System A:
Launch Administrator utility.
Create a schema named testschema.
Create at least one stored procedure in testschema with an account other than root.
Create a backup of testschema using the Administrator utility.

copy generated sql script to System B

System B:
Launch Administrator utility.
Run restore by selecting the generated script as the source, and changing the destination schema name to testschema_b.

Suggested fix:
(1) Do not insert the DEFINER clause in the backup script. The account in the DEFINER clause cannot be assumed to exist on the target system.
(2) During restore, either (1) let the user specify the DEFINER account, or (2) use the account executing the restore as the definer.
(3) Check to ensure the correct parsing of the account name during restore.
[15 Sep 2006 15:26] Scott Hoellrich
Updated OS and tags information for this ticket.
[18 Sep 2006 19:54] Sveta Smirnova
Thank you for the reasonable feature request.
[27 Sep 2006 1:09] Trudy Pelzer
I don't think this feature request should be implemented;
changing the definer via a backup will create a security 
issue. The reason we store the definer in the first place 
is to record whose privileges may need to be checked when 
the routine executes. If there is a reason to change this, 
the correct procedure is to drop the routine and have the 
desired user recreate it. If we make the change, then it
will be possible to circumvent correct privilege checking
merely by dumping the database and restoring with a new
definer -- not a safe thing.
[28 Sep 2006 17:41] Trudy Pelzer
Per my earlier comment, we won't implement this 
functionality.