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

ActiveAdmin Form Field For Strongbox Encrypted Attribute

If you are using ActiveAdmin or formtastic (which ActiveAdmin uses under the hood) and are having trouble rendering a form with an encrypted attribute like with Strongbox the solution is very, very simple. Perhaps though it is not so... Continue →