Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@manabian

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

Posted at

概要

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

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

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

事前準備

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;

image.png

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

image.png

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;

image.png

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

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

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

image.png

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;

image.png

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

image.png

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();

image.png

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

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

image.png

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

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

%sql
GRANT EXECUTE ON CATALOG uc_test_01 TO uc_test_all_read;

image.png

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

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

image.png

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

image.png

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

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

image.png

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;

image.png

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

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

image.png

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;

image.png

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

image.png

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

image.png

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;

image.png

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

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

%sql
SELECT
*
FROM 
    uc_test_02.schema_dyanamic_view.row_level_permissions

image.png

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

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

%sql
CREATE CATALOG IF NOT EXISTS uc_test_50

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

image.png

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;

image.png

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;

image.png

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;

image.png

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();

image.png

リソースの削除

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

image.png

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?