[新機能]Snowflake の Restricted Session Scope が一般提供になったので CoCo 利用時だけ実効権限を狭めてみた

privilege_scopes:
  allowed_privileges:
    - privileges: [<グループ権限>, ...]
      account: [all]                 # または databases: [...] / schemas: [...]
    - privileges: [data write]
      databases: [SANDBOX_DB, USER$] # USER$ はセッションユーザーの個人 DB に解決される
role_scopes:
  blocked_roles: [ACCOUNTADMIN, SYSADMIN]   # または allowed_roles
  blocked_secondary_roles: [ACCOUNTADMIN]    # または allowed_secondary_roles
  allow_role_switching: false                # デフォルトは true

-- (a) Snowflake 定義済みスコープを名前で参照
CREATE SESSION POLICY sp_a
  AGENT_RESTRICTED_SESSION_SCOPE = 'SNOWFLAKE$DATA_READ';

-- (b) 自作の RSS オブジェクトを完全修飾名で参照
CREATE RESTRICTED SESSION SCOPE gov.policies.my_scope AS $$ ... $$;
CREATE SESSION POLICY sp_b
  AGENT_RESTRICTED_SESSION_SCOPE = 'GOV.POLICIES.MY_SCOPE';

-- (c) YAML をインラインで埋め込む
CREATE SESSION POLICY sp_c
  AGENT_RESTRICTED_SESSION_SCOPE = $$
privilege_scopes:
  allowed_privileges:
    - privileges: [data read]
      account: [all]
$$;

-- 適用先はアカウント全体またはユーザー単位
ALTER ACCOUNT SET SESSION POLICY sp_a;
ALTER USER jsmith SET SESSION POLICY sp_b;

SELECT SYS_CONTEXT('SNOWFLAKE$CURRENT', 'ACTIVE_RESTRICTED_SESSION_SCOPES');

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE DATABASE KAWABATA_RSS_PROD_DB;     -- 本番相当
CREATE OR REPLACE SCHEMA KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES;
CREATE OR REPLACE DATABASE KAWABATA_RSS_SANDBOX_DB;  -- エージェントの書き込み先
CREATE OR REPLACE SCHEMA KAWABATA_RSS_SANDBOX_DB.WORK;
CREATE OR REPLACE DATABASE KAWABATA_RSS_GOV_DB;      -- RSS とポリシーの格納先
CREATE OR REPLACE SCHEMA KAWABATA_RSS_GOV_DB.POLICIES;

CREATE OR REPLACE TABLE KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_NOTES (
  note_id    INT AUTOINCREMENT,
  note       STRING,
  created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

CREATE OR REPLACE FUNCTION KAWABATA_RSS_GOV_DB.POLICIES.RSS_ECHO(v STRING)
  RETURNS STRING
  AS $$ 'echo: ' || v $$;

CREATE OR REPLACE PROCEDURE KAWABATA_RSS_GOV_DB.POLICIES.RSS_PING()
  RETURNS STRING
  LANGUAGE SQL
  EXECUTE AS CALLER
AS
$$
BEGIN
  RETURN 'pong from ' || CURRENT_ROLE();
END;
$$;

CREATE OR REPLACE ROLE KAWABATA_RSS_ANALYST;
GRANT USAGE ON WAREHOUSE WH_SI_JP TO ROLE KAWABATA_RSS_ANALYST;

-- 本番相当: SELECT / INSERT / DELETE + CREATE TABLE
GRANT USAGE ON DATABASE KAWABATA_RSS_PROD_DB TO ROLE KAWABATA_RSS_ANALYST;
GRANT USAGE, CREATE TABLE ON SCHEMA KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES TO ROLE KAWABATA_RSS_ANALYST;
GRANT SELECT, INSERT, DELETE ON TABLE KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS TO ROLE KAWABATA_RSS_ANALYST;

-- サンドボックス: SELECT / INSERT + CREATE TABLE
GRANT USAGE ON DATABASE KAWABATA_RSS_SANDBOX_DB TO ROLE KAWABATA_RSS_ANALYST;
GRANT USAGE, CREATE TABLE ON SCHEMA KAWABATA_RSS_SANDBOX_DB.WORK TO ROLE KAWABATA_RSS_ANALYST;
GRANT SELECT, INSERT ON TABLE KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_NOTES TO ROLE KAWABATA_RSS_ANALYST;

-- ガバナンス: UDF / ストアドプロシージャの実行のみ
GRANT USAGE ON DATABASE KAWABATA_RSS_GOV_DB TO ROLE KAWABATA_RSS_ANALYST;
GRANT USAGE ON SCHEMA KAWABATA_RSS_GOV_DB.POLICIES TO ROLE KAWABATA_RSS_ANALYST;
GRANT USAGE ON FUNCTION KAWABATA_RSS_GOV_DB.POLICIES.RSS_ECHO(STRING) TO ROLE KAWABATA_RSS_ANALYST;
GRANT USAGE ON PROCEDURE KAWABATA_RSS_GOV_DB.POLICIES.RSS_PING() TO ROLE KAWABATA_RSS_ANALYST;

GRANT ROLE KAWABATA_RSS_ANALYST TO USER KAWABATA_TOMOHIRO;

ALTER USER KAWABATA_TOMOHIRO SET DEFAULT_ROLE = KAWABATA_RSS_ANALYST;
ALTER USER KAWABATA_TOMOHIRO SET DEFAULT_SECONDARY_ROLES = ();

以下の SQL を、書き換えずに 1 文ずつそのまま実行して、各文の成功/失敗と結果、
エラーが出た場合はエラーメッセージ全文を表にまとめてください。
エラーが出ても中断せず次の文に進んでください。

SELECT
  CURRENT_ROLE()            AS CURRENT_ROLE,
  CURRENT_SECONDARY_ROLES() AS CURRENT_SECONDARY_ROLES,
  SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED') AS IS_AGENT_ACTIVATED,
  SYS_CONTEXT('SNOWFLAKE$CURRENT', 'AGENT_TYPE')         AS AGENT_TYPE;

SELECT COUNT(*) FROM KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS;
INSERT INTO KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS
  VALUES (901, 'テスト 太郎', 'test901@example.com', CURRENT_DATE(), 'Test Item', 1.00, 'Test', 'RSS_TEST');
CREATE TABLE KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.AGENT_BASELINE (id INT);
INSERT INTO KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_NOTES (note) VALUES ('P0 baseline');
SELECT KAWABATA_RSS_GOV_DB.POLICIES.RSS_ECHO('P0');
CALL KAWABATA_RSS_GOV_DB.POLICIES.RSS_PING();

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE RESTRICTED SESSION SCOPE KAWABATA_RSS_GOV_DB.POLICIES.RSS_PROD_READONLY
  COMMENT = 'UC-A: 本番は読むだけ。書けるのは sandbox と個人 DB'
  AS $$
privilege_scopes:
  allowed_privileges:
    - privileges: [data read, object discovery, program usage, compute usage]
      account: [all]
    - privileges: [data write]
      databases: [KAWABATA_RSS_SANDBOX_DB, USER$]
$$;

CREATE OR REPLACE SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_PROD_READONLY
  AGENT_RESTRICTED_SESSION_SCOPE = 'KAWABATA_RSS_GOV_DB.POLICIES.RSS_PROD_READONLY';

-- 自分のユーザーにだけ適用する
ALTER USER KAWABATA_TOMOHIRO SET SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_PROD_READONLY;

DESC RESTRICTED SESSION SCOPE KAWABATA_RSS_GOV_DB.POLICIES.RSS_PROD_READONLY;
DESC SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_PROD_READONLY;

SELECT COUNT(*) FROM KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS;
INSERT INTO KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS
  VALUES (902, 'テスト 次郎', 'test902@example.com', CURRENT_DATE(), 'Test Item', 1.00, 'Test', 'RSS_TEST');
CREATE TABLE KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.AGENT_T1 (id INT);
INSERT INTO KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_NOTES (note) VALUES ('P1 UC-A');
CREATE TABLE KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_T1 (id INT);
SELECT KAWABATA_RSS_GOV_DB.POLICIES.RSS_ECHO('P1');
CALL KAWABATA_RSS_GOV_DB.POLICIES.RSS_PING();

SQL access control error: Insufficient privileges to operate on table 'ORDERS'.
Restricted session scope KAWABATA_RSS_GOV_DB.POLICIES.RSS_PROD_READONLY does not include
INSERT access to TABLE KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS.

ALTER RESTRICTED SESSION SCOPE KAWABATA_RSS_GOV_DB.POLICIES.RSS_PROD_READONLY ADD $$
privilege_scopes:
  allowed_privileges:
    - privileges: [object management]
      databases: [KAWABATA_RSS_SANDBOX_DB]
$$;

ALTER RESTRICTED SESSION SCOPE KAWABATA_RSS_GOV_DB.POLICIES.RSS_PROD_READONLY ADD $$
role_scopes:
  blocked_roles: [ACCOUNTADMIN, SYSADMIN, SECURITYADMIN]
  blocked_secondary_roles: [ACCOUNTADMIN, SYSADMIN, SECURITYADMIN]
  allow_role_switching: false
$$;

USE ROLE ACCOUNTADMIN;
SELECT CURRENT_ROLE();
USE ROLE SYSADMIN;
SELECT CURRENT_ROLE();
USE ROLE KAWABATA_DBT_DEV_ROLE;   -- ブロック対象外だが切替禁止
SELECT CURRENT_ROLE();
USE SECONDARY ROLES ALL;
SELECT CURRENT_SECONDARY_ROLES();
USE SECONDARY ROLES ACCOUNTADMIN;
SELECT CURRENT_SECONDARY_ROLES();

Current session is restricted. USE ROLE not allowed.

SELECT COUNT(*) FROM KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS;
INSERT INTO KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_NOTES (note) VALUES ('P4 DATA_READ');
SELECT KAWABATA_RSS_GOV_DB.POLICIES.RSS_ECHO('P4');
CALL KAWABATA_RSS_GOV_DB.POLICIES.RSS_PING();
CREATE TABLE KAWABATA_RSS_SANDBOX_DB.WORK.AGENT_T3 (id INT);
SHOW TABLES IN SCHEMA KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES;

CREATE OR REPLACE SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_DATA_READ
  AGENT_RESTRICTED_SESSION_SCOPE = 'SNOWFLAKE$DATA_READ';
CREATE OR REPLACE SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_DATA_READ_WITH_AI
  AGENT_RESTRICTED_SESSION_SCOPE = 'SNOWFLAKE$DATA_READ_WITH_AI';
CREATE OR REPLACE SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_DATA_READ_PROGRAM_USAGE
  AGENT_RESTRICTED_SESSION_SCOPE = 'SNOWFLAKE$DATA_READ_PROGRAM_USAGE';

ALTER USER KAWABATA_TOMOHIRO UNSET SESSION POLICY;
ALTER USER KAWABATA_TOMOHIRO SET SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_DATA_READ;
-- 以下、WITH_AI / PROGRAM_USAGE も同様に UNSET → SET

CREATE OR REPLACE SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_INLINE
  AGENT_RESTRICTED_SESSION_SCOPE = $$
privilege_scopes:
  allowed_privileges:
    - privileges: [data read]
      databases: [KAWABATA_RSS_PROD_DB]
$$;
DESC SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_INLINE;

-- IS_AGENT_ACTIVATED は Behavior Change Bundle 2026_06 の有無で VARCHAR / BOOLEAN が変わるため ::BOOLEAN で吸収
CREATE OR REPLACE MASKING POLICY KAWABATA_RSS_GOV_DB.POLICIES.MASK_EMAIL_FOR_AGENT
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED')::BOOLEAN = TRUE
      THEN REGEXP_REPLACE(val, '^[^@]+', '***')
    ELSE val
  END;

ALTER TABLE KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS
  MODIFY COLUMN email SET MASKING POLICY KAWABATA_RSS_GOV_DB.POLICIES.MASK_EMAIL_FOR_AGENT;

-- セッションポリシーは UC-A の RSS_PROD_READONLY に戻しておく
ALTER USER KAWABATA_TOMOHIRO UNSET SESSION POLICY;
ALTER USER KAWABATA_TOMOHIRO SET SESSION POLICY KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_PROD_READONLY;

SELECT
  SYS_CONTEXT('SNOWFLAKE$CURRENT', 'IS_AGENT_ACTIVATED') AS IS_AGENT_ACTIVATED,
  CURRENT_ROLE() AS CURRENT_ROLE,
  order_id, customer_name, email
FROM KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS
ORDER BY order_id
LIMIT 3;

INSERT INTO KAWABATA_RSS_PROD_DB.KAWABATA_RSS_SALES.ORDERS
  VALUES (903, 'テスト 三郎', 'test903@example.com', CURRENT_DATE(), 'Test Item', 1.00, 'Test', 'RSS_TEST');

-- ポリシー側から適用先を確認
SELECT POLICY_NAME, POLICY_KIND, REF_ENTITY_NAME, REF_ENTITY_DOMAIN, POLICY_STATUS
FROM TABLE(KAWABATA_RSS_GOV_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
  POLICY_NAME => 'KAWABATA_RSS_GOV_DB.POLICIES.SP_AGENT_PROD_READONLY'));

-- ユーザー側から逆引き
SELECT POLICY_NAME, POLICY_KIND, REF_ENTITY_NAME, REF_ENTITY_DOMAIN, POLICY_STATUS
FROM TABLE(KAWABATA_RSS_GOV_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_NAME => 'KAWABATA_TOMOHIRO', REF_ENTITY_DOMAIN => 'USER'));