Snowflake dynamic masking

To Nha Notes | July 4, 2022, 9:20 p.m.

CREATE MASKING POLICY emp_contact AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('OFFICEADMIN') THEN val
    ELSE ''
  END;
CREATE MASKING POLICY emp_financial AS (val integer) RETURNS integer ->
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTING') THEN val
    ELSE 0
  END;
CREATE MASKING POLICY emp_hr AS (val integer) RETURNS integer ->
  CASE
    WHEN CURRENT_ROLE() IN ('HR') THEN val
    ELSE 0
  END;

ALTER TABLE employees_table MODIFY COLUMN home_address SET MASKING POLICY emp_contact;
ALTER TABLE employees_table MODIFY COLUMN home_phone SET MASKING POLICY emp_contact;
ALTER TABLE employees_table MODIFY COLUMN salary SET MASKING POLICY emp_financial;
ALTER TABLE employees_table MODIFY COLUMN evaluation SET MASKING POLICY emp_hr;