Mysql Configures the SSL certificate for login

preface

National level protection Level 3 security requirements, mysql SSL requires security certificate encryption, here needs to study, select a few accounts to demonstrate. Mysql is 8.0.20

Mysql > enable SSL

1.1 Checking whether SSL is Enabled

mysql> show variables like '%ssl%'; +--------------------+-----------------+ | Variable_name | Value | +--------------------+-----------------+ | Have_openssl | YES | | have_ssl | YES | # has open SSL | mysqlx_ssl_ca | | | mysqlx_ssl_capath | | | mysqlx_ssl_cert | | | mysqlx_ssl_cipher | | | mysqlx_ssl_crl | | | mysqlx_ssl_crlpath | | | mysqlx_ssl_key | | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_fips_mode | OFF | | Ssl_key | server - key. Pem | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 17 rows in the set (0.56 SEC)Copy the code

1.2 Setting whether to Use SSL Connections

mysql> select ssl_type from user where user = 'dev_fqr' ; + -- -- -- -- -- -- -- -- -- -- + | ssl_type | + -- -- -- -- -- -- -- -- -- -- + | | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.05 SEC)Copy the code

The default user does not use SSL to log in.

We can force the admin user to log in using SSL.

alter user 'xxx'@'%' require ssl; Alter user 'XXX '@'%' require none;Copy the code

After the change, the account cannot be logged in and the view status changes to the following

mysql> select ssl_type from user where user = 'dev_fqr' ; + -- -- -- -- -- -- -- -- -- -- + | ssl_type | + -- -- -- -- -- -- -- -- -- -- + | ANY | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

Test login, this machine cannot log in directly.

[root@localhost data]# mysql -u dev_fqr -p
Enter password: 
ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it
Copy the code

Remote client cannot log in directly:

1.3 Login using SSL

To log in through SSL, the following certificates are required. The login succeeds after the client certificate is verified with the server.

1) Local login

Log in with three file certificates in the data directory.

[root@localhost data]# mysql -udev_fqr -pDev@fqr2021 --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem  mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55 Server version: 8.0.22 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. You are enforcing ssl connection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql>Copy the code

2) Communicate with a remote client

Download these three certificates

Configure the certificate directory for remote access:

Second, the summary

Because the evaluation will not see JDBC inside the configuration, so JDBC will not change, or to change the place is very much, specific demonstration can be used to prepare two accounts in advance, then use the client connection.

The two SSL users of mysql are as follows: