Identifying which connections are secure

A key theme of the MySQL Server 5.7 release is much improved security.  Earlier releases of MySQL 5.7 have introduced features supporting this initiative including automatic generation and detection of TLS key material and client-side preference for TLS connections.  The recent MySQL 5.7.8 release builds upon this and provides additional monitoring and audit capabilities that make it easy to answer the question, “How secure are my client connections?”

Connection Types

The –protocol command-line argument for MySQL clients describes four different protocols, with support varying by platform:

  • TCP/IP (all platforms)
  • Socket (Unix platforms only)
  • Shared Memory (Windows only)
  • Named Pipe (Windows only)

Inside of MySQL Server, SSL/TLS is defined as a fifth option (this is controlled by –ssl* options somewhat independently from the protocol).  Of the above, Socket and Shared Memory connections do not support inter-computer connections (you must be on the same box to use these protocols), and can be considered “secure” transports.  Both Named Pipe and TCP/IP support connections across networks, but do not secure the payload.  As such, they should be considered “insecure” transports.  The only exception to this is when SSL/TLS is used to layer security on these protocols.  Of these two, only TCP/IP currently supports SSL/TLS within MySQL.

This leaves us with the following connection types we can consider “secure”:

  • SSL/TLS
  • Socket
  • Shared Memory

 

Logging

Knowing how connections were established over time is a common audit requirement, and we’ve added connection type information to both the general query log as well as the MySQL Enterprise Audit Log plugin.  For the general query log, the entries for Connect log events have been expanded with “… using [connection type]” as seen below:

 

2015-08-04T19:02:27.027365Z    6 Connect	root@localhost on  using Named Pipe
2015-08-04T19:02:35.208404Z    7 Connect	root@localhost on  using TCP/IP
2015-08-04T19:02:38.263292Z    8 Connect	root@localhost on  using SSL/TLS

This information makes it easy for any user to quickly audit which connection types are used to connect by which accounts. Note that any tools which process general query log contents may need to be updated to account for the additional text added to the Connect event log entries.

Likewise, audit log records will include a new CONNECTION_TYPE tag containing the connection type for connection events.  This information is also added to the Audit Log API, and plugin developers who leverage this API will want to account for this change.

Types of Current Connections

In addition to exposing connection type information in logs, MySQL 5.7 makes it possible to inspect the connection type information for current connections.  A CONNECTION_TYPE column has been added to the PERFORMANCE_SCHEMA.THREADS table:

mysql> SELECT *
    -> FROM performance_schema.threads
    -> WHERE processlist_id = CONNECTION_ID()\G
*************************** 1. row ***************************
          THREAD_ID: 35
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 8
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: SELECT *
FROM performance_schema.threads
WHERE processlist_id = CONNECTION_ID()
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
1 row in set (0.00 sec)

Because the THREADS table includes internal threads in addition to client connections, the CONNECTION_TYPE value may also include NULL:

mysql> SELECT COUNT(*), connection_type
    -> FROM performance_schema.threads
    -> GROUP BY connection_type\G
*************************** 1. row ***************************
       COUNT(*): 27
connection_type: NULL
*************************** 2. row ***************************
       COUNT(*): 1
connection_type: SSL/TLS
2 rows in set (0.00 sec)

Going Deeper

The above gives a good high-level overview of whether connections are leveraging secure transport or not, but there are additional aspects you may want to consider.  Because not all ciphers are created equally, the ability to inspect which TLS cipher is in use for a given client connection can be useful – and with the magic of PERFORMANCE_SCHEMA, it is now possible.

Before MySQL 5.7, it was possible to inspect the TLS cipher in use for the connection making the inquiry, but there was no visibility into which ciphers were in use by other connections.  This information is part of the output from the \S (or STATUS) operation:

 

mysql> \s
--------------
bin\mysql  Ver 14.14 Distrib 5.7.8-rc, for Win64 (x86_64)

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

It was also exposed as a session-scoped status variable:

 mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher'\G
*************************** 1. row ***************************
Variable_name: Ssl_cipher
        Value: DHE-RSA-AES256-SHA
1 row in set (0.00 sec)

MySQL 5.7 now exposes session status variables to other connections via the the new PERFORMANCE_SCHEMA:

mysql> SELECT * FROM performance_schema.status_by_thread
    -> WHERE variable_name = 'Ssl_cipher'\G
*************************** 1. row ***************************
     THREAD_ID: 35
 VARIABLE_NAME: Ssl_cipher
VARIABLE_VALUE: DHE-RSA-AES256-SHA
*************************** 2. row ***************************
     THREAD_ID: 41
 VARIABLE_NAME: Ssl_cipher
VARIABLE_VALUE: DHE-RSA-AES256-SHA
2 rows in set (0.00 sec)

mysql> SELECT
    ->   processlist_id,
    ->   processlist_user,
    ->   processlist_host,
    ->   connection_type,
    ->   variable_value AS cipher
    -> FROM
    ->   performance_schema.threads t
    -> JOIN
    ->   performance_schema.status_by_thread sbt
    -> ON (t.thread_id = sbt.thread_id AND sbt.variable_name = 'Ssl_cipher')
    -> WHERE connection_type IS NOT NULL\G
*************************** 1. row ***************************
  processlist_id: 8
processlist_user: root
processlist_host: localhost
 connection_type: SSL/TLS
          cipher: DHE-RSA-AES256-SHA
*************************** 2. row ***************************
  processlist_id: 15
processlist_user: root
processlist_host: localhost
 connection_type: TCP/IP
          cipher:
2 rows in set (0.00 sec)

In addition to information about the currently-selected TLS cipher for each connection, DBAs can inspect the candidate cipher lists to evaluate which clients might be affected by restricting support for certain ciphers on the server side (by adjusting –ssl-cipher server option).  In this case, DBAs would be interested in the value of the Ssl_cipher_list session status variable:

 

mysql> SELECT
    ->   processlist_id,
    ->   processlist_user,
    ->   processlist_host,
    ->   connection_type,
    ->   variable_value AS cipher
    -> FROM
    ->   performance_schema.threads t
    -> JOIN
    ->   performance_schema.status_by_thread sbt
    -> ON (t.thread_id = sbt.thread_id AND sbt.variable_name = 'Ssl_cipher_list'
)
    -> WHERE connection_type IS NOT NULL\G
*************************** 1. row ***************************
  processlist_id: 8
processlist_user: root
processlist_host: localhost
 connection_type: SSL/TLS
          cipher: DHE-RSA-AES256-SHA:AES256-SHA:DHE-RSA-AES128-SHA:AES128-SHA:AE
S256-RMD:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-D
ES-CBC3-RMD:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RS
A-DES-CBC-SHA
*************************** 2. row ***************************
  processlist_id: 15
processlist_user: root
processlist_host: localhost
 connection_type: TCP/IP
          cipher:
2 rows in set (0.00 sec)

Conclusion

The visibility added in MySQL Server 5.7 into the types of connections used by clients will be useful to DBAs wanting to evaluate and monitor the security posture of their deployment and demonstrate another real-life application of enhanced PERFORMANCE_SCHEMA capabilities.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.