Amazon Redshift [新機能]カラムレベルのアクセス制御の挙動を確認してみた

石川覚

2020.03.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Redshiftのカラムレベルのアクセス制御がクラスタバージョン1.0.13448から利用可能になりました。権限関連は正常系よりも準正常系や異常時が重要になりますので、その挙動を確認しました。

Amazon Redshift のための列レベルのアクセスコントロールの発表

カラムレベルのアクセス制御とは
検証用の環境
ユーザー
テーブルとビュー
カラムレベルのアクセス制御（SELECT権限）の検証
usersテーブルとusers_vwビューにSELECT権限を付与
usersテーブルの一部のカラムにSELECT権限を付与
users_vwビューの一部のカラムにSELECT権限を付与
カラムレベルのアクセス制御（UPDATE権限）の検証
usersテーブルの一部のカラムにUPDATE権限を付与
最後に

カラムレベルのアクセス制御とは

Redshiftのテーブル・ビューに対して、カラム毎にアクセス制御が設定できるようになりました。

テーブルの場合、列レベルでSELECTおよびUPDATE権限のみを付与できます。
ビューの場合、列レベルでSELECT権限のみを付与できます。

例えば、これまでのテーブルレベルのアクセス制御では、SELECTを指定していました。

GRANT SELECT ON TABLE users TO tickit_test;

更に、SELECT(userid, username, firstname, lastname)の様にどのカラムをSELECTできるかを指定できるようになります。

GRANT SELECT(userid, username, firstname, lastname) ON users TO tickit_test;

上記の例は、テーブルですがビューについても同じ様に指定できます。テーブルの場合はSELECTに加えて、UPDATEもカラムレベルでアクセス制御可能です。

検証用の環境

今回の検証内容を簡素化するためにユーザーに直接権限を付与しました。（本番環境では、権限はグループに対して付与するように徹底することをおすすめします。）

ユーザー

ユーザーは、usersテーブルをオブジェクトオーナーであるtickitユーザーと、権限の検証用のtickit_testユーザーを用います。以降では、tickit_testユーザーに付与する権限を切り替えて、カラムレベルのアクセス制御を検証します。

オブジェクトオーナー：tickit
権限の検証用ユーザー：tickit_test

テーブルとビュー

以下のテーブルとビューは、tickitユーザーがオブジェクトオーナーです。

テーブル：users
ビュー：users_vw

cmdb=> \d
List of relations
schema | name | type | owner
--------+----------+-------+--------
tickit | users | table | tickit
tickit | users_vw | view | tickit
(2 rows)

cmdb=> \d users
Table "tickit.users"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
userid | integer | | not null |
username | character(8) | | |
firstname | character varying(30) | | |
lastname | character varying(30) | | |
city | character varying(30) | | |
state | character(2) | | |
email | character varying(100) | | |
phone | character(14) | | |
likesports | boolean | | |
liketheatre | boolean | | |
likeconcerts | boolean | | |
likejazz | boolean | | |
likeclassical | boolean | | |
likeopera | boolean | | |
likerock | boolean | | |
likevegas | boolean | | |
likebroadway | boolean | | |
likemusicals | boolean | | |

cmdb=> \d users_vw
View "tickit.users_vw"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
userid | integer | | |
username | character(8) | | |
firstname | character varying(30) | | |
lastname | character varying(30) | | |
city | character varying(30) | | |
state | character(2) | | |
email | character varying(100) | | |
phone | character(14) | | |
likesports | boolean | | |
liketheatre | boolean | | |
likeconcerts | boolean | | |
likejazz | boolean | | |
likeclassical | boolean | | |
likeopera | boolean | | |
likerock | boolean | | |
likevegas | boolean | | |
likebroadway | boolean | | |
likemusicals | boolean | | |

カラムレベルのアクセス制御（SELECT権限）の検証

usersテーブルとusers_vwビューにSELECT権限を付与

初期状態では、tickit_testユーザーは、usersテーブルとusers_vwビューを参照できません（L.3〜L.7）
テーブルに対して参照権限を付与します（L.10〜L.13）
（当たり前ですが）全てのカラムが参照できます（L.17〜L.57）
元の状態に戻すため、権限を剥奪します（L.62〜L.65）

テーブルに対して参照権限を付与します。下記の通り、当たり前ですが全てのカラムが参照できます。

cmdb=# SET SESSION AUTHORIZATION tickit_test;
SET
cmdb=> SELECT * FROM users LIMIT 1;
ERROR: permission denied for relation users
cmdb=> SELECT * FROM users_vw LIMIT 1;
ERROR: permission denied for relation users_vw
cmdb=> RESET SESSION AUTHORIZATION;
RESET

cmdb=# GRANT SELECT ON TABLE users TO tickit_test;
GRANT
cmdb=# GRANT SELECT ON TABLE users_vw TO tickit_test;
GRANT

cmdb=# SET SESSION AUTHORIZATION tickit_test;
SET
cmdb=> SELECT * FROM users LIMIT 1;
-[ RECORD 1 ]-+----------------------------------------
userid | 2
username | PGL08LJI
firstname | Vladimir
lastname | Humphrey
city | Murfreesboro
state | SK
email | [email protected]
phone | (783) 492-1886
likesports |
liketheatre |
likeconcerts |
likejazz | t
likeclassical | t
likeopera |
likerock |
likevegas | t
likebroadway | f
likemusicals | t

cmdb=> SELECT * FROM users_vw LIMIT 1;
-[ RECORD 1 ]-+----------------------------------------------
userid | 1
username | JSG99FHE
firstname | Rafael
lastname | Taylor
city | Kent
state | WA
email | [email protected]
phone | (664) 602-4412
likesports | t
liketheatre | t
likeconcerts |
likejazz | f
likeclassical | t
likeopera |
likerock |
likevegas | t
likebroadway | f
likemusicals | t

cmdb=> RESET SESSION AUTHORIZATION;
RESET

cmdb=# REVOKE SELECT ON TABLE users FROM tickit_test;
REVOKE
cmdb=# REVOKE SELECT ON TABLE users_vw FROM tickit_test;
REVOKE

以降では、新機能「カラムレベルのアクセス制御」を試してみます。

usersテーブルの一部のカラムにSELECT権限を付与

一部のカラム（userid, username, firstname, lastname）のみに参照権限を付与して、参照や更新を確認します。

cmdb=# GRANT SELECT(userid, username, firstname, lastname) ON users TO tickit_test;
GRANT

cmdb=# SET SESSION AUTHORIZATION tickit_test;
SET

cmdb=> SELECT * FROM users LIMIT 1;
ERROR: permission denied for relation users

cmdb=> SELECT userid, username, firstname, lastname FROM users LIMIT 1;
-[ RECORD 1 ]-------
userid | 1
username | JSG99FHE
firstname | Rafael
lastname | Taylor

cmdb=> SELECT userid, username, firstname, lastname, city FROM users LIMIT 1;
ERROR: permission denied for relation users
cmdb=> RESET SESSION AUTHORIZATION;
RESET

-- REVOKE SELECT(userid, username, firstname, lastname) ON TABLE users FROM tickit_test;

SELECT *（L.7〜L.8）
全てのカラムは参照できないのでエラーになります
SELECT userid, username, firstname, lastname（L.10〜L.15）
権限を付与したカラムが正常に参照できます
SELECT userid, username, firstname, lastname, city（L.17〜L.18）
権限を付与していないカラム（city）を含むのでエラーになります
エラーメッセージはテーブルに権限が付与されていない場合と同じため、カラムの権限不足なのか見分けられません

users_vwビューの一部のカラムにSELECT権限を付与

一部のカラム（userid, username, firstname, lastname）に参照権限を付与して、参照を確認します。

cmdb=# GRANT SELECT(userid, username, firstname, lastname) ON users_vw TO tickit_test;
GRANT

cmdb=# SET SESSION AUTHORIZATION tickit_test;
SET

cmdb=> SELECT * FROM users_vw LIMIT 1;
ERROR: permission denied for relation users_vw

cmdb=> SELECT userid, username, firstname, lastname FROM users_vw LIMIT 1;
-[ RECORD 1 ]-------
userid | 2
username | PGL08LJI
firstname | Vladimir
lastname | Humphrey

cmdb=> SELECT userid, username, firstname, lastname, city FROM users_vw LIMIT 1;
ERROR: permission denied for relation users_vw

cmdb=> RESET SESSION AUTHORIZATION;
RESET

-- REVOKE SELECT(userid, username, firstname, lastname) ON TABLE users_vw FROM tickit_test;

SELECT *（L.7〜L.8）
全てのカラムは参照できないのでエラーになります
SELECT userid, username, firstname, lastname（L.10〜L.15）
権限を付与したカラムが正常に参照できます
SELECT userid, username, firstname, lastname, city（L.17〜L.18）
権限を付与していないカラム（city）を含むのでエラーになります
エラーメッセージはビューに権限が付与されていない場合と同じため、カラムの権限不足なのか見分けられません

カラムレベルのアクセス制御（UPDATE権限）の検証

usersテーブルの一部のカラムにUPDATE権限を付与

上記の権限に１つのカラム（username）に更新権限を付与して、参照や更新を確認します。

cmdb=# GRANT UPDATE(username) ON users TO tickit_test;
GRANT
cmdb=# SET SESSION AUTHORIZATION tickit_test;
SET

cmdb=> SELECT userid, username, firstname, lastname FROM users_vw WHERE userid = 1;
-[ RECORD 1 ]-------
userid | 1
username | JSG99FHE
firstname | Rafael
lastname | Taylor

cmdb=> UPDATE users SET username = 'xxxxxxxx' WHERE userid = 1;
UPDATE 1

cmdb=> UPDATE users SET city = 'kent' WHERE userid = 1;
ERROR: permission denied for relation users

cmdb=> SELECT userid, username, firstname, lastname FROM users_vw WHERE userid = 1;
-[ RECORD 1 ]-------
userid | 1
username | xxxxxxxx
firstname | Rafael
lastname | Taylor

cmdb=> RESET SESSION AUTHORIZATION;
RESET

-- REVOKE SELECT(userid, username, firstname, lastname) ON TABLE users FROM tickit_test;
-- REVOKE UPDATE(username) ON users FROM tickit_test;

UPDATE users SET username = ...（L.13〜L.14）
権限を付与したカラムが正常に更新できます
UPDATE users SET city = ...（L.16〜L.17）
権限を付与していないカラム（city）に対して更新したためエラーになります
エラーメッセージはテーブルに権限が付与されていない場合と同じため、カラムの権限不足なのか見分けられません

最後に

Redshift Spectrumの外部テーブルがカラムレベルのアクセス制御が可能であったため、Redshiftのテーブルもカラムレベルのアクセス制御に対応したものと考えられます。Redshift Spectrumの外部テーブルは、権限のないオブジェクト（スキーマ、テーブル、カラム）は表示されないのに対して、Redshiftのテーブルのカラムレベルのアクセス制御では、表示されるけれどもアクセスするとエラーになるという動作です。また、そのエラーメッセージからテーブルの権限不足に表示されるメッセージと同じため、ユーザーが具体的にどのカラムへのアクセスが可能であるかの判断ができません。セキュリティやアクセス制御という観点では有用ですが、ユーザーは権限エラーが生じた際に混乱するかもしれません。

ユースケースとしては、Redshift Spectrumの外部テーブルのカラムレベルのアクセス制御と同じ様にGRANNT/REVOKEで管理する目的で用いるものであり、ユーザーが利用しやすいのは従来のビューによるアクセスコントロールではないかと考えます。