Categories:

Information Schema , Table Functions

POLICY_REFERENCES

Returns a list of all objects (i.e. tables, views) that have the specified masking policy set on a column or the row access policy set on a table or view.

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 masking or row access policy name.

  • If the fully-qualified policy name (e.g. <database_name>.<schema_name>.<policy_name>) is not used, Snowflake uses the session database and schema of the 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 object type (i.e. table, view) on which the policy is set.

  • If the fully-qualified policy name (e.g. <database_name>.<schema_name>.<policy_name>) is not used, Snowflake uses the session database and schema of the 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 name of the object (i.e. table_name, view_name, external_table_name) on which the policy is set.

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

  • If the fully-qualified policy name (e.g. <database_name>.<schema_name>.<policy_name>) is not used, Snowflake uses the session database and schema of the 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 based on the privileges granted to the role executing the query:

    • If the role has the global APPLY MASKING POLICY privilege, Snowflake returns all masking policy associations in the query result.

    • If the role has the global APPLY ROW ACCESS POLICY privilege, Snowflake returns all row access policy associations in the query result.

    • If the role has the APPLY privilege on a given policy (e.g. APPLY on MASKING POLICY), Snowflake returns associations of that policy only for objects that are owned by the role executing the query.

    • If the role has either of the global APPLY privileges or the OWNERSHIP privilege on the policy, but not OWNERSHIP on the table or view (e.g. SELECT on the table), Snowflake does not show policy associations in the query result.

    • If the role does not have any policy permissions but has the OWNERSHIP privilege on the table, Snowflake returns an error message and does not show policy associations.

  • 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 policy was created.

POLICY_DB

TEXT

The database in which the policy is set.

POLICY_SCHEMA

TEXT

The schema in which the policy is set.

POLICY_NAME

TEXT

The name of the 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 policy is set.

REF_ENTITY_DOMAIN

TEXT

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

REF_COLUMN_NAME

TEXT

The column name on which the policy is set.

REF_ARG_COLUMN_NAMES

ARRAY

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 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'));