MySQL SSL Connection Using JDBC

QueryPie Development #9: Securely Accessing the Database

📑한국어(Korean Version)- https://medium.com/p/1c5da86cc2c5/

Why is Security important for a database?

Almost every company from start-ups to large corporations, uses databases to collect and store important information. But of course, there are multiple security issues in every database used.

Here is an overlook on the issues involved:

Database Security Issues

1. Database Access Information Leaks: Exposure of accessible information such as usernames, passwords and other sensitive information allows access to unauthorized third-parties.
2. Data leakage and malicious change: When access information is leaked, data in the database is leaked out or tampered with for malicious purposes.
3. Difficulty in controlling access to databases: Issues that need to be given and managed by different users, such as modifying, adding, and deleting data depending on the needs of the user

Security issues that occur in databases storing particularly sensitive personal information have a huge impact, because they significantly violate the privacy of individuals. There have been dozens of data leakage incidents that made the entire nation uneasy in the past, like the famous situations with Facebook and Amazon. So there have been growing calls for companies to ensure strict data security. Given these backgrounds, database security solutions are no longer an option but a necessity.

So today, I’d like to suggest some steps to increase the security of a database: How to connect to a SSL-based database using JDBC.

Why do we need Secure Sockets Layer (SSL)?

While surfing the web, sometimes you might run across the error message “Your connection is not private” when HTTPS is not set up locally. Even if you never heard of the term SSL, I think everyone has run into this error page at least once:

Website warning that SSL is not applied

This error commonly occurs when HTTPS (SSL) is not applied to a website that’s accessed through Chrome. You can still use the website after viewing the warning if you click the little Continue button, but if you choose to do so then you will have no security. Accessing a website that does not support SSL, your communication (sending and receiving information) with the site you’re accessing is not encrypted. So third parties may access that communication through the web server and pick off your information.

Man in the Middle Attack

It wouldn’t be a problem if it wasn’t important information. But what if someone intercepted sensitive data such as credit card numbers, account passwords and other private information? Because the information is not encrypted, third parties may steal it and exploit it. This act is defined as a Man in the Middle (MITM) attack.

However, adding a security authentication procedure to the connection between the user and the server can help prevent a 3rd party attack. For example, the TLS/SSL protocol is an authentication process based on public keys and allows applications to protect their users’ privacy by providing essential support for SSL functionality.

Does the database require SSL capabilities?

Similar to websites, SSL features are also useful when connecting to Databases. Without an encrypted connection (SSL) to the databases, query statements or data results exchanged between the user and the database can be exposed to a middle man.

Let’s think about a certain situation in which SSL is not used and the database security is vulnerable. Then let’s assume we run a simple SELECT * FROM STAFF query that shows table data containing personal information of customers while we have an unprotected connection.

Try analyzing the query performed through a packet analysis program, like the open source program WireShirk, and you clearly see the information passed between the user and the database. The image below shows an example where the SELECT * FROM STAFF query is executed by a user, and easily found by any third party with the help of Wireshirk. A situation as simple as this can lead to an attack in which personal information is leaked or queries are falsified by a middle man.

Exposed query information that users run on a general connection

But when connecting to a database with a SSL, the information users send and receive between their system and the database is encrypted. After creating an SSL connection, try using WireShirk again. You won’t be able to see the query information executed by the user since it’s encrypted now.

Encrypted query information thanks to SSL connection

Learn MySQL SSL Connections

1. SSL Mode

So let’s take a look at how to make SSL connections. Before you connect to the MySQL databases, take a look at SSL-related content in the MySQL official document. I will briefly summarize the steps explained in the official document below:

MySQL Connector/J is the driver that implements the JDBC API. It’s used in Java to connect and work with the MySQL database.

MySQL Connector/J uses SSL to encrypt all data that is communicated between the JDBC driver and the MySQL server. The following steps must be taken to enable SSL function on MySQL servers:

1. Install a MySQL server that supports SSL
2. Signed client certificates when using mutual (two-way) authentication

Connector/J sets up a secure connection with your MySQL server by default. And MySQL versions 5.7 and 8.0 that are compiled with OpenSSL automatically configure missing SSL files and configure a SSL connection on launch.

You can select SSL Mode for the SSL connection, depending on your circumstances. Using the SSL mode value, you can determine whether SSL connections are required or how to verify certificates.

Setting up SSL connections for MySQL Workbench

Certificate Options:

1. No ( sslMode=DISABLED )
   Use to make a general connection without SSL.
2. If available, default value ( sslMode=PREFERRED )
   Automatically attempt SSL connection. If the MySQL server does not support SSL, continue with a regular connection.
3. Require ( sslMode=REQUIRED )
   Always connect with SSL. If the MySQL server doesn’t support SSL, the connection will not be established. Certificate Authority (CA) and Hostname are not verified.
4. Require and Verify CA ( sslMode=VERIFY_CA )
   Always connect with SSL. Verifies CA, but allows connection even if Hostname does not match.
5. Require and Verify Identity ( sslMode=VERIFY_IDENTITY )
   Always connect with SSL. Verify both CA and Hostname.

For additional security, you can set up a one-way (client or server) or two-way (client and server) SSL authentication. When setting up two-way SSL authentications, the client validates the public key certificate received from the server and the server validates the certificate sent from the client.

2. Server Certificate Verification (a.k.a Server Authentication Settings)

MySQL Connect/J has different ways of setting up SSL connection attributes depending on the version. Server certificate verification (SCV) is different in both:

For 8.0.12 and earlier: When the connection attributes useSSLand verifyServerCertificate are set to true then SCV is enabled. But hostname verification is not supported.

For 8.0.13 and later: SCV is enabled when the SSLMode property is set to VERIFY_CA or VERIFY_IDENTITY. If SCV is enabled, the client must include a certificate (ca.pem file) when accessing through a SSL connection.

📌Setting up Server Authentication: Create Storage and Add Certificate

You can also set up private key and certificate files. But if you do, you must import the certificate into a custom Java truststore file and configuring the driver accordingly. By using Java’s keytool, you can easily create and add a certificate as shown below:

keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore -storepass mypassword

When adding a certificate list to the truststore, you must remember the ‘mypassword’ attribute is used later in the JDBC connection properties.

Please note that there are many ways to set up truststores in Java, but I’m only going to cover how to set up using JDBC connection properties.

The properties object that contains the JDBC connection properties is a data structure in the form of keys and values. First, the trustCertificateKeyStoreUrl key sets the URL value corresponding to the previously created truststore file location. The trustCertificateKeyStorePassword key sets the password value (mypassword) that you set when you created the truststore.

Properties properties = new Properties();
properties.put(“trustCertificateKeyStoreUrl”, “path_to_truststore_file”);
properties.put(“trustCertificateKeyStorePassword”, “mypassword”);

3️. Client Certificate Validation (a.k.a Client Authentication Settings)

A server can generate and verify SSL certifications and keys (client-cert.pem, client-key.pem) which can be used by any client for communication encryption.

Typically, a server authenticates the client if the client that attempts to connect has its own set of keys and a self-signed SSL certificate that the server can verify.

Some MySQL server builds can generate SSL keys and certificates for communication encryption. The SSL certificate generated by the server build is already signed by the CA authentication that the server allows, so a separate signature is not required.

If a user doesn’t want to use the client keys and certificates generated by the server, they can also make their own. But they must be imported to the Java truststore and the Connector/J driver needs to be configured accordingly.

📌Client Certificate Settings : Java Key Store Generation Method

1. Convert client keys/certificate files to PKCS#12 before creating a keystore

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \ 
-name “mysqlclient” -passout pass:mypassword -out client-keystore.p12

2. Create a Java Keystore using the client-keystore.p12 file

keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 \ 
-srcstorepass mypassword -destkeystore keystore -deststoretype JKS -deststorepass mypassword

3. Import the client key/certificate files, and configure Java / Connector/J so it reads the keystore. This by be done by using these keys: clientCertificateKeyStoreUrl(set URL value where keystore file is located) and clientCertificateKeyStorePassword (enters the password entered when generating the keystore)

Properties prpoperties = new Properties();
properties.put(“clientCertificateKeyStoreUrl”, “file:path_to_keystore_file”);
properties.put(“clientCertificateKeyStorePassword”, “mypassword”);

4️. Enable MySQL Server SSL Options

First check if the MySQL database you are using supports SSL functionary. As previously mentioned, MySQL versions 5.7 and 8.0 that are compiled by OpenSSL include scrips that automatically set SSL settings when launching the database.

After connecting to the MySQL database, check whether SSL-related options are active by running the query show variables like ‘%ssl%’. If you look at the grid in the image below, you can see that the have_ssl value is YES. This means that the MySQL database currently being accessed supports SSL.

Verify MySQL database SSL connection support is available

To enable the SSL option on the MySQL server, you must create the SSL key and set it up in the MySQL server preference file. So let’s use openssl to generate the SSL key.

1. Certificate Authority (CA) Generation

> openssl genrsa 2048 > ca-key.pem
> openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

2. Generate MySQL Server SSL Key and Certificate

> openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
> openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
> openssl rsa -in server-key.pem -out/ server-key.pem

3. Create Client SSL Keys and Certificates

> openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
> openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
> openssl rsa -in client-key.pem -out client-key.pem

4. Modify MySQL Server Configuration File
( Open the /etc/my.cnf file and add/modify the following~ )

[mysqld]
ssl-ca=ca-cert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
[client]
ssl-cert=client-cert.pem
ssl-key=client-key.pem

5. And once the MySQL service restarts, the SSL option is activated.

MySQL provides the mysql_ssl_rsa_setup utility. This makes it easy to create files needed for SSL connections and reduces entry barriers. But I recommend that you obtain certificates and keys from a registered authority because certificates generated by this method may not be very secure.

How to connect to SSL using JDBC

Based on what we’ve covered so far, let’s try to connect SSL to the MySQL database using Connector/J.

The following method was created / tested based on Connector/J 8.0.13 :)

Let’s look at an example of using two-way SSL authentication between client and server. We will set sslMode to VERIFY_CA.

1. Set the connection property value according to the SSL mode type (sslMode=VERIFY_CA)
2. Generate truststorefiles using ca.pem server certificates ( refer back to #2. Server Certificate Verification )
3. Create keystore files using client keys and certificates ( refer back to #3. Client Certificate Validation )

If you set up authentication in the order shown above, the SSL connection attribute setting code should look something like this:

Set SSL Connection Attributes using Connector/K Driver

Database Security and QueryPie

This article on SSL is actually a summary of the connection attributes of the newly prepare database tool QueryPie currently being developed by CHEQUER. I thought I was used to the concept of SSL, but it was not easy to develop it from scratch. Thankfully for me, it was a great opportunity to apply the concept of security related functions using Java (especially certificates) by studying it once again.

But database security requires not only SSL-based connection but also other functional complementary elements. QueryPie is also working hard to prevent information leaks, and to ensure that all its members can use the database safely.

And if you are wondering what QueryPie is all about, and what the developers (including myself) are doing, check here!