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'

score 112 · Answer 1 · edited Aug 19 '11 at 01:58

112

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.

edited Aug 19 '11 at 01:58

Brad Mace

27,194
17
102
148

answered Mar 04 '11 at 07:16

gibzaki

1,153
1
7
3

1

"show procedure status" doesn't work for me, but the update does work. Thanks. – James McMahon Nov 07 '11 at 19:45
By default DEFINER is creating when i create store procedure in godaddy shared host under mysql db. Sample: CREATE DEFINER=`sis`@`%` PROCEDURE `SP_Login`() How to remove DEFINER? Please help me to solve this issue i am breaking my head for last 2 days. – Sensa Apr 24 '12 at 10:37
this saved me after a user cleanup – deltree Oct 11 '12 at 02:49
13

Down voted because direct manipulation of the table are not noticed by the server. (http://dev.mysql.com/doc/refman/5.5/en//stored-routines-privileges.html) – KDrewiske Mar 21 '13 at 17:20
While it is unclear whether or how the server stores or accesses the definer, (thanks KDrewiske), it was still a resource for both the command (show) and the table. Knowing what is wrong is 80% of the battle. – Gerard ONeill Sep 10 '13 at 16:48
`UPDATE` changed the table, but `mysqldump` doesn't see it: `mysqldump: Got error: 1449: The user specified as a definer ('root'@'%') does not exist when using LOCK TABLES`. Even after restarting `mysql`. I'm running `mysql-5.5.31`. – x-yuri Apr 11 '14 at 18:36
1

My bad. I had views with a wrong definer either. `update mysql.proc` approach indeed works. – x-yuri Apr 11 '14 at 21:16
@KDrewiske And what are the consequences to manipulate the table msql.proc manually? Can you explain with details? Despite contraindicated, the solution is useful. – ruzenhack Jul 03 '17 at 22:28
@ruzenhack, theoretically `flush tables` would work, my question is why the alias since it isn't used? – user10089632 Mar 10 '18 at 14:35
4

Thanks @bradmace but actually you can refine your SQL with and db = 'my_database' so the complete SQL will be UPDATE `mysql`.`proc` p SET definer = 'user@%' WHERE definer='root@%' and db = 'my_database'; – user3856437 May 18 '18 at 14:15
Brief update: it seems that @KDrewiske 's point only applies to mysql 5.5 to 5.7. The version 8.0 documentation no longer mentions this point (https://dev.mysql.com/doc/refman/8.0/en/stored-routines-privileges.html) – benvdh Nov 07 '19 at 10:37
SHOW PROCEDURE STATUS may be MariaDB specific – TimSparrow Apr 15 '20 at 13:48
This works only after database is restarted. Is there any way to make the server notice the change without restart (similar to "flush privileges" after modifying mysql.user table)? – Abhijit Buchake Jun 03 '20 at 09:06
Any ideas of what the implications are of manually updating the proc table in a Galera cluster setup? Is it safe to do so or will it break the cluster? – agrajag_42 Dec 28 '21 at 12:48

score 17 · Answer 2 · edited Dec 06 '17 at 20:18

17

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

edited Dec 06 '17 at 20:18

Sean Bright

118,630
17
138
146

answered Oct 05 '14 at 17:49

Darshan Patil

171
1
5

What is "t_p" in this case? – posfan12 Jun 28 '18 at 07:32
1

@posfan12 `t_p` is the name of the Stored Procedure. – Sudar Dec 19 '19 at 17:41

score 8 · Accepted Answer · answered Jul 08 '10 at 18:40

8

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.

answered Jul 08 '10 at 18:40

Fosco

38,138
7
87
101

score 3 · Answer 4 · answered Apr 11 '14 at 20:41

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.

score 0 · Answer 5 · answered Nov 28 '19 at 17:06

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.

score 0 · Answer 6 · answered Aug 14 '21 at 05:19

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

score 0 · Answer 7 · answered Dec 20 '21 at 09:50

0

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

answered Dec 20 '21 at 09:50

Armature

7
3

score 0 · Answer 8 · edited Mar 19 '23 at 18:27

0

You can do it also in this way:

update mysql.proc set definer = "root@localhost" where db = "mydatabase";

edited Mar 19 '23 at 18:27

astasiak

790
1
6
13

answered Mar 13 '23 at 18:24

user8629109

1

score -3 · Answer 9 · answered Apr 29 '13 at 11:31

-3

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

answered Apr 29 '13 at 11:31

Sam

1,124
1
8
12

1

what an unusual way – Shao Khan Jul 04 '17 at 06:50
Not a MySQL syntax. – Abhijit Buchake Jun 03 '20 at 09:06

Change the stored procedure definer

9 Answers9

Linked