Linux/Setup MySQL to use SSL

From voipsupport
Revision as of 23:46, 13 June 2016 by John (talk | contribs) (Created page with "1. Check that SSL is enabled; <pre>echo "SHOW VARIABLES LIKE 'have_ssl';" | mysql</pre> If it is, the following is given as output: <pre>Variable_name Value have_ssl YES</p...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

1. Check that SSL is enabled; 

echo "SHOW VARIABLES LIKE 'have_ssl';" | mysql

If it is, the following is given as output: 

Variable_name	Value
have_ssl	YES

If instead of YES, the value is DISABLED, then your version of MySQL supports SSL but has not been started with the --ssl option. Configure the server to start with -ssl.

If the server does not have ssl, then you will need to install or compile the version of MySQL that has ssl support. Further help can be found with your distribution website or on the mysql website.

2. Generate the keys and certificates.

2.1 Generate a new private key for the Certificate Authority: 

openssl genrsa 2048 > ca-key.pem

2.2 Generate the CA certificate: 

openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem

2.3 Create server private key and certificate request: 

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem

2.4 Remove passphrase from server private key: 

openssl rsa -in server-key.pem -out server-key.pem

2.5 Sign server certificate: 

openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

2.6 Create client private key and certificate request: 

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem

2.7 Remove passphrase from client private key: 

openssl rsa -in client-key.pem -out client-key.pem

2.8 Sign client certificate request: 

openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

2.9 Verify certificates: 

openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

3. Place the CA certificate, the server private key and certificate into a specific directory, for example /etc/ssl/mysql. Make sure that the server private key is readable only by the user that runs mysql. For example you can set the following permissions: 

chmod 440 ca-cert.pem server-key.pem server-cert.pem
chgrp mysql ca-cert.pem server-key.pem server-cert.pem

4. The ca private key ca-key.pem is no longer needed to be stored on the server. It should be kept safely. If transferring it from the server to a local computer use a secured connection (e.g. sftp o ssh).

5. Setup the server to use the CA certificate, server private key and certificate. This can be done by editting /etc/my.cnf and adding/setting these variables in the mysqld section. 

ssl-ca=/etc/ssl/mysql/ca-cert.pem
ssl-cert=/etc/ssl/mysql/server-cert.pem
ssl-key=/etc/ssl/mysql/server-key.pem

6. Copy the client key and certificate and CA certificate to the client machine and configure the client to use them.

7. In order to ensure that a client has access only if over an encrypted connection, use REQUIRE clause in the GRANT statement.

  • REQUIRE SSL - the connection must be encrypted, but places no other constraints on the identity of the connecting party (beyond the password if used).
  • REQUIRE X509 - the connection must be encrypted and a valid client certificate signed by any verifiable CA must be presented by the client. Any certificate will be accepted providing it is signed by any trusted CA.
  • REQUIRE ISSUER - the connection must be encrypted and a valid client certificate signed by the specified issuer must be presented. You can use this option to make sure that the client certificate has been signed by your server CA certificate, something which only someone with the CA private key would be able to do. In order to generate the string needed for ISSUER you can run this command on the client certificate:
openssl x509 -in client-cert.pem -issuer -noout
  • REQUIRE SUBJECT - the certificate must contain the correct subject name. This should be used in conjunction with REQUIRE ISSUER since anyone can generate a certificate matching the SUBJECT name. When used together REQUIRE ISSUER 'XXXX' SUBJECT 'YYYY' are the strongest way to check the certificate of the connecting client. In order to generate the string needed for SUBJECT you can run this command on the client certificate:
openssl x509 -in client-cert.pem -subject -noout
  • REQUIRE CIPHER - is essential because SSL may use weak encryption. This option ensures that the encryption is of sufficient strength.

For example this is the grant statement that requires a client certificate with a specific subject signed by the server CA and using strong encryption: 

GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'username'@'hostname' IDENTIFIED BY 'xxxx' REQUIRE \
ISSUER '/C=XX/ST=XXXX/L=XXXX/O=XXXXX/OU=XXXX/CN=xxxxx/emailAddress=xxxxx@xxxx' \
SUBJECT '/C=XX/ST=XXXX/L=XXXX/O=XXXX/OU=XXXX/CN=XXXXXXXXX/emailAddress=xxxxxxxxx@xxxx' \
CIPHER 'DHE-RSA-AES256-SHA';

8. You can test the connection from a remote host using: 

mysql --ssl -h server -u username -p --ssl-ca=/etc/mysql/ca-cert.pem  --ssl-cert=/etc/mysql/client-cert.pem --ssl-key=/etc/mysql/client-key.pem

9. To see if the connection is encrypted: 

\s
...
SSL:			Cipher in use is DHE-RSA-AES256-SHA
...

10. To see the list of available ciphers: 

SHOW STATUS LIKE 'Ssl_cipher_list';
Retrieved from "http://voipsupport.it/wiki/index.php?title=Linux/Setup_MySQL_to_use_SSL&oldid=640"