はじめに: なぜユーザー管理と権限が必要なのか?
データベースを安全かつ効率的に運用するためには、「誰が」「どのデータに」「何をして良いか」を適切に管理することが不可欠です。これを実現するのがユーザー管理とアクセス権限の設定です。
例えば、顧客情報を扱うデータベースでは、次のような制御が必要になるでしょう。
- 営業担当者は、顧客情報の閲覧(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';
'localhost'
は接続元ホストを指定します。'%'
を指定すると任意のホストから接続可能になりますが、セキュリティリスクが高まります。
ユーザーの変更 (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)は、ユーザーがデータベースオブジェクトに対して実行できる操作の種類を定義します。主な権限には以下のようなものがあります。
権限 | 説明 | 対象オブジェクトの例 |
---|---|---|
SELECT | データの読み取り(参照) | テーブル, ビュー, カラム |
INSERT | データの挿入(追加) | テーブル, カラム |
UPDATE | データの更新 | テーブル, カラム |
DELETE | データの削除 | テーブル |
TRUNCATE | テーブル内の全データ削除(高速だがロールバック不可) | テーブル |
REFERENCES | 外部キー制約の作成 | テーブル, カラム |
CREATE | 新しいオブジェクト(データベース、テーブル、スキーマ等)の作成 | データベース, スキーマ, テーブルスペース |
ALTER | 既存オブジェクトの構造変更 | データベース, スキーマ, テーブル, etc. |
DROP | 既存オブジェクトの削除 | データベース, スキーマ, テーブル, etc. |
USAGE | スキーマやシーケンスなどの利用 | スキーマ, シーケンス, ドメイン, etc. |
EXECUTE | 関数やストアドプロシージャの実行 | 関数, プロシージャ |
ALL PRIVILEGES | 特定のオブジェクトに対するほぼ全ての権限(システムにより範囲は異なる) | データベース, スキーマ, テーブル, etc. |
これらの権限は、データベース全体、特定のスキーマ、特定のテーブル、さらには特定のカラムといった細かいレベルで設定できます。
権限の付与 (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;
REVOKE ALL PRIVILEGES
の挙動は RDBMS によって異なる場合があります。GRANT した権限を個別に REVOKE するのが確実です。
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) です。
ロールは、複数の権限をまとめた「役割」や「グループ」のようなものです。以下の手順で利用します。
- ロールを作成する:
CREATE ROLE
文でロールを作成します。 - ロールに権限を付与する:
GRANT
文で、作成したロールに必要な権限を付与します。 - ユーザーにロールを付与する:
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
に権限を付与すると、意図せず広範囲のユーザーに権限を与えてしまう可能性があるため、慎重に扱います。 - パスワードポリシー: ユーザー作成時には、十分に複雑なパスワードを設定し、定期的な変更を促すなどのパスワードポリシーを適用します。