Enabling SSL for MySQL service

A MySQL client can establish an encrypted connection to a MySQL server. In standard configuration, a client connection is unencrypted, which can lead to data being intercepted on the way. The MySQL encryption can be done separately for each client connection, so both encrypted and unencrypted connections can be used simultaneously. It can also be configured as required for individual connections.

Generating Certificates

You need to issue certificates with 2048 bits and a validity of 3650 days. After this period, the certificates must be renewed or recreated. Depending on your requirements, you might lower the time frame for validity. 

Generate certificates

cd /etc/mysql/
# Generate CA file
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
# Generate server certifacte
openssl req -newkey rsa:2048 -days 3560 -nodes -keyout server-key.pem > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
# Generate client certificate
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
# Change access rights
chmod 400 /etc/mysql/*.pem
chown mysql /etc/mysql/*.pem

Copy client certificate

Copy the required certificate client-cert.pem and client-key.pem to the Datameer client into directory <datameer-install-path>/etc. 

Enabling SSL on MySQL Server 

Enable SSL

Modify the MySQL server configuration to activate the usage of SSL with the accompanying certificates.

[mysqld]
...
ssl=1
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Activate server config

Restart mysqld to make the configuration on the server active.

/etc/init.d/mysql restart

Check server config

Check if the configuration has been activated. 

mysql> show variables like '%ssl%';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| have_openssl  | YES                              |
| have_ssl      | YES                              |
| ssl_ca        | /etc/mysql/ca-cert.pem           |
| ssl_capath    |                                  |
| ssl_cert      | /etc/mysql/server-cert.pem       |
| ssl_cipher    |                                  |
| ssl_key       | /etc/mysql/server-key.pem        |
+---------------+----------------------------------+

Preparing the MySQL Database

There are several ways to assign rights for users with SSL:

• Require X509: Any valid SSL client certificate can be used.
• Require Issuer/Require Subject: The SSL client certificate must come from a specified CA with specific issuer and/or contain a specific subject.
• Require SSL: The connection must be established via SSL encrypted. The authentication can be done either using a password or a SSL client certificate.

In the below example, if the dap user is required to use SSL and has access to ALL PRIVILEGES for all tables in dap.*, a limitation to the localhost isn't necessary as encryption on the same server is often not required. Instead, SSL should take the IP from which encrypted access is required.

Initialize the application database

Create the database and the user. REQUIRE SSL forces the created user to use SSL.

CREATE DATABASE IF NOT EXISTS dap DEFAULT CHARACTER SET utf8;
GRANT ALL PRIVILEGES ON dap.* TO 'dap'@'%' IDENTIFIED BY 'dap' REQUIRE SSL WITH GRANT OPTION;
FLUSH PRIVILEGES;

Test configuration

Test the configuration of the MySQL service from the Datameer application server. 

mysql --ssl-cert etc/client-cert.pem --ssl-key etc/client-key.pem -udap -pdap dap -h<host>

Create tables

Create MySQL tables required by Datameer.

mysql --ssl-cert etc/client-cert.pem --ssl-key etc/client-key.pem -udap -pdap dap -h<host> < bin/create-tables.sql 

Enabling SSL on Datameer Client

To create an encrypted connection from Java to the MySQL service, you need to have a trusted certificate and make the Datameer service aware of the encrypted connection. 

Trust server certificate

The JVM needs to trust the MySQL service custom certificate. 

Add Java truststore to environment

Include truststore in your Datameer environment to make sure that the JVM is using the correct store. To do so, edit etc/das-env.sh.

export JAVA_OPTIONS="$JAVA_OPTIONS -Djavax.net.ssl.trustStore=${JAVA_HOME}/jre/lib/security/cacerts -Djavax.net.ssl.trustStorePassword=changeit"

Modify the connection URL

Make the Datameer service aware that the external MySQL service is using SSL. Edit the connection.url in persistence.xml and include useSSL=true. 

<property name="hibernate.connection.url" value="jdbc:jamon:mysql://${db.host}:${db.port}/${db.name}?jamonrealdriver=com.mysql.jdbc.Driver&amp;useSSL=true"/>

Finally, start the Datameer service.