Connecting to MySQL server with ssl

Question

So I need to connect to a MySQL server over SSL. I have root access on both server and client. The OS on both server and client is Ubuntu Linux. The server is running MySQL 5.5.46.

I generated certificates following the instructions at:

http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html

This created eight .pem files. I successfully got the server to run with SSL enabled. I know because if I get the MySQL prompt on the server and run:

mysql> show global variables like 'have_%ssl';

I get:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+

I also put the correct privileges on the user who will be connecting to this server. I know because If I do:

show grants for <username>

I get a GRANT statement with "REQUIRE SSL" on the end. So I really think the server side is good to go.

Connecting from the client is the problem. I can do it if I use the "-p" option like this:

$ mysql --ssl-ca=./path/to/ca.pem -h <hostname> -u <username> -D <databasename> -p

Then I get prompted for my password, and if I enter it, I can connect.

But I need to automate this connection, and appending the password to the line above is a bad idea (and never works anyway - which I also wonder about).

So how can I securely connect to the client in an automated way without hardcoding a password? When I generated the certificates following the instructions at the link above I got eight .pem documents, most of which I'm not using. Do I need to be using client-key.pem? client-cert.pem? Please note that passing paths to those other certificates on the command line does not work. That is, this:

mysql --ssl-ca=./ca.pem --ssl-cert=./client-cert.pem --ssl-key=./client-key.pem -h <hostname> -u <username> -D <databasename>

fails just like all other combinations of the above: "Access denied"

Any help is much appreciated.

Solution

mysql> GRANT ALL PRIVILEGES ON database.* to user REQUIRE X509;

Try using a GRANT with REQUIRE X509 instead of REQUIRE SSL and omit the IDENTIFIED BY clause. X509 requires the client to submit an ssl-cert. The certificate must be signed by the ssl-ca (Certificate Authority) specified on the server side MySQL configuration file. In other words, this will require the server to validate that the client has a key and that the key was signed by the server's CA.

For the above setup, you will need client-key and client-cert. The client-cert will be sent to the server to validate your client and the client-key will be used to decrypt messages from the server. You don't need ca.pem in this setup, but if you include it, the server's certificate will be validated against the ca.pem file.