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.
It is important that the common name (CN) for client and server certificate are different. For example, |
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.
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 the required certificate client-cert.pem
and client-key.pem
to the Datameer X client into directory <datameer-install-path>/etc
.
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 |
Restart mysqld
to make the configuration on the server active.
/etc/init.d/mysql restart |
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 | +---------------+----------------------------------+ |
There are several ways to assign rights for users with SSL:
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.
If you have already intialized the Datameer X application database and created the tables or a Datameer X installation in use, than you might need to change only the granted privileges. |
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 the configuration of the MySQL service from the Datameer X application server.
mysql --ssl-cert etc/client-cert.pem --ssl-key etc/client-key.pem -udap -pdap dap -h<host> |
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 |
To create an encrypted connection from Java to the MySQL service, you need to have a trusted certificate and make the Datameer X service aware of the encrypted connection.
The JVM needs to /wiki/spaces/DASSB70/pages/33036120763.
Include truststore
in your Datameer X 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" |
Make the Datameer X service aware that the external MySQL service is using SSL. Edit the connection.url in persistence.xml
and include useSSL=true
.
As of Datameer7.4.x, the location of the file has changed. It is no longer in <Datameer X installation dir>/webapps/conductor/WEB-INF/classes/META-INF. |
<property name="hibernate.connection.url" value="jdbc:jamon:mysql://${db.host}:${db.port}/${db.name}?jamonrealdriver=com.mysql.jdbc.Driver&useSSL=true"/> |
Finally, start the Datameer X service.