[新機能]Data Movement Policies が一般提供となったのでデータのアンロード・ダウンロード・フェッチを制御してみた

USE SCHEMA DMP_TEST_DB.GOVERNANCE;

-- 内部ステージへのアンロードを ACCOUNTADMIN 以外ブロック
CREATE OR REPLACE DATA MOVEMENT RULE R_COPY_INTERNAL_BLOCK
  TYPE = 'COPY_INTO_INTERNAL_STAGE'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 0 END
  );

-- プログラムフェッチは ACCOUNTADMIN 無制限 / それ以外 1000行まで
CREATE OR REPLACE DATA MOVEMENT RULE R_PROG_FETCH_LIMIT
  TYPE = 'PROGRAMMATIC_FETCH'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 1000 END
  );

-- Snowsight のダウンロードボタンを ACCOUNTADMIN 以外ブロック
CREATE OR REPLACE DATA MOVEMENT RULE R_UI_DOWNLOAD_BLOCK
  TYPE = 'UI_DOWNLOAD'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 0 END
  );

-- Snowsight ワークシートの結果表示を ACCOUNTADMIN 以外ブロック
CREATE OR REPLACE DATA MOVEMENT RULE R_SNOWSIGHT_BLOCK
  TYPE = 'SNOWSIGHT_UI'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 0 END
  );

-- エージェント / MCP クライアント経由アクセスを全ブロック
CREATE OR REPLACE DATA MOVEMENT RULE R_AGENT_BLOCK
  TYPE = 'AGENT_ACCESS'
  MAX_ROWS AS () RETURNS INTEGER
  -> (0);

-- ALERT 用: プログラムフェッチ 500行超で違反記録(許可はする)
CREATE OR REPLACE DATA MOVEMENT RULE R_PROG_FETCH_ALERT
  TYPE = 'PROGRAMMATIC_FETCH'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 500 END
  );

CREATE OR REPLACE DATA MOVEMENT POLICY DMP_PII_POLICY
  ENFORCE_RULES = (R_COPY_INTERNAL_BLOCK, R_PROG_FETCH_LIMIT, R_UI_DOWNLOAD_BLOCK, R_SNOWSIGHT_BLOCK, R_AGENT_BLOCK)
  ALERT_RULES = (R_PROG_FETCH_ALERT)
  COMMENT = 'PII guard: block unload/download/agent, fetch <= 1000, alert > 500';

DESCRIBE DATA MOVEMENT POLICY DMP_PII_POLICY;

ALTER TAG DMP_TEST_DB.GOVERNANCE.PII_TAG SET PROPAGATE = ON_DEPENDENCY_AND_DATA_MOVEMENT;
ALTER TAG DMP_TEST_DB.GOVERNANCE.PII_TAG SET DATA MOVEMENT POLICY DMP_TEST_DB.GOVERNANCE.DMP_PII_POLICY;

-- 給与カラムにタグを付与
ALTER TABLE DMP_TEST_DB.HR.EMPLOYEE_SALARY MODIFY COLUMN SALARY
  SET TAG DMP_TEST_DB.GOVERNANCE.PII_TAG = 'salary';

SELECT POLICY_NAME, REF_ENTITY_NAME, REF_ENTITY_DOMAIN, POLICY_STATUS
FROM TABLE(DMP_TEST_DB.INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_NAME => 'DMP_TEST_DB.GOVERNANCE.PII_TAG', REF_ENTITY_DOMAIN => 'TAG'));

-- DMP_ANALYST で実行
COPY INTO @~/dmp_test/ FROM DMP_TEST_DB.HR.EMPLOYEE_SALARY;

-- DMP_ANALYST で実行
SELECT * FROM DMP_TEST_DB.HR.EMPLOYEE_SALARY LIMIT 1001;  -- ブロック
SELECT * FROM DMP_TEST_DB.HR.EMPLOYEE_SALARY LIMIT 1000;  -- 成功(上限ちょうど)

CREATE OR REPLACE DATA MOVEMENT RULE R_BASELINE_PROG_FETCH_BLOCK
  TYPE = 'PROGRAMMATIC_FETCH'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 0 END
  );

CREATE OR REPLACE DATA MOVEMENT POLICY DMP_ACCOUNT_BASELINE
  ENFORCE_RULES = (R_BASELINE_PROG_FETCH_BLOCK);

ALTER ACCOUNT SET DATA MOVEMENT POLICY DMP_TEST_DB.GOVERNANCE.DMP_ACCOUNT_BASELINE;

-- DMP_ANALYST で実行
SELECT * FROM DMP_TEST_DB.HR.OFFICE_LOCATION LIMIT 5;

-- DMP_ANALYST で実行
SELECT COUNT(*) FROM DMP_TEST_DB.HR.OFFICE_LOCATION;

-- Snowsight ワークシート表示: ACCOUNTADMIN 以外は 100行まで
CREATE OR REPLACE DATA MOVEMENT RULE R_BASELINE_SNOWSIGHT_LIMIT
  TYPE = 'SNOWSIGHT_UI'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 100 END
  );

-- Snowsight ダウンロードボタン: ACCOUNTADMIN 以外ブロック
CREATE OR REPLACE DATA MOVEMENT RULE R_BASELINE_UI_DOWNLOAD_BLOCK
  TYPE = 'UI_DOWNLOAD'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 0 END
  );

-- ALERT: 内部ステージへのアンロードが 50行を超えたら違反記録
CREATE OR REPLACE DATA MOVEMENT RULE R_BASELINE_COPY_ALERT
  TYPE = 'COPY_INTO_INTERNAL_STAGE'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE WHEN SYS_CONTEXT('SNOWFLAKE$SESSION', 'ROLE') = 'ACCOUNTADMIN' THEN NULL ELSE 50 END
  );

ALTER DATA MOVEMENT POLICY DMP_ACCOUNT_BASELINE
  ADD ENFORCE_RULES = (R_BASELINE_SNOWSIGHT_LIMIT, R_BASELINE_UI_DOWNLOAD_BLOCK);
ALTER DATA MOVEMENT POLICY DMP_ACCOUNT_BASELINE
  ADD ALERT_RULES = (R_BASELINE_COPY_ALERT);

-- DMP_ANALYST で実行
COPY INTO @~/dmp_alert/ FROM DMP_TEST_DB.HR.OFFICE_LOCATION;

SELECT
  MOVEMENT_TYPE,
  USER_NAME,
  ALERTED_POLICIES,
  TIMESTAMP
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_MOVEMENT_VIOLATIONS
WHERE ALERTED_POLICIES IS NOT NULL
ORDER BY TIMESTAMP DESC NULLS FIRST
LIMIT 5;

-- DMP_ANALYST で実行
SELECT * FROM DMP_TEST_DB.HR.EMPLOYEE_SALARY LIMIT 600;  -- 成功
SELECT * FROM DMP_TEST_DB.HR.OFFICE_LOCATION LIMIT 5;    -- ブロック

-- DMP_ANALYST で実行(50行 <= 制限100行)
SELECT * FROM DMP_TEST_DB.HR.OFFICE_LOCATION LIMIT 50;

-- DMP_ANALYST で実行(300行 > 制限100行)
SELECT * FROM DMP_TEST_DB.HR.OFFICE_LOCATION;

USE ROLE ACCOUNTADMIN;
CREATE SCHEMA IF NOT EXISTS KAWABATA_MART_DB.GOVERNANCE;
USE SCHEMA KAWABATA_MART_DB.GOVERNANCE;

-- 対象エージェントのみブロック(他エージェントは NULL = 無制限)
CREATE OR REPLACE DATA MOVEMENT RULE R_AGENT_SCOPED_BLOCK
  TYPE = 'AGENT_ACCESS'
  MAX_ROWS AS () RETURNS INTEGER
  -> (
    CASE
      WHEN SYS_CONTEXT('SNOWFLAKE$DATA_MOVEMENT', 'AGENT_NAME') LIKE '%DMP_AGENT_TEST%' THEN 0
      ELSE NULL
    END
  )
  COMMENT = 'Block data access for the test agent only';

CREATE OR REPLACE DATA MOVEMENT POLICY DMP_AGENT_TEST_POLICY
  ENFORCE_RULES = (R_AGENT_SCOPED_BLOCK)
  COMMENT = 'AGENT_ACCESS verification (temporary)';

ALTER ACCOUNT SET DATA MOVEMENT POLICY KAWABATA_MART_DB.GOVERNANCE.DMP_AGENT_TEST_POLICY;

-- ACCOUNTADMIN で実行
COPY INTO @~/dmp_part/ FROM (
  SELECT LOCATION_NAME, LOCATION_ID FROM DMP_TEST_DB.HR.OFFICE_LOCATION
) PARTITION BY (LOCATION_NAME);