Snowflake の行アクセスポリシーを用いてアクセス可能なレコードを制限する

データベース（以下、DB）のテーブルに対するアクセス権限を付与する際に、テーブル全体へのアクセス権限ではなく、必要最低限のデータのみにアクセスできる権限を設定したいことがあると思います。

Snowflake の Enterprise Edition 以上では、行・列単位でのセキュリティ機能を利用することで、これを実現することが可能です。

本記事では、行アクセスポリシーという機能を用いて、特定のロールからテーブル内の一部のレコードのみにアクセスできる状態を作成していきます。

行アクセスポリシーとは
検証用環境の準備
行アクセスポリシーの実装
まとめ

行アクセスポリシーとは

公式のドキュメントには、行アクセスポリシーについて以下のように記載されています。

行アクセスポリシーは、次の型のステートメントから、テーブルまたはビューの特定の行を表示できるかどうかを決定する、スキーマレベルのオブジェクトです。

SELECT ステートメント

UPDATE、 DELETE、および MERGE ステートメントによって選択された行。

docs.snowflake.com

まとめると、「行アクセスポリシーを利用することで、行単位でアクセスできるレコードに制限をかけることができる」ということになります。

これにより、不必要なデータへのアクセスや書き換えなどを防止することができます。

では、実際に行アクセスポリシーを利用した、アクセス可能なレコードの制限を実践していきます。

検証用環境の準備

今回は、サンプルとして用意した「複数地域の会員情報がまとめて格納されているテーブル（MEMBER）」に対して、「特定のユーザー（TOKYO_MEMBER_ROLE）が東京の会員情報のみを閲覧できる状態」を作成するシナリオを想定しています。

比較用に、全ての会員情報にアクセス可能な ADMIN_MEMBER_ROLE も作成しています。

ロールの作成

以下のクエリを実行し、ADMIN_MEMBER_ROLE と TOKYO_MEMBER_ROLE の作成と、SYSADMIN への継承を行います。

use role useradmin;

-- 使用するロールの作成
create or replace role ADMIN_MEMBER_ROLE;
create or replace role TOKYO_MEMBER_ROLE;

-- SYSADMINにロールを継承
grant role ADMIN_MEMBER_ROLE to role SYSADMIN;
grant role TOKYO_MEMBER_ROLE to role SYSADMIN;

DB、Schema、Warehouse の作成と権限の付与

以下のクエリを実行し、TEST_ACCESS_DB と TEST_SCHEMA の作成と、各オブジェクトを利用するための権限を ADMIN_MEMBER_ROLE と TOKYO_MEMBER_ROLE に付与します。

use role SYSADMIN;

-- DBの作成
create database TEST_ACCESS_DB;

-- DBの権限を付与
grant ownership on database TEST_ACCESS_DB to role ADMIN_MEMBER_ROLE;

use role ADMIN_MEMBER_ROLE;

create schema TEST_ACCESS_DB.TEST_SCHEMA;

-- DB と Schema を利用する権限を各ロールに付与
use role SECURITYADMIN;

grant usage on database TEST_ACCESS_DB to role TOKYO_MEMBER_ROLE;

grant usage on schema TEST_ACCESS_DB.TEST_SCHEMA to role TOKYO_MEMBER_ROLE;


use role ACCOUNTADMIN;

-- Warehouse はデフォルトで存在するものを各ロールに付与
grant usage on warehouse COMPUTE_WH to role ADMIN_MEMBER_ROLE;
grant usage on warehouse COMPUTE_WH to role TOKYO_MEMBER_ROLE;

ステージの作成とデータの準備

会員情報のデータを格納した csv ファイルをアップロードするためのステージを作成します。

-- ステージの作成
create stage TEST_ACCESS_DB.TEST_SCHEMA.TEST_STAGE;

作成後、サンプルとして用意したデータを持つ以下の csv ファイルを、 Snowsight の WebUI を利用してアップロードしています。

csv ファイルが正しくアップロードされたか確認をしておきます。

-- アップロードした csv ファイルの確認
list @TEST_ACCESS_DB.TEST_SCHEMA.TEST_STAGE;

テーブルの作成と権限の付与

以下のクエリを実行し、MEMBER の作成とデータの投入、MEMBER に対する SELECT の権限を ADMIN_MEMBER_ROLE と TOKYO_MEMBER_ROLE に付与します。

use role ADMIN_MEMBER_ROLE;

-- テーブルを作成
create table TEST_ACCESS_DB.TEST_SCHEMA.MEMBER(
    ID NUMBER,
    REGION VARCHAR,
    NAME VARCHAR,
    DoB DATE,
    ADDRESS VARCHAR
);

use warehouse COMPUTE_WH;


-- テーブルにデータを格納
copy into TEST_ACCESS_DB.TEST_SCHEMA.MEMBER
from @TEST_STAGE/member.csv
file_format = ( type = csv encoding = utf8 skip_header = 1);


-- OTHER_ROLE にテーブル対する権限を付与
grant select on TEST_ACCESS_DB.TEST_SCHEMA.MEMBER to role TOKYO_MEMBER_ROLE;
grant select on TEST_ACCESS_DB.TEST_SCHEMA.MEMBER to role OTHER_ROLE;

ここまでの作業が完了した段階で、ADMIN_MEMBER_ROLE と TOKYO_MEMBER_ROLE の両方から MEMBER の全てのレコードに対するアクセス可能になっています。

行アクセスポリシーの実装

ここまでで必要な環境の準備が整ったので、早速行アクセスポリシーの作成と設定を進めていきます。

行アクセスポリシーの作成

以下のクエリを実行し、「limited_member_policy」という行アクセスポリシーを作成します。

-- 行アクセスポリシーの作成
create or replace row access policy limited_member_policy
as (R varchar) returns boolean ->
    'ADMIN_MEMBER_ROLE' = current_role()
    OR ('TOKYO_MEMBER_ROLE' = current_role() AND R = '東京');

この中で「->」以降が条件式となっており、その条件を満たした場合にレコードが表示されるような仕組みとなっています。

また、「(R varchar)」の R には行アクセスポリシーを設定したテーブル内の VARCHAR 型のカラムの値を参照するように設定しています。実際に参照するカラムについては、テーブルに対して行アクセスポリシーを設定する際に指定します。

テーブルへの行アクセスポリシーの設定

以下のクエリを実行し、行アクセスポリシーをテーブルに設定します。

alter table MEMBER add row access policy limited_member_policy on (REGION);

今回は on の後に REGION を指定しているため、先ほどの R の部分が MEMBER の REGION の値を参照して条件に合致するかを判定します。

では、実際に各ロールを利用して MEMBER テーブルのデータにアクセスしていきます。

各ロールを用いたテーブルへのアクセス

まずは、ADMIN_MEMBER_ROLE を用いて MEMBER テーブルへアクセスします。

結果として、全てのレコードが表示されていることが分かります。

これは実行時のロールが ADMIN_MEMBER_ROLE であることにより、常に「'ADMIN_MEMBER_ROLE' = current_role()」の条件を満たしているためです。

次に TOKYO_MEMBER_ROLE を用いて MEMBER テーブルへアクセスします。

結果として、REGION が「東京」のレコードのみが表示されていることが分かります。

先ほどテーブルに対して行アクセスポリシーを設定する際に、条件式の R が各レコードの REGION の値を参照するように設定しました。これにより TOKYO_MEMBER_ROLE を使用した場合、REGION の値が「東京」のレコードでのみ「'TOKYO_MEMBER_ROLE' = current_role() AND R = '東京'」の条件を満たしているためです。

このように適切な行アクセスポリシーを作成、設定することにより、任意のロールに対して一部のレコードのみにアクセス可能な状態にすることができました。