Implementation of mysql configuration SSL certificate login

Preface
1. MySQL enables SSL configuration

1.1 Check whether SSL is enabled
1.2 Set whether the user uses SSL connection
1.3 Login using SSL

2. Conclusion

Preface

According to the national level 3 security requirements, MySQL's SSL requires security certificate encryption. You need to study this and select a few accounts for demonstration. The version of mysql is 8.0.20

1. MySQL enables SSL configuration

1.1 Check whether SSL is enabled

mysql> show variables like '%ssl%';
+--------------------+-----------------+
| Variable_name | Value |
+--------------------+-----------------+
| have_openssl | YES |  
| have_ssl | YES | # SSL is enabled
|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 set (0.56 sec)

1.2 Set whether the user uses SSL connection

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

By default, users are not logged in using SSL.
We can force this administrative user to log in using SSL.

alter user 'xxx'@'%' require ssl;
To cancel SSL verification:
alter user 'xxx'@'%' require none;

After the change, the account cannot be logged in, and the status becomes as follows

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

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

Remote clients cannot log in directly:

1.3 Login using SSL

To log in via SSL, you need to use the following certificates. The client certificate must be verified with the server before the login can be successful.

1) Local login

The three files in the data directory are used for certificate login.

[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>

2) Navicate remote client login

Download these three certificates

Configure the certificate directory for remote access:

2. Conclusion

Because the configuration in JDBC will not be viewed during the evaluation, JDBC will not be changed. Otherwise, there will be a lot of changes to be made. For the specific demonstration, two accounts can be prepared in advance and then connected using the client.
Currently the SSL users of the two MySQL servers are as follows:

This is the end of this article about the implementation of MySQL configuration SSL certificate login. For more relevant MySQL SSL certificate login content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

How to check mysql locks through mysql show processlist command
MySQL SSL connection configuration details
MySQL SHOW PROCESSLIST assists in the entire process of troubleshooting
Various MySQL SSL configurations
Detailed operation tutorial of MySQL master-slave replication based on SSL protocol
Various MySQL SSL configurations
MySQL configuration SSL master-slave replication

<<: Deep understanding of the mechanism of CSS background-blend-mode

>>: jQuery+swiper component realizes the timeline sliding year tab switching effect