Posted inMySQL

MySQL 8 高度なセキュリティ実践：ロール（RBAC）の習得と caching_sha2_password 認証の現場活用

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

データベース拡大に伴う手動ユーザー管理の悪夢

バックエンド開発者、データアナリスト、さらには各種cronジョブを含め、30〜40人規模のメンバーでデータベースを運用した経験があるなら、手動での権限付与に頭を抱えたことがあるはずです。オンボーディングのたびに、各アカウントに対して何十行もの GRANT SELECT, INSERT... コマンドをコピー＆ペーストする羽目になります。さらに危険なのは、異動や退職時の権限剥奪の漏れです。以前の試用期間アカウントに DROP や DELETE 権限が1つ残っているだけで、データ漏洩や重大インシデントに直結しかねません。

私が担当している本番システムは、約50GBのECデータベースを持つMySQL 8.0で稼働しています。日々15以上のマイクロサービスと数十人の開発者が同時にアクセスしています。以前は権限マトリックスが断片化しており、監査のたびにチーム全体で半日を浪費していました。しかし、MySQL 8の2大コア機能であるロール（RBAC – ロールベースアクセス制御）とデフォルトの認証プラグイン caching_sha2_password を導入したことで、すべてが一気に整理されました。権限付与にかかる時間は15分からわずか30秒へと激減しました。

仕組みを正しく理解する：RBACと caching_sha2_password の動作原理

1. MySQL 8 のロール（RBAC）

MySQL 8において、ロール（Role）の実体はログイン用パスワードを持たないアカウントです。ロールはパッケージ化された権限グループとして機能します。ユーザー個別に権限を割り当てる代わりに：

  • 業務や職種に応じたロール（role_analyst、role_backend_dev、role_report_readonly など）をあらかじめ作成します。
  • 必要な権限をそのロールに対して一度だけ付与します。
  • 該当するユーザーにそのロールを割り当てます。権限の追加や制限が必要になった場合も、そのロールを修正するだけで済みます。個別のアカウントに手を加えることなく、全ユーザーに変更が即座に反映されます。

2. caching_sha2_password 認証メカニズム

MySQL 8.0以降、SHA-1を使用していた従来の mysql_native_password に代わり、caching_sha2_password がデフォルトとなりました。このメカニズムは主に2つの大きな課題を解決しています：

  • セキュリティの向上：認証情報をネットワーク経由で送信する際、SHA-256によるハッシュ化とRSA非対称鍵ペアを併用します。TLS/SSL接続が有効化されていない場合でも、パケット盗聴によってペイロードを傍受されても解読できません。
  • パフォーマンスの最適化：サーバーはクライアントの認証ハッシュをRAMキャッシュに保持します。再接続時には、負荷の高いRSA演算を再実行する代わりにメモリから直接照合します。これにより、アプリケーションがコネクションプールから数百の接続を一斉に確立した際でも、データベースのCPUスパイクを防ぐことができます。

実践：ロールによる権限制御とセキュアな認証の設定

ステップ1：業務に応じたロールの作成

例として、ecommerce_db というデータベースを管理する場合を考えます。データアナリスト向けの読み取り専用ロール（role_analyst）と、バックエンドAPI向けの読み書きロール（role_app_write）の2つを準備します。

-- CREATE ROLE 権限を持つ管理者アカウントでログインして実行
CREATE ROLE IF NOT EXISTS 'role_analyst', 'role_app_write';

-- アナリスト向けに ecommerce_db 内の全テーブルへの SELECT 権限を付与
GRANT SELECT ON ecommerce_db.* TO 'role_analyst';

-- バックエンドの業務CRUD処理に必要なDML権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ecommerce_db.* TO 'role_app_write';

-- デフォルトでDDL（DROP、ALTER等）は付与しないため、誤操作によるテーブル削除リスクを防止

ステップ2：caching_sha2_password とパスワードポリシーを適用したユーザー作成

caching_sha2_password プラグインを指定して新規ユーザーを作成し、同時に連続ログイン失敗時にアカウントを一時ロックするポリシーを設定します：

-- 90日ごとのパスワード変更を義務付け、4回連続失敗時に1日間アカウントをロックする制約付きでユーザー作成
CREATE USER 'tuan_data'@'%' 
IDENTIFIED WITH caching_sha2_password BY 'k9#Mv$99xL!zPq12'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 1;

-- 作成したユーザーにアナリストロールを割り当て
GRANT 'role_analyst' TO 'tuan_data'@'%';

ステップ3：必須テクニック — デフォルトロールの自動有効化

MySQL 8を使い始めたばかりの人がよく陥る落とし穴があります。「ロールを正しく割り当てたはずなのに、ユーザーがログインしてクエリを実行すると Access denied エラーになってしまう！」という問題です。

原因は、MySQL 8では新しいセッション開始時にロールが自動的に有効化されない仕様になっているためです。通常はユーザー側で手動で SET ROLE role_analyst; を実行しなければ権限が適用されません。ログイン後すぐに使えるようにするには、以下の2つの解決策があります：

-- 方法1：対象ユーザー個別にデフォルトロールを設定
SET DEFAULT ROLE 'role_analyst' TO 'tuan_data'@'%';

-- 方法2（推奨）：全ユーザーに対して付与されている全ロールをログイン時に自動有効化
SET PERSIST activate_all_roles_on_login = ON;

SET PERSIST コマンドを実行すると、設定が mysqld-auto.cnf に永続化されます。MySQLサービスを再起動しても設定が維持されるため、my.cnf を手動編集する手間が省けます。

ステップ4：実際の有効ロールと権限の確認・監査

現在ユーザーがどのロールで動作しており、どのような権限を持っているかを確認するには、次の2つのクエリを実行します：

-- 現在のセッションで有効化されているロールを確認
SELECT CURRENT_ROLE();

-- role_analyst 経由でユーザーに継承されている権限を確認
SHOW GRANTS FOR 'tuan_data'@'%' USING 'role_analyst';

ステップ5：旧バージョンクライアント／ドライバーでの接続エラー対処法

レガシーシステムや古い管理ツール（旧バージョンのDBeaver/Navicat、PHP 7.1、古いNode.jsドライバーなど）と連携する際、以下のようなエラーが発生することがあります：

Authentication plugin 'caching_sha2_password' cannot be loaded...

セキュリティレベルを下げてしまうため、安易に mysql_native_password へダウングレードしてはいけません。次の手順で解決を図りましょう：

  1. ドライバーのアップグレード：PHPであれば mysqlnd を利用するPHP 7.4以上へ移行し、Node.jsであれば mysql の代わりに mysql2 パッケージを採用します。2020年以降にリリースされた主要ドライバーはほぼすべてSHA-256に対応しています。
  2. 公開鍵取得フラグの有効化：TLS証明書が未設定の環境からMySQL CLIでリモート接続する場合は、パスワード暗号化のためにサーバーからRSA公開鍵を取得するパラメータを付与します：
# 暗号化キーを自動取得するために --get-server-public-key フラグを追加
mysql -u tuan_data -p -h 10.0.1.15 --get-server-public-key

まとめ

適切なデータベース管理において、場当たり的な権限付与は禁物です。ロール（RBAC）を導入すれば、権限を明確にグループ化し、人員の異動や退職時にもコマンド1つでアクセス権を回収できます。これに caching_sha2_password を組み合わせることで、認証情報の窃取を防ぎつつ、高負荷時でも接続遅延を最小限に抑えた堅牢なシステムを構築できます。

Share:
Tags: