Row Access Policy

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with row access policy:

• Manage the lifecycle of new and existing row access policies
• Manage grants of row access policy

Usage

Default Configuration

databases:
  <database-name>:
    schemas:
      <schema-name>:
        row_access_policies:
          <row-access-policy-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

Data Products Configuration

- row_access_policies:
    name: <row-access-policy-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
row_access_expression	Required	String	Specifies the SQL expression. The expression can be any boolean-valued SQL expression
signature	Required	Map of String	Specifies signature (arguments) for the row access policy (uppercase and sorted to avoid recreation of resource). 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.
comment	Optional	String	Specifies a comment for the row access policy
deleted	Optional	Boolean: True enables deletion prevention, False does nothing	Specifies what objects are allowed to be deleted
grants	Optional	Map: See Supported Row Access Policy Grants to Roles	List of Privileges and Roles to which privileges are granted to on the current row access policy
manage_mode	Optional	String: all (default), none, grants	Configures what properties to manage for the row access policy. See Changing Manage Mode before changing the value.

warning

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like signature.

Supported row access policy grants to roles

Following are the privileges you can grant to roles in the row access policy definition:

• APPLY
• OWNERSHIP

Force-replacement behavior

Using precision for data types in signature parameters leads to force replacement behavior when running PLAN or APPLY for a row access policy.

Data types such as NUMBER(38, 0) or VARCHAR(16777216) are returned as NUMBER or VARCHAR respectively by Snowflake when executing the DESCRIBE query for row access policy signature parameters. The returned information is saved in the local state. Due to this, when subsequent pipelines are run, the engine assumes a change is to be made to convert NUMBER to the specified data type e.g., NUMBER(38, 0) again.

Examples

Row access policy with row access expression in a single line

Default Configuration

databases:
  SALES_RECORD:
    schemas:
      SALES:
        row_access_policies:
          ROW_ACCESS_POLICY:
            row_access_expression: "case when current_role() in ('ANALYST') then true else false end"
            signature:
              EMPL_ID: "VARCHAR"
              EMPL_SAL: "VARCHAR"
            comment: "Test row access policy"
            grants:
              apply:
                - SYSADMIN
                - ACCOUNTADMIN

Data Products Configuration

- row_access_policies:
    name: ROW_ACCESS_POLICY
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    row_access_expression: "case when current_role() in ('ANALYST') then true else false end"
    signature:
      EMPL_ID: "VARCHAR"
      EMPL_SAL: "VARCHAR"
    comment: "Test row access policy"
    grants:
      apply:
        - SYSADMIN
        - ACCOUNTADMIN

Row access policy with row access expression in multiple lines

Default Configuration

databases:
  SALES_RECORD:
    schemas:
      SALES:
        row_access_policies:
          ROW_ACCESS_POLICY:
            row_access_expression: >-
              case 
                when current_role() in ('ANALYST') then true 
                else false 
              end
            signature:
              EMPL_ID: "VARCHAR"
              EMPL_SAL: "VARCHAR"
            comment: "Test row access policy"
            grants:
              apply:
                - SYSADMIN
                - ACCOUNTADMIN

Data Products Configuration

- row_access_policies:
    name: ROW_ACCESS_POLICY
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    row_access_expression: >-
      case
        when current_role() in ('ANALYST') then true
        else false
      end
    signature:
      EMPL_ID: "VARCHAR"
      EMPL_SAL: "VARCHAR"
    comment: "Test row access policy"
    grants:
      apply:
        - SYSADMIN
        - ACCOUNTADMIN