Skip to content

Row Access Policy

Snowflake Docs - CREATE ROW ACCESS POLICY

Snowflake Docs - ALTER ROW ACCESS POLICY

Usage

  • A row access policy is made up of 2 parts
    • YAML Configuration
    • SQL code with the policy itself
  • All row access policies must live inside a [DATABASE]/[SCHEMA]/ROW_ACCESS_POLICIES folder and contain both a .yml file and .sql file with the same name.
  • Object name = name of the yml config file within [DATABASE]/[SCHEMA]/ROW_ACCESS_POLICIES folder

Snowflake Attributes

Parameter
 Description
SIGNATURE (List[{name: datatype}]) - Optional
RETURN_TYPE (String) - Optional
EXEMPT_OTHER_POLICIES (Bool) - Optional
COMMENT (String) - Optional
OWNER (String) - Optional
  • If HANDLE_OWNERSHIP=ERROR, be careful not to set OWNER to a role that the deployer does not have access to as it will no longer have access to manage
TAGS ({KEY:VALUE}) - Optional
GRANTS ({KEY:VALUE}) - Optional

Optional Parameter Defaults - if omitted, Snowflake defaults for parameters are used, just like creating an object manually.

Deployment attributes

Parameter
 Description
DEPLOY_ENV (List) - Optional
  • Default = []
  • List of the deployment environments to deploy to
  • Deployment environment set in the environment config yaml using the DEPLOY_ENV parameter. See Setup/Config for docs.
  • When empty or not included, deployed to all environments
DEPLOY_LOCK (Bool) - Optional
  • Default = False
  • Locks the config file to being over written by reverse engineer process.
  • Use if source code should always be source of truth and any changes pulled from database should be ignored

Folder Structure

Configuration: snowflake/data/[database name]/[schema name]/ROW_ACCESS_POLICIES/[policy name].yml

Policy snowflake/data/[database name]/[schema name]/ROW_ACCESS_POLICIES/[policy name].sql

Example Structure

snowflake/data/CONTROL/GOVERNANCE/ROW_ACCESS_POLICIES/SALES_POLICY.yml

snowflake/data/CONTROL/GOVERNANCE/ROW_ACCESS_POLICIES/SALES_POLICY.sql

snowflake/data/CONTROL/GOVERNANCE/ROW_ACCESS_POLICIES/PEOPLE_POLICY.yml

snowflake/data/CONTROL/GOVERNANCE/ROW_ACCESS_POLICIES/PEOPLE_POLICY.sql

This specifies the metadata for 2 row access policies named "SALES_POLICY" and "PEOPLE_POLICY" within the CONTROL.GOVERNANCE schema, each with their own yml config file & sql based on the policy name

Samples

Basic 

SIGNATURE:
- VAR1: VARCHAR
- VAR2: INT
RETURN_TYPE: BOOLEAN
EXEMPT_OTHER_POLICIES: false
OWNER: INSTANCEADMIN
COMMENT: Row access policy associated for sales records based on department
TAGS: 
- {{ref('CONTROL__GOVERNANCE__ENV')}}: {{env}}
GRANTS: 
- {{role('SOME_ROLE')}}: APPLY

With a deploy lock & restricted deployment environments.

SIGNATURE:
- VAR1: VARCHAR
- VAR2: INT
RETURN_TYPE: BOOLEAN
EXEMPT_OTHER_POLICIES: false
OWNER: INSTANCEADMIN
COMMENT: Row access policy associated for sales records based on department
TAGS: 
- {{ref('CONTROL__GOVERNANCE__ENV')}}: {{env}}
GRANTS: 
- {{role('SOME_ROLE')}}: APPLY
DEPLOY_LOCK: true
DEPLOY_ENV:
- PROD
- TEST