[SQLのはじめ方] Part28: ユーザー管理とアクセス権限（GRANT, REVOKE）

はじめに: なぜユーザー管理と権限が必要なのか？

データベースを安全かつ効率的に運用するためには、「誰が」「どのデータに」「何をして良いか」を適切に管理することが不可欠です。これを実現するのがユーザー管理とアクセス権限の設定です。

例えば、顧客情報を扱うデータベースでは、次のような制御が必要になるでしょう。

• 営業担当者は、顧客情報の閲覧（SELECT）のみ可能。
• 個人情報管理者は、顧客情報の追加（INSERT）、更新（UPDATE）、削除（DELETE）も可能。
• システム管理者は、テーブル構造の変更（ALTER）や削除（DROP）も可能。
• 一般のウェブサイト訪問者は、特定の公開情報しか閲覧できない。

このように、役割に応じてアクセスできる範囲や操作の種類を制限することで、データの機密性、完全性、可用性を保つことができます。SQLでは、主にGRANT文とREVOKE文を使ってこれらの権限を管理します。

ユーザーの作成と管理

データベースにアクセスするには、まず「ユーザー」が必要です。多くのデータベースシステム（RDBMS）では、専用のSQLコマンドでユーザーを作成、変更、削除できます。

ユーザーの作成 (CREATE USER)

新しいユーザーを作成するには CREATE USER 文を使用します。多くの場合、パスワードも同時に設定します。（構文はRDBMSによって若干異なります）

例 (PostgreSQLやMySQLなど):

-- 'new_user' という名前のユーザーを作成し、パスワード 'password' を設定
CREATE USER new_user WITH PASSWORD 'secure_password';

-- MySQL の代替構文
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'secure_password';

ユーザーの変更 (ALTER USER)

既存ユーザーのパスワード変更などに ALTER USER 文を使用します。

例:

-- 'new_user' のパスワードを変更
ALTER USER new_user WITH PASSWORD 'new_strong_password';

-- MySQL の代替構文
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'new_strong_password';

ユーザーの削除 (DROP USER)

不要になったユーザーを削除するには DROP USER 文を使用します。

例:

-- 'new_user' を削除
DROP USER new_user;

-- MySQL の代替構文
DROP USER 'new_user'@'localhost';

ユーザーを削除すると、そのユーザーが所有していたオブジェクト（テーブルなど）の扱いに注意が必要です。依存関係がある場合は削除できないこともあります。

権限の種類について

SQLにおける権限（Privilege）は、ユーザーがデータベースオブジェクトに対して実行できる操作の種類を定義します。主な権限には以下のようなものがあります。

これらの権限は、データベース全体、特定のスキーマ、特定のテーブル、さらには特定のカラムといった細かいレベルで設定できます。

権限の付与 (GRANT)

GRANT文は、ユーザーやロールに対して特定の権限を与えるために使用します。基本的な構文は以下の通りです。

GRANT privilege [, ...]
ON object_type object_name [, ...]
TO user_or_role [, ...];

• privilege: 付与する権限 (例: SELECT, INSERT, ALL PRIVILEGES)
• object_type: 権限を付与する対象の種類 (例: TABLE, DATABASE, SCHEMA)
• object_name: 権限を付与する対象の名前 (例: employees, sales_db)
• user_or_role: 権限を受け取るユーザーまたはロールの名前

GRANT の使用例

例1: テーブルに対する参照権限を付与

-- 'staff_user' に 'products' テーブルの SELECT 権限を付与
GRANT SELECT ON TABLE products TO staff_user;

例2: 複数権限を一度に付与

-- 'staff_user' に 'orders' テーブルの SELECT, INSERT, UPDATE 権限を付与
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO staff_user;

例3: スキーマ内の全テーブルに対する権限付与 (PostgreSQL)

-- 'analyst_user' に 'public' スキーマ内の全テーブルに対する SELECT 権限を付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_user;

-- 将来作成されるテーブルにも適用する場合 (PostgreSQL)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analyst_user;

例4: データベースに対する接続権限 (PostgreSQL)

-- 'guest_user' に 'mydatabase' データベースへの接続権限を付与
GRANT CONNECT ON DATABASE mydatabase TO guest_user;

例5: 全ての権限を付与

-- 'admin_user' に 'customers' テーブルの全ての権限を付与
GRANT ALL PRIVILEGES ON TABLE customers TO admin_user;

WITH GRANT OPTION

WITH GRANT OPTION句を付けて権限を付与すると、権限を受け取ったユーザーが、さらに他のユーザーにその権限を付与できるようになります。

-- 'team_leader' に 'projects' テーブルの SELECT 権限を付与し、
-- さらに 'team_leader' が他のユーザーに SELECT 権限を付与できるようにする
GRANT SELECT ON TABLE projects TO team_leader WITH GRANT OPTION;

権限の剥奪 (REVOKE)

REVOKE文は、ユーザーやロールから以前にGRANTで付与された権限を取り消すために使用します。基本的な構文はGRANTと似ています。

REVOKE privilege [, ...]
ON object_type object_name [, ...]
FROM user_or_role [, ...];

REVOKE の使用例

例1: テーブルに対する参照権限を剥奪

-- 'staff_user' から 'products' テーブルの SELECT 権限を剥奪
REVOKE SELECT ON TABLE products FROM staff_user;

例2: 複数権限を一度に剥奪

-- 'staff_user' から 'orders' テーブルの INSERT, UPDATE 権限を剥奪
REVOKE INSERT, UPDATE ON TABLE orders FROM staff_user;

例3: 全ての権限を剥奪

-- 'admin_user' から 'customers' テーブルの全ての権限を剥奪
-- (実際には個別の権限を指定するか、RDBMS 固有の 'ALL' の REVOKE 構文を使う)
REVOKE ALL PRIVILEGES ON TABLE customers FROM admin_user;

GRANT OPTION の剥奪

GRANT OPTION FOR を使って、権限そのものではなく、権限を付与する能力（Grant Option）だけを剥奪できます。また、CASCADE オプションを付けると、そのユーザーが Grant Option を使って他のユーザーに付与した権限も連鎖的に剥奪されます。

-- 'team_leader' から 'projects' テーブルの SELECT 権限に対する Grant Option を剥奪
REVOKE GRANT OPTION FOR SELECT ON TABLE projects FROM team_leader;

-- Grant Option を剥奪し、さらに 'team_leader' が付与した権限も連鎖的に剥奪 (CASCADE)
REVOKE SELECT ON TABLE projects FROM team_leader CASCADE; -- この構文は権限自体も剥奪します

CASCADE の使用は意図しない権限剥奪を引き起こす可能性があるため、影響範囲をよく確認してから実行してください。

ロールによる効率的な権限管理

多数のユーザーに同じような権限セットを付与する場合、一人ひとりにGRANT文を実行するのは手間がかかり、管理も複雑になります。そこで役立つのがロール (Role) です。

ロールは、複数の権限をまとめた「役割」や「グループ」のようなものです。以下の手順で利用します。

1. ロールを作成する: CREATE ROLE文でロールを作成します。
2. ロールに権限を付与する: GRANT文で、作成したロールに必要な権限を付与します。
3. ユーザーにロールを付与する: GRANT文で、ユーザーにそのロールを割り当てます。

これにより、ユーザーに直接権限を付与する代わりに、ロールを付与するだけで済みます。権限の変更が必要になった場合も、ロールに対する権限を変更すれば、そのロールを持つ全てのユーザーに影響が及びます。

ロールの使用例

1. ロールの作成

-- 'sales_staff' という名前のロールを作成
CREATE ROLE sales_staff;

2. ロールへの権限付与

-- 'sales_staff' ロールに 'customers' テーブルと 'orders' テーブルへの SELECT, INSERT 権限を付与
GRANT SELECT, INSERT ON TABLE customers TO sales_staff;
GRANT SELECT, INSERT ON TABLE orders TO sales_staff;
-- 'products' テーブルへの SELECT 権限を付与
GRANT SELECT ON TABLE products TO sales_staff;

3. ユーザーへのロール付与

-- 'user1' と 'user2' に 'sales_staff' ロールを付与
GRANT sales_staff TO user1, user2;

これで、user1 と user2 は sales_staff ロールに定義された権限（customers と orders への SELECT/INSERT、products への SELECT）を持つことになります。

もし後で sales_staff に orders テーブルへの UPDATE 権限も必要になった場合、

GRANT UPDATE ON TABLE orders TO sales_staff;

を実行するだけで、user1 と user2 の両方に自動的に UPDATE 権限が適用されます。非常に効率的ですね！

ロールから権限を剥奪する場合は REVOKE を、ユーザーからロールを剥奪する場合も REVOKE を使用します。

-- 'sales_staff' ロールから 'orders' テーブルの INSERT 権限を剥奪
REVOKE INSERT ON TABLE orders FROM sales_staff;

-- 'user2' から 'sales_staff' ロールを剥奪
REVOKE sales_staff FROM user2;

ベストプラクティスと注意点

安全で管理しやすいデータベース運用のために、以下の点を心がけましょう。

• 最小権限の原則: ユーザーやロールには、業務に必要な最低限の権限のみを付与します。不要な権限はセキュリティリスクとなります。
• ロールの活用: 個々のユーザーに直接権限を付与するのではなく、業務内容に応じたロールを作成し、ユーザーにはロールを割り当てるようにします。これにより、権限管理が大幅に簡素化されます。
• 定期的な権限の見直し: ユーザーの異動や退職、役割変更などに伴い、不要になった権限やロールは速やかに剥奪・削除します。定期的に棚卸しを行いましょう。
• 強力な権限の管理: ALL PRIVILEGES や GRANT OPTION、管理者権限（superuserなど）を持つユーザーは最小限に留め、厳重に管理します。
• PUBLIC ロールへの注意: PUBLIC は全てのユーザーがデフォルトで持つロールです。PUBLIC に権限を付与すると、意図せず広範囲のユーザーに権限を与えてしまう可能性があるため、慎重に扱います。
• パスワードポリシー: ユーザー作成時には、十分に複雑なパスワードを設定し、定期的な変更を促すなどのパスワードポリシーを適用します。