Categories:

Table, View, & Sequence DDL

CREATE ROW ACCESS POLICY

Creates a new row access policy in the current/specified schema or replaces an existing row access policy.

See also:

Table, View, & Sequence DDL

In this Topic:

Syntax

Snowflake supports the following syntax to create a row access policy.

CREATE [ OR REPLACE ] ROW ACCESS POLICY [ IF NOT EXISTS ] <name> AS
(<arg name> <arg type> [ , ... ]) RETURNS BOOLEAN -> <expression>
[ COMMENT = '<string_literal>' ]

Required Parameters

name

Identifier for the row access policy; must be unique for your schema.

The identifier value must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. “My object”). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements

AS ( <arg_name> <arg_type> [ , ] )

The signature for the row access policy.

A signature specifies a set of attributes that must be considered to determine whether the row is accessible. The attribute values come from the database object (e.g. table or view) to be protected by the row access policy.

RETURNS BOOLEAN -> <expression>

Determines the application of the row access policy.

The returned value determines whether the user has access to a given row on the database object to which the row access policy is added.

The expression can be any boolean-valued SQL expression. Snowflake supports expressions that invoke UDFs (User-Defined Functions), External Functions, and expressions that use sub-queries.

Optional Parameters

COMMENT = 'string_literal'

Specifies a comment for the row access policy.

Default: No value

Usage Notes

  • Creating a row access policy requires the CREATE ROW ACCESS POLICY privilege on the current or specified schema.

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

  • If a database object has both a row access policy and one or more Column-level Security masking policies, the row access policy is evaluated first.

    For more information on row access policies during query runtime, see Understanding Row-level Security.

  • You cannot change the policy signature (i.e. argument name or input/output data type) using CREATE OR REPLACE ROW ACCESS POLICY if the policy is attached to a table or view, or using ALTER ROW ACCESS POLICY. If you need to change the signature, execute a DROP ROW ACCESS POLICY statement on the policy and create a new row access policy.

Examples

The following row access policy allows users whose CURRENT_ROLE is the it_admin custom role to see the employee ID number (i.e. empl_id) in the query result.

create or replace row access policy rap_it as (empl_id varchar) returns boolean ->
  case
      when 'it_admin' = current_role() then true
      else false
  end
;

The following row access policy allows users to view data if the query meets either of the following two conditions:

  1. The current role is the sales_executive_role custom role. Query the CURRENT_ROLE function to determine the current role.

  2. The current role is the sales_manager custom role and the query specifies a sales_region that corresponds to the salesmanageregions mapping table.

use role securityadmin;

create or replace row access policy rap_sales_manager_regions_1 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:

rap_sales_manager_regions_1

The name of the row access policy.

as (sales_region varchar)

The signature for the row access policy.

A signature specifies a set of attributes that must be considered to determine whether the row is accessible. The attribute values come from the table to be protected by the row access policy.

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

The following row access policy specifies two attributes in the policy signature:

create or replace row access policy rap_test2 as (n number, v varchar)
  returns boolean -> true;

Where:

rap_test2

The name of the row access policy.

(n number, v varchar)

The signature for the row access policy.

If this policy is added to a database object and either of the specified columns are included in the query on the database object, Snowflake does not return the row in the query result.

returns boolean -> true

Determines the application of the row access policy.

The returned value determines whether the user has access to a given row on the database object to which the row access policy is added.