概要

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

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

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

事前準備

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`グループのユーザーにてリソースを作成

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

%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;

%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;

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

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

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

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

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

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

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

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

%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;

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

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

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

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

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

%sql
GRANT USE CATALOG ON CATALOG uc_test_01 TO uc_test_all_read;

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

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

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

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

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

2-1. 関数の作成

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

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

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

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

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

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

%sql
GRANT EXECUTE ON CATALOG uc_test_01 TO uc_test_all_read;

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

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

2-3. Volumes の作成

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

%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;

%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

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

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

2-4. Volumes への権限付与

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

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

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

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

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

3-0 事前準備

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

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

%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;

%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

%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;

%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;

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

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

%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

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

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

%sql
SELECT
*
FROM 
    uc_test_02.schema_dyanamic_view.column_level_permissions

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

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

%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;

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

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

%sql
SELECT
*
FROM 
    uc_test_02.schema_dyanamic_view.row_level_permissions

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

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

%sql
CREATE CATALOG IF NOT EXISTS uc_test_50

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

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

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

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

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

%sql
CREATE SCHEMA IF NOT EXISTS uc_test_51.schema_01;

%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;

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

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

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

%sql
CREATE SCHEMA IF NOT EXISTS uc_test_51.schema_01;

%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;

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

%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

%sql
SELECT
*
FROM
  uc_test_51.schema_01.table_01

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

リソースの削除

%sql
DROP CATALOG uc_test_01 CASCADE;

%sql
DROP CATALOG uc_test_02 CASCADE;

%sql
DROP CATALOG uc_test_50 CASCADE;

%sql
DROP CATALOG uc_test_51 CASCADE;