Data Scientists toolbox: Securing MySQL connection

Once we have setMySQL_Cloud up our cloud, the next logical step is to create some form of data storage. One possible choice is a MySQL database. 

In this article, we will have a look at two ways how to secure your MySQL instance in the cloud, while allowing access from the outside. As a result you will be enable to access the database in the cloud using the standard tool MySQL Workbench.

Port forwarding

The first way of securing your MySQL instance is using a concept called port forwarding. Basically we are using the standard SSH connection we use to connect to the cloud for the data transfer as well. SSH is encrypted and therefore all the data traffic is encrypted as well. The advantage of this method is that we don’t have to set-up SSL certificates for the MySQL instance and we are not giving access to the instance from the outside of the cloud (no firewall rules have been added). However the disadvantage is that we have to provide the user with an account on the cloud as well as an account on the MySQL instance. So in some situations it might be more convenient to create the SSL certificates.

Port forwarding works by defining a source port on the side of the computer, which we are using to access the cloud and a destination port in the cloud where the service we want to access is running (the default port for MySQL is 3306). The number of the local port can be arbitrary, just make sure that the port is not being used (generally select a a high number for the port number, as those are not reserved).

port_forwarding

The configuration in putty for port forwarding is under “Connection -> SSH -> Tunnels” and we want to create a local forwarding rule. In our case we have chosen the port 9998 on our local machine. The configuration would look like this:

port_forwarding_putty

NOTE: In the destination we have selected the loopback IP address (127.0.0.1) and not the cloud IP address, because the cloud IP address is already defined in the configuration of the session and we only want to change the port on the instance we are already connected to.

And similarly we have to set-up the connection in MySQL Workbench to the selected port on the local instance (our selected port 9998).

workbench_port_forwarding

To be able to connect through putty and port forwarding an active putty session has to exist.

SSL connection to MySQL

In the second option of securing a connection to an instance of MySQL we are using SSL certificates. SSL certificates are used for the encryption and decryption of the traffic. In comparison to using SSH we are now directly connecting to selected port on our cloud instance. The advantage of this solution is that the user doesn’t require an account on the cloud, but only a MySQL account. On the other hand we have opened up the port to outside connections (we can limit this by providing a specific IP address in the firewall rule, so that users can connect only from the specified IP address).  To be able to use a secure connection we have to do the following steps:

  1. generate SSL certificates
  2. add certificates to server as well as client side
  3. change the configuration of MySQL to accept connections from the outside
  4. open the required port in the cloud firewall

If you are using a version of MySQL prior to 5.7.5 then you have to use openSSL, we will not cover this case here (but you can find articles describing the generation step-by-step). So from 5.7.5 MySQL comes with a ssl utility (NOTE: the utility generates only self-signed certificates), which makes it much easier to use in comparison to previous version, where you had to convert the formats of the certificates generated by openSSL.

Generate the certificate:

Run the command mysql_ssl_rsa_setup from the unix shell. The command automatically checks for the existence of certificates and generates them if they do not exist.

Add certificates to server and client side:

In a standard Ubuntu (14.04) the certificate files are generated into the default location /var/lib/mysql , where mysql automatically uses those certificates. Otherwise we have to specify the data directory in the mysql_ssl_rsa_setup.

In the same location we can find the required certificates for the client side:

  • ca.pem
  • client-cert.pem
  • client-key.pem

We copy those files to our computer serving as the MySQL client. In case we use Workbench we edit the existing connection to support SSL connections.

workbench_sslTo ensure that no other connections than SSL connections are allowed, we have to edit the settings for the user in the mysql.user table in MySQL (Otherwise we can specify the allowed connection type in the connection in MySQL Workbench, however then the control if the connection is secured is on the user side).

Change the MySQL configuration

Set the bind address in the my.cnf file (in Ubuntu 14.04 defaultly located in /etc/mysql/) to 0.0.0.0 in order to accepts connections from the outside. For the change to take effect, we have to restart the MySQL server.

Open the required port

Opening the port may depend on the cloud provider and firewall you use. For example for Google compute engine and Amazon Web Services we have to open the ports using their interfaces. While other providers such as Digital Ocean don’t provide an API and their own firewall. In that case on Ubuntu 14.04 “ufw” is standardly used. To add the condition to open up our port (MySQL defaultly runs on port 3306), use the following command:

ufw allow 3306/tcp

Check if SSL is available

SHOW STATUS LIKE 'Ssl_cipher';

If an SSL connections is available, the query returns something like:

Ssl_cipher DHE-RSA-AES256-SHA

And that’s how it is done!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s