Feature Request: Conditional Masking Policy

[Stchena]

Feature Request: Conditional Masking Policy

Description:

dbt-snow-mask currently supports only simple, static rules based just on the meta-masked column and user's role. It would be great to introduce conditional masking policies to allow for more fine-grained control of access.

For example, in our use-case, we couldn't proceed with dbt-snow-mask because we had our data scientists divided into 2 groups with mutually exclusive lists of client applications and their data. We wanted ds1 to have full access to their PII data, while only masked access to ds2 PII data and vice versa. With conditional masking policies, this could be possible.

Link to Snowflake Documentation

Proposed Solution:

An example of how DDLs could be declared with the new feature:

{% macro create_masking_policy_mp_pii(node_database, node_schema, masked_column, conditional_columns) %}

    CREATE MASKING POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.mp_{{masked_column}}_pii AS
    (
        {{masked_column}} string, 

        {% for cond_col in conditional_columns %}
        {{cond_col}} string{{ "," if not loop.last }}
        {% endfor %}
    ) RETURNS string ->
        CASE
            WHEN CURRENT_ROLE() IN ('DS1') AND <your-conditions-here> THEN {{masked_column}}
        ELSE '***MASKED***'
        END
{% endmacro %}

An example of how to declare conditional columns in meta:

...
- name: email
   description:  User personal email
   meta:
     masking_policy: mp_pii
     conditional_columns:
       - cond_col1
       - cond_col2
       - ...
...

Modify get_meta_objects.sql macro to extract conditional columns.

Modify get_masking_policy_list_for_models.sql and create_masking_policy.sql macros to include the newly extracted parameters.

Modify the apply query in the following way:

alter {{materialization}} {{database}}.{{schema}}.{{alias}} modify column {{column}} set masking policy {{masking_policy_db}}.{{masking_policy_schema}}.{{masking_policy_name}} using ({{column}}, {{conditional_columns}}) {% if var('use_force_applying_masking_policy','False')|upper in ['TRUE','YES'] %} force {% endif %};

Benefits:

Allow companies to enforce more fine-grained access control. This functionality brings in best-of-both-worlds from Snowflake's Column Masking Policies and RBAC. The introduction of conditional masking policies would add a lot of flexibility to the dbt-snow-mask tool. Users would be able to define masking policies that are more specific and nuanced than what is currently possible with static rules. This would make the tool more powerful and easier to use in complex data environments.

Challenges:

• Masking policy naming issues - it would be optimal for every new masking policy to include table name and column name to ensure that a new policy does not replace an already existing / just applied policy in Snowflake. I imagine this would create quite a few debugging issues.

Additional Information:

I'd be happy to help implement this.

Thank you for considering this feature suggestion!

[entechlog]

Thank You @Stchena for the feature request write up and the PR. I won't be able to get to the review of this one until this weekend. Is that fine ?

[entechlog]

I have merged this to main now, There are two more pending PR's and will try to cut a release this weekend after reviewing and merging them.

Thank you for spending the time to improve the package

[entechlog]

The other two PR's may need additional review, So I have released version 0.2.4 with this change alone and will be reflected in dbt hub in a couple of hours. Please validate and confirm if this is working good for you and team.