Description:
When mysqldump is run on a database with VIEWs, the CREATE VIEW statements always include the DEFINER information. There is no option documented that I can find to request that this information not be included.
This seriously breaks our environment. We have several copies of our live database used for development and staging of our application. We dump the live database in the morning to a backup file and then use that to repopulate our dev and stage databases. Developers have a script to run that does this. We DO NOT want to give all developers SUPER privileges, but we DO want all developers to be able to create a fresh copy of the live DB (from the nightly-generated file) at any time.
VIEWs breaks this, because the backup file includes the DEFINER stuff for VIEWs, which requires SUPER to redefine on the individual development databases because the import is being run as the developer.
We really do not even remotely care about the DEFINER information. If a user has access to the database, they should have access to the VIEW. The access control is useless to us, and it's just making everything a complete pain.
We have had to modify our backup script extensively to dump only TABLEs, then dump the VIEWs to another file, run a Perl script to remove the useless security control information (so the statement turns into a simple CREATE VIEW `foo` AS etc...), and that append that to the main backup file.
How to repeat:
Create a view. Dump the database with mysqldump. Try to import the data into another database as a different user. Watch how it fails due to unuseful security information.
Suggested fix:
Easiest fix would probably be an option to mysqldump to exclude the DEFINER information from the CREATE VIEW statements so that the dump becomes independent of user and database names.
