One of your DBAs, or one of your developers creating stored procedures is gone. You want to delete his MySQL account to keep a clean user repository. It’s a good idea, but be careful all the same with certain elements related to the security of stored procedures: definer and SQL Security Invoker to avoid any unpleasant surprises.
Possible problem with the DEFINER
When you play a stored procedure on your database server, the account used to create said procedure is used as the “definer”. If you are exporting the procedure from your server, your procedure should start with the following statement:
CREATE definer = 'toto@localhost' PROCEDURE
This can be a problem, because if a nominative account was used to play the procedure, the day you delete the account in question your procedure will no longer run. And you will get messages like “The user specified as a definer (toto@localhost) does not exist”.
What solutions?
To work around the problem, several solutions are available to you:
- use SQL SECURITY INVOKER in your procedures. This statement transfers execution security not to the account that created the procedure (definer), but to the account that executes it. This is a good method to solve this particular problem but also to manage the security of your procedures.
- use a non-nominative account to play the procedures. This account must have sufficient privileges to create and run the procedure.
- Update the definers afterwards. But it’s not recommended because it forces you to update the mysql.proc system table.
The second solution is interesting. On the other hand, it takes rigor to only create the procedures via the account that allows it to be done. A good setting of the privileges will solve this problem. The first solution will fix the runtime issue. On the other hand, when uploading a dump, you may encounter a problem if the definer no longer exists.
