2
Help us understand the problem. What are the problem?

posted at

updated at

小ネタ/Aurora MySQL v1/v2 から v3 に移行する際のユーザ権限トラブルについて

AWS の Aurora MySQL v1(MySQL 5.6 互換)の EoL 発表によって、v2(同 5.7 互換)または v3(同 8.0 互換)への移行を計画している方もいらっしゃるのでは?と思います。

というわけで、わたしも Zenn のほうに記事をいくつか書きながら調査を進めております。

※Zenn に書いている理由 : 一連の記事を GitHub 管理したかったから

調査を進める中でテスト環境を立て、スナップショット復元で v1 → v2 → v3 移行する中でタイトルの問題点に引っかかったので、備忘として残しておきます。

なお、タイトルには Aurora MySQL と書きましたが、本家 MySQL でも発生しうる問題(のはず)です。

発生した問題その 1 : 管理者権限が移行されない

v3 移行後のサーバに MySQL Workbench で接続…はできたのですが、ユーザの一覧も表示されなければスキーマ(DB)もテーブルも何も見えない状態になりました。

原因

Selection of the account that matches incoming TCP client connections could be affected by account creation order. To make the matching algorithm more deterministic, matching the host name part of accounts now checks accounts specified using host IP addresses, in a specific order, before attempting to match accounts specified using host names. Host name matching remains unchanged. See Access Control, Stage 1: Connection Verification.

MySQL 8.0.23 より前は、リテラル IP アドレスの特異性はネットマスクがあるかどうかの影響を受けないため、198.51.100.13 と 198.51.100.0/255.255.255.0 は同等とみなされます。 MySQL 8.0.23 の時点では、ホスト部分に IP アドレスを持つアカウントには次のような特異性があります:
(後略)

実は、

  • '【ユーザ名】'@'%'
    • Aurora クラスタ/インスタンス作成時に自動生成されたユーザアカウント

と同じユーザ名の

  • '【ユーザ名】'@'【接続元 IP アドレス範囲】'

を手動で作って、こちらに前者と同じ権限を付与して運用していたのですが、v1 → v2 スナップショット復元では後者のユーザに管理者として必要な権限が移行されませんでした(メジャーバージョン移行時の仕様であり、不具合ではないと思います)。

それでもv2 時点では'【ユーザ名】'@'%'に指定された権限が優先され、MySQL Workbench ではユーザの操作もスキーマ・テーブルの操作もできました。

ところが前述のとおり MySQL 8.0.23 でユーザの評価ルールが変わり、接続元(アカウント名)の IP アドレス(ホスト)の特定度が高いものが優先されるようになったため、管理者権限が移行されなかった '【ユーザ名】'@'【接続元 IP アドレス範囲】'のほうで接続しているとみなされた、というわけです。

対処

  • 指定の IP 範囲外のクライアントからサーバに接続して'【ユーザ名】'@'【接続元 IP アドレス範囲】'を削除
  • それが無理ならあらためて v2 に戻って'【ユーザ名】'@'【接続元 IP アドレス範囲】'を削除してからスナップショット取得→ v3 で復元

のいずれかです。

発生した問題その 2 : 一部のユーザの権限が無効になる(アクセスが拒否される)

Reader インスタンスへのアクセスでSELECT権限だけ付与していたユーザのアクセスが、v2 までは問題がなかったのに v3 で拒否されるようになってしまいました。

原因

「権限付与対象スキーマとしてワイルドカード'%'(のみ)を指定すると権限が無効になる」 でした。

この指定は v2(MySQL 5.7)までは有効でした。

対処

  • GRANT 【権限】 ON '%'.* TO 【アカウント名】

  • GRANT 【権限】 ON *.* TO 【アカウント名】

に書き換えました。

ただ、本来であればワイルドカードで全てのスキーマに対して権限を付与するのは良くないので、個別のスキーマ名を指定して権限を付与すべきでしょう。

なお、↑のケースでは、SELECTではなく更新系の権限を指定すると、mysqlスキーマに対する更新系の権限をREVOKEする指定が自動的に生成されます。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
2
Help us understand the problem. What are the problem?