Mysql开启双向认证

吃饭不擦嘴
816 阅读3分钟

一、环境

  • jkd1.8
  • mysql-connector-version 5.1.49
  • mysql sever 5.7.34

二、使用客户端ssl链接mysql

  1. 使用root账户登陆mysql
 mysql -uroot -peauUx9rj8ezZx5bb -h127.0.0.1 -P3306
 
 #查看ssl是否启用
 show variables like '%ssl%';
 # yes为启用状态、DISABLED 为关闭状态，5.7+自带证书，证书安装目录在/data/dev/data/mysql/data/,此处无需配置证书信息
 +---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+

​ 如果/data/dev/data/mysql/data/中密钥证书信息都不存在的话，可以通过mysql自带的 ssl安装文件安装证书

/data/dev/im/soft/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/dev/data/mysql/data/

将生成的ssl证书放入到 --datadir 中，此刻建议使用dev账户执行，否则需要更改生成文件的属主和属组信息

​ 如果ssl为关闭状态，则需要把数据目录下.pem的文件，属主和属组改成dev

chown -R dev.dev *.pem
  • ca-cert.pem: CA 证书, 用于生成服务器端/客户端的数字证书.
  • ca-key.pem: CA 私钥, 用于生成服务器端/客户端的数字证书.
  • server-key.pem: 服务器端的 RSA 私钥
  • server-req.pem: 服务器端的证书请求文件, 用于生成服务器端的数字证书.
  • server-cert.pem: 服务器端的数字证书.
  • client-key.pem: 客户端的 RSA 私钥
  • client-req.pem: 客户端的证书请求文件, 用于生成客户端的数字证书.
  • client-cert.pem: 客户端的数字证书.

然后重启服务

  1. 配置账户开启ssl
  • SSL + 密码连接

mysql 5,7+版本默认如果授权没有做任何限制，用户既可以通过秘钥登录，也可以通过用户名和密码登录

​ 如果授权规定用户只能通过ssl方式登陆，则必须通过数据库root账号给予其他账号授权

#给standard账号授权ssl登陆
GRANT ALL PRIVILEGES ON *.* TO 'standard'@'%' IDENTIFIED BY '6lrkJuArkWyaJPRq' REQUIRE SSL; 
#给账号解除ssl限制
GRANT ALL PRIVILEGES ON *.* TO 'standard'@'%' IDENTIFIED BY '6lrkJuArkWyaJPRq' REQUIRE none; 

FLUSH PRIVILEGES;
通过密码直接连接mysql：
 mysql -ustandard -p6lrkJuArkWyaJPRq -h127.0.0.1 -P3306
 # 连接成功
通过ssl连接mysql
 mysql -ustandard -p6lrkJuArkWyaJPRq -h127.0.0.1 -P3306 --ssl
 # 连接成功
  • SSL + 密码 + 密钥连接
GRANT ALL PRIVILEGES ON *.* TO 'standard'@'%' IDENTIFIED BY '6lrkJuArkWyaJPRq' require X509;
使用账户密码连接：

​

[dev@localhost data]$ mysql -ustandard -p6lrkJuArkWyaJPRq -h127.0.0.1 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Unknown error 1045

必须使用ssl证书连接

通过密钥连接：
# 通过ssl连接mysql
mysql -ustandard -p6lrkJuArkWyaJPRq -h127.0.0.1 -P3306 --ssl-cert=/data/dev/data/mysql/data/client-cert.pem --ssl-key=/data/dev/data/mysql/data/client-key.pem
# 查看证书使用状态
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.34, for el7 (x86_64) using  EditLine wrapper

Connection id:          28302
Current database:
Current user:           standard@127.0.0.1
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.34-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               21306
Uptime:                 43 days 2 hours 13 min 55 sec

Threads: 16  Questions: 1592770  Slow queries: 0  Opens: 8682  Flush tables: 1  Open tables: 2000  Queries per second avg: 0.427
--------------

三、Java通过ssl连接mysql

  1. 制作jks文件生成truststore.jks和keystore.jks

使用jdk自带的keytool导入mysql的客户端证书到仓库，并生成文件;定位到证书的目录，在命令行中，使用CA证书ca-cert.pem，生成truststore.jks，这里的密码我使用vrv123456

keytool -import -noprompt -file ca-cert.pem -keystore truststore.jks -storepass vrv123456

使用客户端私钥client-key.pem，将客户端证书client-cert.pem，转化为P12格式，这里的密码我使用相同的vrv123456

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -out keystore.p12 -passout pass:vrv123456

使用生成的keystore.p12，生成keystore.jks，这里的密码需要保持一致

keytool -importkeystore -srckeystore keystore.p12 -srcstoretype PKCS12 -destkeystore keystore.jks -srcstorepass vrv123456 -deststorepass vrv123456

最终会生成三个文件keystore.jks、 truststore.jks、keystore.p12

  1. 将keystore.jks、 truststore.jks拷到java应用目录

我这里放在了项目的根目录 config下，采用配置文件的形式


spring.datasource.username=standard

spring.datasource.password=nzliMD9ZUp@JLesZ

# 两个jks加密文件的密码
ssl.jsk.password=vrv123456

# keystore.jks、 truststore.jks存放的目录
ssl.cert.path =/Develop/project/Only-weng/springBootProject/spring-boot-demo-parent/springboot-quickStart/config
# ssl连接配置
ssl.config=clientCertificateKeyStoreUrl=file:${ssl.cert.path}/keystore.jks&clientCertificateKeyStorePassword=${ssl.jsk.password}\
  &trustCertificateKeyStoreUrl=file:${ssl.cert.path}/truststore.jks&trustCertificateKeyStorePassword=${ssl.jsk.password}

# 数据库配置信息，开启ssl认证
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/IM_LOGIN?useUnicode=true&characterEncoding=utf8&verifyServerCertificate=true&useSSL=true&requireSSL=true&${ssl.config}

设置系统变量：

    @PostConstruct
    void postConstruct(){
        String tsp = "/Develop/project/Only-weng/springBootProject/spring-boot-demo-parent/springboot-quickStart/config/truststore.jks";
        System.setProperty("javax.net.ssl.trustStore", tsp);
        System.setProperty("javax.net.ssl.trustStorePassword", "vrv123456");
        System.setProperty("javax.net.ssl.keyStoreType", "JKS");
        
        String ksp = "/Develop/project/Only-weng/springBootProject/spring-boot-demo-parent/springboot-quickStart/config/keystore.jks.jks;";
        System.setProperty("Security.KeyStore.Location", ksp);
        System.setProperty("Security.KeyStore.Password", "vrv123456");

    }

四、golang通过ssl连接mysql

将client-cert.pem、client-key.pem拷贝到本地目录

为了能够使用证书进行身份验证，必须创建一个tls.Config，然后执行mysql.RegisterTLSConfig("custom", &tlsConf)，并将"?tsl=custom"添加到连接字符串中。 其中tls来自"crypto/tls"，mysql来自"github.com/go-sql-driver/mysql"

demo:

package main

import (
    "crypto/tls"
    "crypto/x509"
    "database/sql"
    "fmt"
    "io/ioutil"
    "log"

    "github.com/go-sql-driver/mysql"
    _ "github.com/go-sql-driver/mysql"
)


// path to cert-files hard coded
// Most of this is copy pasted from the internet
// and used without much reflection
func createTLSConf() tls.Config {

    rootCertPool := x509.NewCertPool()
  pem, err := ioutil.ReadFile("path/cert/ca-cert.pem")
    if err != nil {
        log.Fatal(err)
    }
    if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
        log.Fatal("Failed to append PEM.")
    }
    clientCert := make([]tls.Certificate, 0, 1)

    certs, err := tls.LoadX509KeyPair("path/cert/client-cert.pem", "path/cert/client-key.pem")
    if err != nil {
        log.Fatal(err)
    }

    clientCert = append(clientCert, certs)

    return tls.Config{
        RootCAs:            rootCertPool,
        Certificates:       clientCert,
        InsecureSkipVerify: true, // needed for self signed certs
    }
}


// Test that db is usable
// prints version to stdout
func queryDB(db *sql.DB) {
    // Query the database
    var result string
    err := db.QueryRow("SELECT NOW()").Scan(&result)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(result)
}

func main() {

    // When I realized that the tls/ssl/cert thing was handled separately
    // it became easier, the following two lines are the important bit
    tlsConf := createTLSConf()  
    err := mysql.RegisterTLSConfig("custom", &tlsConf)

    if err != nil {
        log.Printf("Error %s when RegisterTLSConfig\n", err)
        return
    }

    // connection string (dataSourceName) is slightly different
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?tls=custom", "username", "password", "dbHost", "dbPort", "database")
    db1, err := sql.Open("mysql", dsn)

    if err != nil {
        log.Printf("Error %s when opening DB\n", err)
        log.Printf("%s", dsn)
        return
    }
    defer db1.Close()
    e := db1.Ping()
    fmt.Println(dsn, e)
    queryDB(db1)
}

参考链接：

go mysql

go ssl demo

avatar
文章
阅读
粉丝