Skip to content

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

- row_access_policy:
    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>)
databases:
  <database-name>:
    schemas:
      <schema-name>:
        row_access_policies:
          <row-access-policy-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <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
arguments Required Object Specifies the arguments for the row access policy (uppercase and sorted to avoid recreation of resource). This is 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
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this row access policy depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
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 arguments.

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 the arguments 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 arguments. 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

- row_access_policy:
    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"
    arguments:
      EMPL_ID:
        type: VARCHAR
      EMPL_SAL:
        type: VARCHAR
    comment: "Test row access policy"
    grants:
      apply:
        - SYSADMIN
        - ACCOUNTADMIN
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"
            arguments:
              EMPL_ID:
                type: VARCHAR
              EMPL_SAL:
                type: VARCHAR
            comment: "Test row access policy"
            grants:
              apply:
                - SYSADMIN
                - ACCOUNTADMIN

Row access policy with row access expression in multiple lines

- row_access_policy:
    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
    arguments:
      EMPL_ID:
        type: VARCHAR
      EMPL_SAL:
        type: VARCHAR
    comment: "Test row access policy"
    grants:
      apply:
        - SYSADMIN
        - ACCOUNTADMIN
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
            arguments:
              EMPL_ID:
                type: VARCHAR
              EMPL_SAL:
                type: VARCHAR
            comment: "Test row access policy"
            grants:
              apply:
                - SYSADMIN
                - ACCOUNTADMIN