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

Question

I am launching an update for some software that includes running an SQL script to create triggers.

In the syntax of the .sql file .sql I did not include any DEFINER clause, since I want the user of my MySQL client (which they themselves configured) to be able to execute these triggers when the program is running.

Thing tests showed that MySQL automatically creates a DEFINER for TRIGGER with 'CURRENT_USER'@'%' . When you do a reasonable thing and use an (limited) account for daily data processing and another for large updates (root?), You end up trying to execute TRIGGER with one or more users who do not have permission to do this.

Is there a way to remove this auto-matching feature?

I tried logging in DEFINER = '%'@'%' , but this is not accepted.

Answer 1

If you are on the * nix platform, the qualifier can be removed by one 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 case your database.sql is very large, instead of first resetting it and then deleting the determinant, delete the determinant during the dump.

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