【PostgreSQL】 SQL構文（DCL)：GRANT/REVOKE 文

2024.10.05

PostgreSQL SQL構文　DCL（Data Control Language） とは

DCLとは、データベースオブジェクトの権限管理やアクセス制御に関わるSQL文のことです。DCLを使うことで、データベースオブジェクトに対するユーザーのアクセス権限を設定・制御することができます。GRANT文とREVOKE文で制御します。

ここでは主に、以下の項目について紹介します。

  • スーパーユーザーと所有者のデフォルト権限

  • 権限の付与と取り消し方法

  • アクセス制御の対象となるオブジェクトと権限の種類


スーパーユーザーのデフォルトアクセス権限

スーパーユーザーはPostgreSQLの管理者ユーザーで、データベース全体に対して無制限の権限を持ち、以下の操作が可能です。

  • 全てのデータベースオブジェクトに対するフルアクセス

  • 権限付与・取り消し：他のユーザーに権限を付与したり取り消したりすることができます。

  • 他のユーザーとして実行：SET ROLEやSET SESSION AUTHORIZATIONを使って、他のユーザーとして振る舞うことができます。

  • スキーマやデータベースの作成・削除

  • PostgreSQLサーバーの設定変更：サーバーの構成（postgresql.confなど）を変更できます。

  • ファイルシステムへのアクセス：データベースの外部でファイルを読み書きすることができます。
    例えば、COPYコマンドを使用して外部ファイルの読み込みや書き込みが可能です。

  • ロール管理：新しいユーザー（ロール）を作成したり、スーパーユーザー権限を含むロールを管理することができます。

  • セキュリティ機能の無視：例えば、row-level security (RLS)やGRANT/REVOKEによって制限された権限も無視して操作が可能です。


所有者のデフォルトアクセス権限

データベースオブジェクトの所有者は、そのオブジェクトに対する完全なアクセス制御権を持っています。

  • フルコントロール権限：所有するオブジェクトに対して、読み取り、書き込み、削除、変更のすべての操作が可能です。

  • 権限の付与・取り消し：所有しているオブジェクトに対して、他のユーザーに権限を付与 (GRANT) したり、取り消し (REVOKE) することができます。

  • オブジェクトの変更：テーブルの構造を変更する（例：列を追加・削除する、インデックスを作成するなど）ことができます。また、関数やビューの定義を変更することも可能です。

  • オブジェクトの削除：所有するオブジェクトを削除する権限があります

  • トリガーやルールの作成：所有しているテーブルに対して、トリガーやルールを作成できます。

  • オブジェクトの移譲（所有権の変更）：ALTER OBJECT OWNERコマンドを使って、オブジェクトの所有権を他のユーザーに変更できます。

※他のユーザーのオブジェクトにはデフォルトではアクセスできません。



権限の付与と取り消し方法


GRANT文（権限の付与）の基本構文

GRANT文は、特定のユーザーやロールに対して、データベースオブジェクトの操作権限を付与します。

GRANT 権限名 ON 対象オブジェクト TO ユーザー名/ロール名;


GRANT文の例

下記の例では、employeesテーブルに対してユーザーaliceに、SELECTとINSERT権限を付与しています。

GRANT SELECT, INSERT ON TABLE employees TO alice;


REVOKE文（権限の取り消し）の基本構文

REVOKE文は、特定のユーザーやロールに対して、データベースオブジェクトの操作権限を取り消します。REVOKE ALL PRIVILEGESを使用すると、すべての権限を取り消すことができます。

REVOKE 権限名 ON 対象オブジェクト FROM ユーザー名/ロール名;


REVOKE文の例

下記の例では、employeesテーブルに対するユーザーaliceのSELECT権限を取り消しています。

REVOKE SELECT ON TABLE employees FROM alice;



アクセス制御の対象となるオブジェクトと権限の種類

以下、対象となるオブジェクトや権限の種類と、所有者/所有者以外のデフォルトアクセス権を示します。


データベース (Database)

データベースは、スキーマやテーブルなどを含むPostgreSQLの最上位オブジェクトです。

権限の種類権限内容所有者非所有者
CONNECTデータベースへの接続〇〇
CREATEデータベース内に新しいスキーマを作成　　　　　　　　〇×
TEMPORARY一時テーブルの作成〇〇
〇：可能　×：不可


スキーマ (Schema)

スキーマは、データベース内のオブジェクト（テーブル、ビューなど）の論理的なグループです。

権限の種類権限内容所有者非所有者
CREATEスキーマ内へのオブジェクト（テーブル、ビューなど）の作成〇×
USAGEスキーマ内オブジェクトへのアクセス〇×
〇：可能　×：不可


テーブル (Table)／ビュー (View)

テーブルは、データを格納する基本的なオブジェクトです。

権限の種類権限内容所有者非所有者
SELECTテーブル内レコードへの参照　　　　　〇×
INSERTテーブルへのレコード追加〇×
UPDATEテーブル内レコードへの変更〇×
DELETEテーブル内レコードの削除〇×
TRUNCATEテーブル全レコードの一括削除〇×
REFERENCES外部キー制約の参照先/被参照としてのテーブル利用　　　〇×
TRIGGERテーブルに対するトリガーの作成〇×
〇：可能　×：不可


列 (Column)

権限の種類権限内容所有者非所有者
SELECTテーブル内の列の参照〇×
INSERTテーブルへの行（指定した列のみ）の挿入　　　　　　　　〇×
UPDATEテーブルの行（指定した列のみ）の更新〇×
REFERENCES外部キーの参照/被参照〇×
〇：可能　×：不可


シーケンス (Sequence)

シーケンスは、連番を生成するためのオブジェクトです。

権限の種類権限内容所有者非所有者
SELECTシーケンスの現在値参照（currval()）〇×
USAGEシーケンスの利用（nextval()/currval()）　　　　　　　　　〇×
UPDATEシーケンスの現在値変更（nextal()/setval()）〇×
〇：可能　×：不可


関数 (Function)

権限の種類権限内容所有者非所有者
EXECUTE関数の実行　　　　　　　　　　　　　　　　　　　　　　　〇〇
〇：可能　×：不可


言語 (Language)

関数を記述するために使用する言語

権限の種類権限内容所有者非所有者
USAGE手続き型言語を使用した関数の作成　　　　　　　　　　　　〇〇
〇：可能　×：不可



ロール (Role)

ロールは、ユーザーやグループの管理を行うオブジェクトです。

権限の種類権限内容所有者非所有者
CREATE ROLE新しいロールの作成〇×
DROP ROLEロールの削除　　　　〇×
GRANT/REVOKE他のユーザーに対するロールの付与/取り消し　　　　　〇×
〇：可能　×：不可



まとめ

  • スーパーユーザーは、全てのデータベースオブジェクトに対して全ての権限を持っている。

  • オブジェクトの所有者は、所有するオブジェクトに対して全ての権限を持っている。

  • オブジェクトの所有者は所有するオブジェクトのアクセス権を、他のユーザーやロールに対してGRANT文で付与できる。

  • オブジェクトの所有者は付与したアクセス権を、REVOKE文で取り消しできる。

  • 対象となるオブジェクトと権限は、表に示す通り。

タイトルとURLをコピーしました