Author: Hu Chengqing
Member of aikesheng DBA team, good at fault analysis and performance optimization, personal blog: https://www.jianshu.com/u/a95... , welcome to the discussion.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
TLS or SSL ?
SSL(Secure Socket Layer secure socket layer) is a protocol encryption layer based on HTTPS. It was originally developed by Netscape and then standardized by IETF (The Internet Engineering Task Force - Internet Engineering Task Force). RFC contains many specifications of Internet technology.
At first, it's because HTTP uses plaintext when transmitting data (although the data submitted by POST can't be seen in the newspaper, it can still be stolen through packet capture tools). In order to solve this hidden danger, Netscape launched SSL secure socket protocol layer, which is based on a protocol layer above TCP under http, It is based on HTTP standard and encrypts TCP when transmitting data, so HTTPS is the abbreviation of HTTP+SSL/TCP.
Since the launch of HTTPS has been welcomed by many people, when SSL is updated to 3.0, IETF will support SSL3.0 0 is standardized and a few mechanisms are added (but there is almost no difference with SSL3.0). The standardized IETF is renamed tls1 0 (Transport Layer Security Protocol), it can be said that TLS is the new version 3.1 of SSL.
TLS (Transport Layer Security) is a more secure upgraded version of SSL. But the term SSL is more commonly used. In fact, MySQL uses TLS protocol instead of SSL protocol.
1. TLS handshake process
Want to find out what the following pile of files do? Then you must first understand the TLS handshake process
├── ca-key.pem ├── ca.pem ├── client-cert.pem ├── client-key.pem ├── server-cert.pem └── server-key.pem
1.1 TLS handshake process
The process of TLS handshake is actually the process of secret key exchange, which is also the most complex link in the whole TLS encryption technology. Refer to an online figure as follows:
1.2 key algorithm
Symmetric key algorithm: the same key is used for data encryption and decryption.
Asymmetric key algorithm: different keys are used for data encryption and decryption. One is the public key and the other is the private key kept secretly by the user. Data encrypted with public key (or private key) can only be decrypted with the corresponding private key (or public key).
Compared with the asymmetric key algorithm, the symmetric key algorithm has the advantage of fast computing speed. It is usually used to encrypt a large amount of information (such as all messages); The asymmetric key algorithm is generally used for digital signature and encryption of less information.
Note: SSL/TLS protocol uses symmetric key algorithm for data encryption and asymmetric key algorithm for "symmetric key" encryption. The process is as follows:
- In the figure above, the Server sends the public key to the client, and the private key is saved by itself. This is the public key and private key pair in the asymmetric key algorithm;
- The client will create a key, which is the key in the symmetric encryption algorithm. Then encrypt the key with the public key of the Server and send it to the Server;
- The Server receives the encrypted key sent by the client and decrypts it with its own private key to obtain the key before encryption;
- Next, the transmitted data is encrypted and decrypted using a "symmetric key".
This is to use the asymmetric key algorithm to ensure the security of the symmetric key itself.
1.3 digital certificate - how to ensure the authenticity of public key?
If an attacker forges the public key of the Server and the client, the client will visit the fake website and steal information. Obviously, ensuring that the public key of the Server received by the client is true is the first line of defense to ensure the reliability of the whole encrypted connection.
The digital certificate is issued by CA(Certification Authority), and the issuing process is as follows:
- The user first generates his own key pair and transmits the public key and some personal identity information to the CA;
- The CA verifies the identity of the user (some necessary steps will be performed to make sure that the request is indeed sent by the user);
- All information of CA to users (public key, owner, validity...) After Hash calculation, a Hash value is obtained, and then the Hash value is encrypted with the private key to obtain the signature, and the digital certificate is obtained. The certificate contains the user's public key, owner, validity period and other information, as well as the signature information of the ca.
Verification process of digital certificate:
- The client will use the same Hash algorithm to obtain the Hash value H1 of the digital certificate;
- Generally, the browser and operating system integrate CA certificate (including CA public key and owner). The client obtains the CA certificate and decrypts the signature with the CA public key to obtain a Hash value H2;
- Compare H1 and H2. If the values are the same, the digital certificate is trusted.
The above issuance and verification process is shown in the figure below (refer to the network):
If the CA certificate is not in the trusted zone of the browser and operating system, this kind of CA certificate is usually called self signed CA certificate (MySQL automatically generates self signed certificate, see below). To complete the verification of the digital certificate, you must put the self signed CA certificate on the client in advance, and specify the CA certificate file when the client initiates the connection; Or import the self signed CA certificate into the operating system trust zone of the client in advance, so that the CA certificate can be obtained automatically during the TLS handshake.
In addition, the authentication certificate is not necessarily required in the SSL/TLS protocol. For example, the mysql client can only specify -- SSL mode = verify_ CA or -- SSL mode = verify_ The CA certificate is validated only when identity. If -- SSL mode = required, the CA certificate is not verified, and the mysql server side is only required to send the public key to the client, which cannot guarantee that the public key of the server side really belongs to the mysql server. See below for details.
2. MySQL SSL encrypted connection
2.1 MySQL server configuration
Startup parameters:
- --ssl: indicates that the MySQL server allows encrypted connections. This startup parameter is MySQL 8 0 enabled by default
System variables:
- require_secure_transport: Specifies whether the client is required to use an encrypted connection. The default value is OFF. If ON, it means that the client must use an encrypted connection. If the client closes ssl, the connection will report an error.
The following parameters specify the certificate and key file to use when encrypting the connection:
ssl_ca=ca.pem ssl_cert=server-cert.pem ssl_key=server-key.pem
MySQL8.0 will automatically generate SSL certificate, key file and RSA key pair file at startup; Or use mysql_ ssl_ rsa_ The setup program generates the above files. It can also be generated manually:
## SSL certificate and key file
certs
├── ca-key.pem
├── ca.pem
├── client-cert.pem
├── client-key.pem
├── server-cert.pem
└── server-key.pem
# Create CA certificate
# Create CA certificate (including CA public key) and CA private key
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
-key ca-key.pem -out ca.pem
# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
# Server public key and private key
# Use the CA private key to sign the server's public key to obtain the server certificate server-cert.pem. The certificate contains the public key, owner, validity and other plaintext information. It also encrypts the public key, owner and validity through the CA private key Encrypted signature
openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
# Mr. Cheng client public key and private key
# Use the CA private key to sign the client's public key to obtain the client certificate client-cert.pem. Generally, there is no need to verify the client's identity, so these files do not need to be used. Of course, if you want to verify MySQL Server identity and client identity at the same time, you need to use these files.
openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
# View certificate content
openssl x509 -text -in ca.pem
# Verify the CA certificate ("using CA certificate to verify digital certificate" is more appropriate?)
openssl verify -CAfile ca.pem server-cert.pem
# Output result: server-cert.pem: OK2.2 MySQL client configuration
When the MySQL client connects to the Server, specify through the -- SSL mode parameter:
- --SSL mode = preferred, the default behavior. The client side attempts to connect using encryption. If the encrypted connection cannot be built, it will return to the unencrypted connection
- --When SSL mode = required, the Client side needs to encrypt the connection. If the connection cannot be built, the Client side will fail
- --SSL mode = disabled, the Client side uses an unencrypted connection
- --ssl-mode=VERIFY_ For Ca, the Client side needs to encrypt the connection and verify the CA certificate
- --ssl-mode=VERIFY_IDENTITY: the Client side needs an encrypted connection, and also performs authentication against the CA certificate and the server host name in its certificate
Note: hostname authentication verify_ Created by MySQL server automatically or not applicable_ ssl_ rsa_ Setup manually created self signed CA certificate.
The tests are as follows:
##When -- SSL mode = required is specified, only encrypted connection is required, and MySQL Server identity does not need to be verified. Therefore, the public key sent by MySQL Server to the client will be directly trusted (i.e. the plaintext public key in the server-cert.pem digital certificate, ignoring the digital signature information) [root@172-16-21-5 /]# /opt/mysql/base/8.0.21/bin/mysql -h172.16.21.4 -P3306 -utest -ptestpass --ssl-mode=REQUIRED -e "select 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+ ##When -- SSL mode = verify is specified_ For Ca, the CA certificate needs to be verified. Because the CA certificate is self issued, it is not in the trusted zone of the browser and operating system. The CA certificate cannot be obtained from the public channel of the trusted zone of the browser and operating system, so an error is reported: [root@172-16-21-5 /]# /opt/mysql/base/8.0.21/bin/mysql -h172.16.21.4 -P3306 -utest -ptestpass --ssl-mode=VERIFY_CA ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY ##Copy ca.pem of MySQL server to client scp ca.pem 172.16.21.5:/tmp/ ##--ssl-mode=VERIFY_CA specifies that the CA certificate needs to be verified. Because the CA certificate is self signed and is not in the trusted zone of the browser and operating system, you must copy the CA certificate to the client and specify the CA certificate file ##During the TLS handshake, MySQL Server sends the server digital certificate server-cert.pem to the client. The client uses the CA public key in the CA certificate to decrypt the signature in server-cert.pem. After verification, it can log in normally: [root@localhost ~]# mysql -h10.186.61.173 -P3308 -uhucq -p'1qaz@WSX' \ --ssl-ca="/tmp/ca.pem" \ --ssl-mode=VERIFY_CA \ -e "select 1" +---+ | 1 | +---+ | 1 | +---+ ##Since the CA certificate automatically generated by MySQL is a self signed certificate, and -- SSL mode = verify_ Identity is not applicable to MySQL automatically created or used by the server_ ssl_ rsa_ Setup manually creates a self signed CA certificate. Even if a local CA certificate file is specified, the connection will fail [root@localhost ~]# mysql -h10.186.61.173 -P3308 -uhucq -p'1qaz@WSX' \ --ssl-ca="/tmp/ca.pem" \ --ssl-mode=VERIFY_IDENTITY \ -e "select 1" ERROR 2026 (HY000): SSL connection error: Failed to verify the server certificate via X509 certificate matching functions
2.3 TLS handshake process in MySQL SSL connection
The above examples have been described in detail. Here is a brief summary:
- Client initiates ssl connection request;
- MySQL Server sends the digital certificate server-cert.pem to the client (server-cert.pem includes server public key and CA signature information);
- The client uses the CA public key in the CA certificate ca.pem (since this is a MySQL self signed CA certificate and cannot be obtained from the trusted zone of the operating system (not here at all), so the CA certificate file must be saved locally at the client in advance) to decrypt the signature in server-cert.pem for verification;
- After passing the verification, generate a symmetric key, encrypt the "symmetric key" with the public key in server-cert.pem, and send it to MySQL Server;
- MySQL Server uses its own private key server key PEM decrypts and obtains the "symmetric key";
- Next, the transmitted data is encrypted and decrypted using a "symmetric key".
If you only specify -- SSL mode = required, do not specify -- SSL mode = verify_ CA or -- SSL mode = verify_ Identity, step 3 is not required.
3. How JDBC sets up SSL connection
First, the MySQL Server must generate ssl certificate and key files, and specify the startup parameter: - ssl (usually written to my.cnf) when starting. MySQL8. When 0 starts, it will automatically generate ssl certificate and key file, and use -- ssl parameter by default.
JDBC closes ssl connection example: jdbc:mysql://localhost:3306/hucq?useSSL=false
If MySQL Server uses caching_sha2_password (MySQL 8.0 default authentication plug-in), sha256_password authentication plug-in, allowpublickeyretrieval = true must also be specified because caching_ sha2_ The password plug-in requires that RSA public key encryption must be used when exchanging passwords (in the case of no SSL encrypted connection). The allowpublickyretrieval = true parameter is used to request MySQL Server to send RSA public key to the client. If RSA public key is not requested and the client's local RSA public key file is not specified (copy RSA public key from MySQL Server to local first), The connection will report an error. Should configure: JDBC: mysql://localhost:3306/hucq?useSSL=false&AllowPublicKeyRetrieval=True
JDBC enables SSL connection, which means that if a secure connection is required, CA certificate authentication should be enabled. Like MySQL client, you need to import MySQL's self signed CA certificate into the client, or put it on ftp, and then specify the CA certificate path through JDBC parameters. It is complex. Please refer to the official document: https://dev.mysql.com/doc/con...