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.
- Select
- Insert
- Update
- Delete
- Create
- Drop
- Index
- Alter
- Lock Tables
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';
FLUSH PRIVILEGES;
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.