vvgsrk

USE ROLE USERADMIN;

-- Jamie
ALTER USER jamie UNSET TAG dp_governance.tags.employee_sensitive;

-- Avery
ALTER USER avery UNSET TAG dp_governance.tags.employee_sensitive;

-- Blake
ALTER USER blake UNSET TAG dp_governance.tags.employee_sensitive;

vvgsrk

USE ROLE USERADMIN;

-- Robert
ALTER USER robert UNSET TAG dp_governance.tags.customer_sensitive;

-- Jasper
ALTER USER jasper UNSET TAG dp_governance.tags.customer_sensitive;

-- Rowan
ALTER USER rowan UNSET TAG dp_governance.tags.customer_sensitive;

vvgsrk

USE ROLE USERADMIN;

-- Jamie
ALTER USER jamie SET TAG dp_governance.tags.employee_sensitive = 'compensation';

-- Avery
ALTER USER avery SET TAG dp_governance.tags.employee_sensitive = 'payroll';

-- Blake
ALTER USER blake SET TAG dp_governance.tags.employee_sensitive = 'compensation,payroll';

vvgsrk

USE ROLE USERADMIN;

-- Robert
ALTER USER robert SET TAG dp_governance.tags.customer_sensitive = 'customer-survey';

-- Jasper
ALTER USER jasper SET TAG dp_governance.tags.customer_sensitive = 'customer-loyalty';

-- Rowan
ALTER USER rowan SET TAG dp_governance.tags.customer_sensitive = 'customer-survey,customer-loyalty';

vvgsrk

USE ROLE SYSADMIN;

CREATE DTABASE dp_governance;

CREATE SCHEMA dp_governance.tags;
CREATE SCHEMA dp_governance.masking_policies;

vvgsrk

USE ROLE TAG_ADMIN;

ALTER TAG dp_governance.tags.employee_sensitive 
UNSET MASKING POLICY dp_governance.masking_policies.string_mask_employee,
    MASKING POLICY dp_governance.masking_policies.float_mask_employee,
    MASKING POLICY dp_governance.masking_policies.timestamp_mask_employee,
    MASKING POLICY dp_governance.masking_policies.date_mask_employee,
    MASKING POLICY dp_governance.masking_policies.number_mask_employee
;

vvgsrk

USE ROLE tag_admin;

ALTER TAG dp_governance.tags.customer_sensitive 
UNSET 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 TAG_ADMIN;

ALTER TAG dp_governance.tags.employee_sensitive 
SET MASKING POLICY dp_governance.masking_policies.string_mask_employee,
    MASKING POLICY dp_governance.masking_policies.float_mask_employee,
    MASKING POLICY dp_governance.masking_policies.timestamp_mask_employee,
    MASKING POLICY dp_governance.masking_policies.date_mask_employee,
    MASKING POLICY dp_governance.masking_policies.number_mask_employee
;

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