Using Row Access Policies

This topic provides an introduction to implementing row access policies.

In this Topic:

Implementing Row Access Policies

The following steps are a representative guide to configure row access policy privileges and add row access policies to tables and views.

These steps make the following assumptions:

  • The management approach is centralized.

    If the row access policy use case includes a hybrid, or decentralized management approach, see Managing Row Access Policies for a representative distribution of roles and privileges.

  • A mapping table is necessary, similar to the Representative Use Case: Use a Mapping Table to Filter the Query Result.

    The following steps use the CURRENT_ROLE context function to determine whether users see rows in a query result, while the representative use case focuses on the user’s first name (i.e. CURRENT_USER).

    The overall process to implement a row access policy with mapping tables remains the same even though the context functions are different.

  • The SECURITYADMIN system role grants privileges to custom roles to manage and implement row access policies.

    If you do not want to use higher privileged roles (i.e. SECURITYADMIN or ACCOUNTADMIN) in a production environment in favor of less privileged custom roles (e.g. database_admin, finance_admin), verify that the lower-privileged roles roles have the necessary privileges to manage and implement row access policies.

    For more information, see Row Access Policy Privileges and Summary of DDL Commands, Operations, and Privileges.

Step 1: Create a Table for the Data

Create a table for the sales data.

create table sales (
  customer   varchar,
  product    varchar,
  spend      decimal(20, 2),
  sale_date  date,
  region     varchar
);

Step 2: Create a Mapping Table, Custom Role, and Grant the SELECT Privilege

In the security schema, create a mapping table as shown in the representative example. This table defines which rows in the sales table sales managers can see.

create table security.salesmanagerregions (
  sales_manager varchar,
  region        varchar
);

Next, a security administrator creates the mapping_role custom role and grants the SELECT privilege to the custom role. This grant allows users with the custom role to query the mapping table.

use role securityadmin;

create role mapping_role;

grant select on table security.salesmanagerregions to role mapping_role;

Step 3: Create a Row Access Policy

Using the schema owner role, create a row access policy with the following two conditions:

  1. Users with the sales_executive_role custom role can view all rows.

  2. Users with the sales_manager custom role can view rows based on the salesmanagerregions mapping table.

Note that the schema owner role is automatically granted the CREATE ROW ACCESS POLICY privilege. If other roles should be able to create row access policies, the schema owner role can grant the CREATE ROW ACCESS policy privilege to other roles.

use role <schema_owner_role>;

create or replace row access policy security.sales_policy as (sales_region varchar) returns boolean ->
  'sales_executive_role' = current_role()
      or exists (
            select 1 from salesmanagerregions
              where sales_manager = current_role()
                and region = sales_region
          )
;

Where:

security.sales_policy

The name of the row access policy in the Security schema.

as (sales_region varchar)

The signature for the row access policy.

A signature specifies the mapping table attribute and data type. The returned value determines whether the user has access to a given row on the table or view to which the row access policy is added.

returns boolean ->

Specifies the application of the row access policy.

Note that the <expression> of the row access policy immediately follows the right-arrow (i.e. ->).

The expression can be any boolean-valued SQL expression. Snowflake supports expressions that invoke UDFs, External Functions, and expressions that use subqueries.

'sales_executive_role' = current_role()

The first condition of the row access policy expression that allows users with the sales_executive_role custom role to view data.

or exists (select 1 from salesmanagerregions where sales_manager = current_role() and region = sales_region)

The second condition of the row access policy expression which uses a subquery.

The subquery requires the CURRENT_ROLE to be the sales_manager custom role with the executed query on the data to specify a region listed in the {salesmanagerregions} mapping table.

Step 4: Grant Privileges to Custom Roles

Using the SECURITYADMIN system role, execute the following two statements:

grant ownership on row access policy security.sales_policy to mapping_role;

grant apply on row access policy security.sales_policy to role sales_analyst_role;

These two GRANT <privileges> … TO ROLE statements have the following effects:

  • Ownership of the policy does not rest with the SECURITYADMIN system role. At query runtime, Snowflake uses the privileges granted to the custom role because policies are executed with owner’s rights, not the more privileged SECURITYADMIN system role. This approach supports the Principle of Least Privilege.

  • The sales_analyst_role custom role can add or drop the row access policy from a table as needed.

Step 5: Add the Row Access Policy to a Table

Any table or view in Snowflake can support up to one row access policy at a time.

Add (i.e. bind) the row access policy to the region column in the Sales data table.

use role securityadmin;

alter table sales add row access policy security.sales_policy on (region);

Step 6: Allow a Role to Query the Protected Table Data

Grant the SELECT privilege on the protected sales data to the sales_manager_role custom role.

grant select on table sales to role sales_manager_role;

Step 7: Test the Policy

After the sales data populates the Sales data, test the row access policy.

use role sales_manager_role;
select product, sum(spend)
from sales
where year(sale_date) = 2020
group by product;