Amazon Redshift で 行レベルアクセス制御（RLS）

- 例えば、各自が所属する部門に関する行（レコード）だけを表示させたい場合など、ROLEに応じて操作範囲を制限したい場合に使えるRedshiftの機能 (2022年7月GA)。
- `RLS` : Row-level Securityといい、基本は`RBAC`（Role-Base Access Control）を使う。
- SQLだけで操作できる。
- [列レベルのアクセス制御機能](https://qiita.com/suzukihi724/items/675fc743459be189079b)と組み合わせることで、より細かい制御が可能。

参考）
- AWS公式ブログ：[Achieve fine-grained data security with row-level access control in Amazon Redshift](https://aws.amazon.com/jp/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/)
- [AWS公式ドキュメント](https://docs.aws.amazon.com/redshift/latest/dg/t_rls.html)


# ざっくり仕組みを理解

- `ROLE`（IAM ROLEとは別）を定義する。例：管理者、運用者、XX利用部門
- `RLSポリシー`を定義し、行レベルフィルタルールをSQLで書く
- ROLEには[列レベルのアクセス制御](https://qiita.com/suzukihi724/items/675fc743459be189079b)のルールもセットできる
- RLSポリシーをテーブルとROLEに紐付け（`アタッチ`）する
- DBユーザーはROLEと紐づける（`GRANT`）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/9d402be1-e139-df99-5413-89b902911f99.png)
参考）AWS公式ブログ：[Achieve fine-grained data security with row-level access control in Amazon Redshift](https://aws.amazon.com/jp/blogs/big-data/achieve-fine-grained-data-security-with-row-level-access-control-in-amazon-redshift/)

# ユースケース例

- 地理的な位置に基づいてデータアクセスを制限（自分が所属するエリアに限定）
- 個人の給与だけを見るアクセス制限
- マネージャーは直属部下情報のみを参照し、他部門の情報を参照させない
- 医師と看護師が患者のデータ行のみを表示できるようにする
- 従業員の事業部門や会社における役割に基づいて、財務データ行へのアクセスを制限
- 各テナントのデータ行を、他のすべてのテナントのデータ行から論理的に分離する

# やってみる

### 事前準備

まずはサンプル顧客テーブルとサンプルユーザ準備

```SQL
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 staff WITH password 'Abcd1234!'; 
CREATE USER manager WITH password 'Abcd1234!'; 
```

### 1) ROLE作成

`CREATE ROLE [ROLE名];`

例）
```SQL
CREATE ROLE role_household;
CREATE ROLE role_manager;
```
特定セグメントのみアクセスするROLEと、全体をアクセスできるROLEを作成しておく

### 2) RLSポリシー作成

`CREATE RLS POLICY [ポリシー名] WITH ([参照対象列]) USING ([クエリの WHERE 句に適用されるフィルター])`
※ WITHはオプション
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_CREATE_RLS_POLICY.html

例）
```SQL
-- HOUSEHOLDセグメントのみアクセス許可するポリシー
CREATE RLS POLICY policy_household
WITH (marketsegment CHAR(10))
USING (marketsegment = 'HOUSEHOLD');

-- 全部参照可
CREATE RLS POLICY policy_manager
USING (ture);
```

### 3) RLSポリシーをROLEにアタッチ

`ATTACH RLS POLICY [ポリシー名] ON [テーブル] TO ROLE [ROLE名]`
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ATTACH_RLS_POLICY.html
※ TOの後はユーザでもセット可能

例）
```SQL
ATTACH RLS POLICY policy_household ON customer TO ROLE role_household;

ATTACH RLS POLICY policy_manager ON customer TO ROLE role_manager;
```

### 4) ROLEに権限付与

`GRANT [権限] ON [テーブル名] TO ROLE [ROLE名];`

例）
```SQL
GRANT SELECT ON customer TO ROLE role_household;
GRANT SELECT,UPDATE,DELETE ON customer TO ROLE role_manager;
```

### 5) RLSセキュリティ機能を有効化

`ALTER TABLE [テーブル名] ROW LEVEL SECURITY on;`

例）
```SQL
ALTER TABLE customer ROW LEVEL SECURITY on;
```

### 6) ROLEとユーザを紐付け

`GRANT ROLE [ROLE名] TO [ユーザ]`

例）
```SQL
GRANT ROLE role_household TO staff;
GRANT ROLE role_manager TO manager;
```

# 動作確認

**staffユーザの場合**

```SQL
SET SESSION AUTHORIZATION 'staff';
select * from customer;
update customer set phone = 'xxxx';
RESET SESSION AUTHORIZATION;
```
updateは権限がないためPermission Deniedになり、参照結果は意図通りmarketsegementがHOUSEHOLDの行だけ表示される。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/8a6d6e25-94cc-4bda-d732-f4893069dd4f.png)

**managerユーザの場合**

```SQL
SET SESSION AUTHORIZATION 'manager';
select * from customer;
update customer set marketsegment = 'BUILDINGS' where marketsegment = 'BUILDING';
RESET SESSION AUTHORIZATION;
```
制限をかけていないので全行表示され、UPDATE権限も付与したので、更新も成功する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/112537/286b6cc7-2c3f-7a7d-25a9-6e707f27facc.png)