Skip to main content

Row Access Policy

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with Row Access Policy:

  • Manage Lifecycle of new and existing Row Access Policy
  • Manage Grants of Row Access Policy

Supported Parameters

The engine supports the parameters listed below.

  • ROW_ACCESS_EXPRESSION: Specifies the SQL expression. The expression can be any boolean-valued SQL expression.
    • REQUIRED
    • Configuration key: row_access_expression
    • Data Type: String
  • SIGNATURE: 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.
    • REQUIRED
    • Configuration key: signature
    • Data Type: Map of String
  • COMMENT: Specifies a comment for the Row Access policy.
    • Configuration key: comment
    • Data Type: String
  • MANAGE_MODE: Configures what properties to manage for the Row Access Policy.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current Row Access Policy.
    • Configuration key: grants
    • Data Type: Map

Basic syntax

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

Supported Row Access Policy Grants to Roles

Following is the list of Privileges Grant to Roles that can be specified 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 single line

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

Row Access Policy with Row Access Expression in multiple lines

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