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.

Xml Deserialization: a home grown method

Download source code Download source code
If you have ever called System.Xml.Serialization.XmlSerializer.Deserialize and got this exception: System.InvalidOperationException: '' was not expected, you know that getting the xml namespaces right for deserialization can sometimes be a trial and error process.

If you have access to the class you want to deserialize to you can add a custom attribute to define the xml namespace to use for serialization.

[System.Xml.Serialization.XmlTypeAttribute(Namespace="MyNamespace")]
public class MyClass {
    ...
}

If the class is in one of the built in libraries or in a third party library and you can't modify it you can modify the xml to add a namespace attribute instead.

<MyClass xmlns="MyNamespace">

Another alternative is to use different deserialization code that is more forgiving about things like xml namespaces and case sensitivity. I have created one that uses the XML DOM parser System.Xml.XmlDocument and System.Reflection to iterate through the public fields and properties of an object and find the xml elements and attributes that best match the field names.

// simplified snippet
public static T XmlStrToObjBestWeCan<T>(string xml) {
    System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
    doc.LoadXml(xml);
    foreach (System.Reflection.FieldInfo field in typeof(T).GetFields()) {
        foreach (object attribute in field.GetCustomAttributes(typeof(System.Xml.Serialization.XmlElementAttribute), false)) {
            ...
        }
        foreach (System.Xml.XmlNode child in doc.DocumentElement.ChildNodes) {
            ...
        }

Download complete file

You can download the code and include it in your project to see how it compares to the built in deserializer.

ASP.NET 1.1 and 2.0 on Mono

One of the first problems I had when setting up my ASP.NET site on Linux and Mono was that the server I was using was configured to use ASP.NET 1.1 instead of 2.0. This showed up as an error in my Page directive about the Title attribute.

Parser Error

Description: Error parsing a resource required to service this request. Review your source file and modify it to fix this error.

Parser Error Message: Unknown attribute: Title

Source Error:

Line 1: <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>

So I removed the Title attribute and I got new error.

Parser Error

Description: Error parsing a resource required to service this request. Review your source file and modify it to fix this error.

Parser Error Message: Unknown attribute: MasterPageFile

Source Error:

Line 1: <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" %>

---

Version Information: Mono Version: 1.1.4322.2032; ASP.NET Version: 1.1.4322.2032

That was when I noticed the version number at the bottom. It 1.1 instead of 2.0. Just to make sure that Mono wasn't using a versioning system that was different from Microsoft's I created a simple page that used a common .NET 2.0 feature.

<%@ Page Language="C#" %>
<html>
<head><title></title></head>
<body>
<div>System.Version = <%= Environment.Version.ToString() %></div>
<div>List<string>.Count = <%= (new System.Collections.Generic.List<string>()).Count.ToString() %></div>
</body>
</html>

It got a compilation error.

error CS1644: Feature `generics' is not available in Mono mcs compiler. Consider using Mono gmcs compiler instead

Once I knew what the problem was I had to figure out how to fix it. I found a description of what to do here. You have to change the MonoServerPath setting from <path>/mod-mono-server to <path>/mod-mono-server2 in the config file that contains it. This may be mod_mono.conf or httpd.conf or some other file. I am not sure where it is commonly placed.

I am using shared web hosting so I don't have access to change or even view those files, so I contacted my web hosting company (Ubiquity) and asked them to change it. They had me try changing it in my .htaccess file first, which I tried and found that it didn't work. The commands to start up the mod mono server so that it can handle ASP.NET requests must happen in a certain sequence in the config files so tacking it on after startup in an .htaccess file didn't work. It caused HTTP 500 errors for any requests to my directory. It may be possible to set up a fully working sequence of mod mono startup commands in an .htaccess file in order to override the ASP.NET version for a particular directory. I tried a number of different combinations while I was waiting for my hosting company to change it at the server level but none of the things I tried worked.

They wanted to change it at the server level anyway because they advertise supporting ASP.NET 2.0 and I think that is how they had intended to set it up. I suspect that the commonly downloaded version of Mono is still set up to use ASP.NET 1.1 by default instead of 2.0. If that is the case they should change that so people who install it don't have to go find the setting and change it themselves.

Setting up my site on Mono

I got my MySQL database set up through cPanel and PhpMyAdmin without any difficulty. I maintain re-runnable setup scripts for all my tables and stored procedures which makes life easier.

I got FTP set up by downloading WinSCP for my FTP client and setting up a key in cpanel so I could use SFTP. Then I started uploading some files.

I set up a file deployment batch file which would create a releasable package for easy copying. Then I uploaded all my web site files and tried it out.

It didn't work. The problem was that the server was configured to use ASP.NET 1.1 instead of 2.0. (See more about what I did to identify and fix this problem here.)

I opened a support ticket with my web hosting company (Ubiquity) and exlained the situation to them and asked them to change it at the server level to use ASP.NET 2.0 by default. They did, but it took them a couple days to do it and get it working. I am guessing that most of the people that use their shared web hosting are not using Mono because it seemed like the support guys hadn't run into this issue before. If anyone else runs into it now they will probably be faster at fixing it, or better yet, they may change their default setup for new servers so that it works the right way automatically.

Once the ASP.NET 1.1 / 2.0 issue was resolved I uploaded my main project to my directory again and gave it another try. That was when I encountered the next problem: my stored procedures I created in MySQL were not working. I'll put up new blog entry at some point explaining what the issue was there and how I resolved it. Stay tuned.

Choosing a web hosting provider

There are not too many companies that are offer shared web hosting with Mono. One of the ones that does is Ubiquity and that is who I decided to go with. Their price was pretty good at $5.95 a month for a one year commitment with a 45 day money back guarantee plus free domain registration. That is $71.40 up front.

I liked their feature set and the comments on the web about them seemed mostly favorable.

After I went through their sign-up process I was excited to try it out and start playing around with it but when I tried the IP address they gave me it was not set up yet. I kept trying over the next couple days. I looked through their website to find out how long it would take and I saw that it could take up to 72 hours for some set ups and if it still wasn't set up after that to enter a support ticket.

So I entered a support ticket and a few hours later someone got back to me and said it was working fine for them. I emailed them back that I was still unable to access it and that ping and tracert were not working. A few hours later they emailed me back and said my IP address had been blocked on their firewall and that it should be working now.

Doh. I should have opened up a ticket sooner.

It was still a ways away from getting things working at that point though. Read more.

Adventures in Mono

Last week I got my TwixT website working well enough that I decided it was time to share with the world. I developed it in C# and ASP.Net, with a MySQL database and a Silverlight 1.0 app to for interactive game play.

The idea behind the website was to make a place where people can play TwixT each other interactively, or play against the computer.

I had it working pretty well on my local computer but to go live with it I needed a web hosting provider. The first thing I needed to decide was whether to choose a Linux or Windows based hosting. I decided to go with Linux because it was a little cheaper and from what I had read about Mono, it had almost all of the features in .Net 2.0, plus I thought it would be interesting to try.

Read more about how I chose a web hosting provider and tried to get my site working on Mono.