[SOLVED] MySQL cannot view or alter routine / procedure

Stored procedures are a powerful feature that is kind of on the fringe of MySQL, and I’ve run into many (highly infuriating) bugs when working with them.

The MySQL permissions ALTER ROUTINE, CREATE ROUTINE, and EXECUTE are used to grant users the ability to work with routines. However, when working with routines, it’s common to come across an issue where you are unable to view / edit the routine, and may get an error like this:

ERROR 1227 (42000) at line 49: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

In MySQL workbench, a bug in the GUI can even cause the entire application to crash when running into a permissions bug like this. Note that this error can occur even if your user has full permissions on the database they are trying to access a routine within.

The Solution:

MySQL stored procedures are weird. If you want to *alter* a routine, you must either be root / have super privileges, or be the user specified in the DEFINER of the stored routine.

If the user you are logged in as doesn’t fit one of these categories, you won’t be able to edit the procedure even if you have all permissions in that database. To fix the issue, log in as ROOT and change the definer of the procedure in question to the user you want to be able to alter the routine. Ta-da.

Leave a Reply

Your email address will not be published. Required fields are marked *