Conditional Masking Policy

[Stchena]

61 Conditional Masking Policy

Description

dbt-snow-mask currently supports only simple, static rules based just on the meta-masked column and user's role. The introduction of conditional masking policies can add a lot of flexibility to the dbt-snow-mask tool. Users will be able to define masking policies that are more specific and nuanced than what is currently possible with static rules. This can make the tool more powerful and easier to use in complex data environments.

Moreover, this change is non-breaking - existing policies do not need to change. The create_masking_policy_* call's parameterization is dependent on whether mp_conditional_columns was set in .yml or not:

...
{% if conditionally_masked_column is not none %}
            {% set result = run_query(call_masking_policy_macro(masking_policy_db, masking_policy_schema, conditionally_masked_column)) %}
        {% else %}
            {% set result = run_query(call_masking_policy_macro(masking_policy_db, masking_policy_schema)) %}
        {% endif %}
...

How it works & How to use

If you want to use a Conditional Masking Policy for your column, provide an additional meta parameter called mp_conditional_columns. It needs to contain a list of columns to use for conditions.

...
columns:
          - name: first_name
            meta:
              masking_policy: mp_conditional_customer_pii
              mp_conditional_columns: 
                - store_id 
                - active
...

get_meta_objects macro picks up and passes this list to the ALTER statements when applying the masking policies (during apply_masking_policy macro execution. If conditional columns are specified, the conditional masking policy's DDL also needs to contain the actual name of the masked column instead of val.

Example DDLs

Conditional Masking Policy DDL:

{% macro create_masking_policy_mp_conditional_pii(node_database, node_schema, masked_column) %}

    CREATE MASKING POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.mp_conditional_pii AS (
        {{masked_column}} string,
        my_conditional_col_1 string,
        my_conditional_col_2 string
    ) RETURNS string ->
        CASE 
            WHEN CURRENT_ROLE() IN ('ANALYST') AND my_conditional_col_1='foo' THEN {{masked_column}}
            WHEN CURRENT_ROLE() IN ('ANALYST') AND my_conditional_col_2='bar' THEN SHA2({{masked_column}})
             WHEN CURRENT_ROLE() IN ('SYSADMIN') THEN SHA2({{masked_column}})
        ELSE '**********'
        END

{% endmacro %}

Standard, non-conditional Masking Policy DDL

{% macro create_masking_policy_mp_encrypt_pii(node_database,node_schema) %}

    CREATE MASKING POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.mp_encrypt_pii AS (val string) 

    RETURNS string ->
        CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val 
             WHEN CURRENT_ROLE() IN ('SYSADMIN') THEN SHA2(val)
        ELSE '**********'
        END

{% endmacro %}

Extra

Closes: #61

[Stchena]

Hi @entechlog , wanted to reach out to you regarding the pull request and ask if there's a chance for a review sometime soon 😉

[entechlog]

Hello @Stchena , Apologies for the delay. I have busy with some end of Q1 effort at work. I am looking into this one today and will keep you posted