Row Access Policyを使って、特定のユーザが見れる行数を絞ってみた

がく＠ちゅらデータエンジニアです

いやー、長らく記事を書いてませんでしたが、今後はちゃんと書いていこうかなって思ってます！

概要

Tableauからとあるテーブルを見た時に、行数が半端なくって、Vizを開くのにも、パブリッシュするにも難儀する・・・・開発してる時だけでもなんとかならんか・・・・という事がありまして、やってみた次第です。

使用するDB: GAKU_DB
使用するスキーマ：PUBLIC

利用するTableは、SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;
1,500,000 行のテーブルを上記のGAKU_DB.PUBLICにコピーして使いました

CREATE OR REPLACE TABLE orders 
AS 
	SELECT * FROM snowflake_sample_data.tpch_sf1.orders;

※というのも、SNOWFLAKE_SAMPLE_DATAのテーブルはCLONES出来ないため

やりたいこと

• 指定のロール（GAKU_ROLE) で、ordersは、1000行だけ閲覧できる
  • ⇨　結局、1000行はできず、100分の1にした
• それ以外の閲覧可能なロールでは、ordersはすべて（1,500,000行）閲覧できる

を実現したい

どんな時に使えるか

• BIなどでダッシュボードを作る際に、テーブル全部見れると読み込みにすごい時間がかかったりするので、作成・確認時は、テーブルの見れる行数を絞りたい
• ↑は別途ビューとか使えばいいんだけど、それが出来ない事情があったりする場合

対象となるテーブル構成

select count(*) from orders;

role	count(*)
accountadmin	1,500,000
gaku_role	1,500,000

利用するordersは

name	type
O_ORDERKEY	NUMBER(38,0)
O_CUSTKEY	NUMBER(38,0)
O_ORDERSTATUS	VARCHAR(1)
O_TOTALPRICE	NUMBER(12,2)
O_ORDERDATE	DATE
O_ORDERPRIORITY	VARCHAR(15)
O_CLERK	VARCHAR(15)
O_SHIPPRIORITY	NUMBER(38,0)
O_COMMENT	VARCHAR(79)

ROW ACCESS POLICY　第1段階

一旦、「GAKU_ROLE」 だけで見れる　ROW ACCESS POLICY を作ります

create or replace row access policy my_row_access_policy as (O_ORDERKEY NUMBER(38,0)) returns boolean ->
    case when 'GAKU_ROLE' = current_role() then true
    else false
    end
;

次に、上記、ポリシーをテーブルにアタッチします
※ついでに、ポリシーをドロップするSQLも書いておきます。ポリシーを作り直すときは、一旦ドロップしないとできなかった

-- テーブルにポリシーを追加するSQL
ALTER TABLE ORDERS ADD ROW ACCESS POLICY my_row_access_policy ON (O_ORDERKEY);

-- テーブルからポリシーを削除するSQL
ALTER TABLE ORDERS DROP ROW ACCESS POLICY my_row_access_policy;

role	count(*)
accountadmin	0
gaku_role	1,500,000

ROW ACCESS POLICY　第2段階

• GAKU_ROLEで100分の1の行数に絞って見れる
• その他のロールでは見れない

create or replace row access policy my_row_access_policy as (O_ORDERKEY NUMBER(38,0)) returns boolean ->
    case when ('GAKU_ROLE' = current_role()) and (abs(hash(o_orderkey))%100 = 0) then true
    else false
    end
;

role	count(*)
accountadmin	0
gaku_role	14,940

ROW ACCESS POLICY　完成

では最後に、
やりたいこと

• 指定のロール（GAKU_ROLE) で、ordersは、1000行だけ閲覧できる ※が出来なかったんで
• 指定のロール（GAKU_ROLE) で、ordersは、100分の1　だけ閲覧できる
• それ以外の閲覧可能なロールでは、ordersはすべて（1,500,000行）閲覧できる

create or replace row access policy my_row_access_policy as (O_ORDERKEY NUMBER(38,0)) returns boolean ->
    case when ('GAKU_TRAINING_ROLE' = current_role()) and (abs(hash(o_orderkey))%100 != 0) then false
    else true
    end
;
ALTER TABLE ORDERS ADD ROW ACCESS POLICY my_row_access_policy ON (O_ORDERKEY);

role	count(*)
accountadmin	1,500,000
gaku_role	14,940