mysql查询ssl配置情况 mysql ssl配置

文章目录

• 1 说明

• 1.1 方案说明
• 1.2 操作说明

• 2 方案操作与说明

• 2.1 Mysql服务端调整

• 2.1.1 证书生成及验证
• 2.1.2 证书配置

• 2.2 Java客户端调整

• 2.2.1 将客户端密钥和证书文件转换为 PKCS 12 存档
• 2.2.2 将证书放到Java服务中
• 2.2.3 更改jdbc链接参数
• 2.2.4 重启java服务

• 3 避坑指南

• 3.1 证书核验出错: error 18 at 0 depth lookup:self signed certificate
• 3.2 云主机拦截Java服务的mysql请求

1 说明

1.1 方案说明

公司某SaaS平台在做等保3.0评测，等保3.0要求数据库使用ssl链接。这个SaaS平台主体为Java服务，结合等保的要求对平台做了mysql的双向认证ssl链接。

mysql的ssl链接方案包括两个重要的部分，一个是mysql服务端如何操作，另外一个是mysql的客户端证书调整，也就是平台中使用mysql的服务。

在做mysql的ssl链接方案的过程中，有一些坑，在SaaS环境出现，在内网环境未复现，这些问题对笔者造成很大困扰，后经公司架构师的协助排查解决，在后续的上线中问题才的已解决。本方案也会对容易造成ssl链接失败的点加以说明，给做等保的同学一些参考。

1.2 操作说明

如下具体操作中有几个需要注意的点 建议读者根据自己的实际情况进行修改

• 生成证书的操作建议使用mysql的运行用户 避免证书权限不一致 导致mysql无法读取证书 文中使用的是cwise 更换为自己的实际用户
• 生成证书的时间 命令中是36500天 可以自行更改
• 如下演示的代码中 mysql的安装目录是/data/app/mysql 证书的存放目录是/data/app/mysql/certs 读者在参考的时候 根据自己环境的实际情况更换目录即可
• 如下演示中 授权的用户是mysql 要根据实际情况进行修改 改为自己环境使用的链接mysql的用户
• MysqlPass@777为示例密码 可以使用这个密码 建议根据实际情况设置自己的

2 方案操作与说明

2.1 Mysql服务端调整

2.1.1 证书生成及验证

生成服务端证书

# 创建证书目录
[root@localhost mysqlData]# su cwise
[cwise@localhost mysqlData]$ cd /data/app/mysql/
[cwise@localhost mysql]$ mkdir certs && cd certs

# 生成服务器证书
[cwise@localhost certs]$ openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
...........................+++
..............+++
e is 65537 (0x10001)
[cwise@localhost certs]$ openssl req -sha1 -new -x509 -nodes -days 36500 -key ca-key.pem > ca-cert.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:mysql
Email Address []:

[cwise@localhost certs]$ openssl req -sha1 -newkey rsa:2048 -days 36500 -nodes -keyout server-key.pem > server-req.pem
Generating a 2048 bit RSA private key
........................................+++
..............................................................................................+++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:mysql1
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[cwise@localhost certs]$ openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=mysql1
Getting CA Private Key

[cwise@localhost certs]$ openssl rsa -in server-key.pem -out server-key.pem
writing RSA key

生成客户端证书

[cwise@localhost certs]$ openssl req -sha1 -newkey rsa:2048 -days 36500 -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
...............................................+++
.+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:mysql2
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[cwise@localhost certs]$ openssl x509 -sha1 -req -in client-req.pem -days 36500 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=mysql2
Getting CA Private Key

[cwise@localhost certs]$ openssl rsa -in client-key.pem -out client-key.pem
writing RSA key

验证证书是否成功 如下返回两个OK则没有问题

[cwise@localhost certs]$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

2.1.2 证书配置

修改mysql配置

[cwise@localhost mysql]$ vim /data/app/mysql/my.cnf
# [client]下添加
ssl-cert=/data/app/mysql/certs/client-cert.pem
ssl-key=/data/app/mysql/certs/client-key.pem
# [mysqld]下添加
ssl-ca=/data/app/mysql/certs/ca-cert.pem
ssl-cert=/data/app/mysql/certs/server-cert.pem
ssl-key=/data/app/mysql/certs/server-key.pem

重启mysql服务

bash /data/app/mysql/scripts/mysql restart

登录mysql进行授权 强制使用ssl链接

mysql> revoke ALL PRIVILEGES ON *.* FROM 'mysql'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for mysql@'%';
+----------------------------------------+
| Grants for mysql@%                   |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'mysql'@'%' |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' identified by 'MysqlPass@777' REQUIRE SSL;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'localhost' identified by 'MysqlPass@777';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for mysql@'%';
+-------------------------------------------------+
| Grants for mysql@%                         |
+-------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' |
+-------------------------------------------------+
1 row in set (0.00 sec)

测试使用证书连接mysql

[cloudwise@localhost certs]$ /data/app/mysql/bin/mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem  -u mysql -pMysqlPass@777
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 2
Server version: 10.10.10-log Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql>

You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. 这个报错可以忽略 它是说 我们当前使用的socket链接的mysql 使用ssl也并不能比socket链接更安全

查看ssl状态

mysql> status;
--------------
/data/app/mysql/bin/mysql  Ver 14.14 Distrib 10.10.10, for Linux (x86_64) using  EditLine wrapper

Connection id:                5
Current database:
Current user:                Rootmaster@localhost
SSL:                        Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager:                stdout
Using outfile:                ''
Using delimiter:        ;
Server version:                10.10.10-log Source distribution
Protocol version:        10
Connection:                Localhost via UNIX socket
Server characterset:        utf8
Db     characterset:        utf8
Client characterset:        utf8
Conn.  characterset:        utf8
UNIX socket:                /data/appData/mysql/mysql.sock
Uptime:                        1 min 25 sec

Threads: 1  Questions: 14  Slow queries: 0  Opens: 98  Flush tables: 1  Open tables: 25  Queries per second avg: 0.164
--------------

2.2 Java客户端调整

2.2.1 将客户端密钥和证书文件转换为 PKCS 12 存档

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "cwmysqlclient" -passout pass:certPass123 -out client-keystore.p12

2.2.2 将证书放到Java服务中

如果服务是非容器化部署的 可以里直接在服务的目录下创建一个存放证书的目录 例如/data/app/java_service_name/certs 将truststore/client-keystore.p12放到这个目录里

如果是容器化的部署方案 需要创建secret或者configmap 然后挂载到pod里 直接更改deployment的yaml即可

2.2.3 更改jdbc链接参数

在原有的jdbc参数后 添加如下参数

useSSL=true&requireSSL=true&&verifyServerCertificate=true&trustCertificateKeyStoreUrl=file:/data/app/java_service_name/certs/truststore&trustCertificateKeyStorePassword=certPass123&clientCertificateKeyStoreUrl=file:/data/app/java_service_name/certs/client-keystore.p12&clientCertificateKeyStorePassword=certPass123

2.2.4 重启java服务

操作 略

3 避坑指南

3.1 证书核验出错: error 18 at 0 depth lookup:self signed certificate

检查客户端、服务端证书是否可用的时候提示这个报错
注意 服务端证书、客户端证书、根证书， 三者的CN(Common Name)不可以一样，一样会有这个报错。
按照如上的步骤操作 这步不会出错

3.2 云主机拦截Java服务的mysql请求

按照上述步骤操作后，如果云主机购买了企业安全检查等服务，那么Java服务连接mysql的请求可能会被认为是对mysql的暴力破解，以华为云为例，mysql的主机会有类似下边的iptables规则 及时你iptables -F 过一会就会再加上

Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
IN_HIDS_MYSQLD_BIP_DROP  tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306
IN_HIDS_MYSQLD_DENY_DROP  tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306
 
Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
 
Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         
 
Chain IN_HIDS_MYSQLD_BIP_DROP (1 references)
target     prot opt source               destination         
 
Chain   (1 references)
target     prot opt source               destination         
LOG        all  --  10.21.xxx.xxx        0.0.0.0/0            LOG flags 0 level 4 prefix "mysqld_drop: "
REJECT     all  --  10.21.xxx.xxx        0.0.0.0/0            reject-with icmp-port-unreachable

说到底 还是上边的ssl整数有问题 Java客户端拿着并不是完全匹配的证书来链接mysql 因为证书不匹配 自然无法正常连接mysql 因而别识别为对mysql的暴力破解

这个操作很隐蔽 也对我们造成了很大的困扰，走了一些弯路才找到解决办法
具体如下

• 根证书的Common Name字段的值置空
• 服务端证书的Common Name字段的值设置为mysql数据库的ip 必须是真是ip vip不可以
• 客户端证书的Common Name字段的值设置为连接mysql使用的用户名

如上操作 重新生成证书后 更换mysql和Java服务的证书 并重启服务 问题可以得到解决