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.
EXECUTE USING POLICY_CONTEXT( <arg_1> => '<string_literal>' [ , <arg_2> => '<string_literal>' , ... , <arg_n> => '<string_literal>' ] ) AS SELECT <query>
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
Note that if specifying CURRENT_AVAILABLE_ROLES and multiple role values, such as
ROLE2, enclose the list of roles in square brackets as follows:
arg_2 => 'string_literal' , ... , arg_n => 'string_literal'`
Specifies a comma-separated list of a context function and its value as a string.
Specifies the SQL expression to query one or more tables or views.
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
APPLY ROW ACCESS POLICY on ACCOUNT or APPLY on ROW ACCESS POLICY
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).
queryexpression 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).
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
queryexpression 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 privileges granted to roles.
The roles granted to users (including role hierarchy).
The arguments in this function.
If the result from this function is not what you expected:
Consult with your internal policy administrator (e.g. users with the
RAP_ADMINcustom 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).
Function string values.
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.
Simulate the effect of the PUBLIC system role querying the table
execute using policy_context(current_role => 'PUBLIC') as select * from empl_info;