Menu
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'
380
How to change the definer of a Stored Procedure? The definer of a Stored Procedure cannot be directly altered. To change it, you will need to recreate the procedure with the desired definer.
In this stored procedure, the definer is root@localhost that is the superuser which has all privileges. The SQL Security is set to the definer. It means that any user account which calls this stored procedure will execute with all privileges of the definer i.e., root@localhost user account.
Show activity on this post. 2) Open triggers. sql file in your favorite editor and use Find and Replace feature to change DEFINER s. Save updated file.
To identify which DEFINER values exist in each table, use these queries: SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA. EVENTS; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA. ROUTINES; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.
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.
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
