Understanding row access policies¶

How does a row access policy work?¶

A row access policy contains an expression that can specify Snowflake database objects (e.g. table or view), and use Conditional expression functions and Context functions to determine which rows should be visible in a given context.

Snowflake evaluates the policy expression by using the role of the policy owner, not the role of the operator who executed the query. This approach allows Snowflake not to return a row in a query result because the query operator does not require access to the mapping tables in the row access policy.

Row access policies at query runtime¶

At query runtime, Snowflake goes through the following process:

1. Snowflake determines whether a row access policy is set on a database object. If a policy is added to the database object, all rows are protected by the policy.

2. Snowflake creates a dynamic secure view (i.e. a secure inline view) of the database object.

3. The values of the columns specified in the ALTER TABLE or ALTER VIEW command (i.e when adding a row access policy to a table or view) are bound to the corresponding parameters in the policy, and the policy expression is evaluated.

4. Snowflake generates the query output for the user, and the query output only contains rows based on the policy definition evaluating to TRUE.

For more details on the specific execution plan, see Query profile (in this topic).

Snowflake supports nested row access policies, such as a row access policy on a table and a row access policy on a view for the same table. At query runtime, Snowflake evaluates all row access policies that are relevant to a given query in the following sequence:

• The row access policy that is applicable to the table is always executed first.

• The policy for the view is executed after evaluating the policy for the table.

• If nested views exist (e.g. Table 1 -> View 1 -> View 2 -> … View n), the policies are applied in sequential order from left to right.

This pattern continues for however many row access policies exist with respect to the data in the query. The following diagram illustrates the relationship between a query operator, tables, views, and policies.

For more information on row access policy privileges, commands, and a step-by-step implementation, see:

• Row access policy privileges

• Row access policy DDL

• Use row access policies

Representative use case: Simple row filtering¶

A simple application of a row access policy is to specify an attribute in the policy and a role that is allowed to see that attribute in the query result. The advantage of simple policies like this is that there is a negligible performance cost for Snowflake to evaluate these policies to return query results compared to using row access policies with mapping tables.

As a representative example, it may be necessary for information technology administrators (e.g. it_admin custom role) to query an employee identification number (i.e. empl_id) before granting the employee additional privileges to use internal systems. Therefore, the row access policy should return rows in the query result if the CURRENT_ROLE matches the it_admin custom role and not return rows for all other roles. For example:

CREATE OR REPLACE ROW ACCESS POLICY rap_it
AS (empl_id varchar) RETURNS BOOLEAN ->
  'it_admin' = current_role()
;

This policy is the most concise version of a row access policy because there are no other conditions to evaluate, only the value of the CURRENT_ROLE.

If role hierarchy needs to be considered, this policy could similarly use IS_ROLE_IN_SESSION to be more inclusive of other roles to see the employee ID number in the query result.

Alternatively, to consider additional conditions, using the CASE function allows including WHEN/THEN/ELSE clauses to support more detailed conditional logic.

Representative use case: Use a mapping table to filter the query result¶

A row access policy condition can reference a mapping table to filter the query result set, however using mapping tables may result in decreased performance compared to the more simple example.

For example, use a mapping table to determine the revenue values a sales manager can see in a specified sales region. The mapping table should specify the sales manager and the sales region (e.g. WW: Worldwide, NA: North America, EU: European Union).

Sales Manager	Region
Alice	WW
Bob	NA
Simon	EU

Next, define a policy with one or more conditions to query the mapping table with a subquery. At query runtime, Snowflake determines whether the user executing the query matches the sales region specified in the mapping table.

If a match occurs, the user can see those rows in the query result. Based on the mapping table, the expected query results are as follows:

Company	Region	Revenue	Who can view
Acme	EU	2.5B	Alice, Simon
Acme	NA	1.5B	Alice, Bob

For details on implementing a row access policy with a mapping table, see:

• External Tables (in this topic)

• Use row access policies

Policy performance guidelines¶

Row Access Policies are designed to perform well in a wide variety of real-world scenarios. Use the following tips to secure data and enhance performance:

Limit the policy arguments:

Snowflake needs to scan columns that the policy is bound to, even if they are not referenced in queries. Therefore, policies with fewer arguments will generally perform better than policies with many arguments.

Simplify the SQL expression:

Policies with simple SQL expressions, such as CASE statements, generally perform better than policies that access mapping (i.e. lookup) tables. Minimizing the number of table lookups improves performance.

When specifying a mapping table, replace the mapping table reference with a memoizable function. For details, refer to:

• Memoizable function (in the scalar SQL UDF overview).

• Using a memoizable function in a policy (in the Using Row Access Policies topic).

Test with realistic workloads:

Without a row access policy, the query SELECT COUNT(*) FROM t1 executes in milliseconds since Snowflake already knows the number of rows in the table. However, adding a row access policy means Snowflake must scan the table to count the number of rows that are accessible in the current context. Although the performance difference is large, this query is not representative of most real-world workloads.

For more information on this example, see the Considerations section (in this topic).

Cluster by attributes:

For very large tables, clustering by attributes used for policy filtering can improve performance.

For more information, see Clustering Keys & Clustered Tables.

Search optimization service:

The search optimization service can improve the query performance on a table that uses a masking or row access policy.

For details, see Support for Tables With Masking Policies and Row Access Policies in the Search Optimization Service.

Benefits¶

The primary benefit of a row access policy is that the policy enables an organization to properly balance data security, governance, and analytics through an extensible policy. The extensible nature of the row access policy allows one or more conditions to be added or removed at any time to ensure the policy is consistent with updates to data, mapping tables, and the RBAC hierarchy.

Additional benefits include:

Ease of Use:

Write a policy once and apply it to tables across databases and schemas.

Change Management:

Easily change row access policy definitions without having to reapply the policy to tables.

If using a mapping table, update the entitlement information in the mapping table referenced by the policy without having to change the policy.

Data Administration and SoD:

A central data administrator decides which objects to protect, not the object owner. Row access policies are easy to manage and support through centralized, decentralized, and hybrid administration models to support segregation of duties (i.e. SoD).

Data Authorization and Governance:

The row access policy supports contextual data access by role or custom entitlements.

Limitations¶

• Using the CHANGES clause on a view protected by a row access policy is not supported.

• Snowflake does not support using external tables as a mapping table in a row access policy. For more information, see External Tables (in this topic).

• Snowflake does not support attaching a row access policy to the stream object itself, but does apply the row access policy to the table when the stream accesses a table protected by a row access policy. For more information, see Streams (in this topic).

• Future grants of privileges on row access policies are not supported.

  As a workaround, grant the APPLY ROW ACCESS POLICY privilege to a custom role to allow that role to apply row access policies on a table or view.

Considerations¶

• Attaching row access policies to tables that are protected by other row access policies or masking policies may cause errors. For more information, see ALTER TABLE, ALTER EXTERNAL TABLE, and ALTER VIEW.

• Including one or more subqueries in the policy body may cause errors. When possible, limit the number of subqueries, limit the number of JOIN operations, and simplify WHERE clause conditions.

• Snowflake maintains statistics about table and view columns that make it possible to answer many simple queries in milliseconds. Examples of such queries include using the COUNT function, select count(*) from my_table, and the MAX function, select max(c) from my_table.

  Generally, these statistics and optimizations are not applicable with a row access policy since Snowflake must identify the subset of rows the query is permitted to access. Executing queries of this type on tables and views with a row access policy may take longer than expected to obtain the query results since these statistics and optimizations are not used, and the returned statistics are only based on what is permissible to access, not the “true” statistical values (i.e. statistics on the table or view without a row access policy).

• Use caution when creating the setup script for a Snowflake Native App when the row access policy exists in a versioned schema. For details, see version schema considerations.

• If you specify the CURRENT_DATABASE or CURRENT_SCHEMA function in the body of a masking or row access policy, the function returns the database or schema that contains the protected table, not the database or schema in use for the session.

Obtain database objects with a row access policy¶

The Information Schema POLICY_REFERENCES table function can return information about the row access policy assigned to a given object.

• All objects for a given policy:

  Specify the name of the row access policy (e.g. mydb.policies.rap1):

  SELECT *
  FROM TABLE(
    mydb.INFORMATION_SCHEMA.POLICY_REFERENCES(
      POLICY_NAME=>'mydb.policies.rap1'
    )
  );
  

  Copy

• The policy assigned to a specific object:

  Specify the name of the object (e.g. mydb.tables.t1) and the object domain (e.g. table):

  SELECT *
  FROM TABLE(
    mydb.INFORMATION_SCHEMA.POLICY_REFERENCES(
      REF_ENTITY_NAME => 'mydb.tables.t1',
      REF_ENTITY_DOMAIN => 'table'
    )
  );
  

  Copy

Note that this table function is complementary to the Account Usage POLICY_REFERENCES view.

Active role hierarchy & mapping tables¶

The policy conditions can evaluate the user’s active primary and secondary roles in a session directly, look up active roles in a mapping table, or do both depending on how the policy administrator wants to write the policy. If the policy contains a mapping table lookup, create a centralized mapping table and store the mapping table in the same database as the protected table. This is particularly important if the policy calls the IS_DATABASE_ROLE_IN_SESSION function. For details, see the function usage notes.

For these use cases, Snowflake recommends writing the policy conditions to call the IS_ROLE_IN_SESSION or the IS_DATABASE_ROLE_IN_SESSION function depending on whether you want to specify an account role or database role. For examples, see:

• Examples section in the IS_ROLE_IN_SESSION function.

• IS_DATABASE_ROLE_IN_SESSION

• Share data protected by a policy

Apply a row access policy to a table or view¶

There are two options to add a row access policy to a table or view:

1. With a new table or view, apply the policy to a table with a CREATE TABLE statement or a view with a CREATE VIEW statement.

2. With an existing table or view, apply the policy to a table with an ALTER TABLE statement or a view with an ALTER VIEW statement.

For a new table or view, execute the following statements:

-- table
CREATE TABLE sales (
  customer   varchar,
  product    varchar,
  spend      decimal(20, 2),
  sale_date  date,
  region     varchar
)
WITH ROW ACCESS POLICY sales_policy ON (region);

-- view
CREATE VIEW sales_v WITH ROW ACCESS POLICY sales_policy ON (region)
AS SELECT * FROM sales;

Copy

For an existing table or view, execute the following statements:

-- table

ALTER TABLE t1 ADD ROW ACCESS POLICY rap_t1 ON (empl_id);

-- view

ALTER VIEW v1 ADD ROW ACCESS POLICY rap_v1 ON (empl_id);

Copy

Masking policies¶

When a database object has both a row access policy and one or more masking policies, Snowflake evaluates the row access policy first.

A given table or view column can be specified in either a row access policy signature or a masking policy signature. In other words, the same column cannot be specified in both a row access policy signature and a masking policy signature at the same time.

For more information, see CREATE MASKING POLICY and CREATE ROW ACCESS POLICY.

Simulate how a policy will work¶

Call the POLICY_CONTEXT function to simulate a query on a column that is protected by a masking policy, a table or view protected by a row access policy, or both types of policies.

External tables¶

You can create an external table with a row access policy by executing a CREATE EXTERNAL TABLE statement and apply the policy to the VALUE column.

You can apply the row access policy to VALUE column of an existing external table by executing an ALTER TABLE statement on the external table.

A row access policy cannot be added to a virtual column directly. Instead, create a view on the external table and apply the row access policy to the columns on the view.

Streams¶

If a row access policy is added to a table, Snowflake applies the row access policy to the table data when the stream accesses the table data.

For more information, see Limitations.

Views¶

Snowflake supports setting row access policies on the base table and view. The base table or view policy can apply to the view owner (i.e. INVOKER_ROLE) or the query operator role (i.e. CURRENT_ROLE).

For more information, see Limitations.

Materialized views¶

Snowflake supports adding a row access policy to a materialized view provided that a row access policy is not set on the underlying table or view.

Row access policies and materialized views do have the following limitations:

• A materialized view cannot be created from a table if a row access policy is added to the underlying table.

• A row access policy cannot be added to a table if a materialized view has been created from that underlying table.

Dynamic tables¶

You can create a dynamic table with a row access policy, masking policy, and tag. For more information, see:

• CREATE DYNAMIC TABLE

• Additional limitations with incremental refresh

CREATE TABLE statements¶

The following summarizes how row access policies affect CREATE TABLE statements:

CREATE TABLE … CLONE:

The following approach helps to safeguard data from users with the SELECT privilege on the table or view when accessing a cloned object:

• Cloning an individual policy object is not supported.

• Cloning a schema results in the cloning of all policies within the schema.

• A cloned table maps to the same policies as the source table. In other words, if a policy is set on the base table or its columns, the policy is attached to the cloned table or its columns.

  • If a table or view exists in the source schema/database and has references to policies in the same schema/database, the cloned table or view is mapped to the corresponding cloned policy (in the target schema/database) instead of the policy in the source schema/database.

  • If the source table refers to a policy in a different schema (i.e. a foreign reference), then the cloned table retains the foreign reference.

For more information, see CREATE <object> … CLONE.

CREATE TABLE … LIKE:

If a row access policy is set on the base table, the row access policy is not set on a column in the new table. The new table is empty.

CREATE TABLE … AS SELECT:

If a row access policy is set on the base table, the new table contains the filtered rows based on the row access policy definition. The new table does not have a row access policy set on a column.

Query profile¶

At query runtime, Snowflake creates a dynamic secure view.

When using the EXPLAIN command on a database object in which a row access policy is set, the query result indicates that a row access policy is present. When a row access policy is set on the database object, the EXPLAIN query result specifies the following column values:

• The operation column includes the value DynamicSecureView.

• The object column includes the value "<object_name> (+ RowAccessPolicy)".

Each step in the query plan that requires invoking the row access policy results in the operation and object columns specifying the corresponding values for that step in the query plan. If the row access policy was invoked only once in the query, only one row in the EXPLAIN query result includes the DynamicSecureView and "<object_name> (+ RowAccessPolicy)" values.

In the EXPLAIN command result and the Query History page, Snowflake does not show users any row access policy information (i.e. policy name, policy signature, policy expression) or the objects accessed by the policy.

The following example indicates a row access policy being invoked only once.

EXPLAIN SELECT * FROM my_table;

Copy

+-------+--------+--------+-------------------+--------------------------------+--------+-------------+-----------------+--------------------+---------------+
|  step |   id   | parent |     operation     |           objects              | alias  | expressions | partitionsTotal | partitionsAssigned | bytesAssigned |
+-------+--------+--------+-------------------+--------------------------------+--------+-------------+-----------------+--------------------+---------------+
...

| 1     | 2      | 1      | DynamicSecureView | "MY_TABLE (+ RowAccessPolicy)" | [NULL] | [NULL]      | [NULL]          | [NULL]             | [NULL]        |
+-------+--------+--------+-------------------+--------------------------------+--------+-------------+-----------------+--------------------+---------------+

The following example indicates a row access policy being invoked twice on the same table:

EXPLAIN SELECT product FROM sales
  WHERE revenue > (SELECT AVG(revenue) FROM sales)
  ORDER BY product;

Copy

+--------+--------+--------+-------------------+-----------------------------+--------+-------------+-----------------+--------------------+---------------+
|  step  |   id   | parent |     operation     |           objects           | alias  | expressions | partitionsTotal | partitionsAssigned | bytesAssigned |
+--------+--------+--------+-------------------+-----------------------------+--------+-------------+-----------------+--------------------+---------------+
...
| 1      | 0      | [NULL] | DynamicSecureView | "SALES (+ RowAccessPolicy)" | [NULL] | [NULL]      | [NULL]          | [NULL]             | [NULL]        |
...
| 2      | 2      | 1      | DynamicSecureView | "SALES (+ RowAccessPolicy)" | [NULL] | [NULL]      | [NULL]          | [NULL]             | [NULL]        |
+--------+--------+--------+-------------------+-----------------------------+--------+-------------+-----------------+--------------------+---------------+

Time Travel¶

Snowflake supports time travel on tables and views with a row access policy.

At query run time, Snowflake evaluates the row access policy’s mapping tables at the time of the query; in other words, time travel does not affect the mapping table.

For more information, see Understanding & using Time Travel.

Replication¶

Row access policies and their assignments can be replicated using database replication and replication groups.

For database replication, the replication operation fails if either of the following conditions is true:

• The primary database is in an Enterprise (or higher) account and contains a policy but one or more of the accounts approved for replication are on lower editions.

• A table or view contained in the primary database has a dangling reference to a row access policy in another database.

The dangling reference behavior for database replication can be avoided when replicating multiple databases in a replication group.

Note

If using failover or failback actions, the Snowflake account must be Business Critical Edition or higher.

For more information, see Introduction to replication and failover across multiple accounts.

Data Sharing¶

Usage:

• If the provider assigns a policy to a shared table or view and the policy conditions call the CURRENT_ROLE or CURRENT_USER function, or the policy conditions call a secure UDF, Snowflake returns a NULL value for the function or the UDF in the consumer account.

  The reason is that the owner of the data being shared does not typically control the users or roles in the account in which the table or view is being shared. As a workaround, use the CURRENT_ACCOUNT function in the policy conditions.

  Alternatively, as a provider, write the policy conditions to call the IS_DATABASE_ROLE_IN_SESSION function and share the database role. As a consumer, grant the shared database role to an account role. For details, see Share data protected by a policy.

Limitations:

• A data sharing provider cannot create a policy in a reader account.

• Data sharing consumers cannot apply a policy to a shared table or view. As a workaround, import the shared database and create a local view from the shared table or view.

• Data sharing consumers cannot query a shared table or view that references two different providers. For example:

  • rap1 is a row access policy that protects the table named t1, where t1 is in the share named share1 from a provider.

  • The rap1 policy conditions reference a mapping table named t2, where t2 comes from share2 and a different provider.

  • The consumer query on t1 fails.

  • The provider for t1 can query t1.

• External functions:

  Snowflake returns an error if:

  • The policy assigned to a shared table or view is updated to call an external function.

  • The policy calls an external function and you attempt to assign the policy to a shared table or view.

Snowflake Native App Framework¶

For details about using row access policies with a Snowflake Native App, see:

• Restrictions on sharing data content that contains policies.

• Define policies on proxy views.

• Blocked context functions.

Streamlit in Snowflake¶

Row access policies that are used in Streamlit in Snowflake apps have limitations with context functions in the body of a row access policy. For more information, see:

• Context functions and row access policies in Streamlit in Snowflake

• Example: Access data in a table with row access policy using CURRENT_USER

Choosing a centralized, hybrid, or decentralized management approach¶

To manage row access policies effectively, it is helpful to consider whether your approach to filtering rows should follow a centralized, decentralized, or hybrid governance approach.

The following table summarizes some of the considerations with each of these three approaches.

For syntax examples, see Summary of DDL commands, operations, and privileges.

Row access policy privileges¶

Snowflake supports the following row access policy privileges to determine whether users can create, set, and own row access policies.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

Row access policy DDL¶

Snowflake supports the following DDL commands and operations to manage row access policies:

• CREATE ROW ACCESS POLICY

• ALTER ROW ACCESS POLICY

• DROP ROW ACCESS POLICY

• SHOW ROW ACCESS POLICIES

• DESCRIBE ROW ACCESS POLICY

• ALTER TABLE, ALTER EXTERNAL TABLE, and ALTER VIEW (to add/drop a policy on a table or view)

Summary of DDL commands, operations, and privileges¶

The following table summarizes the relationship between the row access policy DDL operations and their necessary privileges.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

Snowflake supports different permissions to create and set a row access policy on an object.

1. For a centralized row access policy management approach, in which the rap_admin custom role creates and sets row access policies on all objects, the following permissions are necessary:

   use role securityadmin;
   grant create row access policy on schema <db_name.schema_name> to role rap_admin;
   grant apply row access policy on account to role rap_admin;
   

   Copy

2. In a hybrid management approach, a single role has the CREATE ROW ACCESS POLICY privilege to ensure consistent policy creation to optimize query performance and individual teams or roles have the APPLY privilege for a specific row access policy to protect their tables and views.

   For example, the custom role finance_role role can be granted the permission to add the row access policy rap_finance on tables and views the role owns:

   use role securityadmin;
   grant create row access policy on schema <db_name.schema_name> to role rap_admin;
   grant apply on row access policy rap_finance to role finance_role;
   

   Copy

Discover row access policies¶

You can use the ROW_ACCESS_POLICIES view in the Account Usage schema of the shared SNOWFLAKE database. This view is a catalog for all row access policies in your Snowflake account. For example:

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.ROW_ACCESS_POLICIES
ORDER BY POLICY_NAME;

Copy

Identify assignments¶

Snowflake supports different options to identify row access policy assignments, depending on whether the query needs to target the account or a specific database.

• Account-level query:

  Use the Account Usage POLICY_REFERENCES view to determine all of the tables that have a row access policy. For example:

  SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
  ORDER BY POLICY_NAME, REF_COLUMN_NAME;
  

  Copy

• Database-level query:

  Every Snowflake database includes a Snowflake Information Schema. Use the Information Schema table function POLICY_REFERENCES to determine all of the objects associated with a specific row access policy:

  SELECT *
  FROM TABLE(
    my_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
      POLICY_NAME => 'rap_t1'
    )
  );
  

  Copy

Accessing the Governance area in Snowsight¶

To access the Governance area, your Snowflake account must be Enterprise Edition or higher. Additionally, you must do either of the following:

• Use the ACCOUNTADMIN role.

• Use an account role that is directly granted the GOVERNANCE_VIEWER and OBJECT_VIEWER database roles.

  You must use an account role with these database role grants. Currently, Snowsight does not evaluate role hierarchies and user-defined database roles that have access to tables, views, data access policies, and tags.

  To determine if your account role is granted these two database roles, use a SHOW GRANTS command:

  SHOW GRANTS LIKE '%VIEWER%' TO ROLE data_engineer;
  

  Copy

  |-------------------------------+-----------+---------------+-----------------------------+------------+-----------------+--------------+------------|
  | created_on                    | privilege | granted_on    | name                        | granted_to | grantee_name    | grant_option | granted_by |
  |-------------------------------+-----------+---------------+-----------------------------+------------+-----------------+--------------+------------|
  | 2024-01-24 17:12:26.984 +0000 | USAGE     | DATABASE_ROLE | SNOWFLAKE.GOVERNANCE_VIEWER | ROLE       | DATA_ENGINEER   | false        |            |
  | 2024-01-24 17:12:47.967 +0000 | USAGE     | DATABASE_ROLE | SNOWFLAKE.OBJECT_VIEWER     | ROLE       | DATA_ENGINEER   | false        |            |
  |-------------------------------+-----------+---------------+-----------------------------+------------+-----------------+--------------+------------|
  

  
  

  If your account role is not granted either or both of these database roles, use the GRANT DATABASE ROLE command and run the SHOW GRANTS command again to confirm the grants:

  USE ROLE ACCOUNTADMIN;
  GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE data_engineer;
  GRANT DATABASE ROLE SNOWFLAKE.OBJECT_VIEWER TO ROLE data_engineer;
  SHOW GRANTS LIKE '%VIEWER%' TO ROLE data_engineer;
  

  Copy

  For details about these database roles, see SNOWFLAKE database roles.

Dashboard¶

As a data administrator, you can use the Dashboard interface to monitor tag and policy usage in the following ways.

• Coverage: specifies the count and percentage based on whether a table, view, or column has a policy or tag.

• Prevalence: lists and counts the most frequently used policies and tags.

The coverage and prevalence provide a snapshot as to how well the data is protected and tagged.

When you select a count number, percentage, policy name, or tag name, the Tagged Objects interface opens. The Tagged Objects interface updates the filters automatically based on your selection in the Dashboard.

The monitoring information is an alternative or complement to running complex and query-intensive operations on multiple Account Usage views.

These views might include, but are not limited to, the COLUMNS, POLICY_REFERENCES, TABLES, TAG_REFERENCES, and VIEWS views.

Tagged Objects¶

As a data administrator, you can use this table to associate the coverage and prevalence in the Dashboard to a list of specific tables, view, or columns quickly. You can also filter the table results manually as follows.

• Choose Tables or Columns.

• For tags, you can filter with tags, without tags, or by a specific tag.

• For policies, you can filter with policies, without policies, or by a specific policy.

When you select a row in the table, the Table Details or Columns tab in Data » Databases opens. You can edit the tag and policy assignments as needed.