CODEWITHSUNDEEP
mysqltriggers
Silver Quettier
Silver Quettier

Reputation: 2050

How to remove the default DEFINER when executing a CREATE TRIGGER on a table?

I'm rolling out an update for some software, which include running a SQL script to create triggers.

In my .sql file syntax, I did not include any DEFINER clause, as I want my customer's production MySQL user (which they set up by themselves) to be able to execute these triggers when the program is running.

Thing is, tests showed that MySQL automatically creates a DEFINER for the TRIGGER, with 'CURRENT_USER'@'%'. When you do the sensible thing, and use a (restricted) account for day-to-day data manipulation and another one for big updates (root ?), you end up trying to execute the TRIGGER with one or multiple users who have no permission to do it.

Is there a way to remove this "auto-add definer" feature ?

I tried entering DEFINER = '%'@'%' but this is not accepted.

Upvotes: 9

Views: 5571

Answers (1)

Shoan
Shoan

Reputation: 4078

If you are on a *nix platform, the definer can be stripped out by either of the following command line scripts.

cat database.sql | sed -e "s/DEFINER=[^*]*\*/\*/" > database_nodefiner.sql

perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < oldfile.sql > newfile.sql

In the event that your database.sql is very large, then instead of dumping it first and then removing the definer, remove the definer during the dump.

mysqldump database | sed -e "s/DEFINER=[^*]*\*/\*/" > database_nodefiner.sql

Upvotes: 2

Related Questions