MySQL stored procedure permissions and cPanel

To set up my site I created all my MySQL tables and stored procedures by running a script in phpMyAdmin, and then I deployed my application files. This resulted in the following error:

execute command denied to user 'my_user'@'localhost' for routine 'my_database.MyProc'
Description: HTTP 500. Error processing request.

Stack Trace: 

MySql.Data.MySqlClient.MySqlException: execute command denied to user 'my_user'@'localhost' for routine 'my_database.MyProc'
  at MySql.Data.MySqlClient.MySqlStream.OpenPacket () [0x00000] 
  at MySql.Data.MySqlClient.NativeDriver.ReadResult (System.UInt64& affectedRows, System.Int64& lastInsertId) [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet () [0x00000] 
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult () [0x00000]

The problem is that cPanel only allows you to grant permissions for a subset of actions.

 SELECT                       CREATE
 INSERT                       ALTER
 UPDATE                       DROP
 DELETE                       LOCK TABLES
 INDEX                        REFERENCES
 CREATE TEMPORARY TABLES      CREATE ROUTINE

The key one that they inexplicably did not add is EXECUTE.

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.

GRANT EXECUTE ON my_database.* TO my_user@localhost;

It didn't work.

#1044 - Access denied for user 'my_admin_user'@'localhost' to database 'my_database'

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.

DELIMITER $$

DROP PROCEDURE IF EXISTS MyProc$$

CREATE DEFINER = my_user@localhost PROCEDURE MyProc()
BEGIN

-- <procedure body>

END $$

DELIMITER ;

I get an error.

#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.

1. Go into cPanel and check the box for CREATE ROUTINE for your my_user account.
2. Upload a script to your website that will create your stored procedures using my_user.
   Here is a script that you can use.
3. Call your script from your browser.
4. Remove the script from your website.
5. Go into cPanel and uncheck the box for CREATE ROUTINE.