dbt_snowflake_policies

• Overview
• Installation Instructions
• How to configure database and schema for the row_access policy ?
• How to apply row_access policy ?
• How to remove row_access policy ?
• How to validate row_access policy ?
• Process flow
  • Create row_access policy
  • Apply row_access policy
• Known Errors and Solutions
• Credits
• References
• Contributions
• How to do an integration test ?

Overview

This dbt package contains macros that can be (re)used across dbt projects with snowflake. dbt_snowflake_policies will help to apply Row Access Policies using dbt meta.

Installation Instructions

• Import the macros(macros/snow-rap) into your dbt project.

• This package uses dbt_utils package. When using dbt_snowflake_policies in your project, please install dbt_utils as well.

     - package: dbt-labs/dbt_utils
       version: 0.8.2

✅ Please refer to the release version in dbt hub for the latest revision

How to configure database and schema for the row access policy ?

By default this process creates the row access policies in same directory as the database objects. There are 2 methods for changing the default behavior by using the following parameters in your dbt_project.yml

Method 1 : Use a common database

To change the database that your row access polices are created in set the following parameters:

• use_common_row_access_policy_db (optional): Flag to enable the usage of a common db/schema for all row_access policies. Valid values are “True” OR "False"
• common_row_access_policy_db (optional): The database name for creating row_access policies
• common_row_access_policy_schema (optional): The schema name for creating row_access policies
• create_row_access_policy_schema (optional): Valid values are “True” OR "False". The default value is “True”. When set to "False", helps to avoid creating schema if the dbt role does not have access to create schema

Example : var block in dbt_project.yml to enable using a common row_access policy database

vars:
  use_common_row_access_policy_db: "True"
  common_row_access_policy_db: "DEMO_DB"
  common_row_access_policy_schema: "COMPLIANCE"

Method 2 : Use a common schema (in the current database)

To change only the schema (so that a common row_access policy schema is used in the same database as your model) set the following parameters:

• use_common_row_access_policy_schema_only (optional): Flag to enable the usage of a common schema in the current database for all row_access policies. Valid values are “True” OR "False"
• common_row_access_policy_schema (optional): The schema name for creating row_access policies
• create_row_access_policy_schema (optional): Valid values are “True” OR "False". The default value is “True”. When set to "False", helps to avoid creating schema if the dbt role does not have access to create schema

Example : var block in dbt_project.yml to enable using a common row_access policy schema (in the current database)

vars:
  use_common_row_access_policy_schema_only: "True"
  common_row_access_policy_schema: "COMPLIANCE"

✅ If both use_common_row_access_policy_db and use_common_row_access_policy_schema_only are set to True, then use_common_row_access_policy_db will supercede use_common_row_access_policy_schema_only.

Allow Custom Materializations

To enable dbt_snowflake_policies to apply row_access policies to models generated from custom materializations in dbt, configure the following parameter:

• custom_materializations_map (optional): A dictionary containing key-value pairs mapping custom materializations in dbt to the objects they generate in Snowflake. For each pair, the key must be the name of the custom_materialization and the value must be either table or view.

Example : var block in dbt_project.yml to enable application of row_access policies to a model generated using a custom materialiazition that ends up as a table in Snowflake.

vars:
  custom_materializations_map: '{ "custom_incremental": "table" }'

How to apply row_access policy ?

• row_access is controlled by meta in dbt resource properties for sources and models.

• Decide your row_access policy name and add the key row_access_policy in the column which has to be applied.

  Example : source.yml

  sources:
    - name: raw_sakila
      tables:
        - name: customer
          columns:
            - name: first_name
              meta:
                  row_access_policy: rap_encrypt

  Example : model.yml

  models:
    - name: stg_customer
      columns:
        - name: email
          meta:
            row_access_policy: rap_encrypt

• Create a new .sql file with the name create_row_access_policy_<row_access-policy-name-from-meta>.sql and the sql for row_access policy definition. Its important for macro to follow this naming standard.

  Example : create_row_access_policy_rap_encrypt.sql

  {% macro create_row_access_policy_rap_encrypt(node_database,node_schema) %}
  
    CREATE ROW ACCESS POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.rap_encrypt AS (val varchar)
  
    RETURNS BOOLEAN ->
        CASE WHEN CURRENT_ROLE() IN ('ANALYST','SYSADMIN') THEN TRUE
        ELSE FALSE
        END
  

{% endmacro %}

> Its good to keep the row_access policy ddl organized in a directory say `\macros\snow-rap-ddl`

- Create the row_access policies by running below command  

| Resource Type | Command                                                                         |
| ------------- | ------------------------------------------------------------------------------- |
| sources       | `dbt run-operation create_row_access_policy --args '{"resource_type": "sources"}'` |
| models        | `dbt run-operation create_row_access_policy --args '{"resource_type": "models"}'`  |

- Alternatively, you can also create the row_access policies by specifying `pre-hook` OR `on-run-start` in your `dbt_project.yml`

```yaml
on-run-start:
  - "{{ create_row_access_policy('models')}}"
  - "{{ create_row_access_policy('sources')}}"

• Apply the row_access policy by running below commands

Resource Type	Command
sources	dbt run-operation apply_row_access_policy --args '{"resource_type": "sources"}'
models	dbt run -- model <model-name>
snapshots	dbt snapshot --select <snapshot-name> --target <target-name>

• Alternatively, you can also apply the row_access policies by specifying below post-hook to dbt_project.yml

  Example : dbt_project.yml

(For models)

models:
  post-hook: 
    - "{{ apply_row_access_policy('models') }}"

(For snapshots)

snapshots:
  post-hook: 
    - "{{ apply_row_access_policy('snapshots') }}"

• If you prefer to add this directly to a dbt model, see below command for a correct example

  {{ config(post_hook = "{{ apply_row_access_policy('models') }}") }}

• Note: "{{ apply_row_access_policy('sources') }}" can be added to on-run-end, but "{{ apply_row_access_policy('models') }}" and "{{ apply_row_access_policy('snapshots') }}" should either be included specifically as post-hooks to the models or snapshots sections of dbt_project.yml or added as a custom config to an individual model.

How to remove row_access policy ?

• Remove the row_access policy applied by this package by running below commands

Resource Type	Command
sources	dbt run-operation unapply_row_access_policy --args '{"resource_type": "sources"}'
models	dbt run-operation unapply_row_access_policy --args '{"resource_type": "models"}'
snapshots	dbt run-operation unapply_row_access_policy --args '{"resource_type": "snapshots "}'

• Alternatively, you can also unapply the policies by specifying below post-hook OR on-run-end to dbt_project.yml

  Example : dbt_project.yml

(For models)

models:
  post-hook: 
    - "{{ unapply_row_access_policy('models') }}"

(For snapshots)

  snapshots:
    post-hook: 
      - "{{ unapply_row_access_policy('snapshots') }}"

How to validate row_access policy ?

-- Show row_access policy
SHOW ROW ACCESS POLICIES;

Process flow

Create row_access policy

graph TD
    A[create_row_access_policy] --> |resource_type='sources',meta_key='row_access_policy'| B[get_row_access_policy_list_for_sources]
    A[create_row_access_policy] --> |resource_type='models',meta_key='row_access_policy'| C[get_row_access_policy_list_for_models]
    B --> |database, schema| D[create_schema]
    C --> |database, schema| D[create_schema]
    D --> |policy_name| E[call_row_access_policy_macro]

Loading

Apply row_access policy

graph TD
    A[apply_row_access_policy] --> |resource_type='sources',meta_key='row_access_policy'| B[apply_row_access_policy_list_for_sources]
    A[apply_row_access_policy] --> |resource_type='models',meta_key='row_access_policy'| C[apply_row_access_policy_list_for_models]
    B --> |meta_key| D[confirm row_access policy is avaliable in db]
    C --> |meta_key| D[confirm row_access policy is avaliable in db]
    D --> |if_available_in_snowflake| E[alter statement to set row_access policy]
    D --> |if_not_available| F[Compilation error raised on the Model]
    E--> G[Clone object with applied row access policies]

Loading

Known Errors and Solutions

Error	Solution
dict object' has no attribute 'create_row_access_policy_rpa_encrypt'	Typo in yaml for row_access_policy, rpa_encrypt instead of rap_encrypt

Credits

This package derives major reference from dbt_snow_mask package and has been tweaked to support exception handling and Cloning functionality. So it is now a combined package to support both masking policies and row access policies.

References

• https://docs.snowflake.com/en/user-guide/security-column-ddm-intro.html
• https://getdbt.slack.com/archives/CJN7XRF1B/p1609177817234800
• https://docs.snowflake.com/en/sql-reference/functions/policy_references.html
• https://docs.snowflake.com/en/user-guide/security-column-ddm-use.html

Contributions

Contributions to this package are welcomed. Please create issues for bugs or feature requests for enhancement ideas or PRs for any enhancement contributions.