GRANT文・REVOKE文とは ― データベースの権限

GRANT文は、データベースの利用者に、表を読む・書き換えるなどの権限を与えるSQL文です。REVOKE文は、GRANT文で与えた権限を取り消すSQL文です。「社員の表は人事部だけが書き換えられる」「給与の列は一般の社員に見せない」といった決まりを、この2つの文とビューで設定します。

多くの利用者が同じ表を使うデータベースでは、利用者ごとに、どの表にどの操作を許すかを決めておく必要があります。この仕組みをアクセス制御と呼びます。GRANT文とREVOKE文は、その設定をSQLで書く文です。

PostgreSQLでは、表を作った利用者が所有者になり、所有者は最初からすべての権限を持ちます。管理者であるスーパーユーザーを除き、ほかの利用者は権限を与えられるまでその表を操作できません。SELECT文も、権限が無ければ拒否されます。

GRANT文には、与える権限・対象の表・相手の利用者の3つを書きます。次の文は、利用者Aに社員テーブルを読み出す権限を与えます。

GRANT SELECT ON 社員 TO A;

表を読み書きする権限の名前は、その権限で実行できるSQL文の名前と同じです。日本語では、これらを参照権限・挿入権限のように呼ぶこともあります。

権限はカンマで区切って一度に複数を与えられ、相手も複数書けます。PostgreSQLでは、PUBLICを相手にすると、すべての利用者に与えます。PostgreSQLとMySQLは、列名を括弧で書いて、列ごとに権限を与えることもできます。

GRANT INSERT, UPDATE ON 社員 TO A, B;
GRANT SELECT ON 社員 TO PUBLIC;
GRANT SELECT (社員ID, 氏名) ON 社員 TO C;

表の権限をまとめて与えるときは、権限の名前の代わりにALL PRIVILEGESと書きます。PostgreSQLでは、SELECT・INSERT・UPDATE・DELETEを含め、表に付けられる権限がすべて付きます。PostgreSQLではPRIVILEGESを省いてALLとも書けますが、標準SQLではPRIVILEGESが必要です。

GRANT ALL PRIVILEGES ON 社員 TO A;
REVOKE ALL PRIVILEGES ON 社員 FROM A;

MySQLの文書では、ALL PRIVILEGESは指定した範囲の権限のうち、GRANT OPTIONとPROXYを除くすべてを指します。まとめて与えると、仕事に必要のない権限まで付くので注意が必要です。

PostgreSQLでは、WHERE句で列を読むUPDATE文やDELETE文を実行するには、SELECT権限も必要です。データベースに接続する権限は、PostgreSQLではCONNECTという権限で表します。利用者の指定のしかたなど、細かな書き方は製品によって違います。

REVOKE文の形はGRANT文とほぼ同じで、相手を書く語がTOからFROMに変わります。与えるときは利用者「へ」、取り消すときは利用者「から」と読むと区別できます。

REVOKE UPDATE ON 社員 FROM A;

取り消すのは指定した権限だけで、同じ利用者のほかの権限は残ります。次の図で、所有者がGRANT文とREVOKE文を順に実行したときの変化を確かめられます。

SELECT * FROM 社員;
INSERT INTO 社員 VALUES (104, '江藤', '営業', 260000);
UPDATE 社員 SET 部署 = '営業' WHERE 社員ID = 101;
DELETE FROM 社員 WHERE 社員ID = 103;

-- まだGRANT文を実行していない

GRANT SELECT ON 社員 TO A, B, C;

GRANT INSERT, UPDATE ON 社員 TO A;

GRANT DELETE ON 社員 TO B;

REVOKE UPDATE ON 社員 FROM A;

REVOKE SELECT ON 社員 FROM C;

ふつうのGRANT文で権限を受け取った利用者は、その権限をほかの利用者に渡せません。GRANT文の最後にWITH GRANT OPTIONを付けると、受け取った利用者も同じ権限を与えられるようになります。PostgreSQLの文書は、PUBLICにはこの権利を与えられないと書いています。

-- 所有者が実行
GRANT SELECT ON 社員 TO A WITH GRANT OPTION;
-- Aが実行
GRANT SELECT ON 社員 TO B WITH GRANT OPTION;
-- Bが実行
GRANT SELECT ON 社員 TO C;

こうして渡した権限を取り消すとき、渡った先をどう扱うかをREVOKE文の最後で指定します。CASCADEは渡った先の権限もまとめて取り消し、RESTRICTは渡った先があれば取り消しを拒みます。

REVOKE SELECT ON 社員 FROM A CASCADE;

同じ状態でRESTRICTを指定すると、REVOKE文はエラーになり、A・B・Cの権限は残ります。標準SQLではどちらかの指定が必要ですが、PostgreSQLは省略するとRESTRICTとして扱います。MySQLのREVOKE文の構文には、CASCADEとRESTRICTがありません。

CASCADEで消えるのは、取り消した利用者を通って渡った権限だけです。たとえばCが所有者からも直接SELECT権限を受けていれば、Cはその権限で表を読み続けられます。

PostgreSQLでは、REVOKE文で取り消せるのは、自分が直接与えた権限だけです。前の例のCの権限はBが与えたものなので、所有者がCから取り消しても、エラーにならずに何も変わりません。Cから外すには、Bが自分で取り消すか、所有者がAの権限をCASCADEで取り消します。

-- 所有者が実行：Cの権限はBが与えたものなので残る
REVOKE SELECT ON 社員 FROM C;
-- Bが実行：Cの権限が消える
REVOKE SELECT ON 社員 FROM C;

権限は残して再付与の権利だけを取り上げるときは、PostgreSQLでは次のように書きます。

REVOKE GRANT OPTION FOR SELECT ON 社員 FROM A CASCADE;

このとき、Aから渡ったB・Cの権限はCASCADEで消えます。

GRANT文の対象には、表だけでなくビューも書けます。給与の列を除いたビューを作り、利用者Dにはそのビューを読む権限だけを与えます。

CREATE VIEW 社員一覧 AS
SELECT 社員ID, 氏名, 部署
FROM 社員;

GRANT SELECT ON 社員一覧 TO D;

するとDは、社員一覧からは氏名や部署を読めますが、社員テーブルを直接読むと拒否されます。ビューのWHERE句で行を絞れば、見せる行も限れます。列ごとの権限でも列は絞れますが、GRANT文の権限では行を選べません。行を絞るときは、ビューが役立ちます。

利用者が増えると、同じ権限を一人ずつ与えるのは手間がかかり、与え忘れや取り消し忘れも起きます。そこで、権限の組をロールという単位にまとめ、ロールを利用者に与えます。

CREATE ROLE 人事部;
GRANT SELECT, UPDATE ON 社員 TO 人事部;
GRANT 人事部 TO B;

PostgreSQLでは、BはロールをとおしてSELECTとUPDATEを実行できるようになります。REVOKE 人事部 FROM B; を実行すれば、これらの権限はまとめて外れます。MySQLも同じ形でロールを与えられますが、与えたロールを有効にする設定が別に必要です。

PostgreSQLでは、ロールを通して受けた権限を、本人から直接は取り消せません。利用者の権限は、直接受けた権限とロールの権限とPUBLICの権限を合わせたものだからです。そのため、所有者がREVOKE SELECT, UPDATE ON 社員 FROM B; を実行しても、Bは人事部ロールを通して表を読めます。

Bに読ませないようにするには、REVOKE 人事部 FROM B; でロールそのものを外します。MySQLの文書も、ロールを外したとき、同じ権限を直接や別のロールで受けていれば、その権限は残ると書いています。

どの権限を与えるかは、最小権限の原則で決めます。仕事に必要な権限だけを与え、不要な権限は与えない考え方です。AWSのIAMでも、同じ原則で権限を決めます。

SQL文は、表を定義するDDLと、データを読み書きするDMLに大きく分けられます。GRANT文とREVOKE文は、権限を扱うDCLとして3つ目の分類にまとめられることがあります。DCLはData Control Languageの略で、データ制御言語と訳されます。

ただし、分け方は資料によって違います。Huawei CloudのGaussDB(DWS)の文書はDCLと呼び、OracleのSQLリファレンスはDDLの一覧に入れています。表の定義はCREATE TABLEと制約の記事で説明しています。

• PostgreSQL公式ドキュメント「GRANT」（WITH GRANT OPTION、PUBLIC、列の権限、所有者の権限、ロールの付与）
• PostgreSQL公式ドキュメント「REVOKE」（CASCADEとRESTRICT、GRANT OPTION FOR、取り消せる権限の範囲）
• PostgreSQL公式ドキュメント「Privileges」（権限の種類、CONNECT、UPDATEにSELECTが必要なこと）
• MySQL公式ドキュメント「GRANT Statement」（WITH GRANT OPTION、列の権限、ロールの付与と有効化、ALL PRIVILEGES）
• MySQL公式ドキュメント「REVOKE Statement」（REVOKE文の構文とロールの取り消し）
• Oracle Database SQL Language Reference「Types of SQL Statements」（GRANT・REVOKEをDDLに分類）
• Huawei Cloud GaussDB(DWS)「DCL Syntax Overview」（DCLの定義）