POLICY_REFERENCES

返回安全策略（Masking Policy 或 Row Access Policy）与表/视图之间的关联。你可以按策略名称查询所有使用该策略的表，或按表名查询应用于该表的所有策略。

另请参阅：

• MASKING POLICY
• ROW ACCESS POLICY

语法

-- Find all tables/views using a specific policy
POLICY_REFERENCES(POLICY_NAME => '<policy_name>')

-- Find all policies applied to a specific table/view
POLICY_REFERENCES(
    REF_ENTITY_NAME => '[<database>.]<table_name>',
    REF_ENTITY_DOMAIN => 'TABLE' | 'VIEW'
)

输出列

示例

查找使用某个 Row Access Policy 的表

-- Create a row access policy
CREATE ROW ACCESS POLICY rap_employees AS (department STRING) RETURNS BOOLEAN ->
  CASE
    WHEN current_role() = 'admin' THEN true
    WHEN department = 'Engineering' THEN true
    ELSE false
  END;

-- Apply the policy to a table
CREATE TABLE employees(id INT, name STRING, department STRING);
ALTER TABLE employees ADD ROW ACCESS POLICY rap_employees ON (department);

-- Find all tables using this policy
SELECT * FROM policy_references(POLICY_NAME => 'rap_employees');

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│   policy_name   │    policy_kind    │ ref_database_name │ ref_entity_name │ ref_entity_domain │ ref_column_name │ ref_arg_column_names │ policy_status │
├─────────────────┼───────────────────┼───────────────────┼─────────────────┼───────────────────┼─────────────────┼──────────────────────┼───────────────┤
│ rap_employees   │ ROW ACCESS POLICY │ default           │ employees       │ TABLE             │ NULL            │ department           │ ACTIVE        │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

查找应用于某个表的所有策略

-- Create a masking policy
CREATE MASKING POLICY mask_salary AS (val INT) RETURNS INT ->
  CASE WHEN current_role() = 'admin' THEN val ELSE 0 END;

-- Apply both policies to the table
ALTER TABLE employees ADD COLUMN salary INT;
ALTER TABLE employees MODIFY COLUMN salary SET MASKING POLICY mask_salary;

-- Find all policies on this table
SELECT * FROM policy_references(
    REF_ENTITY_NAME => 'default.employees',
    REF_ENTITY_DOMAIN => 'TABLE'
);

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│   policy_name   │    policy_kind    │ ref_database_name │ ref_entity_name │ ref_entity_domain │ ref_column_name │ ref_arg_column_names │ policy_status │
├─────────────────┼───────────────────┼───────────────────┼─────────────────┼───────────────────┼─────────────────┼──────────────────────┼───────────────┤
│ mask_salary     │ MASKING POLICY    │ default           │ employees       │ TABLE             │ salary          │ NULL                 │ ACTIVE        │
│ rap_employees   │ ROW ACCESS POLICY │ default           │ employees       │ TABLE             │ NULL            │ department           │ ACTIVE        │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

查找使用带多个参数的 Masking Policy 的表

-- Create a masking policy with conditional arguments
CREATE MASKING POLICY mask_ssn AS (val STRING, user_role STRING) RETURNS STRING ->
  CASE
    WHEN user_role = current_role() THEN val
    ELSE '***-**-****'
  END;

-- Apply to multiple tables
CREATE TABLE employees1(id INT, ssn STRING, role STRING);
CREATE TABLE employees2(id INT, ssn STRING, role STRING);

ALTER TABLE employees1 MODIFY COLUMN ssn SET MASKING POLICY mask_ssn USING (ssn, role);
ALTER TABLE employees2 MODIFY COLUMN ssn SET MASKING POLICY mask_ssn USING (ssn, role);

-- Find all tables using this policy
SELECT * FROM policy_references(POLICY_NAME => 'mask_ssn') ORDER BY ref_entity_name;

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ policy_name │   policy_kind  │ ref_database_name │ ref_entity_name │ ref_entity_domain │ ref_column_name │ ref_arg_column_names │ policy_status │
├─────────────┼────────────────┼───────────────────┼─────────────────┼───────────────────┼─────────────────┼──────────────────────┼───────────────┤
│ mask_ssn    │ MASKING POLICY │ default           │ employees1      │ TABLE             │ ssn             │ role                 │ ACTIVE        │
│ mask_ssn    │ MASKING POLICY │ default           │ employees2      │ TABLE             │ ssn             │ role                 │ ACTIVE        │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘