Minimum MySQL Privileges for Rails

Every time I go to setup a dedicated user in a MySQL database server to support a Rails application I find myself searching the web for the privileges that Rails needs. By the way, if you are hooking to a MySQL database in your Rails application and using the root account to connect the app to the database, you are doing it wrong.

I’ve decided to document them here with a snippet of SQL for easy reference. Below are the minimal necessary privileges needed by Rails to do its thing. I built this list during Rails 3 but I’m not aware of Rails 4 needing any privileges beyond what is below either so it should work as of Rails 4.

If your Rails application does anything out of the ordinary additional privileges may be necessary.

Below is how you might setup a user on your MySQL server from the MySQL command line.

CREATE DATABASE cutepuppies_production;
CREATE USER 'cutepuppies'@'localhost';
SET PASSWORD FOR 'cutepuppies'@'localhost' = PASSWORD('poopingonyourcarpet');
GRANT Select,Insert,Update,Delete,Create,Drop,Index,Alter,Lock Tables ON cutepuppies_production.* TO 'cutepuppies'@'localhost';

If your database is running on a separate server from your application (and hopefully it is) you should substitute ‘localhost’ for the IP or hostname of your application server(s). If you have many application servers it might be worth the security risk to just use a wildcard (%) for the hostname part, see the MySQL documentation for more info.


Now read this

Installing wkhtmltopdf on MBP OSX Mavericks 10.9

After spending most of my work day evening installing wkhtmltopdf on my MBP I feel it necessary to at least document my decisions and what actually worked. I try to use Homebrew for everything I possibly can that does not come with and... Continue →