MYSQL8 Security – SSL Authentication

SSL Concept

SSL (Secure Socket Layer: Secure Socket Layer) utilizes data encryption, authentication and message integrity verification mechanisms for application layer protocols based on reliable connections such as TCP.
The main features provided by the SSL protocol are:

1. Confidentiality of data transmission; encryption of transmitted data using symmetric key algorithms
2. Authentication mechanism: certificate-based authentication of servers and clients using digital signature methods, where client authentication is optional
3. Message Integrity Verification: The MAC algorithm is used to verify the integrity of the message during transmission.

If a user’s transmission is not via SSL, then their data is transmitted in plaintext across the network.

On the database side, the client connects to the server using an SSL connection that encrypts the communication data.

1. Enable SSL: You can enable SSL in the MySQL configuration file. You need to specify the path to the SSL certificate, private key, and CA certificate.
2. SSL Handshake Authentication: MySQL 8 supports SSL handshake authentication, which ensures that the client connects to the correct server.
3. SSL Client Authentication: MySQL 8 supports client certificate authentication to further enhance security.
4. SSL Connection Limit: You can limit the maximum number of SSL connections and the maximum number of concurrent connections by modifying parameters in the MySQL configuration file.

Overall, MySQL 8’s SSL features help users manage their databases more securely and improve data security.

MYSQL Process for Implementing SSL

1. First create an SSL certificate and private key for the MYSQL server
2. Configure SSL in MYQL and start the service
3. Create users with SSL tags (require ssl)
4. Bring SSL when connecting to the database

MYSQL Deployment SSl

1. Manual Configuration: mysql versions below 5.7.6 can only be configured manually
2. Auto-configuration: mysql 5.7.6 or above supports auto-configuration

0. SSL Policy

--ssl-mode is an option in the MySQL command-line client that specifies the mode of the SSL/TLS connection.

It has four possible values:

• DISABLED：Disable SSL
• REQUIRED：SSL must be used
• VERIFY_CA：Verify CA
• VERIFY_IDENTITY：verify sb's identity

When connecting to a remote MySQL server, you need to secure sensitive information and data, so you should use SSL to encrypt communications.
And in order to provideHighest level of trust and securityshould be--ssl-mode set toREQUIRED。
--ssl-mode REQUIRED beMySQL8mosthigh security levelIt requires that the clientMust use encrypted SSL/TLS Connect to the server and authenticate to the server.
(indicates contrast)--ssl-mode VERIFY_IDENTITY SSL/TLS encryption is not mandatory Connection, whichCheck and validate server certificate onlyThereforeHow about --ssl-mode REQUIRED Security。
This means that the MySQL client will attempt to establish an SSL connection and will not connect to the MySQL server if it is unable to establish an SSL connection, ensuring that the database can only be accessed over an SSL connection.

Detailed explanations are given below:

• REQUIRED Require all MySQL clients to establish a connection to the database server via a TLS cipher suite to providehighest leveltheTrust and security。。

• VERIFY_IDENTITY option requires the MySQL client to authenticate the database server and hold a hostname or IP address consistent with its grant. However, if the database server uses a self-signed certificate, it may hand over compromised entries due to the inability to download the CRL and OCSP responses in the public key infrastructure, making the checksum insecure.

• PREFERRED flag allows the client to suggest and attempt an SSL connection, but does not require an SSL connection to be established. If a MySQL client requests an unencrypted connection, the server answers the request.

• VERIFY_CA Enabling the SSL protocol on the server side for the client and ensuring that the SSL connection is established on a certificate issued by the root certificate authority and that the client provides a match-tested professional certificate is ideal for situations where the client software has high standards of trustworthiness or where enhanced protection of the transmitted data is required.

1、Creating a certificate

# Install openssl dependencies
dnf install -y openssl

# View openssl version
openssl version

# Generate RSA key pairs required for SSL connections
## datadir Specify the database file deer tendon
## user and uid Specify the user who runs the mysql_ssl_rsa_setup command
mysql_ssl_rsa_setup --datadir=/var/lib/mysql --user=mysql --uid=mysql

# Just do it by default
mysqld_ssl_rsa_setup

## -vvv Detailed, debug mode ##

will be automatically added to thedatadirCreate the following certificate file in the directory

• ca-key.pem: CA certificate private key file, used to generate server and client certificates required for SSL connections.
• ca.pem: CA certificate public key file, used to verify the legitimacy of the server and client certificates in SSL connections.
• client-cert.pem: client certificate, used to authenticate the client in an SSL connection.
• client-key.pem: the private key of the client certificate, used to encrypt and decrypt the data sent by the client in an SSL connection.
• private_key.pem: private key file, used to encrypt and decrypt data in SSL connections.
• public_key.pem: public key file used to authenticate data in an SSL connection.
• server_cert.pem: server certificate, used to authenticate the MySQL database server.
• server_key.pem: the private key of the server certificate, used to encrypt and decrypt the data sent by the server in an SSL connection.

2、Configure SSL certificate

https://blog.csdn.net/Sn_Keys/article/details/126425869

[mysqld]
# Specify the path to the CA certificate public key file
ssl-ca=/path/to/ca.pem
# Specify the path to the mysql server certificate
ssl-cert=/path/to/server_cert.pem
# Specify the path to the private key for the mysql server certificate
ssl-key=/path/to/server_key.pem


[client]
# Specify the path to the CA certificate public key file
ssl-ca=/path/to/ca.pem
# Specify the path to the mysql client certificate
ssl-cert=/path/to/client_cert.pem
# Specify the path to the private key of the mysql client certificate
ssl-key=/path/to/client_key.pem

Restart mysql service

# Restart mysql service
systemctl restart mysqld

checking status

-- Check if SSL is enabled on the database
show variables LIKE 'have_SSl';

-- View all variable names and values in global variables that contain the "SSL" character.
show global variables LIKE '%SSL%';

-- View tls secure transport version
show global variables LIKE 'tls_version';

fromMySQL5.7.35 To begin with, it is not recommended to useTLSv1 respond in singingTLSv1.1 connection protocol

3、Configure SSL users

Creating a User Normal Authentication Method

-- Create users
CREATE USER username@'%' IDENTIFIED BY 'Table name';

-- Authorization of users
GRANT ALL ON *. * TO username@'%';

-- Application rights configuration
FLUSH PRIVILEGES;

-- View user privileges
SELECT user,host,ssl_type,ssl_cipher FROM mysql.user;

Creating user-enforced certificate authentication

REQUIRE SSL Forces the client to use theSSL/TLS encryption protocolsCommunicating with the server
REQUIRE X509Forces the client to not only use theSSL/TLS connectionsand it is also necessary to provide an effectivex509 certificate. In the use ofREQUIRE X509 The MySQL server verifies that the client-supplied certificate is thetrustedand whether the certificate matches theAlready registered user accountshit the nail on the headcertificates。

-- require ssl Force users to authenticate with certificates
CREATE USER username@'%' IDENTIFIED BY 'table name' require ssl;

-- require x509 Force users to authenticate with certificates
CREATE USER username@'%' IDENTIFIED BY 'table name' require x509;

-- Authorization of users
GRANT ALL ON *. * TO username@'%';

-- Application rights configuration
FLUSH PRIVILEGES;

-- View user privileges
SELECT user,host,ssl_type,ssl_cipher FROM mysql.user;

Setting up mandatory certificate login for users

-- Setting up forced ssl
alter user user0001@'%' require ssl;

-- Cancel forced ssl
alter user user0001@'%' require none;

4. SSL login

SSL encrypted login method 1;

# --ssl-mode=disable: indicates that SSL encryption mode is disabled
mysql -uroot -p --ssl-mode=disable

# View encryption mode after logging into mysql
mysql> status;

# --ssl-mode=required: Indicates that SSL encryption mode is mandatory.
mysql -uroot -p --ssl-mode=required

-- View encryption mode after logging into mysql
status;

SSL Encrypted Login Method 2.

# Specify the CA certificate and client certificate and private key
mysql -uroot -p --ssl-ca=/var/lib/mysql/ca.pem \
--ssl-cert=/var/lib/mysql/client-cert.pem \
--ssl-key=/var/lib/mysql/client-key.pem