Categories:

Information Schema , Table Functions

POLICY_REFERENCES

Returns a row for each object that has the specified policy assigned to the object or returns a row for each policy assigned to the specified object.

See also: POLICY_REFERENCES View (Account Usage View)

Syntax

POLICY_REFERENCES(
      POLICY_NAME => '<string>' )

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

Arguments

POLICY_NAME => 'string'

Specifies the policy name. Currently, Snowflake only supports masking policies and row access policies.

  • 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.

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

  • If the object 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.

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 Snowflake 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 if 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

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 masking policy is set.

TAG_DATABASE

TEXT

The name of the database containing the tag that has a policy assigned to the tag or NULL if a policy is not assigned to the tag.

TAG_SCHEMA

TEXT

The name of the schema containing the tag that has a policy assigned to the tag or NULL if a policy is not assigned to the tag.

TAG_NAME

TEXT

The name of the tag that has a policy assigned to it or NULL if a policy is not assigned to the tag.

POLICY_STATUS

TEXT

Specifies the status of the policy, which can be one of four possible values: ACTIVE, MULTIPLE_MASKING_POLICY_ASSIGNED_TO_THE_COLUMN, COLUMN_IS_MISSING_FOR_SECONDARY_ARG, or COLUMN_DATATYPE_MISMATCH_FOR_SECONDARY_ARG.

Note the following for the POLICY_STATUS column:

ACTIVE

Specifies that the column (i.e. REF_COLUMN_NAME) is only associated with a single policy by a tag.

MULTIPLE_MASKING_POLICY_ASSIGNED_TO_THE_COLUMN

Specifies that multiple masking policies are assigned to the same column.

COLUMN_IS_MISSING_FOR_SECONDARY_ARG

Specifies that the policy (i.e. POLICY_NAME) is a conditional masking policy and the table (i.e. REF_ENTITY_NAME) does not have a column with the same name.

COLUMN_DATATYPE_MISMATCH_FOR_SECONDARY_ARG

Specifies that the policy is a conditional masking policy and the table has a column with the same name but a different data type than the data type in the masking policy signature.

Examples

Return a row for each object (i.e. table or view) that has the masking policy named ssn_mask set on column:

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

Return a row for each policy assigned to the table named 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'));