Posted inMySQL

MySQLのユーザー管理と権限設定：実践から学ぶTips & Tricks

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

よくある問題：アプリがrootでデータベースに接続している

スタートアップから20人以上の経験豊富なチームまで、10件以上のプロジェクトで同じパターンを見てきた——アプリの.envファイルにDB_USER=rootと書かれていて、誰もそれを問題だと思っていない。アプリは動いているし、デプロイも問題ない。それでいいじゃないか、と。

インシデントが起きるまでは。

深夜3時にデータベース破損を対処し、バックアップからのリストアに2時間かかったことがある。その経験から、プロジェクト全体のMySQLユーザー管理を見直した結果、これまで軽視していた問題が山積みだと気づいた。

なぜアプリにrootを使うのが間違いなのか

MySQLのrootアカウントはLinuxのsudoと同等——あらゆることができる。DATABASE DROPも、レコードの全件DELETEも、サーバー設定の変更も。

アプリがrootを使う場合のリスク：

  • 小さなSQLインジェクションの脆弱性がデータベース全体の消去につながりうる
  • コードのバグが意図せずDELETEの代わりにDROP TABLEを実行してしまう可能性がある
  • 認証情報が漏洩すると、攻撃者がMySQLサーバーを完全に掌握できる
  • 誰が何をしたか監査できない——すべてが「root」の操作になってしまう

セキュリティの基本原則は最小権限の原則（Principle of Least Privilege）——必要な権限だけを付与し、それ以上は与えない。MySQLには細かい権限設定システムがあり、最初から正しく設定しても5分もかからない。

MySQLのユーザー管理と権限設定コマンド

新しいユーザーの作成

ユーザーを作成する方法は3通りある——アプリがどこから接続するかによって選択する：

-- localhostからのみ接続を許可するユーザーを作成
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong_password_here';

-- 任意のIPアドレスから接続を許可するユーザーを作成
CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password_here';

-- 特定のIPアドレスからのみ接続を許可するユーザーを作成
CREATE USER 'appuser'@'192.168.1.100' IDENTIFIED BY 'strong_password_here';

よくある落とし穴：'user'@'localhost'と'user'@'%'はMySQLでは別々のユーザーとして扱われる。@'%'でユーザーを作成しても、アプリがlocalhost経由で接続すると、明確な理由なくaccess deniedエラーが返されることがある。

GRANTによる権限付与

MySQLには30種類以上の権限があるが、一般的なWebアプリが必要とするのは以下のものだけだ：

-- 特定のデータベースに対する読み書き権限を付与（通常のアプリ向け）
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'appuser'@'localhost';

-- 特定のデータベースに対するフル権限を付与（開発者・管理者向け）
GRANT ALL PRIVILEGES ON myapp_db.* TO 'devuser'@'localhost';

-- 読み取り専用権限を付与（レポート・分析向け）
GRANT SELECT ON myapp_db.* TO 'readonly_user'@'localhost';

-- 特定のテーブルに対する権限を付与
GRANT SELECT, INSERT ON myapp_db.orders TO 'appuser'@'localhost';

-- 変更を即座に反映
FLUSH PRIVILEGES;

現在の権限確認

-- 特定ユーザーの権限を確認
SHOW GRANTS FOR 'appuser'@'localhost';

-- 現在ログイン中のユーザーの権限を確認
SHOW GRANTS;

-- システム内の全ユーザー一覧を確認
SELECT user, host, account_locked FROM mysql.user;

REVOKEによる権限剥奪

-- DELETE権限を剥奪（SELECT、INSERT、UPDATEは維持）
REVOKE DELETE ON myapp_db.* FROM 'appuser'@'localhost';

-- データベースに対する全権限を剥奪
REVOKE ALL PRIVILEGES ON myapp_db.* FROM 'appuser'@'localhost';

-- ユーザーを完全に削除
DROP USER 'appuser'@'localhost';

ユーザーパスワードの変更

-- MySQL 5.7.6以降
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'new_strong_password';
FLUSH PRIVILEGES;

実践から学んだベストプラクティス

1. アプリごとに専用ユーザーを作成する

サーバーで3つのアプリ（ブログ、ショップ、CRM）を動かしている？それぞれのデータベースに対応した権限を持つ3つの独立したユーザーを作成しよう。1つのアプリが侵害されても、攻撃者が別のアプリのデータベースに侵入できなくなる。

CREATE USER 'blog_user'@'localhost' IDENTIFIED BY 'pass_blog';
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_db.* TO 'blog_user'@'localhost';

CREATE USER 'shop_user'@'localhost' IDENTIFIED BY 'pass_shop';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_user'@'localhost';

2. アプリユーザーにGRANT OPTIONを絶対に付与しない

GRANT OPTIONを持つユーザーは他のユーザーに権限を付与できる——実質的に管理者権限だ。DBAだけが持つべき権限であり、チームのセットアップスクリプトにGRANT ... WITH GRANT OPTIONがあれば、セキュリティモデル全体を見直す必要があるサインだ。

3. ユーザーにリソース制限を設ける

悪用されたユーザーが毎秒数千クエリを送信してサーバー全体をダウンさせる可能性がある。それが起きる前にリソース制限を設定しておこう：

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'pass'
  WITH MAX_QUERIES_PER_HOUR 10000
       MAX_CONNECTIONS_PER_HOUR 200
       MAX_USER_CONNECTIONS 20;

4. 定期的な棚卸し——使われていないユーザーを削除する

毎月、このクエリを実行してリストを確認しよう：

SELECT user, host, password_last_changed, account_locked 
FROM mysql.user 
ORDER BY password_last_changed;

退職した開発者、クローズしたプロジェクト、誰も使わなくなった古いステージング環境——全部DROPしよう。「幽霊アカウント」を放置して悪用されるのを待つ必要はない。

新規プロジェクトの標準セットアップ

データベースをセットアップするたびに使うワークフロー——アプリユーザーとデプロイユーザーを明確に分離する：

-- ステップ1: データベースを作成
CREATE DATABASE myproject_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ステップ2: アプリ用ユーザーを作成（本番環境で日常的に使用）
CREATE USER 'myproject_app'@'localhost' IDENTIFIED BY 'strong_app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myproject_db.* TO 'myproject_app'@'localhost';

-- ステップ3: マイグレーション・デプロイ用ユーザーを作成（デプロイ時のみ使用）
CREATE USER 'myproject_deploy'@'localhost' IDENTIFIED BY 'strong_deploy_password';
GRANT ALL PRIVILEGES ON myproject_db.* TO 'myproject_deploy'@'localhost';

-- ステップ4: 反映して確認
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'myproject_app'@'localhost';
SHOW GRANTS FOR 'myproject_deploy'@'localhost';

アプリユーザーはSELECT, INSERT, UPDATE, DELETEのみ——DROPもALTERもない。デプロイユーザーはALL PRIVILEGESを持つが、その認証情報は本番の.envには入れない。CI/CDパイプラインの中だけに存在し、マイグレーション実行時にのみ使用する。

深夜3時にデータベースを復旧させたあの経験から、すべてのプロジェクトにこの原則を適用し、さらにもう一つの習慣を加えた：毎日バックアップを確認すること。適切な権限設定はインシデント発生時の被害を最小化するが、本当にすべてが崩壊したときに救ってくれるのはバックアップだ。

Share:
Tags: