はじめに
Snowflakeには他の現代的なデータベースと同様に行レベルセキュリティ(Row-Level Security)の機能があります。
この機能によりテーブルを分離することなしに、クエリの結果にフィルタをかけ表示される内容を変えることができます。
つまり複数の企業などにテーブルを参照させる場合に、特定の企業には見せたくない行をカラムで判定させることができるため、専用のビューやテーブルコピーなどを行う必要がなくなりデータ量も抑えることができます。
Snowflakeではこの機能を行アクセスポリシーと呼んでいます。
本記事では行アクセスポリシーのうち、リッチなほうのマッピングテーブルを使う方法を検証します。
- 注意事項
- 行アクセスポリシーを使うにはSnowflakeのEnterprise Edition以上である必要があります。
- 今回のようなマッピングテーブルを使う場合は、クエリ性能が低下する場合があります。
- 行アクセスポリシー内のサブクエリを単純化するなど、評価回数を減らすようにしてください。
概要
以下に記載されています。
- 行アクセスポリシーを作動させることができるクエリは以下の種類です。
- SELECT ステートメント
- UPDATE、 DELETE、および MERGE ステートメントによって選択された行
概念図
以下に記載されています。
設定はまず行アクセスポリシーを作成し、テーブルに適用する流れになります。(下図。引用元は上記公式ページ。)
行アクセスポリシーを適用されたテーブルにselect文などのクエリを実行すると、必ず行アクセスポリシーによるチェックを経由する形になります。
また、行アクセスポリシーは、[列レベルのセキュリティ]や[マスキングポリシー]よりも先に評価されます。
行アクセスポリシーはViewへの適用も可能です。評価順などは以下の情報を参照してください。
公式手順
公式ドキュメントでは以下のページに行アクセスポリシーの手順が記載されています。
しかし上記の公式ドキュメントではデータのインサートが抜けていたり、本質的ではないロール分離の設定が記載されていたりと、非常に理解しづらい書き方になっています。そこで本記事では公式ドキュメントをアレンジした手順をご紹介します。
本記事の構成
本例では下図のような構成を作成します。
- 本例の行アクセスポリシーでは以下のような設定をします。
- 幹部ロールはクエリ対象テーブルの全データを閲覧可能
- マネージャユーザは自分が担当しているリージョンのデータのみ閲覧可能
1. 前準備
Snowflakeの公式手順では最小限の権限思想に基づき、複数の専用ロールを用意する手順になっています。本手順はわかりやすくするため全てACCOUNTADMINロールで作業を実施しています。本番で利用する際は行アクセスポリシーやマッピングテーブルを管理するロールをACCOUNTADMINの下位ロールで設定したほうがよいと思います。
1-1. データベース・スキーマ・ロールの作成
テスト用のデータベース・スキーマ・ロールを作成します。
use role ACCOUNTADMIN;
-- テスト用のデータベースを作成
create database TEST01;
-- 行レベルセキュリティで用いる管理用テーブルを置くスキーマを作成します。
create schema TEST01.SECURITY;
--ロールの作成---------------------
-- 幹部用ロールを作成します。行アクセスポリシーの影響を受けないロールとします。上位をsysadminにします。
create role SALES_EXECTIVE_ROLE;
grant role SALES_EXECTIVE_ROLE to role SYSADMIN;
-- 営業部のマネージャー用ロールを作成します。行アクセスポリシーの影響を受けるロールとします。上位を幹部用ロールにします。
create role SALES_MANAGER_ROLE;
grant role SALES_MANAGER_ROLE to role SALES_EXECTIVE_ROLE;
-- 営業部のベースのロールを作成します。各種USAGE権限はこちらにつけ、マネージャや幹部ロールはpublicの上位に位置づけます。
create role SALES_PUBLIC_ROLE;
grant role SALES_PUBLIC_ROLE to role SALES_MANAGER_ROLE;
-- 各種USAGE権限を最下位のSALES_PUBLIC_ROLEに付与していきます。
grant usage on warehouse COMPUTE_WH to role SALES_PUBLIC_ROLE;
grant usage on database TEST01 to SALES_PUBLIC_ROLE;
grant usage on schema TEST01.PUBLIC to SALES_PUBLIC_ROLE;
grant usage on schema TEST01.SECURITY to SALES_PUBLIC_ROLE;
1-2. ユーザの作成
本例ではテストのため単純なパスワードを設定していますので、そのまま試す場合は充分注意してください。
--幹部用ユーザ---------------------
-- ユーザALICEを作成します。
create or replace user ALICE
password = 'Alice12345'
login_name = ALICE
default_role = SALES_MANAGER_ROLE
must_change_password = FALSE;
-- ALICEには幹部用ロールを付与します。
grant role SALES_EXECTIVE_ROLE to user ALICE;
--マネージャ用ユーザ---------------------
-- ユーザBOBを作成します。
create or replace user BOB
password = 'Bob12345'
login_name = BOB
default_role = SALES_MANAGER_ROLE
must_change_password = FALSE;
-- BOBには営業部のマネージャー用ロールを付与します。
grant role SALES_MANAGER_ROLE to user BOB;
-- ユーザSIMONを作成します。
create or replace user SIMON
password = 'Simon12345'
login_name = SIMON
default_role = SALES_MANAGER_ROLE
must_change_password = FALSE;
-- SIMONには営業部のマネージャー用ロールを付与します。
grant role SALES_MANAGER_ROLE to user SIMON;
2. テーブル関連の作業
2-1. クエリ対象のテーブルの作成
今回はテストのため、行アクセスポリシーを適用したいテーブルを新規作成します。
-- ウェアハウスはご自分の環境のものを指定してください。
use warehouse COMPUTE_WH;
use database TEST01;
use schema PUBLIC;
-- クエリ対象のテーブルを作成します。このregionカラムが今回のフィルタになります。
create table SALES (
customer varchar,
product varchar,
spend decimal(20, 2),
sale_date date,
region varchar
);
-- 適当なデータを5つ投入します。フィルタのテストのためregionは多少バラけさせます。
insert into SALES (customer, product, spend, sale_date, region)
values
('kimura', 'pizza', 15.0, '2010-09-01', 'EU'),
('kobayashi', 'macaroni', 5.0, '2010-09-02', 'EU'),
('suzuki', 'pasta', 20.0, '2010-09-03', 'NA'),
('sato', 'bread', 3.0, '2010-09-04', 'NA'),
('takai', 'pasta', 20.0, '2010-09-05', 'EU');
-- 念の為、テーブルの中身を確認します。
select * from SALES;
-- 作成したテーブルへのselect権限をSALES_PUBLIC_ROLEに付与します。
grant select on table SALES to role SALES_PUBLIC_ROLE;
2-2. マッピング用のテーブルの作成
ユーザと、ユーザが表示できる行、を紐付けるマッピングテーブルを定義しデータをインサートします。
-- SECURITYスキーマに、マッピング用として[SALES_MAP]テーブルを作成します
create table SECURITY.SALES_MAP (
sales_manager varchar,
mapped_region varchar
);
-- マッピングテーブルへデータを投入します。それぞれ以下のregionの担当というシナリオにします。
-- ALICE(幹部) -> 全データの閲覧をできるようにするため、マッピングテーブルへの登録は不要
-- BOB(マネージャ) -> NA 担当
-- SIMON(マネージャ) -> EU 担当
INSERT INTO security.sales_map(sales_manager,mapped_region)
VALUES('BOB','NA');
INSERT INTO security.sales_map(sales_manager,mapped_region)
VALUES('SIMON','EU');
-- 念の為、テーブルの中身を確認します。
select * from SECURITY.SALES_MAP;
見ておわかりいただけるかと思いますが、マッピングテーブルは特別なテーブルではなく、普通のテーブルです。
本例では説明のためマッピングテーブルにユーザ名を登録する形にしています。そのためユーザが追加・削除されたり担当エリアが変わった場合はこのマッピングテーブルを更新する必要があります。ユーザベースで判定させるとそのような手間が生じるため、本番環境ではロールを登録する形をおすすめします。(ロールを登録した場合は、後続の行アクセスポリシーでcurrent_user()ではなくcurrent_role()関数を使う形になります）
2-3. 行アクセスポリシーの作成
create or replace row access policy SECURITY.SALES_POLICY as (region varchar) returns boolean ->
IS_ROLE_IN_SESSION('SALES_EXECTIVE_ROLE')
or exists (
select 1 from SECURITY.SALES_MAP
where sales_manager = current_user()
and mapped_region = region
)
;
-- 念の為、行アクセスポリシーの中身を確認します。
describe row access policy SECURITY.SALES_POLICY;
ここは少し解説します。
行アクセスポリシーのコマンド構文
行アクセスポリシーは通常のテーブルではありません。上記のdescribeコマンドで中身を確認することができます。
コマンドの結果は以下のようになっており、bodyカラムに全てのポリシー評価式が文字列として格納されます。
| name | signature | return_type | body |
|---|---|---|---|
| SALES_POLICY | (REGION VARCHAR) | BOOLEAN | IS_ROLE_IN_SESSION('SALES_EXECTIVE_ROLE') or exists ( select 1 from SECURITY.SALES_MAP where sales_manager = current_user() and mapped_region = region ) |
create row access policyコマンド構文の詳細は以下を参照してください。
https://docs.snowflake.com/ja/sql-reference/sql/create-row-access-policy.html
1行目のポイントは以下の通りです。
-
AS ( <引数名> <引数型> [ , ... ] )- クエリ対象のテーブルにある、フィルタ条件となるカラム名とデータ型を指定します。
- 本例では[SALES]テーブルの[region]カラムをフィルタ条件にしたいので、AS構文の引数にもそれを指定しています。
-
returns boolean ->- この-> 以降が行アクセスポリシーの評価式になります。
- 結果がTRUEとなる場合は特定の行にアクセスできます。FALSEの場合はアクセスができません。
行アクセスポリシーの評価式
かなり自由に条件文を設定できるようです。
以下の全関数一覧や、コンテキスト関数一覧を一通り眺めてみるとアイデアが湧いてくるかと思います。
https://docs.snowflake.com/ja/sql-reference/functions-all.html
https://docs.snowflake.com/ja/sql-reference/functions-context.html
本例で指定した評価式を説明します。
-
IS_ROLE_IN_SESSION('SALES_EXECTIVE_ROLE') -
現在のロールが幹部用ロールの場合はTRUEを返し、制限なく行の中身を全て閲覧できます。IS_ROLE_IN_SESSION()関数は親ロールも含めて評価してくれます。関数の仕様は以下を参照ください。
- https://docs.snowflake.com/ja/sql-reference/functions/is_role_in_session.html
- 公式マニュアルによると「IS_ROLE_IN_SESSION は、マッピングテーブルを含む行アクセスポリシーでは使用できません。」と記載されていますが、この行はマッピングテーブルを参照する構文ではないためか、利用できています。(後続行ではマッピングテーブルを参照しています）
- 他にもcurrent_role()という現在のロールを返す関数もありますが、current_role()は親ロールを参照してくれないため、SYSADMINやACCOUNTADMINといったロールも or 文で明示的に許可してあげないと、管理者がクエリする際にいちいちSALES_EXECTIVE_ROLEに切り替える手間が発生します。
-
or exists ( -
上記の関数、あるいは以下のサブクエリで結果が返ってくれば、TRUEを返し行にアクセスできます。
-
select 1 from SECURITY.SALES_MAP -
[select 1]はexists句とセットのようなものですね。条件としてselectを利用しているだけです。
-
先程作成したマッピング用テーブル[SECURITY.SALES_MAP]を参照します。
-
where sales_manager = current_user() -
マッピングテーブルのsales_managerカラムと、現在のユーザ名が一致する場合で、(下の行に続く)
-
and mapped_region = region) -
かつ、mapped_regionと、クエリ対象テーブルのregionカラムの中身が一致すれば、一致する行にアクセスできます。
【注意】この行アクセスポリシー内で用いるコンテキスト関数(current_user等)は完全一致で評価されます。
Snowflakeオブジェクトの大文字小文字には充分注意してください。
Snowflakeではオブジェクト作成時にダブルクォーテーションをつけなかった場合、小文字で作成しても実際は大文字で保持されます。
たとえば以下のようにコンテキスト関数内でロール名を小文字で書いてしまったりすると文字列が一致せずにフィルタが機能せず、デバッグで苦しむことになります。
IS_ROLE_IN_SESSION('sales_exective_role')
オブジェクトの識別子について詳しく知りたい方は以下を参照してください。
https://docs.snowflake.com/ja/sql-reference/identifiers-syntax.html
他にも以下のような構文も利用できます。
- if-then-else形式のCASE文
2-4. テーブルへ行アクセスポリシーをアタッチ
alter table TEST01.PUBLIC.SALES add row access policy TEST01.SECURITY.SALES_POLICY on (region);
今回のテストケースでは1つのテーブルにアタッチしていますが、1つの行アクセスポリシーを複数のテーブルにアタッチすることも可能です。
構文は以下のようになっており、on のあとに、クエリ対象テーブルの判定を行うカラムを指定します。
ADD ROW ACCESS POLICY <ポリシー名> ON (<列名> [ , ... ])
構文の詳細は以下を参照してください。
3. テスト
3-1. カスタムロール[SALES_EXECTIVE_ROLE]でのテスト
幹部ユーザALICEが所属しているロールです。
use role SALES_EXECTIVE_ROLE;
use warehouse COMPUTE_WH;
select * from TEST01.PUBLIC.SALES;
結果は以下のように、全てのデータ(5件)が返ってきました。
正しい動きです。ALICEは行アクセスポリシーに指定した「IS_ROLE_IN_SESSION」関数の[SALES_EXECTIVE_ROLE]ロールに所属しているため、全ての行を表示できます。
集計系の処理も正常に動きます。幹部なのでリージョンを横断した分析が可能です。
select product, sum(spend)
from TEST01.PUBLIC.SALES
where year(sale_date) = 2010
group by product;
3-2. システムロール[SYSADMIN]でのテスト
[SYSADMIN]ロールは、[SALES_EXECTIVE_ROLE]ロールの親に指定しています。
行アクセスポリシーで利用した[IS_ROLE_IN_SESSION()]関数によって、正しく親ロールにもポリシーが適用されているか確認します。
結果は以下のように、[SALES_EXECTIVE_ROLE]ロールと同様に全てのデータ(5件)が返ってきました。正しい動きです。
3-3. カスタムロール[SALES_PUBLIC_ROLE]でのテスト
今度は行アクセスポリシーに記載していない、つまりポリシー上FALSEとなるはずの[SALES_PUBLIC_ROLE]ロールでデータを参照してみます。
結果は以下のように、データを参照することができません。正しい動きです。
3-4. ユーザ[BOB]でのテスト
操作していたACCOUNTADMIN可能なユーザをログアウトし、ユーザBOBでログインし直します。
use database TEST01;
use schema PUBLIC;
select * from SALES;
結果は以下のように、BOBが担当しているNAリージョンのデータだけが2件返ってきました。
正しい動きです。BOBは行アクセスポリシーのマッピングルールが適用されます。
3-5. ユーザ[SIMON]でのテスト
操作していたユーザをログアウトし、ユーザSIMONでログインし直します。
use database TEST01;
use schema PUBLIC;
select * from SALES;
結果は以下のように、SIMONが担当しているEUリージョンのデータだけが3件返ってきました。
正しい動きです。SIMONは行アクセスポリシーのマッピングルールが適用されます。
4. 環境の削除
テスト環境を削除するためのコマンド一式を載せておきます。
ACCOUNTADMIN可能なユーザで実施してください。
use role ACCOUNTADMIN;
use warehouse COMPUTE_WH;
drop role SALES_EXECTIVE_ROLE;
drop role SALES_MANAGER_ROLE;
drop role SALES_PUBLIC_ROLE;
drop user ALICE;
drop user BOB;
drop user SIMON;
-- 本来は最後の[drop database TEST01;]を行うだけで事足りるのですが、運用ではデタッチなどのオペレーションも発生するため、子オブジェクトから削除する手順を書いてみます。
-- テーブルから行アクセスポリシーをデタッチする
alter table TEST01.PUBLIC.SALES drop row access policy TEST01.SECURITY.SALES_POLICY;
-- 行アクセスポリシーを削除する
drop row access policy TEST01.SECURITY.SALES_POLICY;
-- マッピングテーブルを削除する
drop table TEST01.SECURITY.SALES_MAP;
-- クエリ対象テーブルを削除する
drop table TEST01.PUBLIC.SALES;
-- データベースを削除する
drop database TEST01;
さいごに
行アクセスポリシーはかなり自由な評価式を書くことができます。
本記事の例は文字列の完全一致に依存するところが多く、空データなどの対応を考慮していません。
巨大なテーブルにポリシーを適用した場合のオーバーヘッドや、ポリシーにLIKE文などを使った場合どうなるかなど、実運用を踏まえるとまだまだ検証する余地はあると思います。
本記事記載時点(2021/8/28)では、正しく動くSnowflakeの行アクセスポリシーの例を私はWeb上に見つけられなかったため、本記事を書いてみました。さらなる検証の一助となれば幸いです。
以上です。