Definer in Mysql Stored Procedures and Routines

The definer is a mysql security tactic used for executing mysql stored procedures. If you’re coming from an SQL Server world it’s similar to the schema that owns the SP. In other words when a definer is set it gives the user executing the SP the same perms as the definer in the context of the SP. This is much easier to explain by example.

Suppose you have a sp that runs a select on Table1. The definer must have select permissions to table1 while the user executing doesn’t need it. When the user is executing the sp they inherit the permissions as the account specified as the definer. This allows your application security to be setup so that application users only need to have execute perms on the SP’s and not perms to underlying objects.

Often when developing a SP this option is left blank and by default it is set to the user that created the sp. The problem with this is that when the SP gets pushed to production, the developer account doesn’t exist, and the SP won’t execute.

There are two workarounds here, the first is to create a standard user with the perms required to access the underlying objects in delivery. Also have this user in Test/Dev/Stress (Different Password then in production) and have this user as the definer. This is of course is the optimal solution, but requires quite a bit a work in developing your security infrastructure. The hack which works while you’re developing this infrastructure is to run the following statement in delivery after release.

update proc set definer='Defineruser@localhost';

This changes the definer to an appropriate user.