Remove DEFINER clause from MySQL Dumps

I have a MySQL dump of one of my databases. In it, there are DEFINER clauses which look like,

"DEFINER=`root`@`localhost`" 

Namely, these DEFINER clauses are on my CREATE VIEW and CREATE PROCEDURE statements. Is there a way to remove these DEFINER clauses from my dump file?

I don't think there is a way to ignore adding DEFINERs to the dump. But there are ways to remove them after the dump file is created.

1. Open the dump file in a text editor and replace all occurrences of DEFINER=root@localhost with an empty string ""

2. Edit the dump (or pipe the output) using perl:

   perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql
   

3. Pipe the output through sed:

   mysqldump ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql
   

You can remove using SED

sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql

In MacOS:

sed -i '' 's/DEFINER=[^*]*\*/\*/g' mydump.sql

Since mysql version 5.7.8 you can use the --skip-definer option with mysqlpump, e.g.:

mysqlpump --skip-definer -h localhost -u user -p yourdatabase

See updated mysql manual at http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer

I used these ideas to strip the DEFINER clause from my own mysqldump output, but I took a simpler approach:

Just remove the ! before the code and DEFINER, and the rest of the comment becomes a regular comment.

Example:

/*!50017 DEFINER=`user`@`111.22.33.44`*/

is rendered helpless, as little as doing this ..

/* 50017 DEFINER=`user`@`111.22.33.44`*/

The easiest regexp, though, is to remove the ! and the numbers

mysqldump | /usr/bin/perl -pe 's/\!\d+ DEFINER/DEFINER/' > dumpfile.sql

That removes !#### DEFINER and replaces with DEFINER ... you could remove DEFINER too, it doesn't really matter - once the "!" is gone

As per the other's recommendations, here is an example of how to remove the DEFINER from the dump, after the dump has finished:

mysqldump -u user --password='password' -h localhost database | grep -v "50013 DEFINER" > dump.sql

As others mentioned stripping the definer with any kind of regular expression is not too complex. But the other examples stripped the view definitions for me.

This is the regular expression which worked for me:

sed -e 's/DEFINER=[^ ]* / /'

For an automated solution, you could look at mysqlmigrate. It's a Bash wrapper around mysqldump which allows you to migrate databases and ignore the DEFINER statements. Example:

$ mysqlmigrate -u root -p pass --ignore-definer from_db to_db

http://thesimplesynthesis.com/post/mysqlmigrate (or GitHub)

Otherwise, yes, a command like Abhay points out is needed:

$ mysqldump -u root -ppass the_db | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > the_db.sql

You should look the answer here: https://dba.stackexchange.com/questions/9249/how-do-i-change-the-definer-of-a-view-in-mysql/29079#29079

The best way in my opinion to address this problem, is not adding an extra parse of string with sed or grep or any other, instead mysqldump is able to generate a good dump by adding --single-transaction

Another option on OSX to remove definers in-file:

sed -i '' 's/DEFINER=`[^`][^`]*`@`[^`][^`]*`//g' file.sql

A quick way to do this is to pipe the output of mysqldump through sed to remove the DEFINER statements wrapped in conditional comments. I use this to remove DEFINER from CREATE TRIGGER statements, but you can tweak the condition comment version number in the regex to suit your purposes.

mysqldump -u user --password='password' -h localhost database | \
  sed 's/\/\*!50017 DEFINER=`.*`@`.*`\*\///' 

Not sure if it helps but I use SQLyog Community Edition, available at: -

https://code.google.com/p/sqlyog/wiki/Downloads

When dumping sql or copying to another database it allows you to 'Ignore DEFINER'

It is free and provides the basic functionality that a lot of people require

There is also a paid version available from: -

https://www.webyog.com/product/sqlyog

Cheers

For me, this works: perl -p -i.bak -e "s/DEFINER=[^ |\s]*//g" yourdump.dmp. This removed DEFINER=root@localhost from my dump, on each create procedure statment

Best way of dumping and restoring without problems:

MYSQL DUMP

mysqldump -h HOST -u USER -pPASSWORD --single-transaction --quick --skip-lock-tables --triggers --routines --events DATABASE | gzip > "DATABASE.sql.gz" &

MYSQL RESTORE

gunzip DATABASE.sql.gz

sed -i 's/DEFINER=[^ ]* / /' DATABASE.sql
sed -i 's/SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;/ /' DATABASE.sql
sed -i 's/SET @@SESSION.SQL_LOG_BIN= 0;/ /' DATABASE.sql
sed -i 's/SET @@GLOBAL.GTID_PURGED='';/ /' DATABASE.sql
sed -i 's/SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;/ /' DATABASE.sql

mysql -h HOST -u USER -pPASSWORD < DATABASE.sql

Replace all of your definer users with CURRENT_USER. In my gradle script that creates the backup, my replace script looks like:

ant.replaceregexp(file: "$buildDir/sql/setupDB.sql", match: "`${project.ext.prod_db_username}`@`%`", replace: "CURRENT_USER", byline: true);

which really is just calling ANT. Then you won't have to worry about it.

Here is a complete working solution to remove DEFINER information for MySQL 5.6.x and Linux.(Tested on CentOS 6.5).

Usaually we have to replace following entries from Mysql dump(if taken along with data and triggers/routines/functions).

/*!50013 DEFINER=`MYSQLUSER`@`localhost` SQL SECURITY DEFINER */
/*!50013 DEFINER=`MYSQLUSER`@`%` SQL SECURITY DEFINER */
CREATE DEFINER=`MYSQLUSER`@`%` PROCEDURE `PROCEDURENAME`(
CREATE DEFINER=`MYSQLUSER`@`localhost` PROCEDURE `PROCEDURENAME`(
CREATE DEFINER=`MYSQLUSER`@`%` FUNCTION `FUNCTIONNAME`(
CREATE DEFINER=`MYSQLUSER`@`localhost` FUNCTION `FUNCTIONNAME`(
/*!50003 CREATE*/ /*!50017 DEFINER=`MYSQLUSER`@`%`*/ /*!50003 TRIGGER `TRIGGERNAME`
/*!50003 CREATE*/ /*!50017 DEFINER=`MYSQLUSER`@`localhost`*/ /*!50003 TRIGGER `TRIGGERNAME`

The dump was taken with below mysqldump command.

mysqldump -uMYSQLUSER -pPASSWORD DATABASENAME -R > dbdump.sql

The required dump file with no DEFINER information can be obtained with below three commands.

Command-1    
sed -i 's|DEFINER=[^*]*\*|\*|g' [PATH/]dbdump.sql

Command-2
find -name [PATH/]dbdump.sql | xargs perl -pi -e "s/ DEFINER=\`MYSQLUSER\`@\`localhost\`//"

Command-3
find -name [PATH/]dbdump.sql | xargs perl -pi -e "s/ DEFINER=\`MYSQLUSER\`@\`%\`//"

If the dump file is in your current folder then ignore [PATH/].

If data in tables is very huge then take the dump in two files, in one dump file take the dump with data and in other dump file on take the dump of the scripts only (Triggers/Functions/Procedures.) and run the above three commands on 2nd dump(scripts) file.

On Linux machines you can use this one-liner:

mysql -uuser -ppwd -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL and table_schema like 'mydb%'" | mysql -uuser -ppwd -A --skip-column-names | sed -rn 's/.*?VIEW ([^\s]+?) (AS .*?)\s([^\s]+?)\s([^\s]+?)/DROP VIEW \1;\nCREATE VIEW \1 \2;/p' | mysql -uuser -ppwd -A --skip-column-names

You have only to replace strings in bold with your DB user credentials and database name / like pattern.

More info here: https://blog.novoj.net/posts/2014-05-16-recreate-mysql-views-without-definer-one-liner-solution-linux/

The only way I could get it working under Windows:

Install http://gnuwin32.sourceforge.net/ to have sed in the command line and add it to the Windows PATH: D:\programs\GetGnuWin32\bin;

sed -i "s|DEFINER=`mydbuser`@`%%` SQL SECURITY DEFINER||g" "D:/prod_structure.sql"
    
sed -i "s|DEFINER=`mydbuser`@`%%`||g" "D:/prod_structure.sql"
    
mysqldump -P{port} -h{host} -u{user} -p{password} --routines --no-data --lock-tables=false database_prod > D:\prod_structure.sql

Thank you all for the hints. Being lazy, I wrote a script named: "MYsqldump" :

DB=$1
HOST=$2
USER=$3
MYSQLDUMP='/usr/bin/mysqldump'
PARAMS="--complete-insert --disable-keys=0 --extended-insert=0 --routines=0 --skip-comments"
DAY=`date +%d`
MONTH=`date +%m`
YEAR=`date +%Y`
FILE=$DB.$DAY-$MONTH-$YEAR.sql

	if (( $# < 3 )) ; then
		echo ""
		echo "usage: MYsqldump <db> <host> <user>"
		echo ""
		exit 1
	fi

	$MYSQLDUMP -h $HOST -u $USER -p  $PARAMS $DB | grep -v '/*!50013 DEFINER' > $FILE

For something like:

DELIMITER ;;
CREATE DEFINER=DELIMITER ;;
CREATE DEFINER=mydb@localhost FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
@DELIMITER ;;
CREATE DEFINER=DELIMITER ;;
CREATE DEFINER=mydb@localhost FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
@localhost FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
 FUNCTION DELIMITER ;;
CREATE DEFINER=DELIMITER ;;
CREATE DEFINER=mydb@localhost FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
@DELIMITER ;;
CREATE DEFINER=DELIMITER ;;
CREATE DEFINER=mydb@localhost FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
@localhost FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
 FUNCTION myfunction() RETURNS int(11)
begin
(...)
end ;;
() RETURNS int(11)
begin
(...)
end ;;

Try this:

mysqldump --force --routines --opt --user=myuser --databases mydb | sed -e 's/DEFINER=mysqldump --force --routines --opt --user=myuser --databases mydb | sed -e 's/DEFINER=.*@.*\ //g'
@mysqldump --force --routines --opt --user=myuser --databases mydb | sed -e 's/DEFINER=mysqldump --force --routines --opt --user=myuser --databases mydb | sed -e 's/DEFINER=.*@.*\ //g'
@.*\ //g'
\ //g'

1. open your database with any editor, I used with notepad++,

2. find all test that DEFINER=root@localhost and replace it with nothing("") IE. delete it.

Then you can import it to the any host that you want.

The simplest regex that works for me with all objects is:

sed 's/DEFINER=[^ ]*`//' ...

Note that quote-names has to be TRUE (which is by default).

Hope that in newer versions the switch --skip-definer introduced in mysqlpump in version 5.7 comes to mysqldump, too.

I have used PowerShell script to remove all the lines with DEFINER:

get-content $file_in_full_path | select-string -pattern $line_string_delete -notmatch | Out-File -width 1000000000 -Encoding Default $file_out_full_path

Nothing was working for me, so I needed to write my own regex.

1. Try to dump your database into file, cat whole file and grep only your definer statements to see them.

   cat file.sql | grep -o -E '.{,60}DEFINER.{,60}'

2. Write your regex

3. Pipe your dump into sed with this regex. For example mine statement is:

   mysqldump | sed -E 's//*!500[0-9][0-9] DEFINER=.+?*///' | sed -E 's/DEFINER=?[^ ]+??@?[^ ]+? ?//'

4. Profit!

remove_definers.bat:

@echo off
(for /f "tokens=1,2*" %%a in (dumpfile.sql) do (
 if "%%b"=="" (echo %%a) else (
  echo %%b | find "DEFINER" > null && echo %%a %%c || echo %%a %%b %%c
 )
)) > dumpfile_nodefiners.sql

Referring to https://stackoverflow.com/a/19707173/1488762, I suspect the following may perform better on very large databases - modify the definer at the end, rather than performing additional regex processing on every line:

mysqldump myDB > dump.sql
cat >> dump.sql << END
SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ", 
table_name, " AS ", view_definition, ";") 
FROM information_schema.views 
WHERE table_schema='myDB';
END

Just for my future reference, sd version.

mysqldump ... | sd 'DEFINER=.*?\*' '*' > file.sql

My solution for removing DEFINER tested on my MariaDB 10.3:

mysqldump | grep -v -P 'SQL SECURITY DEFINER \*/$' | perl -pe 's/(?<=^\/\*!50003 CREATE\*\/ )\/\*!50017 DEFINER=`.+`@`.+`\*\/ (?=\/\*!50003 TRIGGER)//'

grep removes it for views, perl removes it for triggers

This solution is is attacker-proof, because it matches start and end of lines. So even if attacker knows you are doing replace of DEFINER on your dumps, his DEFINER arranged in the data won't be replaced.

If your SQL file has DEFINER occurances (with and) without conditional comments, then you might be interested in this alternative REGEXP.

sed -Ei \
	's/DEFINER\s*=\s*(["'\''`]?)[^"'\''`@]+\1\s*@\s*(["'\''`]?)[^"'\''`\*]+\2//g' \
#      [-----1------][----2----][----4----]-5[--6--][----------7------------]
	"$FILE"

Explanation:

1. DEFINER {potential whitespace} = {potential whitespace} …
2. … {any potential quote or backtick, captured} …
3. (break sequence, add escaped single-quote, resume sequence – because we wrap the construct with single-quotes)
4. … (username) {string: no quote or @} …
5. … {use first capturing group – the quote – if any} …
6. … {potential whitespace} @ {potential whitespace} …
7. … {same for host with new capturing group, but also exclude asterisk}.

(Run without the commented explanation line because that would throw an error)

As others have said, one possible solution is to replace the usernames in DEFINER clauses to CURRENT_USER.
Note that we must take into account DEFINER in both plain SQL statements and in special comments, like in the following examples:

CREATE DEFINER=`root`@`localhost` FUNCTION...
/*!50013 DEFINER=`MYSQLUSER`@`%` SQL SECURITY DEFINER */
/*!50017 DEFINER=`root`@`localhost`*/

So, for me, what works is this:

mysqldump <my-options> | sed -E 's/DEFINER=[^ *]+/DEFINER=CURRENT_USER/g' > dumpfile.sql