CREATE ROUTINE, ALTER ROUTINE, and EXECUTE were all added to MySQL in version 5.0.3 and have been available since March 2005. I am using shared web hosting that was set up with the latest version of MySQL (version 5.0.67, which came out in August 2008) and the latest version of cPanel (version 11 with the latest build from November 2008). So if I do a little math in my head I can see that sometime in the last three and a half years, the people who make cPanel became aware that MySQL added support for stored procedures and realized that they needed to add a way for people to grant MySQL users the CREATE ROUTINE permission but they still haven't done anything about the EXECUTE permission.

It is not an ideal situation.

What I would like is to have an admin user that has full permissions to do everything on a particular database -- including make schema changes and create stored procedures -- and another user that only has permission to SELECT from tables and EXECUTE routines. The user with the limited permissions is the one that I put in my web.config and use from my web application code.

So I tried granting the permissions by manually executing the command in phpMyAdmin.

This is another thing that cPanel has not done correctly. My admin user should have been set up with the GRANT permission on my_database. Now there is no way for me to give a user EXECUTE permission. It won't even work if I try using the DEFINER syntax in MySQL.

#1227 - Access denied; you need the SUPER privilege for this operation

So what do I do?

The Solution

Create the stored procedures using the account with limited permissions. MySQL keeps track of which account was used to create each stored procedure and automatically allows that account permission to execute it.

Here is a step by step guide to the process.

Go into cPanel and check the box for CREATE ROUTINE for your my_user account.

Upload a script to your website that will create your stored procedures using my_user. Here is a script that you can use.

But this means that the user creating the procedure has to have the permissions to do whatever is inside the procedure...

If you want to use procedure for security (imagine that your user can only execute procedures created by the "super-user" but cannot do DELETE or UPDATE, for example) you cannot do it. The creator of the procedure can no longer have more previleges than the executor. Ant this makes it impossible to use procedures as an intermediate layer to avoid SQL injection.