mysql的连接是加密_MySQL连接加密

MySQL连接加密

--http://mysqlserverteam.com/identifying-insecure-connections/

A key theme of theMySQL Server 5.7release is much improved security.Earlier releases of MySQL 5.7have introduced features supporting this initiative includingautomatic generation and detection of TLS key material and client-side preference for TLS connections. The recentMySQL 5.7.8 releasebuilds 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--protocolcommand-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 Pipeonly when combined withSSL/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 thegeneral query logas well as the MySQL EnterpriseAudit Log. For thegeneral 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.027365Z6Connect root@localhost on  using Named Pipe

2015-08-04T19:02:35.208404Z7Connect root@localhost on  using TCP/IP

2015-08-04T19:02:38.263292Z8Connect 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 Logrecords will include a newCONNECTION_TYPEtag containing the connection type for connection events. This information is also added to theAudit 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. ACONNECTION_TYPEcolumn has been added to the

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

1row inset(0.00sec)

Because thetable includes internal threads in addition to client connections, theCONNECTION_TYPEvalue 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

2rows inset(0.00sec)

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 ofPerformance 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  Ver14.14Distrib5.7.8-rc,forWin64(x86_64)

Connection id:8

Current database:

Current user:root@localhost

SSL:Cipher inuseisDHE-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

1row inset(0.00sec)

MySQL 5.7 now exposes session status variables to other connections via the some newPERFORMANCE_SCHEMAtables:

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

2rows inset(0.00sec)

mysql>SELECT

->processlist_id,

->processlist_user,

->processlist_host,

->connection_type,

->variable_value AScipher

->FROM

->performance_schema.threadst

->JOIN

->performance_schema.status_by_thread sbt

->ON(t.thread_id=sbt.thread_id ANDsbt.variable_name='Ssl_cipher')

->WHERE connection_type ISNOTNULL\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:

2rows inset(0.00sec)

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-cipherserver option). In this case, DBAs would be interested in the value of theSsl_cipher_listsession 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 AScipher

->FROM

->performance_schema.threadst

->JOIN

->performance_schema.status_by_thread sbt

->ON(t.thread_id=sbt.thread_id ANDsbt.variable_name='Ssl_cipher_list'

)

->WHERE connection_type ISNOTNULL\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:

2rows inset(0.00sec)

Conclusion

The visibility added inMySQL Server 5.7into 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 enhancedPerformance Schemacapabilities in MySQL 5.7.

If you have any comments or questions about these new features, please let us know. As always,THANK YOUfor using MySQL!