vvgsrk

USE ROLE TAG_ADMIN;

ALTER TAG dp_governance.tags.customer_sensitive 
SET MASKING POLICY dp_governance.masking_policies.string_mask_customer,
    MASKING POLICY dp_governance.masking_policies.timestamp_mask_customer,
    MASKING POLICY dp_governance.masking_policies.date_mask_customer
;

vvgsrk

USE ROLE MASKING_ADMIN;

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.string_mask_employee AS (val STRING) RETURNS STRING ->
  CASE
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.employee_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'compensation') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.employee_sensitive') = 'compensation' THEN val
    WHEN EXISTS (SELECT 1 FROM TABLE(SPLIT_TO_TABLE(SYSTEM$GET_TAG('dp_governance.tags.employee_sensitive', dp_governance.tags.get_current_user(), 'user'), ',')) WHERE value = 'payroll') and SYSTEM$GET_TAG_ON_CURRENT_COLUMN('dp_governance.tags.employee_sensitive') = 'payroll' 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 employee experience sensi

vvgsrk

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 c

vvgsrk

USE ROLE TAG_ADMIN;

CREATE OR REPLACE TAG dp_governance.tags.employee_sensitive
    COMMENT = 'Reserved for all sensitive employee experience data';

vvgsrk

USE ROLE TAG_ADMIN;

CREATE OR REPLACE TAG dp_governance.tags.customer_sensitive
    COMMENT = 'Reserved for all sensitive customer experience data';

vvgsrk

SELECT 'ALTER TABLE IF EXISTS ' || LOWER(table_catalog) || '.' || LOWER(table_schema) || '.' || LOWER(table_name) || ' MODIFY COLUMN ' || LOWER(column_name) || ' SET TAG ' || ' dp_governance.tags.customer_sensitive = ''add_source_name'';' AS set_statement,
'ALTER TABLE IF EXISTS ' || LOWER(table_catalog) || '.' || LOWER(table_schema) || '.' || LOWER(table_name) || ' MODIFY COLUMN ' || LOWER(column_name) || ' UNSET TAG ' || ' dp_governance.tags.customer_sensitive;' AS unset_statement
FROM dp_dev.information_schema.columns
WHERE table_name = 'TABLE_NAME_X'
AND column_name IN ('COLUMN_NAME_1', 'COLUMN_NAME_2', 'COLUMN_NAME_3')
;

vvgsrk

-- Devlopment
USE ROLE dp_owner_dev;

/*

Table: schema_name.table_name

Columns:
column_name_1
column_name_2

vvgsrk

-- Devlopment
USE ROLE dp_owner_dev;

/*

Table: schema_name.table_name

Columns:
column_name_1
column_name_2

vvgsrk

vvgsrk