Many Rails setups use MySQL as back-end storage. So let’s set up a secure MySQL server, which will run on the same machine as Ruby on Rails and the web server. In the following we will be using MySQL version 5.0 on a Unix system.
Users
Before starting to secure MySQL, we have to install it, and therefore we create a special user and group. The MySQL server will run with these user’s privileges. The MySQL documentation strongly recommends not to run the MySQL server as Unix root user. So make sure the default “mysql” user exists, otherwise create it:
# groupadd mysql
# useradd -g mysql mysql
The installation process differs from distribution to distribution: you can either use the apt-get command to install a package or download it directly from the MySQL website. The latter requires you to run several commands by hand, including the setup of the MySQL grant tables, if you haven’t installed MySQL before:
# scripts/mysql_install_db
Mysql_install_db initializes the MySQL data directory. In most cases, however, the server and the data will be put into /usr/local, and the configuration file will be in /etc.
Ownership and privileges
Change the ownership of the MySQL binaries to root, and the ownership of the data directory to the “mysql” user:
# chown -R root /usr/local/mysql
# chown -R mysql /usr/local/mysql/data
# chgrp -R mysql /usr/local/mysql
Also make sure that the data directory cannot be read or written to by normal users. The only user with read or write privileges, should be the user, that the MySQL server runs as.
Configuration
The configuration file “my.cnf” can either be found in /etc or in /etc/mysql, you can find default configuration files in support-files/my-xxxx.cnf. Change the ownership and privileges of it to as follows:
# chown root /etc/my.cnf
# chgrp root /etc/my.cnf
# chmod 644 /etc/my.cnf
Edit it and go to the [mysqld]:
user = mysql #run the server as mysql user
old_passwords = false #use new-style passwords
bind-address = 127.0.0.1 #don't allow traffic from the internet,only local Rails allowed
Starting the server
The server daemon is the program “mysqld” or “mysqld_safe”. Mysqld_safe “is the recommended way to start a mysqld server on Unix and NetWare.” Start it:
# mysqld_safe &
MySQL users
It is good practice to revoke all privileges for any user besides the root user, and grant privileges at more specific levels. If you have access, the server will check, if you have access to the requested database, then table, column or routine. The corresponding tables for these privileges are db, tables_priv, columns_priv and procs_priv.
At first, start the MySQL client, but don’t enter passwords here, they could be revealed by the history files, especially if there are other users on the machine:
# mysql -u root -p
Set a hard to guess password for the (MySQL, not Unix) root account:
# SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
Then remove all other accounts, including the anonymous. But you should inspect the mysql.users table before, maybe it contains some users it needs, e.g on Debian there is the debian-sys-maint user, which is used to stop the server.
# SELECT * FROM mysql.user; -- first inspect it!
# DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");
Now we want to create a special “rails” user, which is used for the Ruby on Rails application. In most cases the application will only be needing privileges to add, remove, update or review data in one database.
# CREATE USER 'rails'@'localhost' IDENTIFIED BY 'password';
# GRANT DELETE,INSERT,SELECT,UPDATE ON tiger_dev.* TO 'rails'@'localhost';
Then we remove the sample database “test”, reload the privileges from the grant tables (otherwise the changes to the privileges will take effect after a restart only), and exit the MySQL client:
# DROP DATABASE test;
# FLUSH PRIVILEGES;
# exit
Finally, the MySQL history file, which holds all executed SQL commands, including your newly assigned root password, should be emptied, and set proper access rights, so no one else can read it:
# cat /dev/null > ~/.mysql_history
# chmod 600 ~/.mysql_history
To be continued…
Please post your comments.
