Uncategorized, Page 9 Category

Follow-up of your comments:

• Dan Kubb has some interesting comments about the OS security, and uses MySQL in a strict mode by using the sql-mode directive in my.cnf:

  sql-mode = ansi,traditional,no_engine_substitution,
no_auto_value_on_zero,no_dir_in_create,
no_unsigned_subtraction

• And, yes, the MySQL user I’m creating is only for “normal” Rails access, not for db migration or testing. I agree, Rails/Rake should provide means to use a different user for migration works.

Next up I’m preparing the web server section, after that the Rails specific security. Here are some links which address Rails’ security and more:

Security checklist
Security concerns in the Rails wiki
Some basic countermeasures in a Rails book
And my favorite Hacme Casino with lots of security holes
The OWASP Top 10 security flaws

Rails’ database connection
We have to update Rails’ database configuration in the project’s config/database.yml file. We have to enter both, the user name and password in the clear, so it is good advice to protect the file from unauthorized reading.

Encryption
Both, in MySQL and Rails (plugins), there are means to encrypt data. In MySQL, you can use the symmetric encryption algorithm AES with the AES_ENCRYPT() and AES_DECRYPT() functions, or the secure hash algorithm SHA1(). Rails provides the same encryption methods as plugins.
You should also consider encrypting data in transit, if the connection from Rails to MySQL goes over the internet. As we chose Rails to be on the same machine, we don’t have to think about the data in transit between Rails and MySQL, however Rails supports SSL connections to MySQL. In fact, we have to consider encrypting the data between the client (web browser) and the web server. More on this, you can find in the web server section and the Ruby on Rails section.

Logging
MySQL can create several log files in order to keep track of errors, slow queries, to log every query, or to log those statements that modify data.
The general query log records every SQL statement the server receives. It can, however, slow down the performance. If you want to use this logging method, for example, to identify a problem query, insert a ‘log’ entry, specifying the location of the log file, into the MySQL configuration file.
The binary log contrasts to the general query log, it doesn’t log statements that do not modify any data, and it logs them only after they have been executed. This logging method slows down the performance by about 1%, according to the MySQL documentation. However, you can use this log for restore operations or to replicate data. To enable this logging, insert a ‘log_bin’ entry, specifying the directory for the binary logs, into the MySQL configuration file.
Bear in mind that especially the general query log, and the binary log files may contain sensitive data, in particular user names and passwords, either of MySQL or of users of your application. Consider removing old log files (also, because they can occupy a lot of disk space), or setting adequate access rights, and encrypting sensitive data, before sending it to MySQL, i.e. in Rails.

Storage engine
MySQL provides basically two major storage engines for its tables, InnoDB and MyISAM. Storage engines differ in the way the data is saved, and both have its pros and cons.
The main advantage of the InnoDB storage engine is, that it supports transactions. Transactions are units of interaction with a DBMS, which must be either completed entirely or not at all.
InnoDB is the default storage engine for Rails’ MySQL database adapter. You will definitely need it, if you want to use transactions in Rails. See the Rails section for more on transactions in Rails. And if you want to test your Rails application the easiest and default way, you will also need transactions, because after each test the database is rolled back to the initial state, instead of having to delete and insert for every test case, which would be very costly.

The MyISAM storage engine is faster for some tasks, and provides fulltext searching capabilities, however, it does not support transactions. It is said, that MyISAM does not perform good, when there are many modifications of the data, but works fine for (mostly) static data, such as a zip code table, for example. So, if there are many modifications, InnoDB is said to be faster, because it uses row locking instead of table locking (i.e., concurrent processes can insert data into the table).

Backup
You should always back up at least your databases, and consider backing up the configuration and log files. To back up the databases you can simply copy the corresponding data files from your data directory. You could also use the binary log to replicate the data to another server, even incremental backups are possible then. Another (additional) possibility is to use the mysqldump program to create a textual backup of SQL statements. You can then compress them and put it in a safe place.

Verify setup
Before you actually use MySQL, you should at least verify the security of connections and the users. If you have a remote machine, assure, that you cannot connect to the MySQL server:

# telnet [host] 3306

3306 is the default port where MySQL runs, and this command shouldn’t give you access to the server, as we banned any connections from remote hosts.
On the local host try connecting with imaginary user names or with no password:

# mysql -u xyz
# mysql -u root -p


Then access it with the rails user and try some statements, which you shouldn’t be allowed to:

# mysql -u rails -p
# UPDATE user SET user="dbadmin" WHERE user="root"; # not allowed
# SHOW DATABASES; # should return only “information_schema” and your database

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.

What’s happening here?

It’s about Ruby on Rails, it’s about the technologies around RoR and it’s

about its security. It might turn out to be about the fast growing role of

LARM. (Yes, I invented this term right now, it’s in the style of LAMP, which

stands for the architecure of Linux + Apache + MySQL + PHP. So cross out PHP,

put in RoR, stir it well and you get: Linux + Apache + Ruby on Rails + MySQL = LARM.

Of course Apache might require some extra modules and RoR has in fact two R’s,

but hey, sounds good.)

Security

Why is security so important, you might ask, when you’re only programming this little

web shop for your neighbour’s death-metal band? Well, it’s to make life harder

for crackers or script kiddies who can do a lot of nasty things with your web site. They could deface (i.e. change parts of your web site) to harm others (e.g. get hold of

the accounts of your customers, distribute worms and so on) or simply down your web site.

Don’t panic

But don’t worry, with a little more understanding and security advices you can

make your web site a secure zone. And that’s what we can learn here together.

I’m doing a several-months-every-day-research into security of Ruby on Rails.

And I thought the results might be not only interesting for me.

Let’s do it

I think many of us share the perception of Rails being a “secure” framework.

And that might well be true, because we need less code to get things done and less

code means a better overview of what’s happening.

But though Rails seems to be safer, doesn’t allow us to lean back. There has

been a security bug (more detailed) in Rails last year and even in Ruby.

In fact most of the security issues with web sites or web applications don’t

necessarily stem from the programming language or framework we use, but affect

web applications in general. It’s the way we config our servers and it’s the way

we program.

Starting point

As a good starting point, I’ve found a good Ruby on Rails example, which deliberately

includes several security vulnerabilities: The Hacme Casino. Especially reading the user guide gives you a good insight on what can go wrong.

Use these links to find out more on the terms used in the user guide:

SQL-Injection

Cross Site Request Forgery

Onwards

I will most likely categorize the posts according to the layer. There’s the layer

of the web server, the layer of the database server and there’s the RoR application

itself. I will most like start out at the bottom with the servers.

I strongly encourage you to give me feedback by comments.