create_masking_policies_for_customer.sql

USE ROLE MASKING_ADMIN;

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.string_mask_customer AS (val STRING) RETURNS STRING ->
  CASE
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-survey') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-survey' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-loyalty') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-loyalty' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.technical_user', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'ServiceAccount') THEN val
    ELSE '***MASKED***'
  END
COMMENT = 'String masking policy for customer experience sensitive columns';

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.timestamp_mask_customer AS (val TIMESTAMP) RETURNS TIMESTAMP ->
  CASE
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-survey') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-survey' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-loyalty') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-loyalty' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.technical_user', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'ServiceAccount') THEN val
    ELSE date_from_parts(0001, 01, 01)::TIMESTAMP
  END
COMMENT = 'Timestamp masking policy for customer experience sensitive columns';

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.date_mask_customer AS (val DATE) RETURNS DATE ->
  CASE
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-survey') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-survey' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.customer_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'customer-loyalty') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.customer_sensitive') = 'customer-loyalty' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.technical_user', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'ServiceAccount') THEN val
    ELSE date_from_parts(0001, 01, 01)::DATE
  END
COMMENT = 'Date masking policy for customer experience sensitive columns';