マスキングポリシーと行アクセスポリシーを設定したテーブルを Alteryx から参照してみた #SnowflakeDB

特集

安原朋紀

2023.09.01

はじめに

Snowflake はデータガバナンスのための機能として、行・列レベルのセキュリティに関する機能を提供しています。
行・列レベルのセキュリティのためには、対象のテーブルやビューにポリシーを付与します。当然、ポリシーを適用したオブジェクトをサードパーティーの製品から参照するとその内容が反映された状態かと思いますが、実際に試したことはなかったので、それぞれの基本的な内容もあわせてまとめておきたく記事としました。

ここでは、テーブルを参照する際のツールとして Alteryx を使用しています。

検証環境

以下の環境で検証しています。

列レベルのセキュリティ

Snowflake では、列レベルのセキュリティとして、ダイナミックデータマスキングという機能を提供しています。

マスキングポリシーというものを作成し、テーブルやビューに適用することで、ポリシーの定義に基づき、クエリ時にデータをマスクした状態で表示することが可能となります。

以下は、公式ドキュメント記載の例を引用した内容ですが、マスキングポリシーは SQL で定義されます。

CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('ANALYST') THEN val
    ELSE '*********'
  END;

上記の場合、email_mask という名称でマスキングポリシーを作成しています。CASE式でクエリを発行したユーザーの現在のロールに基づきマスクした値を返すかどうかを制御しています。

マスキングポリシーの主な特徴として以下のようなものがあげられます。

  • スキーマレベルのオブジェクト
  • ポリシーを適用する列のデータ型と、マスク後の値は同じデータ型とする必要がある
  • 保存データは変更されない
  • 利用には Enteprise Plan 以上が必要

ダイナミックデータマスキングについて | Snowflake DOCUMENTATION

行レベルのセキュリティ

こちらには、行アクセスポリシーという機能が提供されています。

行アクセスポリシーを使用することで、クエリ結果で返す行を制御することが可能となります。CASE式を用いた比較的単純な場合ものから、マッピングテーブル というものをポリシーに含めることで、レコード単位での制御を行うことも可能です。

主な特徴は以下の通りです。

  • 行アクセスポリシーとマスキングポリシーは、同時に設定できるが、行アクセスポリシーが先に評価される
  • 同じカラムに対して行アクセスポリシーとマスキングポリシー䛿付与できない
  • 保存データは変更されない
  • 利用には Enteprise Plan 以上が必要

行アクセスポリシーについて | Snowflake DOCUMENTATION

環境の用意

ここでは、検証用のデータベース・スキーマ、ロール階層の定義から行うこととします。

ロール階層の定義

ロール階層は こちらを参考に、オブジェクトを制御する Access Role と、組織における部門や役職などで分割される Functional Role とにわけ、Access Role が Functional Role を継承する形で作成してみます。

コマンドとしては、以下を使用しました。

/*role を定義*/
use role securityadmin;

--PJ管理者
create role test_admin;

--functional role
create role hr;
create role analyst;

--access role
create role test_read;

/*role 階層を定義*/
grant role test_read to role hr;
grant role test_read to role analyst;

grant role hr to role test_admin;
grant role analyst to role test_admin;

grant role test_admin to role sysadmin;

今回の検証環境の管理者ロールとして test_admin を用意し、その配下に Functional Role として人事部を表すhr ロールと、分析者に付与することを想定した analyst を作成しています。

Access Role にはtest_read というロールのみを用意しています。後ほどこちらにテーブルへの読み取り専用アクセスを与えます。

test_admin はカスタムロールですので、sysadmin に紐づけておきます。

コマンド実行後のロール階層は以下のようになります。

ウェアハウス、データベース、テーブルの作成、権限付与

以下のコマンドを使用しました。

/*warehouseを作成*/
use role sysadmin;

create warehouse test_wh
    with warehouse_size = 'XSMALL'
    warehouse_type = 'standard'
    auto_suspend = 60
    initially_suspended = TRUE 
    auto_resume = TRUE ;

--warehouseの所有権をPJ管理者ロールに移譲
grant ownership on warehouse test_wh to role test_admin;

/*PJ用データベースを作成*/
create database test_db;

grant ownership on database test_db to role test_admin;
grant ownership on schema test_db.public to role test_admin;

--PJ管理者にスイッチ
use role test_admin;
use warehouse test_wh;
use schema test_db.public;

/*テーブル作成*/
create table employee_data (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(15),
    department VARCHAR(50)
);

insert into employee_data (id, name, email, phone_number, department) values
(1, '田中太郎', 'tanaka.taro@example.com', '012-3456-7890', '営業部'),
(2, '佐藤花子', 'sato.hanako@example.com', '012-3456-7891', '人事部'),
(3, '鈴木一郎', 'suzuki.ichiro@example.com', '012-3456-7892', 'IT部'),
(4, '高橋真子', 'takahashi.mako@example.com', '012-3456-7893', '営業部'),
(5, '中村悟', 'nakamura.satoru@example.com', '012-3456-7894', '総務部');

/*accessroleへの権限付与*/
--子ロールのtest_readに権限を与えたので親ロールも同じ権限を持つ
grant usage on warehouse test_wh to role test_read;
grant usage on database test_db to role test_read;
grant usage on all schemas in database test_db to role test_read;
grant select on all tables in database test_db to role test_read;

--check
use role hr;
use warehouse test_wh;
use schema test_db.public;
select * from employee_data;

use role analyst;
use schema test_db.public;
use warehouse test_wh;
select * from employee_data;

検証用のサンプルデータもここで追加しています。データは以下のような内容で用意しました。

マスキングポリシーの作成

マスキングポリシーは、サンプルデータの[EMAIL]列に付与することとします。

CASE式でis_role_in_session関数を使用し、hr以上のロールには値を見せるが、それ以外のロールには「*****@example.com」のようにアカウント名部分をマスクするポリシーを作成しました。

How To : Achieve Role Inheritance With Dynamic Data Masking | Snowflake Community

--roleの継承を考慮したmasking policyを定義
use role test_admin;
create or replace masking policy email_mask as (val string) returns string ->
  case
    when is_role_in_session('HR') then val
    else regexp_replace(val,'^[^@]+','*****')
end;

--masking policyを適用
alter table if exists employee_data modify column email set masking policy email_mask;

なお、ポリシーの作成・付与には以下の権限が必要なので必要に応じてロールに付与しておきます。

  • CREATE MASKING POLICY
    • スキーマレベルの権限
    • ポリシーの作成に必要
  • APPLY MASKING POLICY
    • テーブルまたはビューの列に対して列レベルのマスキングポリシーの設定/解除に必要

ポリシーを付与したらテーブルをSnowflake と Alteryx からそれぞれ確認してみます。

  • hrロール（カラムの値が表示されるロール）
use role hr;
select * from employee_data;

Snowflake

Alteryx

  • Analyst ロール（カラムの値がマスクされるロール）
use role analyst;
select * from employee_data;

Snowflake

Alteryx

Alteryx から参照しても、値がマスクされていることを確認できました。

上述の通り、ポリシーでは CASE 式でis_role_in_session関数を使用しているので、検証用データベースの管理者として設定したtest_adminロールでテーブルを参照した場合も値はマスクされずに表示されることになります。

ポリシーの解除、削除には以下のコマンドを使用します。

-- ポリシーの解除
alter table <name> modify column <column_name> unset MASKING POLICY;
--ポリシーの削除
drop masking policy <name>;

行アクセスポリシー

次に、行アクセスポリシーを設定します。
ここでは、マッピングテーブルを使用することとし、以下の設定とします。

  • hrロール
    • [DEPARTMENT]列が人事部のレコードのみ表示される
  • analystロール
    • すべてのレコードが表示される
  • その他のロール
    • analystロールの親ロールは、すべてのレコードが表示される

はじめに以下の手順で、マッピングテーブルを作成します。

/*マッピングテーブル作成用のスキーマを定義*/
use role test_admin;
use database test_db;
create schema security;

--マッピングテーブルの作成
create table security.map_tbl (
  mapped_role varchar,
  mapped_department varchar
);

insert into security.map_tbl (mapped_role, mapped_department) values
('HR', '人事部')
;

--check
select * from security.map_tbl;

マッピングテーブルの内容は下図のようになります。
ここでは1レコードのみですが、要件に応じてこちらを変更していけばよいことになります。

行アクセスポリシーの作成・付与は、以下のコマンドで実施しました。 [department_policy]という名称のポリシーを作成し、テーブルの[department]列に付与しています。

create or replace row access policy security.department_policy
as (department varchar) returns boolean ->
  is_role_in_session('ANALYST')
      or exists (
            select 1 from security.map_tbl
              where mapped_role = current_role()
                and mapped_department = department
          )
;

--row access policy の適用
alter table public.employee_data add row access policy security.department_policy on (department);

条件の詳細は以下の通りです。

  • IS_ROLE_IN_SESSION('ANALYST')
    • クエリ発行時のロールがANALYSTロールとその親ロールであれば真となり、すべてのレコードが表示されます
  • exist句以降
    • 現在のロールと [mapped_role] 列の値が一致するマッピングテーブルの[mapped_department] 列の値と、このポリシーを適用するテーブルの [department] 列の値が一致するレコードが表示されます
    • ※クエリ発行時のロールがhrであれば、[mapped_role] 列に値が存在します。そのときの [mapped_department] 列の値は「人事部」なので、employee_data テーブルの[department]列の値も「人事部」であるレコードのみが真となり表示されることになります

なお、ポリシーの作成・付与には以下の権限が必要なので、こちらも必要に応じてロールに付与しておきます。

  • CREATE ROW ACCESS POLICY
    • スキーマレベルの権限
    • ポリシーの作成に必要
  • APPLY ROW ACCESS POLICY
    • テーブルまたはビューに対して行アクセスポリシーを追加およびドロップする際に必要

結果をそれぞれ確認してみます。

  • hrロール
    • マッピングテーブルを参照し、[DEPARTMENT]列が「人事部」のレコードのみ表示されます
use role hr;
select * from employee_data;

Snowflake

Alteryx

  • analystロール
    • IS_ROLE_IN_SESSION('ANALYST')条件より、すべてのレコードが表示されます
    • 先の手順でマスキングポリシーを付与しているので、[EMAIL]列はマスクされた状態となります
use role analyst;
select * from employee_data;

Snowflake

Alteryx

ポリシーを付与後、Alteryx からもすぐにその内容が反映されていることが確認できました。

さいごに

行・列レベルのセキュリティを設定し、Alteryx から参照をしてみました。 SQLを通して柔軟な制御が可能なので、非常に便利な機能と思います。ツールから参照する際も当然その内容が反映されるので、DWH にアクセスするユーザーが増えても安心な機能かと思います。
今回は以上になります。

AWS

関連記事

SnowflakeでFunctional Role+Access Roleのロール設計を実現するTerraformのModule構成を考えてみた

さがら

2024.05.01

Snowflakeの認定試験を管理するSnowflake Certification portalの移行作業やってみた

さがら

2024.04.25

SnowflakeのリソースをTerraform×GitHub Actionsで管理するための手順をまとめてみた

さがら

2024.04.09

2024年3月にリリースされたSnowflakeの新機能・変更点のまとめ #SnowflakeDB

安原朋紀

2024.04.04