Zenn
Finatext Tech BlogPublicationへの投稿
🚀

【Snowflake】参照元のテーブルを見せずにVIEWを公開するときに権限設定でハマったこと

t-hiroto
に公開
SQL
Terraform
Snowflake
DataEngineering
tech

背景

SnowflakeのVIEWオブジェクトに対して、VIEWの参照元のテーブルは参照させずにSELECTクエリを実行できる権限をROLEに付与したかったが、このとき権限付与がうまくいかず、VIEWを参照させることができず詰まってしまったので、その原因と解決策について記事として残しておきます。

事象の再現

今回作成したオブジェクトは以下のような構成になっています。
権限はschema単位で管理しており、青色のDATABASE ROLEの階層を用意し、必要の応じて黄色のACCOUNT ROLEに権限を付与しています。

  • OWNERSHIP_SCHEMA：SCHEMAのOWNERSHIP権限を持つ
  • OWNERSHIP_VIEW：SCHEMA内の（ALL, FUTURE）VIEWのOWNERSHIP権限を持つ
  • OWNERSHIP_TABLE：SCHEMA内の（ALL, FUTURE）TABLEのOWNERSHIP権限を持つ
  • CREATE_VIEW：SCHEMA内のVIEWのCREATE VIEW権限を持つ
  • CREATE_TABLE：SCHEMA内のTABLEのCREATE TABLE権限を持つ
  • SELECT_VIEW：SCHEMA内の（ALL, FUTURE）VIEWのSELECT権限およびREFERENCES権限を持つ
  • SELECT_TABLE：SCHEMA内の（ALL, FUTURE）TABLEのSELECT権限およびREFERENCES権限を持つ

今回の権限管理構成については以下の記事で詳細に書いてますので、参照いただければと思います。
https://techblog.finatext.com/snowflake-access-control-and-cost-management-0e69d2d1ad1f

環境SETUP

  1. SCHEMAの作成と各種ROLEを作成
------------ databaseの作成 ------------
create database workspace;

------------ schemaの作成 ------------
create schema demo_schema;

------------ roleの作成 ------------
-- database roleの作成
create or replace database role access_demo_ownership_schema
    comment = 'schemaのownership権限を持ちます'
;
create or replace database role access_demo_ownership_view
    comment = 'viewのownership権限を持ちます'
;
create or replace database role access_demo_ownership_table
    comment = 'tableのownership権限を持ちます'
;
create or replace database role access_demo_create_view
    comment = 'schemaに対するcreate view権限を持ちます'
;
create or replace database role access_demo_create_table
    comment = 'schemaに対するcreate table権限を持ちます'
;
create or replace database role access_demo_select_view
    comment = 'viewのselect権限とreference権限を持ちます'
;
create or replace database role access_demo_select_table
    comment = 'tableのselect権限とreference権限を持ちます'
;

-- account roleの作成
create or replace role create_demo_objects_role comment = 'TABLEとVIEWを作成するaccount roleです';
create or replace role select_demo_view_role comment = 'VIEWのみを参照できるaccount roleです';
  1. DATABASE ROLEの構成を構築
------------ database roleの構成作成 ------------
-- access_demo_ownership_view
grant database role access_demo_create_view to database role access_demo_ownership_view;
grant database role access_demo_select_view to database role access_demo_ownership_view;

-- access_demo_ownership_table
grant database role access_demo_create_table to database role access_demo_ownership_table;
grant database role access_demo_select_table to database role access_demo_ownership_table;

-- access_demo_ownership_schema
grant database role access_demo_ownership_view to database role access_demo_ownership_schema;
grant database role access_demo_ownership_table to database role access_demo_ownership_schema;

-- account roleへのgrant
grant database role access_demo_ownership_schema to role create_demo_objects_role;
grant database role access_demo_select_view to role select_demo_view_role;
grant database role access_demo_ownership_schema to role sysadmin;
grant role create_demo_objects_role to role sysadmin;
grant role select_demo_view_role to role sysadmin;

  1. DATABASE ROLEに権限をGRANT
------------ database roleに権限付与 ------------
-- access_demo_create_view
grant usage on database workspace to database role access_demo_create_view;
grant usage on schema demo_schema to database role access_demo_create_view;
grant create view on schema demo_schema to database role access_demo_create_view;

-- access_demo_select_view
grant usage on database workspace to database role access_demo_select_view;
grant usage on schema demo_schema to database role access_demo_select_view;
grant select on all views in schema demo_schema to database role access_demo_select_view;
grant references on all views in schema demo_schema to database role access_demo_select_view;
grant select on future views in schema demo_schema to database role access_demo_select_view;
grant references on future views in schema demo_schema to database role access_demo_select_view;

-- access_demo_create_table
grant usage on database workspace to database role access_demo_create_table;
grant usage on schema demo_schema to database role access_demo_create_table;
grant create table on schema demo_schema to database role access_demo_create_table;

-- access_demo_select_table
grant usage on database workspace to database role access_demo_select_table;
grant usage on schema demo_schema to database role access_demo_select_table;
grant select on all tables in schema demo_schema to database role access_demo_select_table;
grant references on all tables in schema demo_schema to database role access_demo_select_table;
grant select on future tables in schema demo_schema to database role access_demo_select_table;
grant references on future tables in schema demo_schema to database role access_demo_select_table;
  1. DATABASE ROLEにOWNERSHIP権限をGRANT
-- grant ownership
grant ownership on all views in schema demo to database role access_demo_ownership_view copy current grants;
grant ownership on future views in schema demo to database role access_demo_ownership_view copy current grants;
grant ownership on all tables in schema demo to database role access_demo_ownership_table copy current grants;
grant ownership on future tables in schema demo to database role access_demo_ownership_table copy current grants;
grant ownership on schema demo to database role access_demo_ownership_schema copy current grants;

TABLEとVIEWを作成し、SELECTクエリを実行

  1. 参照元のTABLEを作成（このとき、実行ROLEは先述で作成したcreate_demo_objects_roleを使用）
-- 参照元のtable作成
use role create_demo_objects_role;

CREATE TABLE customer_table (
    customer_id INT AUTOINCREMENT,
    first_name STRING,
    last_name STRING,
    email STRING,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id)
);

INSERT INTO customer_table (first_name, last_name, email)
VALUES 
    ('Taro', 'Yamamoto', 'taro.yamamoto@example.com'),
    ('Hanako', 'Sato', 'hanako.sato@example.com'),
    ('Jiro', 'Tanaka', 'jiro.tanaka@example.com'),
    ('Sakura', 'Suzuki', 'sakura.suzuki@example.com'),
    ('Kenta', 'Kobayashi', 'kenta.kobayashi@example.com')
;
  1. 参照させたいVIEWを作成
-- viewの作成
CREATE VIEW customer_view AS
SELECT 
    customer_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    email,
    created_at
FROM 
    customer_table
;
  1. VIEWのSELECT権限のみを付与したROLE（select_demo_view_role）でVIEWを参照
use role select_demo_view_role;
select * from customer_view;

すると、以下のようなエラーが発生してしまいました

Failure during expansion of view 'CUSTOMER_VIEW': SQL compilation error:
Object 'WORKSPACE.DEMO.CUSTOMER_TABLE' does not exist or not authorized.

以下のようにVIEWの参照に必要な権限は与えているはずなのに何故...？ 🤔

SELECT VIEWに必要な権限：
✅ DATABASEおよびSCHEMAに対するUSAGE権限
✅ VIEWに対するSELECT権限（VIEWの作成元となるオブジェクトに対するSELECT権限は必要ない）

原因

原因としては、VIEWのOWNERSHIP権限を持つROLEに参照元のテーブルのSELECT権限を持っていないことで、VIEWのSELECTができていないようでした。

事象を整理すると...

  • CREATE VIEW実行時のROLEでは、SCHEMAのOWNERSHIP権限およびSCHEMA内のTABLEとVIEWのOWNERSHIP権限を持っていたため正常にVIEWの作成ができた
  • 作成されたVIEWのOWNERSHIP権限はaccess_demo_ownership_view DATABASE ROLEに継承されてしまっている
  • access_demo_ownership_view DATABASE ROLEは参照元のテーブルに対するSELECT権限は持っていない
  • したがって、access_demo_select_viewのみを持つselect_demo_view_roleではVIEWをSELECTすることができない

サポートに問い合わせしてみたところ以下のような回答が返ってきました（抜粋）

In your case, the error occurred because the database role that owns the view didn't have SELECT privileges on the underlying CUSTOMERS table.
This is why granting SELECT privileges to the view owner role resolved the issue.

To prevent similar issues in the future, ensure that:
1. The role creating/owning the view has SELECT privileges on all referenced objects
2. Users/roles that need to query the view are granted SELECT privilege on the view itself

This design allows for proper security encapsulation,
where you can grant users access to specific data through views without giving them direct access to the underlying tables.

上記をまとめると以下のような権限付与が必要なことがわかりました

  • VIEWを作成/所有するROLEに、参照されるすべてのオブジェクトに対するSELECT権限が付与されている必要がある
  • Viewが参照しているオブジェクトへの参照権限は誰かが持っていないといけなくて、この権限管理はOWNERSHIPを持つROLEが担っている
  • OWNERSHIP権限を継承する時には、その継承したROLEも同様に参照するオブジェクトのselect権限を持っている必要がある（じゃないと、VIEWさえ作ればVIEW経由で参照元のテーブルを権限無視して見れてしまう）

解決策

OWNERSHIP権限をGRANTしているDATABASE ROLEにVIEWの作成元となるオブジェクトに対するSELECTを付与することで、参照元のテーブルを見せずにVIEWをクエリできるようになりました

-- 参照元のテーブルに対するselect権限をaccess_demo_create_viewにgrantする
use role accountadmin;
grant database role access_demo_select_table to database role access_demo_create_view;

use role select_demo_view_role;
select * from customer_view;

まとめ

今回は、参照元のTABLEを見せずに特定のVIEWの参照権限を付与するときに、詰まったことをまとめてみました

基本的には、CREATE VIEWを実行したROLEがVIEWのOWNERSHIPを持つことが多いので、遭遇しずらいとは思いますが、DATABASE ROLEにOWNERSHIPをFUTURE GRANTSしている場合は注意する必要がありそうです。「外部のSnowflakeアカウントにShare経由で特定のVIEWだけを参照できるように権限付与を行いたい」のようなケースで遭遇するかなと思いました

ちなみに、EXTERNAL TABLEについても、EXTERNAL TABLEのOWNERSHIPを持つROLEには、参照しているSTAGEのREAD権限を付与していないと、同様な理由でクエリが失敗します

「OWNERSHIP権限を継承する時には、その継承したROLEも同様に参照するオブジェクトのSELECT権限を持っている必要がある」のような文言は、私が探した限り見つけられなかったので、もし同じような事象に遭遇したら役に立てていただければと思います！

Finatext Tech Blog
Finatext Tech BlogPublication

『金融を"サービス"として再発明する』Finatextグループのテックブログです。

Discussion