Wednesday, November 26, 2008

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.

3 comments:

Anonymous said...

Hi,
I know this comment may be a little late but I had to post it anyway.

Thank you very much, I was stuck with this problem and couldn't find a solution for it anywhere even with the technical support until I found this ingenious solution of yours.

Thank you

Mohamed

Anonymous said...

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.

Damn!

Vinh Khoa said...

I also came up with the same solution today as well. I think cpanel is quite sh*t and the fact there is no way for you to run the GRANT EXECUTE command is just a shame.

Good post, I did bookmark it just in case I forogt how to do this in the future :-)

Thanks,
Khoa