Categories:

Context functions

POLICY_CONTEXT¶

Simulates the results of a query based upon the value of one or more context functions, which lets you determine how policies affect query results. Context functions return a value based on the current context of a query, for example, who is executing the query or the account from which the query is being executed. Policy bodies often use context functions to determine which value to return from the policy.

This function evaluates the following policies to determine the query results:

Syntax¶

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

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         |
+----------------+
Copy

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

        • APPLY AGGREGATION POLICY on ACCOUNT or APPLY on AGGREGATION POLICY policy_name

        • APPLY PROJECTION POLICY on ACCOUNT or APPLY on PROJECTION 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>') ... ;
      Copy

  • The result depends on the following:

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

    • The row access policy or aggregation 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 to determine which tables, views, and columns are protected by policies, and to better understand the body definitions of those policies. This administrator might have a custom role like POLICY_ADMIN, MASKING_ADMIN, or RAP_ADMIN.

    • 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;
Copy
Language: English