例えばPIIやPCIなどの機密情報対策として、テーブル、ビュー、マテリアライズドビューで列(カラム)レベルでGRANT/REVOKE形式のアクセス制御ができるRedshiftの機能.
SQLだけで操作できる。
参考)
列レベルでの権限追加例
公式ブログの内容抜粋をベースに、一般的にはユーザ単位よりグループ単位やROLEで権限管理が便利なので、ここではグループ単位での例で。
1) まずはサンプル顧客テーブルとサンプルSalesユーザ準備
CREATE TABLE customer
(
customerid INT8 NOT NULL,
customername VARCHAR(25) NOT NULL,
phone CHAR(15) NOT NULL,
nationid INT4 NOT NULL,
marketsegment CHAR(10) NOT NULL,
accountbalance NUMERIC(12,2) NOT NULL
);
INSERT INTO customer VALUES
(1, 'Customer#000000001', '33-687-542-7601', 3, 'HOUSEHOLD', 2788.52),
(2, 'Customer#000000002', '13-806-545-9701', 1, 'MACHINERY', 591.98),
(3, 'Customer#000000003', '13-312-472-8245', 1, 'HOUSEHOLD', 3332.02),
(4, 'Customer#000000004', '23-127-851-8031', 2, 'MACHINERY', 9255.67),
(5, 'Customer#000000005', '13-137-193-2709', 1, 'BUILDING', 5679.84)
;
CREATE USER sales WITH password 'Abcd1234!';
CREATE GROUP division;
ALTER GROUP division ADD USER sales;
2) 権限付与
普通にテーブル単位で参照権限を付与するなら
GRANT SELECT ON [table name] TO GROUP [group name]
だが、列レベル指定したい場合は、
単純に以下のようにSELECT([列名])とするだけでOK
GRANT SELECT([列名]) ON [table name] TO GROUP [group name]
列レベルで付与できる権限は、SELECT
とUPDATE
のみ
権限付与例)
GRANT SELECT (marketsegment, accountbalance),UPDATE (marketsegment)
ON customer
TO GROUP division;
customerテーブルの marketsegment と accountbalance カラムに参照権限、marketsegment に更新権限を、divisionグループに対して付与
3) 動作確認
SET SESSION AUTHORIZATION 'sales';
-- 許可あり列の参照
select marketsegment from customer;
-- 許可なし列の参照
select phone from customer;
select * from customer;
RESET SESSION AUTHORIZATION;
許可あり列の参照は成功するが、許可なし列を含む参照は意図通りに permission denied
エラーになる
4) 列レベル権限設定状況の確認方法
システムテーブルpg_attribute_info
を活用した以下のクエリでチェックできる
SELECT b.attacl, b.attname as columnname, c.relname as tablename
FROM pg_catalog.pg_attribute_info b
JOIN pg_class c ON c.oid=b.attrelid
WHERE b.attacl IS NOT NULL AND b.attacl != '{}' ORDER BY c.relname, b.attname;
結果イメージはこちら
customerテーブルのaccountbalance列には、divisionグループに r (参照)が、marketsegment列には rw(参照と更新)がそれぞれ付与されている。"IAM:xxxx"は付与者。
下の2行はグループ単位ではなくユーザ単位でGRANTした場合の見え方例。
備考
GitHubで公開されているamazon-redshift-utils
の、グループ単位でテーブル権限を確認する以下のAdminViewsでは、上記列レベル設定の結果は出力されないので、使ってる人は注意。
5) 権限のREVOKE
こんな感じで通常REVOKE同様にテーブル単位での設定。
REVOKE UPDATE ON customer FROM GROUP division
まとめ
従来型の別途表示範囲を絞ったビューを使ったやり方でも同様の要件は満たせるが、通常にテーブル単位のGRANT操作にカラム指定するだけでできる点は便利。