Menu
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.
290
Show activity on this post. 2) Open triggers. sql file in your favorite editor and use Find and Replace feature to change DEFINER s. Save updated file.
The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If the DEFINER clause is present, the user value should be a MySQL account specified as ' user_name '@' host_name ' , CURRENT_USER , or CURRENT_USER() .
To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema: mysql> DROP TRIGGER test.
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
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
