MySQLユーザーアカウントのセキュアな管理と分離

あるプロジェクトでMySQLのマスターユーザーアカウントをチーム内で使い回すという禁忌を犯していたので、最適な権限を付与した上で各アカウントを作成しました。恥ずかしながらアカウント作成の操作をやったことがなかったので、調査した手順をまとめました。

アカウント分離の必要性

マスターユーザーアカウントはデータベースの管理と設定変更専用に保持され、通常のアプリケーションの運用には使用されません。以下の理由から、アカウント分離が必要となります。

1. 最小権限の原則 ： アプリケーション用のユーザーアカウントは、そのアプリケーションが必要とする最小限のデータベース権限だけを持つべきという原則です。アカウントが不正に使用された場合でも、その影響を最小限に抑えることができます。例えば、アプリケーションがデータの読み取りと書き込みのみを必要とする場合、そのユーザーアカウントにはSELECT、INSERT、UPDATE、およびDELETEの権限を与えます。
2. 分離： アカウント分離は、アプリケーションごと、または異なる機能ごとにユーザーアカウントを分けるという概念です。このアプローチにより、もしユーザーアカウントが不正に使用された場合でも、その影響が他の部分に広がることを防ぐことができます。
3. 監査： 個々のユーザーアカウントの使用によって、アプリケーションの動作を細かく追跡することが可能になります。各アカウントがどのような操作を行ったのかの追跡により、問題の特定や不正行為の早期発見が容易になります。

アカウントの追加方法

1. マスターユーザーでMySQLクライアントツール(MySQL Workbench)を使用してRDSインスタンスに接続： 接続情報（エンドポイント、ポート番号）、マスターユーザー名、マスターパスワードが必要です。

   • エンドポイント(MySQL Hostname)
   • ポート番号(MySQL Server Port)
   • マスターユーザー名(Username)
   • マスターパスワード(Password)

2. 新しいユーザーの作成： 以下のSQLコマンドを使用します。

   CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpassword';
   

   newuserは新しいユーザー名で、newpasswordはそのユーザーの新しいパスワードです。'%'で任意のホストからの接続を許可します。特定のIPアドレスまたはホスト名からの接続のみを許可する場合は、これを適切な値に変更します。

3. 新しいユーザーに権限を付与： 以下のSQLコマンドを使用します。

   GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'newuser'@'%';
   

   database_nameはユーザーに権限を付与するデータベースの名前です。'SELECT', 'INSERT', 'UPDATE', 'DELETE'は新しいユーザーに付与する権限の例です。必要に応じてこれらの権限を調整します。主な権限は以下です。

   1. ALL PRIVILEGES: 全ての権限。
   2. ALTER: テーブルの構造を変更する権限。
   3. CREATE: データベースやテーブルを作成する権限。
   4. DROP: データベースやテーブルを削除する権限。
   5. DELETE: レコードを削除する権限。
   6. INSERT: レコードを追加する権限。
   7. SELECT: データを参照する権限。
   8. UPDATE: レコードを更新する権限。
   9. GRANT OPTION: 他のユーザーに権限を付与または取り消す権限。
   10. PROCESS: サーバープロセスを表示または管理する権限。
   11. RELOAD: サーバー設定をリロードする権限。
   12. SHUTDOWN: サーバーをシャットダウンする権限。
   13. FILE: ファイルシステムレベルでのファイルアクセス権限。
   14. REFERENCES: 外部キーの制約を作成する権限。
   15. INDEX: インデックスを作成または削除する権限。
   16. ALTER ROUTINE: ストアドプロシージャと関数を変更または削除する権限。
   17. CREATE ROUTINE: ストアドプロシージャと関数を作成する権限。
   18. EXECUTE: ストアドプロシージャと関数を実行する権限。
   19. CREATE VIEW: ビューを作成する権限。
   20. SHOW VIEW: ビューの構造を表示する権限。
   21. CREATE TEMPORARY TABLES: 一時テーブルを作成する権限。
   22. LOCK TABLES: テーブルにロックを掛ける権限。
   23. REPLICATION CLIENT: マスター/スレーブレプリケーションの情報を要求する権限。
   24. REPLICATION SLAVE: レプリケーションスレーブとして動作する権限。

4. 変更を適用： 以下のSQLコマンドを実行して、変更をデータベースに適用します。

   FLUSH PRIVILEGES;
   

パスワードはAWS Systems Manager Parameter Storeなどで安全に保存し、定期的に変更します。

アカウントのパスワード変更方法

新しく作成したMySQLユーザーのパスワードを変更するには、以下のSQLコマンドを使用します。

ALTER USER 'username'@'%' IDENTIFIED BY 'newpassword';

'username'はパスワードを変更するユーザーの名前で、'newpassword'は新しいパスワードです。新しく作成したユーザーは、MySQL Workbenchのようなデータベースクライアントツールから接続するために使用できます。

アカウントの削除方法

ユーザーを削除するには、以下のSQLコマンドを使用します。

DROP USER 'username'@'%';

'username'は削除するユーザーの名前です。これらのコマンドを実行するには、十分な権限を持つユーザー（例えば、初めに設定したマスターユーザー）でデータベースに接続する必要があります。また、これらの操作はデータベースのセキュリティに影響を与えるため、適切な権限と知識を持つ人が行う必要があります。

アカウントの権限確認方法

ユーザーに設定した権限を確認するには、以下のSQLコマンドを使用します。

SHOW GRANTS FOR 'username'@'%';

こんな感じで表示されます。

アカウントのリスト確認方法

作成したアカウントのリストを確認するには、以下のSQLコマンドを使用します。

SELECT User, Host FROM mysql.user;

こんな感じで表示されます。

ログ監査

データベースの操作を詳細にログに記録するには、データベースエンジンの設定を適切に行う必要があります。MySQLでは、general_logというシステム変数をONに設定してGeneral Query Logを有効にする必要があります。

general_logを確認するためには、以下のSQLコマンドを実行します。

SELECT * FROM mysql.general_log;

パフォーマンスやストレージ使用量への影響があるので、general_logはデバッグを行うときだけONにします。

SET GLOBAL general_log = 'ON';

slow_log(Slow Query Log)を確認するためには、以下のSQLコマンドを実行します。

SELECT * FROM mysql.slow_log;