Databricks Unity Catalog における基本的なオブジェクトへの権限付与に関する検証

## 概要

Unity Catalog における基本的なオブジェクトへの権限付与に関する次のケースの調査内容をコードも含めて共有します。

1. テーブル参照に必要となる権限
1. 関数と Volumes を利用する際に必要となる権限
1. 動的ビュー参照に必要となる権限
1. カタログ、あるいは、スキーマを払い出す際に必要となる権限

次のリンク先にてコードを公開しています。

https://github.com/manabian-/uc_securiy_01.git

## 事前準備

### 1. 権限検証に利用グループとユーザーの作成

アカウントレベルにて次のグループを作成して、それぞれのグループにユーザーを割り当ててください。`uc_test__uc_test_01__admin`グループのユーザーにてリソースの作成や権限付与を行い、`uc_test_all_read`グループのユーザーにて権限の充足を確認します。

| #    | グループ名                 | 概要                     |
| ---- | -------------------------- | ------------------------ |
| 1    | uc_test__uc_test_01__admin | アカウント管理者グループ |
| 2    | uc_test_all_read           | 権限付与対象者グループ   |

## 1. テーブル参照に必要となる権限

### 1-1 カタログ、スキーマ、テーブルの作成

#### 1-1-1. `uc_test__uc_test_01__admin`グループのユーザーにてリソースを作成

```python
%sql
-- カタログの作成
CREATE CATALOG IF NOT EXISTS uc_test_01;
ALTER CATALOG uc_test_01 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
-- スキーマ作成
CREATE SCHEMA IF NOT EXISTS uc_test_01.schema_01;
ALTER SCHEMA uc_test_01.schema_01 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
-- テーブル作成
CREATE
OR REPLACE table uc_test_01.schema_01.table_01 (
  str_col string,
  int_col int,
  date_col date
);
INSERT INTO uc_test_01.schema_01.table_01
SELECT 
  'a'
  ,1
  ,CAST('2020-01-01' as date)
;
ALTER TABLE
  uc_test_01.schema_01.table_01 OWNER TO uc_test__uc_test_01__admin;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/0e31ceb1-00d4-ccda-0c21-a34934878334.png)


#### 1-1-2. `uc_test_all_read`グループのユーザーにて動作確認  

 `uc_test_all_read`グループに権限を付与していないため、オブジェクトを確認できません。

```python
%sql
-- 権限不足で確認できない想定
DESCRIBE CATALOG uc_test_01
```

```python
%sql
-- 権限不足で確認できない想定
DESCRIBE SCHEMA uc_test_01.schema_01
```

```python
%sql
-- 権限不足で確認できない想定
DESCRIBE TABLE uc_test_01.schema_01.table_01
```

```python
%sql
-- 権限不足で確認できない想定
SELECT * FROM uc_test_01.schema_01.table_01
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/aa328e43-e635-2b16-9eff-22ca727feb24.png)

### 1-2 スキーマのみへの権限付与

#### 1-2-1. `uc_test__uc_test_01__admin`グループのユーザーにて権限付与

```python
%sql
GRANT USE SCHEMA ON SCHEMA uc_test_01.schema_01 TO uc_test_all_read;
GRANT
SELECT
  ON SCHEMA uc_test_01.schema_01 TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d25989bb-b56d-fbff-ff7a-ef066aa09789.png)


#### 1-2-2. `uc_test_all_read`グループのユーザーにて動作確認  

`USE CATALOG`権限が付与されていないため、オブジェクトを表示できません。

```python
%sql
-- `USE CATALOG`権限の不足で確認できない想定
DESCRIBE SCHEMA uc_test_01.schema_01
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/5544e9cd-5fae-a053-55a9-8c454cf2f217.png)


### 1-3 カタログへの権限付与

#### 1-3-1. `uc_test__uc_test_01__admin`グループのユーザーにて権限付与

```python
%sql
GRANT USE CATALOG ON CATALOG uc_test_01 TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6a63201f-4e1f-b9cd-552c-31fde1fde63b.png)

#### 1-3-2. `uc_test_all_read`グループのユーザーにて動作確認  

`USE CATALOG`、`USE SCHEMA`、および、`SELECT`権限が付与されているため、想定通りに動作します。

```python
%sql
-- 表示される想定
DESCRIBE TABLE uc_test_01.schema_01.table_01
```

```python
%sql
-- 表示される想定
SELECT * FROM uc_test_01.schema_01.table_01
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6c5c3195-f069-517c-35fe-d7da5e3fd877.png)

## 2. 関数と Volumes を利用する際に必要となる権限の権限

### 2-1. 関数の作成

#### 2-1-1. `uc_test__uc_test_01__admin`グループのユーザーにてリソースを作成

```python
%sql
CREATE
OR REPLACE FUNCTION uc_test_01.schema_01.get_rand_num() RETURNS float NOT DETERMINISTIC CONTAINS SQL RETURN rand();
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/984452e7-80a0-20e3-952f-ca56001e0a11.png)

#### 2-1-2. `uc_test_all_read`グループのユーザーにて動作確認  

```python
%sql
-- 権限不足で実行できない想定
SELECT 
  uc_test_01.schema_01.get_rand_num()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6c80658c-eaed-9c4f-4944-6891e0f07255.png)

### 2-2. 関数への権限付与

#### 2-2-1. `uc_test__uc_test_01__admin`グループのユーザーにて権限付与

```python
%sql
GRANT EXECUTE ON CATALOG uc_test_01 TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/f441a952-f55f-3c30-7f7a-b4c88d694190.png)

#### 2-2-2. `uc_test_all_read`グループのユーザーにて動作確認  

```python
%sql
-- 実行できる想定
SELECT 
  uc_test_01.schema_01.get_rand_num()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/24f8b022-d8ba-961a-76ed-24eadbfdba11.png)

### 2-3. Volumes の作成

#### 2-3-1. `uc_test__uc_test_01__admin`グループのユーザーにてリソースを作成

```python
%sql
CREATE VOLUME IF NOT EXISTS uc_test_01.schema_01.volumes_01;
ALTER VOLUME uc_test_01.schema_01.volumes_01 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
CREATE
OR REPLACE TABLE delta.`/Volumes/uc_test_01/schema_01/volumes_01/delta_01` AS
SELECT
  "abc" AS string_col,
  1 :: int AS int_col,
  CAST('2020-01-01' AS date) AS date_col
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/68dbab73-85a6-f8a8-f092-2b89b59ba84d.png)

#### 2-3-2. `uc_test_all_read`グループのユーザーにて動作確認  

```python
%sql
-- 権限不足で表示できない想定
list '/Volumes/uc_test_01/schema_01/volumes_01/delta_01'
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/137aebaa-e064-d8f2-7e48-179318e35680.png)

### 2-4. Volumes への権限付与

#### 2-2-4. `uc_test__uc_test_01__admin`グループのユーザーにて権限付与

```python
%sql
GRANT READ VOLUME ON SCHEMA uc_test_01.schema_01 TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3c3293e4-5dae-a2af-0555-f6afd2d2cadf.png)

#### 2-2-4. `uc_test_all_read`グループのユーザーにて動作確認  

```python
%sql
-- 表示できる想定
list '/Volumes/uc_test_01/schema_01/volumes_01/delta_01'
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/cc1a4068-22e2-d17e-a7e6-9b43f04807de.png)

### 3. 動的ビュー参照に必要となる権限

### 3-0 事前準備

#### 3-0-1. `uc_test__uc_test_01__admin`グループのユーザーにてリソースを作成

```python
%sql
CREATE CATALOG IF NOT EXISTS uc_test_02;
ALTER CATALOG uc_test_02 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
CREATE SCHEMA IF NOT EXISTS uc_test_02.schema_01;
ALTER SCHEMA uc_test_02.schema_01 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
CREATE TABLE uc_test_02.schema_01.src_table AS
SELECT
  "A" AS COMPANY_CODE,
  "A会社" AS COMPANY_NAME,
  100 AS SALES_AMOUNT
UNION ALL
SELECT
  "B" AS COMPANY_CODE,
  "B会社" AS COMPANY_NAME,
  200 AS SALES_AMOUNT
UNION ALL
SELECT
  "C" AS COMPANY_CODE,
  "C会社" AS COMPANY_NAME,
  300 AS SALES_AMOUNT
```

```python
%sql
-- 動的ビューを配置するスキーマを作成
CREATE SCHEMA IF NOT EXISTS uc_test_02.schema_dyanamic_view;
ALTER SCHEMA uc_test_02.schema_dyanamic_view OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
-- 動的ビューを配置するスキーマへの権限を付与
GRANT USE CATALOG ON CATALOG uc_test_02 TO uc_test_all_read;
GRANT USE SCHEMA ON SCHEMA uc_test_02.schema_dyanamic_view TO uc_test_all_read;
GRANT
SELECT
  ON SCHEMA uc_test_02.schema_dyanamic_view TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d9f8724d-fb0b-6577-e974-f010ddb6e037.png)


### 3-1 列レベルのアクセス許可

#### 3-1-1. `uc_test__uc_test_01__admin`グループのユーザーにて動的ビューを作成後、権限付与

```python
%sql
CREATE
OR REPLACE VIEW uc_test_02.schema_dyanamic_view.column_level_permissions AS
SELECT
  CASE
    WHEN is_account_group_member('uc_test__uc_test_01__admin') THEN COMPANY_CODE
    WHEN is_account_group_member('uc_test_all_read')
    AND COMPANY_CODE = "A" THEN COMPANY_CODE
    ELSE 'REDACTED'
  END AS COMPANY_CODE,
  CASE
    WHEN is_account_group_member('uc_test__uc_test_01__admin') THEN COMPANY_NAME
    WHEN is_account_group_member('uc_test_all_read')
    AND COMPANY_CODE = "A" THEN COMPANY_NAME
    ELSE 'REDACTED'
  END AS COMPANY_NAME,
  SALES_AMOUNT
FROM
  uc_test_02.schema_01.src_table
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/325f69a6-dfec-8438-9d72-37e55fac1a63.png)

#### 3-1-2. `uc_test_all_read`グループのユーザーにて動作確認  

`COMPANAY_CODE`が`A`のレコード以外の`COMPANAY_CODE`と`COMPANAY_NAME`がマスキングされることを確認します。

```python
%sql
SELECT
*
FROM 
    uc_test_02.schema_dyanamic_view.column_level_permissions
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3b8e3b8b-f409-4c74-ad24-42120046e77e.png)


### 3-2 行レベルのアクセス許可

#### 3-2-1. `uc_test__uc_test_01__admin`グループのユーザーにて動的ビューを作成後、権限付与

```python
%sql
CREATE VIEW uc_test_02.schema_dyanamic_view.row_level_permissions AS
SELECT
  COMPANY_CODE,
  COMPANY_NAME,
  SALES_AMOUNT
FROM
  uc_test_02.schema_01.src_table
WHERE
  CASE
    WHEN is_account_group_member('uc_test__uc_test_01__admin') THEN TRUE
    WHEN is_account_group_member('uc_test_all_read')
    AND COMPANY_CODE = "A" THEN TRUE
    ELSE FALSE
  END;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8d7e843b-2412-09a6-0d6b-59bbae490b67.png)

#### 3-2-2. `uc_test_all_read`グループのユーザーにて動作確認  

`COMPANAY_CODE`が`A`のレコードのみ表示されることを確認します。

```python
%sql
SELECT
*
FROM 
    uc_test_02.schema_dyanamic_view.row_level_permissions
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/107c0c25-3c96-9c6a-d856-6a922abca6f2.png)

### 4. カタログ、あるいは、スキーマを払い出す際に必要となる権限

### 4-1 カタログの払出検証

```python
%sql
CREATE CATALOG IF NOT EXISTS uc_test_50
```

```python
%sql
-- 権原付与
ALTER CATALOG uc_test_50 OWNER TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/36e71659-65c3-740f-ce7d-6153759b684a.png)

#### 4-1-1. `uc_test__uc_test_01__admin`グループのユーザーにてリソースを作成後、権限付与

```python
%sql
-- スキーマを作成できることを確認
CREATE SCHEMA IF NOT EXISTS uc_test_50.schema_01;
ALTER SCHEMA uc_test_50.schema_01 OWNER TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d201fc5f-a4d6-44d7-99e5-1742aa485c89.png)

#### 4-1-2. `uc_test_all_read`グループのユーザーにて動作確認  

```python
%sql
CREATE CATALOG IF NOT EXISTS uc_test_51;
ALTER CATALOG uc_test_51 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
CREATE SCHEMA IF NOT EXISTS uc_test_51.schema_01;
```

```python
%sql
-- 権原付与
GRANT USE CATALOG ON CATALOG uc_test_51 TO uc_test_all_read;
ALTER SCHEMA uc_test_51.schema_01 OWNER TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d0abdac8-172c-3879-6dc9-4ea8356c763b.png)

### 4-2 スキーマの払出検証

#### 4-2-1. `uc_test__uc_test_01__admin`グループのユーザーにてリソースを作成後、権限付与

```python
%sql
CREATE CATALOG IF NOT EXISTS uc_test_51;
ALTER CATALOG uc_test_51 OWNER TO uc_test__uc_test_01__admin;
```

```python
%sql
CREATE SCHEMA IF NOT EXISTS uc_test_51.schema_01;
```

```python
%sql
-- 権原付与
GRANT USE CATALOG ON CATALOG uc_test_51 TO uc_test_all_read;
ALTER SCHEMA uc_test_51.schema_01 OWNER TO uc_test_all_read;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4231509e-d345-b73e-5fe7-67c3f53c7810.png)

#### 4-2-2. `uc_test_all_read`グループのユーザーにて動作確認  

```python
%sql
-- テーブルを作成できることを確認
CREATE OR REPLACE TABLE uc_test_51.schema_01.table_01
AS
SELECT
  "efg" AS string_col
  ,3::int AS int_col
  ,CAST('2022-01-01' AS date) AS date_col
```

```python
%sql
SELECT
*
FROM
  uc_test_51.schema_01.table_01
```

```python
%sql
-- 関数を作成
CREATE
OR REPLACE FUNCTION uc_test_51.schema_01.get_rand_num() RETURNS float NOT DETERMINISTIC CONTAINS SQL RETURN rand();
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/59c701d3-b196-d5b7-9bc5-ff652b0495c4.png)

### リソースの削除

```python
%sql
DROP CATALOG uc_test_01 CASCADE;
```

```python
%sql
DROP CATALOG uc_test_02 CASCADE;
```

```python
%sql
DROP CATALOG uc_test_50 CASCADE;
```

```python
%sql
DROP CATALOG uc_test_51 CASCADE;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9d006076-7928-6b01-0179-3765ca0cb452.png)