Understanding Row-level Security

This topic provides an introduction to Row-level Security and row access policies.

In this Topic:

What is Row-level Security

Snowflake supports Row-level Security through the use of row access policies to determine which rows to return in the query result. The row access policy can be relatively simple to allow one particular role to view rows, or be more complicated to include a mapping table in the policy definition to determine access to rows in the query result.

A row access policy is a schema-level object that determines whether a given row in a table or view can be viewed from the following types of statements:

Row access policies can include conditions and functions to transform the data at query runtime when those conditions are met. The policy-driven approach supports segregation of duties to allow governance teams to define policies that can limit sensitive data exposure. This approach also includes the object owner (i.e. the role with the OWNERSHIP privilege on the object, such as a table or view) who normally has full access to the underlying data. A single policy can be set on different tables and views at the same time.

The row access policy admin can apply row access policies to tables and views.

Row access policies do not currently prevent rows from being inserted, or prevent visible rows from being updated or deleted. A row access policy is added to a table using an ALTER TABLE statement and a view using an ALTER VIEW statement.

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

Row access policies with tables and views.

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

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 Using Row-level Security.

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.

  • Search Optimization Service. Database objects protected by a row access policy cannot be used with the Search Optimization Services. For more information, see Troubleshooting Row Access Policies (in this topic).

  • Materialized views:

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

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

  • Streams:

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

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.

  • IS_ROLE_IN_SESSION cannot be used with row access policies that contain mapping tables. This function can be used with row access policies that do not contain mapping tables.

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

Using Row-level Security with Snowflake Objects and Features

The following sections describe how row access policies affect tables and views along with other Snowflake features.

Obtain Database Objects with a Row Access Policy

To obtain a list of database objects with row access policies, execute the following statement.

select *
from table(
  information_schema.policy_references(
    policy_name=>'<policy_name>'
  )
);

For more information, see:

Column-level Security 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.

External Tables

You can apply the row access policy to the External Table VALUE column by executing an ALTER EXTERNAL 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.

CREATE TABLE Statements

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

CREATE TABLE … CLONE

If a row access policy is set on the base table, the row access policy will be attached to the cloned table.

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 profile web interface, 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;

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

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

Policy-protected objects can be replicated if they are in the same database as the policy.

The individual row access policies can be replicated.

The replication operation fails if either of the following conditions are 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 reference to a row access policy in another database.

    Note

    If replication is using Failover or Failback, the Snowflake account must be Business Critical Edition or higher.

    For more information, see Database Replication and Failover/Failback.

Data Sharing

Snowflake allows data sharing providers and consumers to add row access policies to database objects.

Data sharing providers must use Context Functions (e.g. INVOKER_SHARE) in their row access policies to determine the authorized user population. If the row access policy references an External Function, the table or view cannot be shared.

Data sharing consumers cannot apply a row access policy to a shared database or table. As a workaround, import the shared database or table and apply the row access policy to a local view on that shared database or table.

Auditing Row Access Policies

Snowflake supports the following approaches to facilitate row access policy auditing and governance operations.

  • Use SHOW ROW ACCESS POLICIES to produce a list of row access policies that have not been dropped from your account.

  • Views:

    • Query the POLICY_REFERENCES View to learn about policy associations. Note that this view only returns row access policy information if your account is enabled for and using row access policies.

    • Query the ROW_ACCESS_POLICIES View to obtain a list of all row access policies created in your account.

  • Row access policy administrators (i.e. users with the row access policy OWNERSHIP privilege) can use time travel or streams to capture historical data about any mapping tables referenced in their row access policies.

  • To determine the data a given user can access, the row access policy administrator can assume the role of the user and run a query.

    • Snowflake supports defining a row access policy expression with custom logic to support this behavior in the CREATE ROW ACCESS POLICY command.

    • Snowflake does not currently have a default mechanism (e.g. a dedicated system or context function) to support this operation.

  • If a given row access policy uses mapping tables to determine which role and user populations can access row data, the row access policy owner can query the mapping tables to determine authorized user access on demand.

  • Snowflake captures and logs error message information related to row access policies in the account usage QUERY_HISTORY View view. If an error occurs in a query, Snowflake records the first error message that occurs during the query evaluation. For more information on row access policy error messages, see Troubleshooting Row Access Policies.

  • To determine the data a given user accessed in the past as it relates to row access policies on database objects, use time travel in combination with the row access policy account usage and information schema views.

    • If the policy and mapping tables, if present, have not changed, the row access policy administrator can assume the role of the user and run a time-travel query. The values of relevant session parameters, such as CURRENT_ROLE, are available in the query result.

    • If the policy or mapping tables have changed, the row access policy administrator must run a time travel query on the mapping table and reconstruct the row access policy that existed at the specified incident time. After those steps, the row access policy administrator can begin to query the data and proceed with their analysis.

Troubleshooting Row Access Policies

The following behaviors and error messages apply to row access policies.

Behavior

Error Message

Troubleshooting Action

Cannot create a row access policy (Boolean).

003551=SQL compilation error: Row access policy return type ‘’{0}’’ is not BOOLEAN.

A row access policy definition must have RETURNS BOOLEAN. Rewrite the row access policy as shown in CREATE ROW ACCESS POLICY.

Cannot create a row access policy (Database).

This session does not have a current database. Call ‘USE DATABASE’, or use a qualified name.

Since a row access policy is a schema-level object, define a database and schema for the current session or use the fully qualified name in the CREATE ROW ACCESS POLICY command. For more information, see Object Name Resolution.

Cannot create a row access policy (Object exists)

SQL compilation error: Object ‘<name>’ already exists.

Since a row access policy in the schema already exists with the stated name, recreate the row access policy with a different name value.

Cannot create a row access policy (Schema ownership).

SQL access control error: Insufficient privileges to operate on schema ‘S1’

Verify the privileges to create a row access policy in Summary of DDL Commands, Operations, and Privileges (in this topic).

Cannot create a row access policy (Schema usage).

SQL compilation error: Schema ‘<schema_name>’ does not exist or not authorized.

Verify that the specified schema exists and the privileges to create a row access policy in Summary of DDL Commands, Operations, and Privileges (in this topic).

Cannot describe a row access policy (Usage only).

SQL compilation error: Row access policy ‘RLS_AUTHZ_DB.S_B.P1’ does not exist or not authorized.

Having the USAGE privilege on the parent database and schema in which the row access policy exists is not sufficient to execute a DESCRIBE operation on the row access policy. Verify the row access policy exists and the privileges to describe a row access policy in Summary of DDL Commands, Operations, and Privileges (in this topic).

Cannot drop a row access policy. (Maintenance).

SQL compilation error: Row access policy ‘RLS_AUTHZ_DB.S_B.P1’ does not exist or not authorized.

Verify the specified row access policy exists and the privileges to drop a row access policy in Summary of DDL Commands, Operations, and Privileges (in this topic).

Cannot execute UNDROP on a row access policy. (Maintenance)

Unsupported feature ‘UNDROP not supported for objects of type ROW_ACCESS_POLICY’.

To reinstate a row access policy, execute a CREATE ROW ACCESS POLICY command, and then add the row access policy to a database object using an ALTER TABLE or ALTER VIEW command as shown in ALTER TABLE or ALTER VIEW.

Cannot update a row access policy (Name/Operation).

SQL compilation error: Object found is of type ‘ROW_ACCESS_POLICY’, not specified type ‘MASKING_POLICY’

Double-check the query to verify the name of the object and the intended operation on the object. . . For example, Snowflake does not support ALTER ROW ACCESS POLICY <name>;. . . Instead, use a CREATE OR REPLACE ROW ACCESS POLICY command to update a row access policy. For more information on row access policy operations, see Summary of DDL Commands, Operations, and Privileges (in this topic).

Cannot use row access policies with a Snowflake feature or service (Unsupported feature).

Unsupported feature ‘CREATE ON OBJECTS ENFORCED BY ROW ACCESS POLICY’.

Some Snowflake features and services do not support row access policies. For more information, see the Limitations and Using Row-level Security with Snowflake Objects and Features sections in this topic.

Cannot update a row access policy (Unsupported token).

Unsupported feature ‘TOK_ROW_ACCESS_POLICY’.

TOK refers to token, which can be returned if an query is unsupported and/or inaccurate; Snowflake’s SQL compiler does not know how to process the given query. . For example alter row access policy p1_test set comment = 'test policy 1';. In this example, the ALTER command cannot be used on the policy object directly; use an ALTER TABLE or ALTER VIEW command instead as shown in Summary of DDL Commands, Operations, and Privileges (in this topic).

Managing Row-level Security

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.

Policy Action

Centralized

Hybrid

Decentralized

Create policies

Governance officer

Governance officer

Individual teams

Apply policies to columns

Governance officer

Individual teams

Individual teams

Tip

As a best practice, Snowflake recommends that your organization gathers all relevant stakeholders to determine the best management approach for implementing Row-level Security in your environment.

Row Access Policy Privileges

Snowflake supports the following Row-level Security privileges to determine whether users can create, set, and own row access policies.

Note

Row access policies are schema-level objects.

Operating on a row access policy also requires the USAGE privilege on the parent database and schema.

Privilege

Usage

CREATE

Enables creating a new row access policy in a schema.

APPLY

Enables the add and drop operations for the row access policy on a table or view.

OWNERSHIP

Transfers ownership of a row access policy, which grants full control over the row access policy. Required to alter most properties of a row access policy.

Row Access Policy DDL

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

Summary of DDL Commands, Operations, and Privileges

The following table summarizes the relationship between the row-level security DDL operations and their necessary privileges.

Operation

Privilege required

Create row access policy

A role with the USAGE privilege on the parent database and schema with the CREATE ROW ACCESS POLICY privilege in the same schema.

Alter row access policy

The role with the OWNERSHIP privilege on the row access policy.

Add/Drop row access policy

A role with the APPLY ROW ACCESS POLICY privilege on the schema or a role with the OWNERSHIP privilege on the database object and the APPLY privilege on the row access policy object.

Drop row access policy

A role with the OWNERSHIP privilege on the row access policy or a role with the OWNERSHIP privilege on the schema in which the row access policy exists.

Show row access policies

The role with the OWNERSHIP privilege on the row access policy or a role with the APPLY privilege on the row access policy or a role with the global APPLY ROW ACCESS POLICY privilege.

Describe row access policy

A role with the OWNERSHIP privilege on the schema.

Next Topics: