データベースにおけるセキュリティ対策は重要です。データベースにはそのシステムにおけるデータが格納されており、データの閲覧や更新などの処理は厳重に管理される必要があります。
DBMSでは、ユーザに対して参照、書き込み、更新などの権限を個別に付与することができます。権限を与えられたユーザしかその操作ができません。
アクセス権の付与はSQLにて実行することができ、付与にはGRANT、はく奪にはREVOKE文を用います。今回はこれらSQLを使ったアクセス権の設定方法を紹介します。
今回はPostgresqlベースで紹介します。他のDBでもほとんど変わりませんが、権限の種類などは各DBのドキュメントを参照してください。
注意
1つのGRANT文で1つのデータベースオブジェクト(テーブルなど)への権限付与が基本です。複数テーブルまとめて権限付与はできないのでこちらも注意してください。
GRANT文ではあるテーブルの特定列に対するアクセス権の設定はできますが、特定行に対するアクセス権の設定はできません。特定行に対する権限を付与したい場合は、別途ビューを作成する必要があります。
GRANT文
GRANT文はデータベースオブジェクトに対する権限をユーザに付与するSQLです。まずは基本構文を確認します。
GRANT 権限名 ON データベースオブジェクト
TO ユーザ名
[WITH GRANT OPTION]
WITH GRANT OPTIONというのはオプションのコマンドで、必要な場合にのみ付けます。WITH GRANT OPTIONを付けると、アクセス権やロールが、同じアクセス権やロールを他のユーザに付与する権利を持つユーザ にも与えられます。
例えばuser1にテーブル1のSELECT権限を付与したとき、WITH GRANT OPTIONを付けると、user1はその権限からuser2とuser3にも同様のアクセス権(この場合SELECT権限)を付与できます。
その後user1のSELECT権限を剥奪したとき、user1だけでなくuser2、user3の権限も同時に剥奪されます。このような権限の連鎖がWITH GRANT OPTIONです。
権限の種類
権限の種類には様々ありますが、以下のようなものがあります。
SELECT | SELECTを許可します |
INSERT | INSERTを許可します |
UPDATE | UPDATEを許可します |
DELETE | DELETEを許可します |
REFERENCES | 外部キー制約の作成を許可します。参照する側、される側の両方にこの権限が必要です。 |
TRIGGER | トリガ作成を許可します |
CREATE | データベースでは、データベース内の新規スキーマ作成を許可します。 スキーマでは、スキーマ内の新規オブジェクトの作成を許可します。 |
USAGE | 手続き言語において、関数作成を指定言語で行うことを許可します |
ALL PRIVILEGES | オブジェクトに対して適用可能な権限を全て付与します。ALLでも可 |
SQLの例
GRANT文のSQL例をいくつか紹介します。
//user1に対し、table1のSELECT、UPDATE権限を付与
GRANT SELECT, UPDATE ON table1 TO user1;
//user1に対し、table1の全権限を付与。さらにWITH GRANT OPTIONを適用
GRANT ALL ON table1 TO user1 WITH GRANT OPTION;
//スキーマ内のテーブル全ての全権限をuser1に付与
GRANT ALL ON ALL TABLES IN SCHEMA public TO user1;
REVOKE文
GRANT文で付与した権限を剥奪する際にはREVOKE文を使います。基本構文を確認します。
REVOKE 権限名 ON データベースオブジェクト
FROM ユーザ名
GRANT時にWITH GRANT OPTIONがついている場合は対象ユーザからも連鎖的に権限が剥奪されます。
SQLの例
REVOKE文をSQL例をいくつか紹介します。
//user1からtable1のSELECT、UPDATE権限を剥奪
REVOKE SELECT, UPDATE ON table1 FROM user1;
//user1に対し、table1の全権限を剥奪
REVOKE ALL ON table1 FROM user1;
まとめ
SQLでのアクセス権の設定方法を紹介しました。ユーザの権限管理はデータのセキュリティ上重要です。不要な権限は与えず、最低限の権限だけを与えるようにしましょう。
ではでは👋