Page Comparison

...

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 

...

[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

...

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.