Secure MySQL Connection

Before you begin...

Okay, a quick bit of history first. I used to have a Hibernate module at this point. Worked pretty well. Then Red Hat bought it and merged it into the JBoss Tool Set. I'm sorry, they really messed things up. I tried twice to see if I could include the Hibernate stuff into Eclipse and failed both times. Hibernate is interesting, and they plan on coming up with a Persitent Storage course which may cover it, but I promise you that you would be overwhelmed with trying to install it at this point.

So, what next? Well, one thing that always bugged me is that when we connected to the MySQL server, we did it over an un-encrypted connection. Now if the server is on the same machine as you are on, that isn't a big deal, but if you are connecting over a network, that is a reall security problem.

Every vendor is going to have a different procedure on how to do this, but we will at least walk through MySQL to see how it is done there. Once done, you will have an encrypted connection to the database, which is how it should be done in the "real" world.

Where to start

As with any encrypted connection, you typically have a public and private key to make things work. You give out the public key, you keep the private key hidden away.

Now in older versions of MySQL, you had to actually generate the keys. With version 8, then come already generated and "almost" ready to go. Just to cover things properly, let's talk about where you find the keys.

First, you should know that for this exercise, If you were to look in /var/lib/mysql on a Unix/Linux server you would see the following files:

auto.cnf
ca-key.pem
ca.pem
class
client-cert.pem
client-key.pem
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
ibtmp1
mysql
mysql.ibd
performance_schema
private_key.pem
public_key.pem
server-cert.pem
server-key.pem
sys
web-host-name.err
web-host-name.pid

I'm sure you notice several *.pem files, these are various keys that can be used by the server and clients to make secure connections. Now a little trial and error showed me that the client-key.pem file which is used to connect to the server comes configured with a key-phrase, which you don't want.

A little magic, and we can remove the phrase so we can connect with our clients, like so:

root@yourserver:/var/lib/mysql# openssl rsa -in client-key.pem -out client-key-nopassw.pem

At this point, you can use the following files to make a securie connection using your MySQL workbench connection, if you specify the SSL option:

Use SSL: Require
SSL Key File: client-key-nopassw.pem
SSL CERT File: client-cert.pem
SSL CA File: ca.pem

Building the Client

Okay, now how about trying to figure out how to connect with a a Java program to MySQL using an SSL connection. Well, as you would have it, you need to first use that "ca.pem" file that was incuded in the above tar bundle. Ah, it would be simple if you could use it directly, but to use it with Java you need to define a keystore (in this case they call it a "truststore") that contains the key.

So, next step on our secure journey is to create the keystore.

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

Where MySQLCACert is any arbitrary name you want, the ca.pem is from the certs above "trustore" is an abitrary name that you pick, but we'll use "truststore" and the "mypassword" is the password you assign to the truststore. Apps will use this password to connect to the MySQL server.

You'll need to use the keytool program that was installed with Java to build your "truststore"

Now that you have a truststore, you need to set up your JDBC connection slightly differently. Since we are using a more modern MySQL server on web9, and since you have the latest Java version, we can now do something like this:

 public static final String DATABASE_USER = "user";
 public static final String DATABASE_PASSWORD = "password";
 public static final String MYSQL_AUTO_RECONNECT = "autoReconnect";
 public static final String MYSQL_MAX_RECONNECTS = "maxReconnects";

 private static final Properties connProperties = new Properties();
   private static final char[] JKS_PASSWORD = null;
   private static final char[] KEY_PASSWORD = null;
   private static final String HOME="/workspaceMint18/SecureMySQL/src/com/beartoothmountain/";
   // things to do when we first start up this class
   static {
   // default is to use the development database, only if we are on the production server will we change this
   String hostname = HOST;
   
   // and the URL we connect to the database with
   url = "jdbc:mysql://" + hostname + ":3306/";

   connProperties.put(DATABASE_USER, "loginname");
   connProperties.put(DATABASE_PASSWORD, "loginpassword");
   
   // set additional connection properti?serverTimees:
   // if connection stales, then make automatically
   // reconnect; make it alive again;
   // if connection stales, then try for reconnection;
   connProperties.put(MYSQL_AUTO_RECONNECT, "true");
   connProperties.put(MYSQL_MAX_RECONNECTS, "4");

//		 now the ssl stuff
   connProperties.put("useSSL", "true");

   connProperties.put("verifyServerCertificate", "false");
   // Setting the truststore
   connProperties.put("trustStore", HOME + "truststore");
   connProperties.put("trustCertificateKeyStoreUrl", "file:/" + HOME + "truststore");
   connProperties.put("trustCertificateKeyStorePassword", "mypassword");
   connProperties.put("serverTimezone", "US/Eastern");
   

What we are doing here is setting up a Properties object, and then putting connection information into that Object that will be used when we connect.

Oh, and you may notice that little "serverTimezone" entry. This is an idiosyncrasy that exists in later version of the MySQL server that ensures that Timestamp values are correct. If you don't set it, it won't let you connect to the server.

Now when you try to make the connection to the MySQL server, you use the Properties object like so:

 try (Connection conn = DriverManager.getConnection(url + db, connProperties);
      Statement statement = conn.createStatement()) {

Note that the getConnection() method uses the url + db information, plus the connProperties object which holds username, password and SSL info.

At this point, you have a fully encrypted connection to the MySQL server!

Authenicate the Client as well

While we won't mess with this, you can also configure the server to use client keys to require SSL verification with clients as well, ensuring that only authorized clients can connect to the server.