MySQL, CFQUERY, and creating a user

Awhile ago I had worked on some code on our back end servers in which I needed to setup some databases through Coldfusion, and for security reasons I needed to set up a new user for each database that we created. So I went ahead and used CFEXECUTE and ran a nice little batch file each time I wanted to create a database. This worked excellent until we decided to run similar code on our production servers, in which the mysql server was seperate from the Coldfusion server. The goal was to create a create multiple databases in mysql and give each one their own username and password. Sounds easy, and, well, guess what? It is in Coldfusion!

One way of doing it, I found out from browsing around the internet, was to create a share over the network. I could create folders very easily for MyISAM databases (not so easily with Inodb), and mysql would recognize it as a new database. Setting up a user was a little more complex as I had to somehow create a batch file and then call it remotely. Now that's all fine, if you don't give a crap about security. You see, sharing files and opening ports is a big risk, especially if you are passing information that could grant a hacker access to all your mysql databases.

So I looked around the internet and I only found hints at a solution to my dilema, but it was enough to get an idea. The one example I did find was actually incorrect, and the person that had posted the question about why it was not working had not received an answer. So I decided to experiment, and I quickly found out how to accomplish what I needed to do: setup a database on a server within my network where the MySQL server wasn't on the same machine as the CF Server.

First, to create a database in Coldfusion using CFQUERY you need to have root access to your mysql database server. For those of you on shared hosting, you are out of luck because you aren't going to get that username and password! But if you have root access you can quickly create a database like this:

[cfquery name = "createdb" datasource="avalid_datasource" username="myrootuser" password="myrootpassword"]
CREATE DATABASE mydatabasename
[/cfquery]

Now of course, you would want to put some kind of error capture in around this or you will risk exposing your root username and password to someone if an error occurs and you have robust exemptions turned on.

Next, for security reasons mostly, I wanted to setup a username and password for each of my databases that I made. This is how I accomplished this:

[cfquery name = "createdb" datasource="avalid_datasource" username="myrootuser"]
GRANT SELECT, INSERT, REFERENCES, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON mydatabasename.* TO 'newusername'@'%' IDENTIFIED BY 'newpassword'
[/cfquery]

Note that it is important you specify the root username and password in order to perform these actions or you will get an error.

Hope this helps someone out there! :-)