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 ;