mysql - Change the stored procedure definer

Question

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 definer 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'

Answer 1

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.