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/Linux 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 thus be considered “secure” transports. Both Named Pipe and TCP/IP, however, support connections across networks and 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 top of 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”:
- TCP/IP or Named Pipe only when combined with 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. For the general query log, the entries for Connect log events have been expanded with “... using [connection type]” as seen below:
|
1
2
3
|
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 PERFORMANCE_SCHEMA.THREADS table includes internal threads in addition to client connections, the CONNECTION_TYPE value may also be NULL:
|
1
2
3
4
5
6
7
8
9
10
|
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 a 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:
|
1
2
3
4
5
6
7
8
9
|
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:
|
1
2
3
4
5
|
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 some new PERFORMANCE_SCHEMA tables:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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 MySQL deployments and it demonstrates another real-life application of the enhanced Performance Schema capabilities in MySQL 5.7.
If you have any comments or questions about these new features, please let us know. As always, THANK YOU for using MySQL!