Feb 28 2021
Change Procedure/Function Security Type and DEFINER
Mysql procedures and functions security type is set as DEFINER which is the default value, as described in the “Create Procedure and create function chapter” on mysql.com.
Why one needs to be cautious? Anyone with EXECUTE privilege can run the procedure or function with the DEFINER permissions. This might not be what you want.
An error may be raised when someone tries to run the procedure/function which definer has been deleted
ERROR 1449 (HY000): The user specified as a definer (‘definer’@’localhost’) does not exist
As a result, you may have to change definer and/or security type on a lot of procedures and functions at once.
It is interesting to note that a missing user doesn’t bother Mysql while dumping and restoring unlike views that raise an error.
First off, you can have a quick glance at your procedures and functions with the 2 basic:
SHOW FUNCTION STATUS;
SHOW PROCEDURE STATUS;You may add a LIKE ‘my_proc’, or WHERE Db LIKE ‘my_database’ for filtering out results
Now, it is always possible to change definers and security type with Mysql Workbench, or recreating them in SQL but your best bet is to change them all at once in command line (narrow update using the WHERE clause to apply to some):
UPDATE mysql.proc SET security_type='INVOKER'
WHERE security_type='DEFINER';
You can also update DEFINER as well with the following query:
UPDATE mysql.proc SET definer='root@localhost'
WHERE NOT definer='root@localhost';