mysqldump view definer prevents the dump file being portable

(I'd have made this a feature request except that option is not available in the form)

When mysqldump dumps a database with a view in it, it writes a line that looks something like this:

/*!50013 DEFINER=`user`@`host` SQL SECURITY INVOKER */

It may also say SQL SECURITY DEFINER, and apparently for some versions of mysqldump, the SQL SECURITY CLAUSE is missing, but I don't know which versions do that or if they include mariadb issued versions.

The problem is that unless `host` is localhost or `%`, the dump file cannot be portable between machines, and it is also not going to be portable between users.

This issue has been unaddressed by mysql for 6 years. See <http://bugs.mysql.com/bug.php?id=24680>, which proposes an option on the mysqldump command to skip the DEFINER. There are also a variety of filters out there written in sed or perl to fix the mysqldump file. eg <https://gist.github.com/1228701>

The proposal on the mysql bug would solve the problem for me, but I also put forward another approach for consideration: where the view uses SQL SECURITY INVOKER, dump the view using DEFINER=CURRENT_USER. It seems to me that this has no effect on the security model of the view after reloading it, but it makes the view portable. I think this is probably safe to do by default, without the user setting an option.

Where the view uses SQL SECURITY DEFINER, it is more likely that that would modify the effective security model, and that should require setting an option explicitly. The filter script linked above has sensible options, which could be implemented within mysqldump rather than as an external filter.

I don't use triggers, but I expect similar issues might exist there.