MYSQL SSL Configuration
Configuration file
In order to enable SSL on the MySQL server, the following lines need to be added to the configuration file:
ssl-cert=/etc/dbod/certificates/hostcert.pem
ssl-ca=/etc/dbod/certificates/ca.pem
ssl-key=/etc/dbod/certificates/hostkey_mysql.pem
A re-start of the database is needed for the new configuration to be taken into account.
Important
Values for this parameters which differ to the ones provided will cause a fatal error preventing the startup of your database instance.
On the client side
On the client side, just passing the --ssl-cipher parameter to the client will
be enough to get an encrypted connection (not authenticated).
For MySQL server versions later than 5.7.11
it is recommended to use the --ssl-mode=required option.
When using client certificates you will need to use also --ssl-cert, --ssl-key,
and --ssl-ca to indicate the path to your respective files.
A note about keys
If your client (or library) complains about not being able to load your personal certificate key you can convert it to a readable one by running:
pcitdb46:dbod $ openssl rsa -in userkey.pem -out userkey2.pem
writing RSA key
Requiring TLS/SSL for database accounts
You can enable and require the presence of SSL when creating users on the database, as well as requiring the use of user certificates, specific certificate subjects or certain cipher suites:
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE SSL;
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE X509;
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL/CN=CA/emailAddress=ca@example.com';
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL demo client certificate/
CN=client/emailAddress=client@example.com';
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL demo client certificate/
CN=client/emailAddress=client@example.com'
AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
O=MySQL/CN=CA/emailAddress=ca@example.com'
AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
For full reference please consult the official MySQL Documentation here.
A warning regarding matching in subject, issuer, etc
MySQL will perform very basic string comparison for this fields, so you have to setup the value exactly as it will expect it. An example for the case of requiring certificate subject matching for CERN Grid User certificates would be:
mysql> grant all privileges on test.* to 'dbuser'@'host.cern.ch' require subject
'/DC=ch/DC=cern/OU=Organic Units/OU=Users/CN=username/CN=XXXXXX/CN=Name Surname'
Note this value differs slightly from the one you would obtain from the same
certificate when running openssl x509 -in usercert.pem -subject -noout
Verification and related parameters
If you are using a vanilla MySQL client, an easy way to check if the connection is being encrypted is by executing `\s':
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.32, for Linux (x86_64) using EditLine wrapper
Connection id: 1
Current database:
Current user: dod_mysql@pcitdb46.dyndns.cern.ch
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: less
Using outfile: ''
Using delimiter: ;
Server version: 5.6.17-log MySQL Community Server (GPL)
Protocol version: 10
Connection: dbod-pinocho via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 5500
Uptime: 44 min 5 sec
Threads: 2 Questions: 2960 Slow queries: 0 Opens: 71 Flush tables: 1 Open tables: 64 Queries per second avg: 1.119
-------------
Another possibility is to list all ssl related status variables. This will also show some extra statistics and configuration parameters as well as the validity dates for the server certificates:
mysql> show status like '%ssl%';
+--------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+--------------------------------+-------------------------------------------------------------+
| Com_show_processlist | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | DHE-RSA-AES256-SHA |
| Ssl_cipher_list | DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 500 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | Oct 25 12:28:42 2017 GMT |
| Ssl_server_not_before | Sep 20 12:28:42 2016 GMT |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | TLSv1 |
+--------------------------------+-------------------------------------------------------------+
26 rows in set (0.00 sec)