mita2 database life

主にMySQLに関するメモです

MySQL 8.0 の新機能 デュアルパスワードでパスワード変更時のダウンタイムを回避する

MySQL 8.0で、「デュアルパスワード」がサポートされました。 1つのユーザに対して、新旧2つのパスワード(プライマリとセカンダリ)を設けることができます。

# どっちのパスワードでもログインできる
$ mysql -uapp -h$HOSTNAME -pPassw0rd@init -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

$ mysql -uapp -h$HOSTNAME -pPassw0rd@2nd -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

DB上でのパスワード変更のオペレーション と アプリケーション側に設定されているパスワードの変更 をまったく同時に行うことは困難です。 そのため、従来はパスワード変更の際は、メンテナンス時間を確保して対応する必要がありました。

もしくは、新しいパスワードを設定した別のユーザを追加し、アプリケーション(やスレーブ)を徐々に追加したユーザに切り替えていくことでダウンタイムを回避していました。 ユーザ名の変更が難しいケースもあると思います(SQL SECURITY DEFINER で指定しているとか、ユーザ名を利用してアクセス監査しているなど)。 デュアルパスワード機能で、ユーザ名を維持しつつ、ダウンタイムを回避してパスワード変更が可能になります。

試します

テスト用にユーザを作ります。

mysql> CREATE USER app@'%' IDENTIFIED BY 'Passw0rd@init';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, plugin, authentication_string, User_attributes FROM mysql.user Where user = 'app' \G
*************************** 1. row ***************************
                 User: app
                 Host: %
               plugin: caching_sha2_password
authentication_string: $A$005$#]
7J.Y=5F`mN[hZn/xX6nyS2Bmb2m0Vr6WnCH6aQz.kUFO2AdtIuh/.9
      User_attributes: NULL
1 row in set (0.00 sec)

パスワード変更時に RETAIN CURRENT PASSWORD を指定することで、旧パスワードを保持した上で、パスワード変更が可能です。 旧パスワード(セカンダリ)は mysql.user テーブルの User_attributes に記録されてました。

mysql> ALTER USER 'app'@'%' IDENTIFIED BY 'Passw0rd@2nd' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Host, plugin, authentication_string, User_attributes FROM mysql.user Where user = 'app' \G
*************************** 1. row ***************************
                 User: app
                 Host: %
               plugin: caching_sha2_password
authentication_string: $A$005$F2.("3Cli9Uq{9
TLFbCcuja.VoDO8FrvhR3Zi92cp.v5Mec/Zjcezww9eq0
      User_attributes: {"additional_password": "$A$005$#]\n\u00077J\u0003\u0002.\u0013Y=5F`m/\bN[hZn/xX6nyS2Bmb2m0Vr6WnCH6aQz.kUFO2AdtIuh/.9"}
1 row in set (0.00 sec)

新旧両方のパスワードでログインできます。

$ mysql -uapp -h$HOSTNAME -pPassw0rd@init -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

$ mysql -uapp -h$HOSTNAME -pPassw0rd@2nd -e 'SELECT CURRENT_USER()'
+----------------+
| CURRENT_USER() |
+----------------+
| app@%          |
+----------------+

最終的に、アプリケーション側のパスワード設定を変更し終わったら、DISCARD OLD PASSWORD で旧パスワードを破棄します。

mysql> ALTER USER 'app'@'%' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.00 sec)

権限まわり

RETAIN CURRENT PASSWORD を実行するには、CREATE USER 権限 or APPLICATION_PASSWORD_ADMIN 権限 が必要。

mysql> ALTER USER 'app'@'%' IDENTIFIED BY 'Password@10th' RETAIN CURRENT PASSWORD;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER or APPLICATION_PASSWORD_ADMIN privilege(s) for this operation

出来ないこと

  • セカンダリパスワードだけを変更する。一方、セカンダリを維持しつつ、プライマリのパスワードだけを変更することは可能。
  • ユーザがどっちのパスワードを利用して接続しているかの判別

(余談) 僕が本当に探しているもの

MySQL 8.0 移行時に、強制的に・短期間で、パスワードを mysql_native_password から caching_sha2_passwordマイグレーションする方法。 いろいろ調べてるけど、初期パスワードを発行しなおして、アプリケーション開発者に再設定してもらう方法に落ち着きそう。