1. 如果服务端配置了require_secure_transport = ON，那么客户端必须使用加密连接。如果客户端未开启SSL/TLS，则连接会报错

2. 如果服务端未配置require_secure_transport = ON，那么启用不启用主要看客户端。在mysql-connector-java-5.1.48版本中，如果服务端是5.7以上的版本并且配置了SSL/TLS，那么客户端默认就会启用加密连接

普通连接

客户端确定Authentication Plugin

private void proceedHandshakeWithPluggableAuthentication(String user, String password, String database, Buffer challenge) throws SQLException {

  int counter = 100;

  while (0 < counter--) {

      if (!done) {

          if (challenge != null) {

              if (this.connection.getUseSSL()) {
                  negotiateSSLConnection(user, password, database, packLength);
              }

              String pluginName = null;
              // Due to Bug#59453 the auth-plugin-name is missing the terminating NUL-char in versions prior to 5.5.10 and 5.6.2.
              if ((this.serverCapabilities & CLIENT_PLUGIN_AUTH) != 0) {
                  if (!versionMeetsMinimum(5, 5, 10) || versionMeetsMinimum(5, 6, 0) && !versionMeetsMinimum(5, 6, 2)) {
                      pluginName = challenge.readString("ASCII", getExceptionInterceptor(), this.authPluginDataLength);
                  } else {
                      pluginName = challenge.readString("ASCII", getExceptionInterceptor());
                  }
              }

              plugin = getAuthenticationPlugin(pluginName);
              if (plugin == null) {
                  plugin = getAuthenticationPlugin(this.clientDefaultAuthenticationPluginName);
              } else if (pluginName.equals(Sha256PasswordPlugin.PLUGIN_NAME) && !isSSLEstablished() && this.connection.getServerRSAPublicKeyFile() == null
                      && !this.connection.getAllowPublicKeyRetrieval()) {
                  plugin = getAuthenticationPlugin(this.clientDefaultAuthenticationPluginName);
                  skipPassword = !this.clientDefaultAuthenticationPluginName.equals(pluginName);
              }

              this.serverDefaultAuthenticationPluginName = plugin.getProtocolPluginName();

              checkConfidentiality(plugin);
              fromServer = new Buffer(StringUtils.getBytes(this.seed));
          } else {
              // no challenge so this is a changeUser call
          }

      } else {



      }

      // call plugin
      try {
          plugin.setAuthenticationParameters(user, skipPassword ? null : password);
          done = plugin.nextAuthenticationStep(fromServer, toServer);
      } catch (SQLException e) {
          throw SQLError.createSQLException(e.getMessage(), e.getSQLState(), e, getExceptionInterceptor());
      }

      // send response
      if (toServer.size() > 0) {
          if (challenge == null) {
              // no challenge so this is a changeUser call
          } else if (challenge.isAuthMethodSwitchRequestPacket()) {
              // write Auth Method Switch Response Packet
              last_sent = new Buffer(toServer.get(0).getBufLength() + HEADER_LENGTH);
              last_sent.writeBytesNoNull(toServer.get(0).getByteBuffer(), 0, toServer.get(0).getBufLength());
              send(last_sent, last_sent.getPosition());

          } else if (challenge.isRawPacket() || old_raw_challenge) {
              // write raw packet(s)
              for (Buffer buffer : toServer) {
                  last_sent = new Buffer(buffer.getBufLength() + HEADER_LENGTH);
                  last_sent.writeBytesNoNull(buffer.getByteBuffer(), 0, toServer.get(0).getBufLength());
                  send(last_sent, last_sent.getPosition());
              }

          } else {
              // write Auth Response Packet
              String enc = getEncodingForHandshake();

              last_sent = new Buffer(packLength);
              last_sent.writeLong(this.clientParam);
              last_sent.writeLong(this.maxThreeBytes);

              appendCharsetByteForHandshake(last_sent, enc);

              last_sent.writeBytesNoNull(new byte[23]);	// Set of bytes reserved for future use.

              // User/Password data
              last_sent.writeString(user, enc, this.connection);

              if ((this.serverCapabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA) != 0) {
                  // send lenenc-int length of auth-response and string[n] auth-response
                  last_sent.writeLenBytes(toServer.get(0).getBytes(toServer.get(0).getBufLength()));
              } else {
                  // send 1 byte length of auth-response and string[n] auth-response
                  last_sent.writeByte((byte) toServer.get(0).getBufLength());
                  last_sent.writeBytesNoNull(toServer.get(0).getByteBuffer(), 0, toServer.get(0).getBufLength());
              }

              if (this.useConnectWithDb) {
                  last_sent.writeString(database, enc, this.connection);
              }

              if ((this.serverCapabilities & CLIENT_PLUGIN_AUTH) != 0) {
                  last_sent.writeString(plugin.getProtocolPluginName(), enc, this.connection);
              }

              // connection attributes
              if (((this.clientParam & CLIENT_CONNECT_ATTRS) != 0)) {
                  sendConnectionAttributes(last_sent, enc, this.connection);
              }

              send(last_sent, last_sent.getPosition());
          }

      }

  }

  if (counter == 0) {
      throw SQLError.createSQLException(Messages.getString("CommunicationsException.TooManyAuthenticationPluginNegotiations"), getExceptionInterceptor());
  }

  try {
      this.mysqlConnection = this.socketFactory.afterHandshake();
  } catch (IOException ioEx) {
      throw SQLError.createCommunicationsException(this.connection, this.lastPacketSentTimeMs, this.lastPacketReceivedTimeMs, ioEx,
              getExceptionInterceptor());
  }
}

1. 优先使用服务端在Handshake包里下发的authentication_plugin。

2. 如果下发的插件客户端找不到，那么会使用客户端配置的默认插件

Auth Switch

Authentication Plugin配置

mysql> select user, host, authentication_string, plugin from mysql.user where user in ('test1', 'test11', 'test');
+--------+------+------------------------------------------------------------------------+-----------------------+
| user   | host | authentication_string                                                  | plugin                |
+--------+------+------------------------------------------------------------------------+-----------------------+
| test   |      | $A$005$
Tss.S\\77RERCSJzyxzNvkVoegaB.Xmu38KI4QrwZaE3oh2K6Cy.CNIQ7 | caching_sha2_password |
| test1  | %    | $5$#>!6TaP-m:m&lp9INc}$ZdOraw1Hk7IUHnAm6.Z1605JEG3J23BSi0jRWA05Ba4    | sha256_password       |
| test11 | %    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19                              | mysql_native_password |
+--------+------+------------------------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

三种常用的身份认证插件

mysql_native_password

select UPPER(SHA1(UNHEX(SHA1('password'))));

sha256_password

caching_sha2_password

• 使用SHA2算法来转换密码。具体来说，它使用SHA256算法。

• 保存在authentication_string列中的哈希值为加盐后的值，由于盐是一个20-byte的随机数，即使两个用户使用相同的密码，转换后的最终结果也将完全不同。

• 为了使使用暴力破解机制更难以猜测密码，在将最终转换存储在mysql.user表中之前，对密码和盐进行了 5000轮SHA2散列。

• COMPLETE：要求客户端安全地发送实际密码（通过TLS连接或使用RSA密钥对）。服务器生成5000轮哈希，并与mysql.user中存储的值进行比较。

• FAST：允许使用SHA2哈希的进行基于challenge-response的身份验证。同时实现高性能和安全性。

抓包

用户配置和默认一致

Auth Switch抓包

加密连接

SSL/TLS 握手

1. ClientHello
1. 客户端声明自己支持的一些能力（TLS版本/加密方法/压缩方法等）
2. 产生一个随机数，稍后用于生成“对话密钥”

2. ServerHello
1. 确定要用的TLS版本、加密方法等
2. 产生一个随机数，稍后用于生成“对话密钥”
3. 服务器证书下发

3. 客户端回应
1. 客户端先验证证书合法
2. 再产生一个随机数（pre-master），用于生成“对话密钥”，并用服务端公钥加密
3. 编码改变通知，表示随后的信息都将用双方商定的加密方法和密钥发送。
4. 客户端握手结束通知，表示客户端的握手阶段已经结束。这一项同时也是前面发送的所有内容的hash值，用来供服务器校验

4. 服务端最后回应
1. 编码改变通知，表示随后的信息都将用双方商定的加密方法和密钥发送
2. 服务器握手结束通知，表示服务器的握手阶段已经结束。这一项同时也是前面发送的所有内容的hash值，用来供客户端校验

关于Sha256PasswordPlugin的理解误区

public class Sha256PasswordPlugin implements AuthenticationPlugin {
    protected byte[] encryptPassword() throws SQLException {
        return encryptPassword("RSA/ECB/OAEPWithSHA-1AndMGF1Padding");
    }

    protected byte[] encryptPassword(String transformation) throws SQLException {
        byte[] input = null;
        try {
            input = this.password != null ? StringUtils.getBytesNullTerminated(this.password, this.connection.getPasswordCharacterEncoding())
                    : new byte[] { 0 };
        } catch (UnsupportedEncodingException e) {
            throw SQLError.createSQLException(Messages.getString("Sha256PasswordPlugin.3", new Object[] { this.connection.getPasswordCharacterEncoding() }),
                    SQLError.SQL_STATE_GENERAL_ERROR, null);
        }
        byte[] mysqlScrambleBuff = new byte[input.length];
        Security.xorString(input, mysqlScrambleBuff, this.seed.getBytes(), input.length);
        return ExportControlled.encryptWithRSAPublicKey(mysqlScrambleBuff,
                ExportControlled.decodeRSAPublicKey(this.publicKeyString, this.connection.getExceptionInterceptor()), transformation,
                this.connection.getExceptionInterceptor());
    }
}

用户不存在时的认证问题

异常CASE1

MySQL Protocol
    Packet Length: 253
    Packet Number: 1
    Login Request
        Client Capabilities: 0xa28f
        Extended Client Capabilities: 0x013a
        MAX Packet: 16777215
        Charset: utf8 COLLATE utf8_general_ci (33)
        Unused: 0000000000000000000000000000000000000000000000
        Username: root111
        Password: 6b62b5981db5ef7237c705c99832c5afa87b934833d923e6c1d406d374fd1f6d
        Schema: test
        Client Auth Plugin: caching_sha2_password
        Prefix: 139
        Length: 139
        Connection Attributes
    Payload: 00000000000000000000000000

异常CASE2

MySQL Protocol
    Packet Length: 254
    Packet Number: 1
    Login Request
        Client Capabilities: 0xa28f
        Extended Client Capabilities: 0x013a
        MAX Packet: 16777215
        Charset: utf8 COLLATE utf8_general_ci (33)
        Unused: 0000000000000000000000000000000000000000000000
        Username: root1111
        Password: 4bceb582d8c4f7ae48ebdc420008f74fee6979c9e1cb45c14e15d19c709649b8
        Schema: test
        Client Auth Plugin: caching_sha2_password
        Prefix: 139
        Length: 139
        Connection Attributes
    Payload: 00000000000000000000000000

参考

1. MySQL原生密码认证

2. 技术分享 | MySQL : SSL 连接浅析

3. 故障分析 | Java 连接 MySQL 8.0 排错案例

4. SSL/TLS协议运行机制的概述

5. [MySQL] MySQL身份验证插件

6. MySQL · 源码分析 · 鉴权过程

7. Connection Phase

8. 两个密码验证插件的故事

9. mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password

10. 【得物技术】MySQL 8.0：新的身份验证插件（caching_sha2_password）

11. 从源码分析 MySQL 身份验证插件的实现细节