MySQL Bugs: #24680: mysqldump writes the definer of a view to the dump file

Bug #24680	mysqldump writes the definer of a view to the dump file
Submitted:	29 Nov 2006 9:10	Modified:	3 Jan 2009 11:31
Reporter:	Kai Sautter (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	5.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
It would be nice to have an option, that would allow to suppress the DEFINER statement in the CREATE VIEW statements generated by mysqldump. This would help when transfering data structures between databases with different security models.

How to repeat:
issue a mysqldump statement against a database containing views with scripting of tables enabled.

Thank you for a reasonable feature request.

Bug #42148 was marked as a duplicate of this one.

Bug #43386 was marked as a duplicate of that one.

Bug #24680 is a duplicate of this.

Bug #50594 is a duplicate of this (not Bug #24680 in previous comment, which is this feature request)

the issue with portability of views between hosts with different user accounts may cause issues when views are migrated. 1 method of overcoming the "Access Denied" error found when running a view that has a non-existant definer is to switch the security model of the view.

By default, "create view" uses "SQL SECURITY DEFINER" - so views run as the definer, causing the issue. If this feature request were to be implmented, the user account used to create the view from the dump will become the effective new DEFINER.

However, "CREATE SQL SECURITY INVOKER `view_name` ... " will ensure that the view is run with the permissions of the invoker rather, so even if definer is non-existant, a workable set of permissions will apply.

I think that the new mysqldump option should also allow to specify CURRENT_USER/other user as new definer, not just suppressing the old definer.

This bug of course affects routines as well, for instance when dumping routines from a machine to another machine where the user used as definer when creating the original routine in the first machine is not present in the second machine.

The workaround is to use the sed command or the community tool "mysqldump filter".

For more information about mysqldump filter, please take a look at http://datacharmer.blogspot.com/2009/12/filtering-mysqldump-output.html.

As per the "sed" command. It allows you to remove the definer clause from your dump file with a single line command (so no manual editing needed).

When using the "sed" command, you have two options: using it directly while backup is running:

mysqldump your_bck_options_here | sed -e ‘s/DEFINER=[^*]*\*/\*/’ > dump.sql

or creating a new file after the backup has already finished:

sed 's/DEFINER=[^*]*\*/\*/' backup.sql > backup_without_definer.sql

I prefer the second option because you can always use the "diff" program to check what exactly "sed" has changed.

I totaly second feature request for "--skip-definers" flag to mysqldump. 

Also it would help if mysql accepted queries creating new functions with definer matching current user executing query. Now you can't use definer clause as unprivleged user even you you spefiy yourself. That means you can't mysqldump database and recreate it without MANUALY removing definers which could be very hard if dump is huge.

I'd really like to see this implemented, but with this bug having been open for 6 years already i guess i shouldn't count on it.

I want to be able to periodically clone my current production database to my dev environment.  Unfortunately the machine name in the DEFINER definition is never going to match.  Even when I set SQL SECURITY=INVOKER, mysqldump still wants to copy the invoker across, which seems rather pointless, and breaks portability of the dump file.

views and mysqldump are largely incompatible until this is fixed.

this is the error i'm getting when trying to do mysqldump:

mysqldump: Couldn't execute 'show table status like 'blocked\_browser\_addon\_events\_last\_day\_clsids'': SELECT command denied to user 'user'@'%' for column 'COL1' in table 'MYTABLE' (1143)

this is the command:

mysqldump -uroot -p DBNAME > DBNAME.sql

mysqldump: Couldn't execute 'show table status like 'ComputerView'': SELECT command denied to user 'some_user'@'%' for column 'ID' in table 'computers' (1143)

The error log is clear. It has begun this morning, the database had worked from 2007 till now.

Also I'm able to execute 'show table status like 'ComputerView'' query on behalf 'some_user'@'%' in mysql console client.

Please note that Bug #42148 and Bug #50594 are not limited to VIEWs specifically and therefore they are not exact duplicates of this one. They mention "any DEFINER".

I agree with the idea of a new parameter --skip-definers to mysqldump which would omit the DEFINER clause on all applicable objects (views, stored procedures etc.).

New tool, mysqlpump, can do it. Just run it with --skip-definer option.

It's recommended to use MySQL Shell Dump & Load Utility over mysqldump and mysqlpump.

Dump & Load is much faster and supports many options that makes it easy to use and allow many other things like creating hidden Primary Key for InnoDB Tables with PK.

It is also the best solution to migrate to MySQL Database Service.

And of course has a strip_definers option too.

Here's the link to the manual: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html