Categories:

Information Schema , Table Functions

POLICY_REFERENCES

Returns a list of all objects (i.e. tables, views) that have the specified Column-level Security masking policy set on a column.

Returns a list of policy associations that have the specified object name and object type.

See also: POLICY_REFERENCES View (Account Usage View)

Syntax

POLICY_REFERENCES(
      POLICY_NAME => '<string>' )

POLICY_REFERENCES(
      REF_ENTITY_DOMAIN => '<string>'
      , REF_ENTITY_NAME => '<string>' )

Arguments

POLICY_NAME => 'string'

Specifies the Column-level Security masking policy name.

  • If the fully-qualified masking policy name (e.g. <database_name>.<schema_name>.<policy_name>) is not used, Snowflake uses the session database and schema of the masking policy. For more information, see Object Name Resolution.

  • The entire policy name must be enclosed in single quotes.

  • If the policy name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/ characters. The double quotes must be enclosed within the single quotes (i.e. '"<policy_name>"').

REF_ENTITY_DOMAIN => 'string'

The name of the object (i.e. table_name, view_name, external_table_name) on which the masking policy is set.

  • If the fully-qualified masking policy name (e.g. <database_name>.<schema_name>.<policy_name>) is not used, Snowflake uses the session database and schema of the masking policy. For more information, see Object Name Resolution.

  • The entire policy name must be enclosed in single quotes.

  • If the policy name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/ characters. The double quotes must be enclosed within the single quotes (i.e. '"<policy_name>"').

REF_ENTITY_NAME => 'string'

The object type (i.e. table, view) on which the masking policy is set.

If the object is an external table, use 'table' as the argument.

  • If the fully-qualified masking policy name (e.g. <database_name>.<schema_name>.<policy_name>) is not used, Snowflake uses the session database and schema of the masking policy. For more information, see Object Name Resolution.

  • The entire policy name must be enclosed in single quotes.

  • If the policy name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/ characters. The double quotes must be enclosed within the single quotes (i.e. '"<policy_name>"').

Usage Notes

  • Results are returned for the ACCOUNTADMIN role only.

  • When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function must use the fully-qualified object name. For more details, see Information Schema.

  • Choose one syntax variation to execute a query. Mixing arguments results in errors and query failure.

    The arguments ref_entity_name and ref_entity_domain must be included together otherwise the query fails.

  • Snowflake returns errors if the specified object name does not exist or if the query operator is not authorized to view any policy on the object. Snowflake can return a result set of a policy associations if the operator is allowed to view a subset of the policy associations. Unsupported object types listed as the ref_entity_domain (e.g. 'stream') also return errors.

  • Snowflake does not return a result set of the query operator does not have either the APPLY or OWNERSHIP privileges on the policy.

Output

The function returns the following columns:

Column

Data Type

Description

CREATED_ON

TIMESTAMP_LTZ

The date and time when the masking policy was created.

POLICY_DB

TEXT

The database in which the masking policy is set.

POLICY_SCHEMA

TEXT

The schema in which the masking policy is set.

POLICY_NAME

TEXT

The name of the masking policy.

POLICY_KIND

TEXT

The type of policy in Snowflake.

REF_DATABASE_NAME

TEXT

The name of the database containing an object that the queried object references.

REF_SCHEMA_NAME

TEXT

The name of the schema containing an object that the queried object references.

REF_ENTITY_NAME

TEXT

The name of the object (i.e. table_name, view_name, external_table_name) on which the masking policy is set.

REF_ENTITY_DOMAIN

TEXT

The object type (i.e. table, view) on which the masking policy is set.

REF_COLUMN_NAME

TEXT

The column name on which the masking policy is set.

REF_ARG_COLUMN_NAMES

TEXT

Returns NULL for rows in the query result in which a Column-level Security masking policy is set.

Examples

Retrieve the list of objects that has the masking policy my_db.my_schema.ssn_mask set on a column:

use database my_db;
use schema information_schema;
select *
  from table(information_schema.policy_references(policy_name => 'my_db.my_schema.ssn_mask'));

Retrieve a list of policy associations using the ref_entity_name and ref_entity_domain arguments for the table my_table:

use database my_db;
use schema information_schema;
select *
  from table(information_schema.policy_references(ref_entity_name => 'my_db.my_schema.my_table', ref_entity_domain => 'table'));