本文へスキップ
ウェブエンジニア問題集
第12章

MySQLのユーザーと権限 — CREATE USER・GRANT・ロール・最小権限

約11分
この章の目次開く

スキーマ変更とマイグレーションでは、本番テーブルを安全に変更する方法を扱いました。そのDDLを実行できるのは誰か、アプリケーションがどのテーブルを読めるかは、MySQLのユーザーと権限で決まります。

Webアプリケーションを root で接続すれば、権限不足にはなりません。しかし、SQLインジェクションや設定漏れが起きたとき、全データベースの削除やユーザー作成まで許してしまいます。この章では、用途ごとにアカウントを分け、必要な操作だけを許可する設計を作ります。

アカウントはuserとhostの組み合わせ

MySQLのアカウントは、ユーザー名だけではなく 'user'@'host' で識別されます。

'app_user'@'localhost'
'app_user'@'10.0.1.25'
'app_user'@'%'

これらはすべて別のアカウントです。同じパスワードや権限を持つとは限りません。

host部分接続元
'localhost'MySQLサーバーと同じホストからのローカル接続
'10.0.1.25'指定したIPアドレスからの接続
'%'任意の接続元。便利だが範囲が広い

現在の認証結果は次で確認できます。

SELECT USER(), CURRENT_USER();
sql

USER() はクライアントが名乗ったユーザーと接続元、CURRENT_USER() はMySQLが権限判定に実際に使ったアカウントを返します。想定外のhost側アカウントへ一致したときの調査に役立ちます。

MySQLの権限を確認するときは、ユーザー名だけでなくhost部分まで含めて特定します。

CREATE USERで認証主体を作る

アカウントの作成と権限付与は別の操作です。MySQL 8.4では、先に CREATE USER、次に GRANT を実行します。

CREATE USER 'app_rw'@'10.0.1.25'
  IDENTIFIED BY 'replace-with-a-generated-secret'
  REQUIRE SSL;
sql

構文： CREATE USER 'ユーザー名'@'接続元' IDENTIFIED BY 'パスワード' [REQUIRE SSL]

指定説明
ユーザー名用途が分かる名前。アプリ・読み取り・バックアップなどを分ける
接続元接続を許可するホストやIPアドレス
IDENTIFIED BY認証に使うパスワードを設定する
REQUIRE SSLTLSで暗号化された接続だけを受け付ける

結果： 権限を持たない新しいアカウントを作成する

作成内容のうち、認証方式やTLS要件などは SHOW CREATE USER で確認できます。

SHOW CREATE USER 'app_rw'@'10.0.1.25';
sql

権限はまだありません。CREATE USER が成功しただけでは、アプリケーションのテーブルを読めない状態が正解です。

GRANTは必要な操作と範囲を指定する

読み書きするアプリケーションへ、1つのデータベース内で必要なDMLだけを付与します。

GRANT SELECT, INSERT, UPDATE, DELETE
ON myapp.*
TO 'app_rw'@'10.0.1.25';
sql

構文： GRANT 権限 [, 権限 ...] ON 対象 TO 'ユーザー名'@'接続元'

指定説明
権限許可する操作。SELECT、INSERT、UPDATE、DELETE など
対象*.*、database.*、database.table などの範囲
アカウント事前に CREATE USER した付与先

結果： 指定したアカウントまたはロールへ、指定範囲の権限を追加する

権限の範囲は、小さいほど事故時の影響を限定できます。

範囲例用途
グローバル*.*サーバー管理。通常のアプリには付けない
データベースmyapp.*1つのアプリが使うDB全体
テーブルmyapp.ordersレポートなど対象を限定したい場合
列myapp.users (id, display_name)特定列だけ許可したい場合
学習者学習者

あとで権限エラーになるのが怖いので、アプリにも ALL PRIVILEGES を付けたくなります。

権限エラーは、設計から漏れた操作を発見できたということです。本番アプリに CREATE USER、DROP、GRANT OPTION まで渡して障害範囲を広げるより、必要な権限を明示してテストで不足を見つけます。

最小権限とは「今動く最小」ではなく、そのアカウントの担当業務を完了できる最小の権限です。

用途ごとにアカウントを分ける

1つのアカウントをアプリケーション、マイグレーション、バックアップ、分析で共有すると、どの処理にも最も強い権限が必要になります。用途ごとに分ければ、通常時に強い権限を使わずに済みます。

用途主な権限付けない権限の例
WebアプリSELECT, INSERT, UPDATE, DELETEDROP, CREATE USER, GRANT OPTION
マイグレーションCREATE, ALTER, INDEX, DROPユーザー管理権限
読み取り専用SELECT書き込み・DDL
バックアップSELECT, SHOW VIEW, TRIGGER などアプリデータの更新

本番のWebプロセスには app_rw だけを渡し、強い schema_admin はデプロイ作業の間だけ使用します。第13章のバックアップも専用アカウントへ分離します。

SHOW GRANTSで答え合わせする

付与後は、意図した権限になっているか確認します。

SHOW GRANTS FOR 'app_rw'@'10.0.1.25';
sql

構文： SHOW GRANTS [FOR 'ユーザー名'@'接続元'] [USING ロール [, ロール ...]]

指定説明
FOR確認するアカウント。省略時は現在のユーザー
USING指定ロールを有効にした場合の権限も表示する

戻り値： 対象アカウントへ付与された権限とロールを、再現可能な GRANT 文の形で返す

GRANT USAGE ON *.* TO `app_rw`@`10.0.1.25`
GRANT SELECT, INSERT, UPDATE, DELETE ON `myapp`.* TO `app_rw`@`10.0.1.25`

USAGE は「何でも使える」という意味ではなく、グローバル権限を付与していないアカウントが存在することを表します。

設定内容をチェックしている人のイラスト
GRANTしたらSHOW GRANTSで、想定より広くも狭くもないか確認する

ロールで権限セットを再利用する

同じ権限を複数ユーザーへ繰り返し付与すると、変更時に漏れが出ます。ロールは権限をまとめるためのアカウントです。

CREATE ROLE 'app_reader', 'app_writer';
 
GRANT SELECT
ON myapp.*
TO 'app_reader';
 
GRANT INSERT, UPDATE, DELETE
ON myapp.*
TO 'app_writer';
sql

作成したロールをユーザーへ割り当て、接続時に有効になる既定ロールを設定します。

GRANT 'app_reader', 'app_writer'
TO 'app_rw'@'10.0.1.25';
 
SET DEFAULT ROLE 'app_reader', 'app_writer'
TO 'app_rw'@'10.0.1.25';
sql

部署やアプリケーション単位でロールを作ると、「誰に何を許したか」ではなく「この役割に何を許すか」を管理できます。

REVOKE・ALTER USER・DROP USERで変更する

不要になった権限は明示的に取り消します。

REVOKE DELETE
ON myapp.*
FROM 'app_rw'@'10.0.1.25';
sql

結果： 指定した権限だけを対象アカウントから取り消す

一時的に接続を止めるなら、削除せずロックできます。

ALTER USER 'app_rw'@'10.0.1.25' ACCOUNT LOCK;
 
ALTER USER 'app_rw'@'10.0.1.25' ACCOUNT UNLOCK;
sql

パスワードを更新するときも ALTER USER を使います。

ALTER USER 'app_rw'@'10.0.1.25'
  IDENTIFIED BY 'new-generated-secret';
sql

アカウントが完全に不要になったら削除します。

DROP USER 'app_rw'@'10.0.1.25';
sql

アプリケーションの設定変更より先に削除すると接続障害になるため、新しい認証情報で接続できることを確認してから古いアカウントを止めます。

認証情報を安全に運用する

  • 本番アプリケーションは root で接続しない
  • ソースコードやDockerイメージへパスワードを埋め込まない
  • 接続元を限定できるなら '%' より狭くする
  • ネットワーク越しの接続はTLSを使う
  • 用途ごとに別アカウントを作り、共有を避ける
  • 定期的に SHOW GRANTS と利用状況を棚卸しする
  • ローテーションでは新旧認証情報の共存期間を作る
  • 退職者・終了したバッチ・廃止サービスのアカウントを残さない

よくあるハマりどころ

GRANTだけでユーザーも作られると思う

古いMySQLの情報では GRANT と同時にアカウントを作る例があります。MySQL 8.4では、CREATE USER でアカウントを作ってから GRANT します。

userだけを見てhostを見落とす

'app'@'localhost' に権限を付けても、コンテナや別ホストから接続する 'app'@'%' には反映されません。CURRENT_USER() と SHOW GRANTS で実際に一致したアカウントを確認します。

ロールを割り当てただけで有効だと思う

ロールが割り当て済みでも、既定ロールでなければ再接続時に有効になりません。SET DEFAULT ROLE と CURRENT_ROLE() まで確認します。

バックアップ処理へ書き込み権限を渡す

バックアップは基本的に読み取り処理です。必要な補助権限だけを追加し、INSERT、UPDATE、DELETE は渡しません。

WITH GRANT OPTIONをアプリへ付ける

WITH GRANT OPTION は、自分が持つ権限を他アカウントへ付与できる権限です。通常のアプリケーションには不要で、侵害時の横展開を容易にします。

ちゃんと使うためのポイント

  • MySQLアカウントは 'user'@'host' の組み合わせで識別される
  • CREATE USER で認証主体を作り、GRANT で権限を別に付与する
  • アプリケーションにはDMLだけを付け、DDL・ユーザー管理・GRANT OPTION を渡さない
  • アプリ、マイグレーション、参照、バックアップでアカウントを分ける
  • SHOW GRANTS で、付与後の実際の権限を確認する
  • 共通の権限セットはロールへまとめ、SET DEFAULT ROLE で接続時に有効化する
  • 不要な権限は REVOKE、一時停止は ACCOUNT LOCK、廃止は DROP USER で行う
  • 認証情報はGitへ入れず、TLS・接続元制限・ローテーションを組み合わせる

最小権限を作っても、データを失ったときに戻せなければ運用は完成しません。バックアップ・スロークエリログ・設定運用では、mysqldump の取得と復元テスト、遅いSQLの発見、設定値の永続化を扱います。

参考リンク

SQLクイズに挑戦するユーザーと権限の知識を、4択クイズでアウトプットして定着させよう