As noted in an earlier post, MySQL Server 5.7 prefers and enables SSL/TLS connections by default. That’s great and useful progress towards secure connections, but we know that not all SSL/TLS ciphers are created equal – some are older and more vulnerable. Furthermore, some recent vulnerabilities rely on the ability to negotiate less-secure ciphers during the handshake. Monitoring which ciphers are used can help identify connections using low-grade ciphers, but also to build an appropriate restricted cipher list. Using improvements to PERFORMANCE_SCHEMA
introduced in 5.7, you can now easily do this – and this post will show you how.
The cipher used for each TLS connection is stored in a session status variable named Ssl_cipher. Using this status variable, it is easy to observe which TLS cipher has been negotiated:
mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+ 1 row in set (0.00 sec)
As of MySQL Server 5.7.6, session status variables such as this can be exposed in PERFORMANCE_SCHEMA
tables. Most importantly, these session status variables can be observed outside the context of the connection using TLS. In order to do so, though, you will need ensure the global variable show_compatibility_56 is set to OFF:
mysql> SET @@global.show_compatibility_56 = OFF; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * -> FROM performance_schema.status_by_thread -> WHERE variable_name = 'Ssl_cipher'; +-----------+---------------+--------------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+--------------------+ | 27 | Ssl_cipher | DHE-RSA-AES256-SHA | +-----------+---------------+--------------------+ 1 row in set (0.00 sec)
The THREAD_ID
column can be used to join the STATUS_BY_THREAD
table, which includes user name and host information:
mysql> SELECT -> variable_value AS cipher, -> processlist_user AS user, -> processlist_host AS host -> FROM performance_schema.status_by_thread sbt -> JOIN performance_schema.threads t -> ON (t.thread_id = sbt.thread_id) -> WHERE variable_name = 'Ssl_cipher'; +--------------------+------+-----------+ | cipher | user | host | +--------------------+------+-----------+ | DHE-RSA-AES256-SHA | root | localhost | +--------------------+------+-----------+ 1 row in set (0.00 sec)
Leveraging the new abilities of PERFORMANCE_SCHEMA
to inspect the status variables of other connections, tasks such as this become trivially easy.