MySQL DB Procedure Execution by a non-root user

In my this blog, I'll share my experience of one of the issues that I faced during procedures execution for a migration project

Problem Statement

Recently I have been involved in an application migration project from one cloud to another cloud. I have done the database migration and due to security reasons, I have changed the identifier of all procedures to a non-root use so that I don't need to provide root credentials. However, One strange issue encountered during the procedure execution. Below is the sample snippet of my procedure I have been executing with a non-root user-

CALL save_wallet_request('233284','23342sad','1','2021-01-13 06:54:23','eeedewewqewqeqe',@out_pgref_no,@out_dup_err)    

Error Code: 1449. The user specified as a definer ('root'@'%') does not exist   0.107 sec

Although I have a valid identifier in my procedure i.e. 'user001@%' but still I was not able to execute the procedure.

Cause
The procedure save_wallet_request I was trying to execute is using a DB table which involves triggers i.e. any update/insert to the table fires triggers. The further investigation cleared that trigger is using the definer 'root@%' and hence entire execution was getting failed. 

Solution
Since a non-root use cannot change the definer of root user so need to catch up a trick using below query-

set global log_bin_trust_function_creators=1;

However, execution of this query via a non-root user is not permitted directly, rather following error encountered again

You do not have the SUPER privilege and binary logging is enabled

Step1- So, the solution is to login as a root user first then execute the above SQL as

> mysql -u root -p

>set global log_bin_trust_function_creators=1;

Step 2- Now we are good to go for the delimiter changes for procedure as a non-root user as

CREATE DEFINER=`user001`@`%` and save the trigger

Step 3- Execute the procedure via user001 and that will work!