Skip to main 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

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 KeyRequired/OptionalData Types and ValuesDescription
row_access_expressionRequiredStringSpecifies the SQL expression. The expression can be any boolean-valued SQL expression
signatureRequiredMap of StringSpecifies 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.
commentOptionalStringSpecifies a comment for the row access policy
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
grantsOptionalMap: See Supported Row Access Policy Grants to RolesList of Privileges and Roles to which privileges are granted to on the current row access policy
manage_modeOptionalString: all (default), none, grantsConfigures 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

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