Categories:

Context Functions

POLICY_CONTEXT

Simulates the query result for table or view columns protected by a masking policy, a table or view protected by a row access policy, or both if the table or view is protected by both types of policies.

Syntax

EXECUTE USING
POLICY_CONTEXT( <arg_1> => '<string_literal>' [ , <arg_2> => '<string_literal>' , ... , <arg_n> => '<string_literal>' ] )
AS
SELECT <query>

Arguments

arg_1 => 'string_literal'

Specifies a context function and its value as a string.

Required. You must specify at least one function and its value.

Snowflake supports the following context functions and their values as arguments:

To determine the format to use as a string value, execute a query using the function. For example:

SELECT CURRENT_USER();

+----------------+
| CURRENT_USER() |
|----------------|
| JSMITH         |
+----------------+

The string value should be 'JSMITH'.

Note that if specifying CURRENT_AVAILABLE_ROLES and multiple role values, such as ROLE1 and ROLE2, enclose the list of roles in square brackets as follows:

['ROLE1', 'ROLE2']

arg_2 => 'string_literal' , ... , arg_n => 'string_literal'`

Specifies a comma-separated list of a context function and its value as a string.

Optional.

query

Specifies the SQL expression to query one or more tables or views.

Required.

Usage Notes

  • This function requires the following:

    • At least one argument that specifies a supported context function and its value.

    • If a table is protected by a policy, the specified user or role must be granted the following privileges:

      • OWNERSHIP on the table or view, and

      • The APPLY privilege for the policy, either at the account-level or on the policy itself:

        • APPLY MASKING POLICY on ACCOUNT or APPLY on MASKING POLICY policy_name

        • APPLY ROW ACCESS POLICY on ACCOUNT or APPLY on ROW ACCESS POLICY policy_name

  • Snowflake returns an error message if any of the following conditions are true:

    • Using one or more unsupported functions as an argument. Snowflake only supports the functions listed in the Arguments section.

    • Not specifying a function string value properly, including using a string for a value that does not exist (e.g. no account, user, or role).

    • The SELECT query expression does not query a table or view properly (e.g. not specifying a table or view at all).

    • Certain data sharing uses cases (see the next bullet).

  • Data sharing:

    • A data sharing consumer cannot use this function to simulate query results on tables or views that were made available by the data sharing provider.

      Additionally, if the consumer query expression includes a table or view made available through Secure Data Sharing and another table or view in the consumer account not associated with the data sharing provider account (i.e. their own table or view), Snowflake returns an error message.

    • A data sharing provider account can simulate how a data sharing consumer account views tables or views made available through a share.

      To do this, the data sharing provider specifies the consumer account name as the argument. For example:

      execute using policy_context(current_account => '<consumer_account_name>') ... ;
      
  • The result depends on the following:

    • The masking policy that is set on a column, if any.

    • The row access policy that is set on the table or view, if any.

    • The policy definition(s).

    • The query expression.

    • The privileges granted to roles.

    • The roles granted to users (including role hierarchy).

    • The arguments in this function.

    Important

    If the result from this function is not what you expected:

    • Consult with your internal policy administrator (e.g. users with the POLICY_ADMIN, MASKING_ADMIN, RAP_ADMIN custom roles) to understand the columns that are protected by a masking policy, the row access policy protecting the table or view, and the corresponding policy definition(s).

    • Double-check the:

      • Function string values.

      • SELECT query expression.

      • Privileges granted to roles (e.g. SELECT on table or view, USAGE on parent database and schema) and the corresponding privilege inheritance.

      • Role hierarchy, especially if specifying the CURRENT_AVAILABLE_ROLES function and its values as an argument for this function.

    Update the SQL statement using this function, as needed, and try again.

Examples

Simulate the effect of the PUBLIC system role querying the table empl_info:

execute using policy_context(current_role => 'PUBLIC') as select * from empl_info;