MySQLプロトコルを勉強および仕様に沿って実装する際に便利だったもの

ngrep

https://formulae.brew.sh/formula/ngrep

ngrepを使用することでMySQLサーバーとクライアント間のパケットのやり取りを監視できる。

sudo ngrep -x -q -d lo0 '' 'port {MySQLのポート番号}'

上記コマンドで監視を開始し、MySQLサーバーとクライアント間で通信を開始すると以下のようにパケットのやり取りが確認できるようになる。

interface: lo0 (127.0.0.0/255.0.0.0)
filter: ( port 13306 ) and (ip || ip6)

T 127.0.0.1:13306 -> 127.0.0.1:57537 [AP] #5
  4a 00 00 00 0a 38 2e 30    2e 33 33 00 5f 1e 00 00    J....8.0.33._...
  0c 20 40 4c 62 63 61 4f    00 ff ff ff 02 00 ff df    . @LbcaO.���..��
  15 00 00 00 00 00 00 00    00 00 00 56 68 25 2a 51    ...........Vh%*Q
  19 12 54 64 31 74 12 00    63 61 63 68 69 6e 67 5f    ..Td1t..caching_
  73 68 61 32 5f 70 61 73    73 77 6f 72 64 00          sha2_password.

T 127.0.0.1:57537 -> 127.0.0.1:13306 [AP] #7
  6f 00 00 01 07 a2 3e 19    ff ff ff 00 ff 00 00 00    o....�>.���.�...
  00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
  00 00 00 00 6c 64 62 63    5f 6d 79 73 71 6c 5f 6e    ....ldbc_mysql_n
  61 74 69 76 65 5f 75 73    65 72 00 20 39 9b 1e 96    ative_user. 9...
  8f fc ff 2c aa a6 5d 2e    ab d6 88 ec 2a 17 f7 ff    .��,��].��.�*.��
  57 30 58 63 23 7a 72 d2    37 82 78 9e 63 61 63 68    W0Xc#zr�7.x.cach
  69 6e 67 5f 73 68 61 32    5f 70 61 73 73 77 6f 72    ing_sha2_passwor
  64 00 00                                              d..

これを参考に世にあるライブラリと自作したライブラリでのパケットの違いを見比べるとどこがおかしいのかがよくわかる。

クライアントとサーバーの能力を交換

クライアントとのTCP接続が確立されたらサーバーからERRパケットもしくは、Initial Handshakeパケットが送られてくる。

各種バージョンや認証プラグイン、文字セットや照合順序、Server Capabilitiesなどの情報が含まれる

上記の他にも値の条件によって追加される項目がある。(v10参照)

最初のハンドシェイクはサーバーが Protocol::Handshake パケットを送ることから始まる。この後、オプションとしてクライアントは Protocol::SSLRequest: パケットで SSL 接続の確立を要求することができ、その後クライアントは Protocol::HandshakeResponse: パケットを送信する。

Capabilities Flags

MySQL プロトコルで使用されるビットマスクの値。

この情報を交換することで、クライアント/サーバー間で期待されていないフォーマットでデータを送るような事を防げる。

クライアントとサーバーの認証

クライアントがMySQLサーバーへ接続するときにLoginRequestというフェーズでユーザ情報を送信する。そして、サーバー側では送られたユーザがmysql.userテーブルにあるか検索し、どの認証プラグインを使用するか決定する。認証プラグインが決定した後に、サーバーはそのプラグインを呼び出してユーザー認証を開始し、その結果をクライアント側に送信する。このようにMySQLでは認証がプラガブル（様々なタイプのプラグインを付け外しできる）になっている。選べる認証プラグインは公式ページに記載されている。

https://dev.mysql.com/doc/refman/8.0/ja/authentication-plugins.html

今回は主に以下3つの認証プラグインに関してまとめる

• ネイティブプラガブル認証
• SHA-256 プラガブル認証
• SHA-2 プラガブル認証のキャッシュ

認証が正常に行われない場合はクライアント・サーバー間では以下の処理が行われる

• 認証方法の変更
• 接続の拒否

ネイティブプラガブル認証

ネイティブプラガブル認証のプラグインの名前はmysql_native_passwordです。

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_authentication_methods_native_password_authentication.html

mysql_native_passwordはパスワードハッシュ方式に基づいた認証を実装している。ハッシュ関数はSHA1が用いられている。以下のようにユーザ作成を行うと、mysql.userのauthentication_stringカラムにSHA1(SHA1(password))の結果が格納される。よって同じパスワードであれば同じauthentication_stringになる。

mysql> select user, host, plugin, authentication_string from mysql.user Where user = 'ldbc_mysql_native_user';
+------------------------+------+-----------------------+-------------------------------------------+
| user                   | host | plugin                | authentication_string                     |
+------------------------+------+-----------------------+-------------------------------------------+
| ldbc_mysql_native_user | %    | mysql_native_password | *88528E8B55AE11968B8861A21809294988EECA28 |
+------------------------+------+-----------------------+-------------------------------------------+
1 row in set (0.01 sec)

クライアント・サーバー間ではチャレンジ＆レスポンス認証が行われる。 サーバ側からInitial Handshakeパケット送信時に、クライアント側に20バイトのランダムデータ（Nonce）が送信され、クライアント側でその値を使用して、以下の計算をしてサーバーに送信する。

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

サーバーはauthentication_stringの値、すなわちSHA1( SHA1( password )とランダムデータの値を知っているので、それらとクライアントから送られてきた計算結果にXORとSHA1を作用させることで、パスワードハッシュを照合することができる。言い換えれば、ユーザが入力したパスワードに２回SHA1を作用させた値（クライアント側）とauthentication_stringの値（サーバー側）の照合をすることができる。

Scalaでの実装はこのようになる。

def hashPassword(password: String, scramble: Array[Byte]): Array[Byte] =
    if password.isEmpty then Array[Byte]()
    else
      val sha1 = MessageDigest.getInstance("SHA-1")

      val hash1 = sha1.digest(password.getBytes("UTF-8"))
      val hash2 = sha1.digest(hash1)
      val hash3 = sha1.digest(scramble ++ hash2)

      hash1.zip(hash3).map { case (a, b) => (a ^ b).toByte }

SHA-256 プラガブル認証

SHA-256 プラガブル認証のプラグインの名前はsha256_passwordです。

https://dev.mysql.com/doc/refman/8.0/ja/sha256-pluggable-authentication.html

SHA-256 プラガブル認証ではサーバー側でsalt付きのパスワードハッシュ値を作成しており、クライアント側へはそのsalt値を送信しないので、サーバーと同様のパスワードハッシュ値を生成できない。そのため、クライアント側で先ほどの計算式のような計算結果をサーバー側に送ったとしても、salt無しでハッシュ化されたパスワードが渡るのみで、サーバー側ではauthentication_stringとの照合ができない。

SHA-256 プラガブル認証ではSSL/TLS接続とRSA暗号化通信で送信するパスワードの形式が異なる。

• SSL/TLS接続時は安全な暗号化された通信経路での通信となるためパスワードの値をハッシュ化せずにそのままサーバーへ送信を行う。
• RSA暗号化通信を行う場合はサーバーからRSA 公開鍵が送られてくるため、クライアントはその公開鍵を使用して暗号化したパスワードをサーバーへ送信する。

MySQLデフォルトの公開鍵は以下クエリで確認できる。

mysql> SHOW STATUS LIKE 'Rsa_public_key'\G
*************************** 1. row ***************************
Variable_name: Rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----

SHA-2 プラガブル認証のキャッシュ

SHA-256 プラガブル認証のプラグインの名前はcaching_sha2_passwordです。

https://dev.mysql.com/doc/refman/8.0/ja/caching-sha2-pluggable-authentication.html

sha256_password認証プラグインはソルト付きパスワードに対して複数回のSHA256ハッシュを使用して、ハッシュ変換の安全性を高めていました。ただし、暗号化された接続または RSA キー ペアのサポートが必要です。したがって、パスワードのセキュリティは強化されますが、安全な接続と複数回のハッシュ変換により、認証プロセスにより多くの時間がかかってしまうというデメリットがありました。

caching_sha2_passwordではこのデメリットを解消するために以下のような処理を行なっています。
以下2つのフェーズで動作します。

1. Fast authentication
2. Complete authentication

サーバは、指定されたユーザのハッシュエントリをメモリにキャッシュしている場合、クライアントから送信されたスクランブルを使用して高速認証を実行する。成功すれば認証は完了し、接続はコマンドフェーズに移行する。エラーが発生した場合、サーバはクライアントに、安全な接続サーバ経由で パスワードを送信する完全認証に切り替えるよう合図を送る。サーバは次に、指定されたユーザアカウントの authentication_string とパスワードを照合する。成功した場合、サーバはそのアカウントのハッシュエントリをキャッシュし、接続はコマンドフェーズに入る。エラーの場合、サーバはクライアントにエラー情報を送信し、接続は終了する。

つまり

1. SHA256を使用したパスワードハッシュで接続を行う(mysql_native_passwordと同じ)
2. ハッシュ化されたパスワードのキャッシュがサーバー内に存在するか確認を行う

ここでサーバー内にキャッシュが存在するかしないかによって後続の処理が変わる。

キャッシュが存在する場合

キャッシュが存在する場合はmysql_native_passwordの時と同じようにパスワードのハッシュ値がクライアント・サーバー間で一致する場合は接続が成功となる。

キャッシュが存在しない場合

1. SSL/TLS接続時もしくはRSA暗号化通信を行いクライアント・サーバー間で認証を行う
2. 認証が成立した場合、サーバーはその認証情報でパスワードのキャッシュをキャッシュ内に保存を行う

これにより次回以降の接続は、サーバー内のキャッシュを使用して高速に認証を行うことができるようになる。

このようにcaching_sha2_passwordではユーザのパスワードハッシュの値をキャッシュ内に保存するため、クライアントがサーバーへ接続する際に該当するキャッシュが見つかれば、クライアントから送られてきたその値とパスワードハッシュの照合ができるようになるので、暗号化されていないチャネルを介した安全な認証が可能になります。
これはmysql_native_passwordのSHA1 ベースのチャレンジ/レスポンス メカニズムと比較してより高速なものとなっています。

サーバーとクライアント間の状態遷移とメッセージ交換の条件分岐は以下のようになる。

https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html

認証方法の変更

認証方法の不一致が発生した場合、サーバーはクライアントにProtocol::AuthSwitchRequestを送信する。Protocol::AuthSwitchRequestには、使用するクライアント認証方法の名前と、新しい方法によって生成される最初の認証ペイロードが含まれる。クライアントは、要求された認証メソッドに切り替え、そのメソッドに従った交換を継続する。

例えば、MySQLサーバーのデフォルト認証方式がcaching_sha2_passwordであった場合、Initial Handshakeにはcaching_sha2_passwordのプラグイン情報が送られてくるので、クライアントはまずcaching_sha2_password形式で認証を開始する。

しかし、クライアントログインを希望していたユーザーアカウントが認証方法にmysql_native_password を使用していた場合、認証方式が異なるためサーバーはmysql_native_passwordで使用する認証ペイロードをクライアントに送信し、クライアントはそのデータを使用して再度パスワードをハッシュ化してサーバーへ送信を行う。