[新機能]Feature Policy Rules が一般提供となったので TEMPORARY テーブルとサーバーレスタスクの作成だけをブロックしてみた

CREATE FEATURE POLICY <name>
  [ BLOCKED_OBJECT_TYPES_FOR_CREATION = ( <type> [ , ... ] ) ]
  AS $$
    blocked_creation_rules:
      - object_type: <OBJECT_TYPE>
        block_when: "<SQL式>"
  $$;

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE DATABASE FP_POLICY_DB;   -- ポリシー格納用
CREATE SCHEMA FP_POLICY_DB.POLICIES;
CREATE OR REPLACE DATABASE FP_TEST_DB;     -- 適用対象
CREATE OR REPLACE DATABASE FP_TEST_DB2;    -- 優先順位検証用

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TASKS_ALL
  BLOCKED_OBJECT_TYPES_FOR_CREATION = (TASKS)
  COMMENT = 'Block all task creation (legacy style)';

ALTER DATABASE FP_TEST_DB
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TASKS_ALL;

CREATE TASK FP_TEST_DB.PUBLIC.T_WH
  WAREHOUSE = COMPUTE_WH SCHEDULE = '60 MINUTE' AS SELECT 1;

ALTER DATABASE FP_TEST_DB UNSET FEATURE POLICY;

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TEMP_TABLES
  COMMENT = 'Block only temporary tables'
  AS $$
    blocked_creation_rules:
      - object_type: TABLE
        block_when: "SYS_CONTEXT('SNOWFLAKE$REQUEST', 'GET_OBJECT_PROPERTY', 'IS_TEMPORARY') = 'TRUE'"
  $$;

ALTER DATABASE FP_TEST_DB
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TEMP_TABLES;

CREATE TABLE FP_TEST_DB.PUBLIC.T2 (ID INT);            -- 成功
CREATE TEMPORARY TABLE FP_TEST_DB.PUBLIC.T3 (ID INT);  -- ブロック (003001)
CREATE TRANSIENT TABLE FP_TEST_DB.PUBLIC.T4 (ID INT);  -- 成功

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_SERVERLESS_TASKS
  COMMENT = 'Block only serverless tasks'
  AS $$
    blocked_creation_rules:
      - object_type: TASK
        block_when: "SYS_CONTEXT('SNOWFLAKE$REQUEST', 'GET_OBJECT_PROPERTY', 'WAREHOUSE') IS NULL"
  $$;

ALTER DATABASE FP_TEST_DB
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_SERVERLESS_TASKS;

-- FORCE で既存の DB レベルポリシーを直接置き換える
ALTER DATABASE FP_TEST_DB
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_SERVERLESS_TASKS
  FORCE;

-- ウェアハウス指定タスク → 成功
CREATE TASK FP_TEST_DB.PUBLIC.T_WH
  WAREHOUSE = COMPUTE_WH SCHEDULE = '60 MINUTE' AS SELECT 1;

-- サーバーレスタスク → ブロック (003001)
CREATE TASK FP_TEST_DB.PUBLIC.T_SERVERLESS
  SCHEDULE = '60 MINUTE' AS SELECT 1;

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TEMP_AND_WH
  COMMENT = 'Named condition + legacy param combined'
  BLOCKED_OBJECT_TYPES_FOR_CREATION = (WAREHOUSES)
  AS $$
    conditions:
      - name: is_temp
        expression: "SYS_CONTEXT('SNOWFLAKE$REQUEST', 'GET_OBJECT_PROPERTY', 'IS_TEMPORARY') = 'TRUE'"
    blocked_creation_rules:
      - object_type: TABLE
        block_when_any:
          - is_temp
      - object_type: STAGE
        block_when_any:
          - is_temp
  $$;

ALTER DATABASE FP_TEST_DB UNSET FEATURE POLICY;
ALTER DATABASE FP_TEST_DB
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TEMP_AND_WH;

CREATE TEMPORARY TABLE FP_TEST_DB.PUBLIC.T6 (ID INT);  -- ブロック
CREATE TEMPORARY STAGE FP_TEST_DB.PUBLIC.S_TEMP;       -- ブロック (Create STAGE denied)
CREATE STAGE FP_TEST_DB.PUBLIC.S1;                     -- 成功

CREATE WAREHOUSE FP_TEST_WH WITH WAREHOUSE_SIZE = 'XSMALL' INITIALLY_SUSPENDED = TRUE;

DESC FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_SERVERLESS_TASKS;

SHOW FEATURE POLICIES IN DATABASE FP_POLICY_DB;  -- 作成した4ポリシーが一覧表示
SHOW FEATURE POLICIES ON DATABASE FP_TEST_DB;    -- 適用中の1ポリシーのみ (set_on = DATABASE)

SELECT POLICY_NAME, POLICY_KIND, REF_ENTITY_NAME, REF_ENTITY_DOMAIN, POLICY_STATUS
FROM TABLE(FP_POLICY_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
  POLICY_NAME => 'FP_POLICY_DB.POLICIES.BLOCK_TEMP_AND_WH'));

ALTER DATABASE FP_TEST_DB UNSET FEATURE POLICY;

-- アカウント内のすべての通常の DB に TEMPORARY テーブル禁止を適用
ALTER ACCOUNT
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_TEMP_TABLES FOR ALL DATABASES;

CREATE TEMPORARY TABLE FP_TEST_DB.PUBLIC.T8 (ID INT);

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_NOTHING
  BLOCKED_OBJECT_TYPES_FOR_CREATION = ()
  COMMENT = 'Empty policy to lift account-level restrictions';

ALTER DATABASE FP_TEST_DB
  SET FEATURE POLICY FP_POLICY_DB.POLICIES.BLOCK_NOTHING;

CREATE TEMPORARY TABLE FP_TEST_DB.PUBLIC.T8 (ID INT);   -- 成功
CREATE TEMPORARY TABLE FP_TEST_DB2.PUBLIC.T9 (ID INT);  -- 引き続きブロック

ALTER ACCOUNT UNSET FEATURE POLICY FOR ALL DATABASES;

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.NULL_TRAP2
  AS $$
    blocked_creation_rules:
      - object_type: TABLE
        block_when: "SYS_CONTEXT('SNOWFLAKE$REQUEST', 'GET_OBJECT_PROPERTY', 'WAREHOUSE') = 'COMPUTE_WH'"
  $$;

CREATE FEATURE POLICY FP_POLICY_DB.POLICIES.NULL_TRAP3
  AS $$
    blocked_creation_rules:
      - object_type: TASK
        block_when: "SYS_CONTEXT('SNOWFLAKE$REQUEST', 'GET_OBJECT_PROPERTY', 'WAREHOUSE') = 'COMPUTE_WH'"
  $$;

-- (FP_TEST_DB に適用後)
CREATE TASK FP_TEST_DB.PUBLIC.T_B SCHEDULE = '60 MINUTE' AS SELECT 1;