create_masking_policies_for_employee.sql

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 sensitive columns';

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.float_mask_employee AS (val FLOAT) RETURNS FLOAT ->
  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 '-1'
  END
COMMENT = 'Float masking policy for employee experience sensitive columns';

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.timestamp_mask_employee AS (val TIMESTAMP) RETURNS TIMESTAMP ->
  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 date_from_parts(0001, 01, 01)::TIMESTAMP
  END
COMMENT = 'Timestamp masking policy for employee experience sensitive columns';

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.date_mask_employee AS (val DATE) RETURNS DATE ->
  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 date_from_parts(0001, 01, 01)::DATE
  END
COMMENT = 'Date masking policy for employee experience sensitive columns';

CREATE OR REPLACE MASKING POLICY dp_governance.masking_policies.number_mask_employee AS (val NUMBER) RETURNS NUMBER ->
  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 RANDOM()
  END
COMMENT = 'Number masking policy for employee experience sensitive columns';