【Databricks】Dynamic Viewでの列・行レベルアクセスを試してみた

# 背景・目的
BigQueryでは列レベルや行レベルでのアクセス制御が可能です。この記事では、Databricksでの同様のアクセス制御の実現方法とその検証結果を詳しく紹介します。

# まとめ
- 列と行レベルのセキュリティを行うには、UnityCatalogで実現できる。
- Dynamic Viewでは関数が利用できる
    - current_user
    - is_account_group_member
- 下記は非推奨
    - is_member。Hiveメタストア互換のため
    - Viewで参照されているテーブルとビューを読み取る機能をユーザに付与しない

# 概要
[DatabricksのDynamic view](https://docs.databricks.com/ja/data-governance/unity-catalog/create-views.html)を元に整理します。

## Create a dynamic view
> Unity Catalogでは、動的ビューを使用して、次のようなきめ細かなアクセス制御を構成できます。
> - 列または行のレベルのセキュリティ。
> - データマスキング

- Unity CatalogでDynamic Viewが使用できる。Dynamic Viewでは列と行のセキュリティが利用できる。

> 動的ビューを使用したきめ細かなアクセスコントロールは、 シングルユーザー access modeのクラスターでは使用できません。

- シングルユーザアクセスモードのクラスタでは使用できない。

> Unity Catalog では、ビュー内の行、列、またはレコードにアクセスできるユーザーを動的に制限できる次の関数が導入されています。
> - current_user(): 現在のユーザーの Eメール アドレスを返します。
> - is_account_group_member(): 現在のユーザーが特定のアカウントレベルのグループのメンバーである場合に TRUE を返します。 Unity Catalog データに対する動的ビューでの使用をお勧めします。
> - is_member(): 現在のユーザーが特定のワークスペース レベルのグループのメンバーである場合に TRUE を返します。 この関数は、既存の Hive metastoreとの互換性のために提供されています。 Unity Catalog データに対するビューでは、アカウントレベルのグループメンバーシップを評価しないため、使用しないでください。
> Databricks では、ビューで参照されているテーブルとビューを読み取る機能をユーザーに付与しないことをお勧めします。

- is_memberはHive metastore互換性のため用意されている。Unity Catalogデータに対するViewでは使用しないこと。
- Databricksでは、Viewで参照されているテーブルとビューを読み取る機能をユーザに付与しない

### 列レベルの権限
> 動的ビューを使用すると、特定のユーザーまたはグループがアクセスできる列を制限できます。 次の例では、 auditors グループのメンバーのみが sales_raw テーブルから Eメール アドレスにアクセスできます。 クエリー分析中に、Apache Spark は CASE ステートメントをリテラル文字列 REDACTED または Eメール アドレス列の実際の内容に置き換えます。 他の列は通常どおり返されます。 この戦略は、クエリー のパフォーマンスに悪影響を与えません。

```
    -- Alias the field 'email' to itself (as 'email') to prevent the
    -- permission logic from showing up directly in the column name results.
    CREATE VIEW sales_redacted AS
    SELECT
      user_id,
      CASE WHEN
        is_account_group_member('auditors') THEN email
        ELSE 'REDACTED'
      END AS email,
      country,
      product,
      total
    FROM sales_raw
```

- auditorsグループのときには、Eメールを返し、グループ外では、REDACTEDの文字列を返す。

### 行レベルの権限
> 動的ビューでは、行レベルまたはフィールド レベルまで権限を指定できます。 次の例では、 managers グループのメンバーのみが $1,000,000 を超えたときにトランザクション金額を表示できます。 一致する結果は、他のユーザーに対して除外されます。

```
     CREATE VIEW sales_redacted AS
     SELECT
       user_id,
       country,
       product,
       total
     FROM sales_raw
     WHERE
       CASE
         WHEN is_account_group_member('managers') THEN TRUE
         ELSE total <= 1000000
       END;
```
- managerグループであれば、レコードを表示することができ、グループ外ではtotal が100,000以下であれば表示可能

# 実践
## 前提
### 検証
下記のような顧客テーブルがある場合に、適切にアクセス権を付与し想定通りに制限（閲覧）できるかを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/887349e7-7a0a-8a35-3370-eb540aca8e4e.png)

- 営業1部と営業2部の社員がアクセスできるのは、自身が担当している顧客になります。
    - 全ての列にアクセスが可能ですが顧客は制限されます。
        - 営業1部は、たろうさん、じろうさん、さぶろうさん
        - 営業2部は、しろうさん、ごろうさん
- 分析担当者は、すべての顧客にアクセスできますが、列は顧客タイプ（customer_type）、職業(job)、担当列(in_charge)に限定されます。
- 最後に、制限なしにアクセスできる管理者がいます。

シナリオは下記のとおりです。
1. 営業部1と2の社員
    - 担当顧客のみ閲覧が可能であること
    - 担当顧客以外は閲覧ができないこと
1. 分析担当
    - 全てのレコードにアクセスできること
    - アクセス可能な列の値を参照できること
    - アクセス不可能な顧客氏名や住所が閲覧できないこと
1. 管理者
    - 全ての顧客のデータが参照できること


## 準備
### グループを作成
下記のグループを作成します。

- 営業1部 ： sales1
- 営業2部 ： sales2
- 分析： analysis

※ 管理者はデフォルトのadminをそのまま利用します。

1. Workspaceにサインインし、「Admin Settings」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/dace6668-6daf-e969-8b30-dd70e98dc1f5.png" width=200>

1. ①.「Groups」タブをクリックすると、デフォルトでは、adminsとusersしかありません。②.「Add group」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/8dbd4edd-bcd0-590d-67ef-c99cb98b20d1.png)

1. ポップアップが表示されるので、「Add new」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/f130bae7-b8c9-00ff-c794-a0b26771361e.png" width=500>

1. group nameに「sales1」を入力し、「Add」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/7b47ca19-aa04-bb15-d3a7-49802c61561e.png)

1. sales1が追加されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/64ce2d35-6448-3217-3b0a-85f3b6c60020.png)

1. グループ名「sales1」をクリックします。
1. ①「Entitlements」をクリックし、②「Databricks SQL access」のEnabledをクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/859f5cf8-178f-2e6e-67b7-8133f676b3f1.png" width=500>

1. ポップアップが表示されるので、「Enable」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/11d6f99b-cf81-78c5-7f81-dbebf1dc4219.png" width=500>

1. 有効化されました。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/550ce46d-2fa4-6e0c-932e-4826df443070.png" width=500>

1. これを繰り返し、sales2とanalyticsを追加します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/bd78c036-387c-95e1-9a76-bf3682e31515.png)


1. AccountコンソールのGroupsにも追加されてました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/9d4ff1a4-3d9c-dda6-ad83-e76da7fbbeb3.png)


### ユーザの作成
下記のユーザを作成します。
- 営業1部所属の社員 ： sales1 employee1
- 営業2部所属の社員 ： sales2 employee1
- 分析所属の社員： analysis employee1

1. Admin Settingsで①「Users」をクリックします。②「Add User」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/3009ef09-5616-afb0-965b-c96c41ab432d.png)

1. ポップアップが表示されるので、「Add new」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/7796da3b-b174-d58c-18bc-8fd35f8f56db.png" width=500>

1. emailを入力し、「Add」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/3fbe046e-cbee-425c-f28b-37a9962782bb.png" width=500>

1. Inviteメールが届くので、リンクをクリックします。
1. これをSales2,Analysisも同様に繰り返します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/74c52b47-4d12-31cc-8bfb-1791c4a9021b.png)


### グループにユーザを追加
下記のように、グループにユーザを追加します。

|グループ（論理名）| グループ | ユーザ |
|:---|:---|:---|
|営業1部 | sales1 | sales1 employee1|
|営業2部 | sales2 | sales2 employee1|
|分析 | analysis | analysis1 employee1|

1. Admin Settingsで、①「Groups」をクリックし、②「sales1」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/28a66538-6cd2-b593-049d-a707bd238fa9.png)

1. 「Add Members」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/daaa3a92-3870-6163-e917-a4d04ffcc283.png" width=500>

1. ポップアップが表示されるので、所属させるユーザを選択し、「Add」をクリックします。
    <img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/b1cdb54f-8b86-c898-c495-bf9782ba73c2.png" width=500>

1. 追加されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/10961b74-1d1c-0352-8381-1b8d5b163b06.png)

1. 上記と同じ手順で、sales2、analysisにユーザを追加します。各グループにユーザが追加されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/175860b0-fe13-4968-b3e4-3c99b6467154.png)

### テーブルとデータの作成
#### スキーマの作成
1. 下記のDDLでスキーマを作成します。
    ```
    create schema main.test
    ```

#### テーブルの作成
1. 下記のDDLでテーブルを作成します。
    ```
    CREATE TABLE test.customer (
      id INTEGER
      ,full_name VARCHAR(90)
      ,address VARCHAR(300)
      ,customer_type VARCHAR(10)
      ,job VARCHAR(30)
      ,in_charge VARCHAR(100)
    ) 
    ```
#### データを作成
1. 下記のDMLでデータを登録します。
    ```
    INSERT INTO main.test.customer VALUES(1,'たろう','Tokyo','C1','Accountant','sales1');
    INSERT INTO main.test.customer VALUES(2,'じろう','Osaka','C1','Lawyer','sales1');
    INSERT INTO main.test.customer VALUES(3,'さぶろう','Fukuoka','C2','Journalist','sales1');
    INSERT INTO main.test.customer VALUES(4,'しろう','Hokkaido','C2','Doctor','sales2');
    INSERT INTO main.test.customer VALUES(5,'ごろう','Kyushu','C3','Engineer','sales2');
    ```
1. 下記のクエリで登録されたデータを確認します。確認できました。
    ```
    select * from `main`.`test`.`customer` limit 100;
    ```

    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/139c27d1-60d7-0872-0b1a-9cb9ec810e0e.png)

## 検証
### Dynamic View の作成
1. 下記のDDLでViewを作成します。
    ```
    CREATE VIEW `main`.`test`.`customer_v` AS
    SELECT
        CASE WHEN is_account_group_member('sales1') OR is_account_group_member('sales2') THEN id
        END AS id
        ,CASE WHEN is_account_group_member('sales1') OR is_account_group_member('sales2') THEN full_name
        END AS full_name
        ,CASE WHEN is_account_group_member('sales1') OR is_account_group_member('sales2') THEN address
        END AS address
      ,customer_type
      ,job
      ,in_charge
    FROM `main`.`test`.`customer`
    WHERE CASE
           WHEN is_account_group_member('sales1') AND in_charge = 'sales1' THEN TRUE
           WHEN is_account_group_member('sales2') AND in_charge = 'sales2' THEN TRUE
           WHEN is_account_group_member('analysis') THEN TRUE
          END;
    ```

### 権限の付与
1. 下記のクエリでグループに権限を付与します。
    ```
    GRANT USE SCHEMA  ON SCHEMA `main`.`test` TO `sales1`;
    GRANT USE SCHEMA  ON SCHEMA `main`.`test` TO `sales2`;
    GRANT USE SCHEMA  ON SCHEMA `main`.`test` TO `analysis`;
    
    GRANT SELECT  ON VIEW `main`.`test`.`customer_v` TO `sales1`;
    GRANT SELECT  ON VIEW `main`.`test`.`customer_v` TO `sales2`;
    GRANT SELECT  ON VIEW `main`.`test`.`customer_v` TO `analysis`;
    GRANT ALL PRIVILEGES  ON VIEW `main`.`test`.`customer_v` TO `管理者ユーザ`;
    ```

1. 下記のクエリで付与した権限を確認します。
    ```
    SHOW GRANTS ON `main`.`test`.`customer_v`
    ```

### 確認
#### sales1ユーザで確認
1. sales1グループに所属するユーザでログインし、下記のクエリを実行し想定どおりか確認します。

    ```
    SELECT is_account_group_member('sales1'),is_account_group_member('sales2'),is_account_group_member('analysis'), * FROM main.test.customer_v
    ```

1. 想定通り、全列、対象行のみ表示されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/0cd6c67e-a73a-3703-8de5-97dfe44b11b3.png)

#### sales2ユーザで確認
1. sales2グループに所属するユーザでログインし、下記のクエリを実行し想定どおりか確認します。
    ```
    SELECT is_account_group_member('sales1'),is_account_group_member('sales2'),is_account_group_member('analysis'), * FROM main.test.customer_v
    ```

1. 想定通り、全列、対象行のみ表示されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/59eb1806-e837-e79c-5396-0c40e9498d9c.png)


#### analysisユーザで確認
1. analysisグループに所属するユーザでログインし、下記のクエリを実行し想定どおりか確認します。
    ```
    SELECT is_account_group_member('sales1'),is_account_group_member('sales2'),is_account_group_member('analysis'), * FROM main.test.customer_v
    ```

1. 対象列、全行が表示されました。なお、対象外の列はNullで表示されることは意外でした。


#### 管理者で確認
:::note info
管理者は、テーブルを直接参照します。
:::

1. 作成者（管理者）で、下記のクエリで確認します。見えません。
    ```
    SELECT * FROM main.test.customer_v
    ```

    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/153c8d13-fc05-1890-6c52-b39071bad965.png)


# 考察
今回は、Dynamic Viewで所属するグループで行列アクセス制御を試してみました。今後は、2023/9/17現在、Public Previewの段階ですが[Filter sensitive data with row filters and column masks ](https://docs.databricks.com/en/data-governance/unity-catalog/row-and-column-filters.html)を試してみようと思います。

# 参考
https://docs.databricks.com/ja/data-governance/unity-catalog/create-views.html#create-a-dynamic-view

https://docs.databricks.com/en/data-governance/unity-catalog/row-and-column-filters.html

https://cloud.google.com/bigquery/docs/column-level-security-intro?hl=ja

https://cloud.google.com/bigquery/docs/row-level-security-intro?hl=ja