Change the stored procedure definer

I have a around one hundred stored routines in my MySQL database with with most of them have 'root' as the definer.

I have another mysql account named 'abc', how to change the de开发者_开发百科finer of all routines to 'abc'.

Is it possible to do the same if I have access to MySQL server only as 'abc' user and not as 'root'

To see the definers:

show procedure status;

You can change them like this:

UPDATE `mysql`.`proc` p SET definer = 'YournewDefiner' WHERE definer='OldDefinerShownBefore'

For example:

 UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%'

Be careful, because this will change all the definers for all databases.

You can update the definer in mysql.proc table against the required table/db...

update mysql.proc set definer='root@localhost' where name='t_p';

As far as I know: You would have to get the script text of them all and drop/recreate them as abc. You would not be able to do this as abc unless abc has permission to drop/create procedures.

Export all the procedures of the database <DB>:

mysqldump --single-transaction --no-data --no-create-info --routines \
    --skip-triggers <DB> >routines.sql

Edit routines.sql and recreate them:

cat routines.sql | mysql <DB>

Specify -u and -p switches if necessary.

I've done mass Definer changes by exporting the database, opening the exported file in a text editor (not a word processor) and doing a find and replace trading the new Definer for the old. I then rename the original database and create a new database with the original database's name. Just import the modified export file with the new Definer information and you're done. I like this solution because it gives me a fallback if things go sideways. If something doesn't work, drop the database you created and rename the copy.

i had this problem finally solved it using below method. i got this error: 1449 while inserting the data into table. solution: go to triggers option there must be some driggers difined by user before drop them from there. that's it you got the solution now you can make operations on table in phpmydmin

instead use a simple method, create a user (abc) , the grand that user all permissions and in the connection string, connect

with abc. will work finer, the mysql version 8 does not support mysql.proc

alter procedure old_proc_name
delimiter //
create PROCEDURE new_proc_name (parameter)
begin
 Your Sql Statement;
end//
delimiter ;