Snowflake: 行アクセスポリシー

Snowflake
この記事は約9分で読めます。

こんにちは！小澤です！
みなさんはSnowflakeの行アクセスポリシーをご存知でしょうか？
最近、行アクセスポリシーについて学ぶ機会があったため、概要や具体的な使用例を皆さんに共有します！

本記事でわかること

  • 行アクセスポリシーの概要
  • 行アクセスポリシーの使用例
  • 行アクセスポリシー使用時の注意事項

目次

  • 行アクセスポリシーとは
  • 行アクセスポリシーの使用例
  • 注意事項

行アクセスポリシーとは

Snowflakeの行アクセスポリシーを使用すると、テーブルやビューの特定の行を表示するかどうかを制御することができます。

行アクセスポリシーには条件と関数を含めることができ、その条件がTrueの行のみ表示します。

使用例では、行アクセスポリシーを使用してクエリ結果を制御する例を紹介します！

参考）公式ドキュメント「行アクセスポリシーについて」

行アクセスポリシーの使用例

今回の例では、以下のオブジェクトを作成します。

  • データベース（HR）
  • スキーマ（PROD）
  • 行アクセスポリシー（RAP_FOR_ENPLOYEE_TBL）
  • マッピングテーブル（MAP_TBL）
  • 従業員テーブル（EMPLOYEE）
  • ロール（EXECUTIVE、MANAGER、MEMBER）
  • ユーザ（ODA_NOBUNAGA、TOYOTOMI_HIDEYOSHI、TOKUGAWA_IEYASU）

これらを用いて、従業員テーブルのクエリ結果を制御します。

具体的には、「EXECUTIVE」ロールのユーザは全行、「MANAGER」ロールのユーザは自らが管理する部署に所属する従業員の行、「MEMBER」ロールのユーザは自らの行のみ表示されるように制御します。

「HR」という名前のデータベースを作成
-- データベースの作成
USE ROLE SYSADMIN;
CREATE DATABASE HR;
「PROD」というスキーマを作成
-- スキーマの作成
CREATE SCHEMA HR.PROD;
「EMPLOYEE」という従業員テーブルの作成・データの挿入
-- 従業員テーブルの作成
CREATE TABLE HR.PROD.EMPLOYEE (
 EMP_ID NUMBER,
 DEPARTMENT_ID NUMBER,
 DEPARTMENT_NAME VARCHAR,
 EMP_NAME VARCHAR
);

-- 従業員テーブルにデータを挿入
INSERT INTO HR.PROD.EMPLOYEE VALUES (1, 1, '役員', '織田　信長');
INSERT INTO HR.PROD.EMPLOYEE VALUES (2, 5, '営業部', '豊臣　秀吉');
INSERT INTO HR.PROD.EMPLOYEE VALUES (31, 5, '営業部', '千利休');
INSERT INTO HR.PROD.EMPLOYEE VALUES (32, 5, '営業部', '石田　三成');
INSERT INTO HR.PROD.EMPLOYEE VALUES (33, 5, '営業部', '福島　正則');
INSERT INTO HR.PROD.EMPLOYEE VALUES (13, 9, '人事部', '徳川　家康');
INSERT INTO HR.PROD.EMPLOYEE VALUES (14, 9, '人事部', '酒井　忠次');

従業員テーブルは以下の通りです。

EMP_IDDEPARTMENT_IDDEPARTMENT_NAMEEMP_NAME
11役員織田　信長
25営業部豊臣　秀吉
315営業部千利休
325営業部石田　三成
335営業部福島　正則
139人事部徳川　家康
159人事部酒井　忠次
ロールの作成と権限の付与

今回は以下の想定で、3つのロールを作成します。

  • すべての行を表示できる「EXECUTIVE」
  • 自らの部署の行を表示できる「MANAGER」
  • 自分の行のみ表示できる「MEMBER」
-- ロールの作成
USE ROLE USERADMIN;

CREATE ROLE EXECUTIVE;
CREATE ROLE MANAGER;
CREATE ROLE MEMBER;

-- ロールに適切な権限を付与
USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE HR TO ROLE MEMBER;
GRANT USAGE ON SCHEMA HR.PROD TO ROLE MEMBER;
GRANT SELECT ON TABLE EMPLOYEE TO ROLE MEMBER;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE MEMBER; -- 任意のウェアハウスを使用する権限を与える

GRANT ROLE MEMBER TO ROLE MANAGER;
GRANT ROLE MANAGER TO ROLE EXECUTIVE;
GRANT ROLE EXECUTIVE TO ROLE SYSADMIN;
ユーザの作成

各ロールを付与された以下のユーザを作成し、ロールを割り当てます。

  • EXECUTIVEロールを付与されたユーザ「ODA_NOBUNAGA」
  • MANAGERロールを付与されたユーザ「TOYOTOMI_HIDEYOSHI」
  • MEMBERロールを付与されたユーザ「TOKUGAWA_IEYASU」
-- ユーザの作成
USE ROLE USERADMIN;

CREATE USER ODA_NOBUNAGA
 PASSWORD = '<任意のパスワード>'
 LOGIN_NAME = <任意のログイン名>;

CREATE USER TOYOTOMI_HIDEYOSHI
 PASSWORD = '<任意のパスワード>'
 LOGIN_NAME = <任意のログイン名>;
 
CREATE USER TOKUGAWA_IEYASU
 PASSWORD = '<任意のパスワード>'
 LOGIN_NAME = <任意のログイン名>;
 
-- ロールの付与
USE ROLE SECURITYADMIN;

GRANT ROLE EXECUTIVE TO USER ODA_NOBUNAGA;
GRANT ROLE MANAGER TO USER TOYOTOMI_HIDEYOSHI;
GRANT ROLE MEMBER TO USER TOKUGAWA_IEYASU;
マッピングテーブルの作成・データの挿入
-- マッピングテーブル作成
USE ROLE SYSADMIN;

CREATE TABLE HR.PROD.MAP_TBL (
 USERNAME VARCHAR,
 EMP_ID NUMBER,
 DEPARTMENT_ID NUMBER
 );

-- データの挿入
INSERT INTO HR.PROD.MAP_TBL VALUES ('TOYOTOMI_HIDEYOSHI', 2, 5);
INSERT INTO HR.PROD.MAP_TBL VALUES ('TOKUGAWA_IEYASU', 13, 9);
行アクセスポリシーの作成
-- 行アクセスポリシーの作成
CREATE ROW ACCESS POLICY RAP_FOR_ENPLOYEE_TBL AS (EMP_ID NUMBER, DEPARTMENT_ID NUMBER) RETURNS BOOLEAN ->
CASE
 -- ①EXECUTIVEロールの場合
 WHEN CURRENT_ROLE() = 'EXECUTIVE' THEN TRUE
 
 -- ②MANAGERロールの場合
 WHEN CURRENT_ROLE() = 'MANAGER' THEN
 EXISTS(
    SELECT 1 FROM HR.PROD.MAP_TBL AS M WHERE M.USERNAME = CURRENT_USER() AND M.DEPARTMENT_ID = DEPARTMENT_ID
 )
 
 -- ③MEMBERロールの場合
 WHEN CURRENT_ROLE() = 'MEMBER' THEN
 EXISTS(
    SELECT 1 FROM HR.PROD.MAP_TBL AS M WHERE M.USERNAME = CURRENT_USER() AND M.EMP_ID = EMP_ID
 )
 
 ELSE FALSE
END;
従業員テーブルに行アクセスポリシーを適用する
--行アクセスポリシーを従業員テーブルに適用する
ALTER TABLE HR.PROD.EMPLOYEE ADD ROW ACCESS POLICY RAP_FOR_ENPLOYEE_TBL ON (EMP_ID, DEPARTMENT_ID);
各ユーザでクエリをする
  • EXECUTIVEロールを付与されたユーザ「ODA_NOBUNAGA」の場合
-- ODA_NOBUNAGAでログインし、EMPLOYEEテーブルをクエリする
SELECT * FROM HR.PROD.EMPLOYEE;
EMP_IDDEPARTMENT_IDDEPARTMENT_NAMEEMP_NAME
11役員織田　信長
25営業部豊臣　秀吉
315営業部千利休
325営業部石田　三成
335営業部福島　正則
139人事部徳川　家康
159人事部酒井　忠次
EXECUTIVEロールのユーザは全行表示される

-- TOYOTOMI_HIDEYOSHIでログインし、EMPLOYEEテーブルをクエリする
SELECT * FROM HR.PROD.EMPLOYEE;
EMP_IDDEPARTMENT_IDDEPARTMENT_NAMEEMP_NAME
25営業部豊臣　秀吉
315営業部千利休
325営業部石田　三成
335営業部福島　正則
MANAGERロールのユーザは自らの部署のみ表示される

  • MEMBERロールを付与されたユーザ「TOKUGAWA_IEYASU」
-- TOKUGAWA_IEYASUでログインし、EMPLOYEEテーブルをクエリする
SELECT * FROM HR.PROD.EMPLOYEE;
EMP_IDDEPARTMENT_IDDEPARTMENT_NAMEEMP_NAME
139人事部徳川　家康
MEMBERロールのユーザは自分自身のみ表示される

このように同じテーブルデータでも定義した条件で表示行を制御することができます。

行アクセスポリシーの注意事項

  • オブジェクトのOWNERSHIPを保持しているロールも、行アクセスポリシーによる制御の対象です。
  • 外部テーブルをマッピングテーブルとして使用することはできませんが、外部テーブル自体に行アクセスポリシーを適用できます。
  • ネストされた行アクセスポリシーはサポートされています。（ベーステーブル、ビューの両方に行アクセスポリシーが適用されている場合、ベーステーブルの行アクセスポリシーから先に評価されます。）
  • 行アクセスポリシーは同一オブジェクトに一つのみ適用することができます。
  • マスキングポリシーと併用は可能ですが、同一列にマスキングポリシーも適用されている場合はエラーが発生します。
  • 行アクセスポリシーはマテリアライズドビューにも適用できますが、ベーステーブルに行アクセスポリシーが適用されている場合は、エラーが発生します。

参考）公式ドキュメント「行アクセスポリシーについて」

まとめ

行アクセスポリシーを使用することで、クエリ結果の表示を制御することができます。
マッピングテーブルを使用することで、誰にどのようなレコードを見せるのかという、細かい制御を実現することができます。
皆さんもぜひ行アクセスポリシーを使ってみてください！

コメント

タイトルとURLをコピーしました