BankOrg Docs

In progress

BankOrg Data Platform / Data Engineering

PII Masking

Tag-based dynamic data masking of the PII columns: the PII tag, two masking policies, the RAW and staging columns tagged and the role that sees clear values.

The PII columns of the core banking data are masked with Snowflake dynamic data masking (Enterprise edition). A tag, PII, marks a column, and two masking policies linked to the tag mask it for every role except DBT_ROLE and PII_READER_ROLE.

Create the role

Create PII_READER_ROLE as SECURITYADMIN and grant it to SYSADMIN. It is the role that sees PII in clear.

Snowflake
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS PII_READER_ROLE;
GRANT ROLE PII_READER_ROLE TO ROLE SYSADMIN;

Create the tag

Create the database PRD_GOVERNANCE, the schema MASKING and the tag PII as SYSADMIN. The tag value selects the masking style, and ALLOWED_VALUES rejects any other value.

Snowflake
USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS PRD_GOVERNANCE;
CREATE SCHEMA IF NOT EXISTS PRD_GOVERNANCE.MASKING;

USE SCHEMA PRD_GOVERNANCE.MASKING;

CREATE TAG IF NOT EXISTS PII
    ALLOWED_VALUES 'email', 'identifier', 'text', 'date'
    COMMENT = 'PII column; the value selects the masking style (see policies MASK_PII_STRING / MASK_PII_DATE)';
Tag valueMasked valueExample
email***@ and the domainnam.phan263@example.com becomes ***@example.com
identifierAll characters hidden except the last 30992880321 becomes *******321
text***Quoc Nam becomes ***
dateShifted by 1 to 365 days, forward or backA date of birth moves within a year of the real date

Create the policies

One policy per data type. MASK_PII_STRING covers the string columns of RAW and staging and reads the tag value on the column to pick the style. MASK_PII_DATE covers real DATE columns. A NULL stays NULL, and an unknown tag value on a string column returns ***.

Snowflake
CREATE MASKING POLICY IF NOT EXISTS MASK_PII_STRING AS (val STRING) RETURNS STRING ->
    CASE
        WHEN val IS NULL THEN NULL
        WHEN CURRENT_ROLE() = 'DBT_ROLE' OR IS_ROLE_IN_SESSION('PII_READER_ROLE') THEN val
        WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRD_GOVERNANCE.MASKING.PII') = 'email'
            THEN '***@' || SPLIT_PART(val, '@', 2)
        WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRD_GOVERNANCE.MASKING.PII') = 'identifier'
            THEN REPEAT('*', GREATEST(LENGTH(val) - 3, 0)) || RIGHT(val, 3)
        WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('PRD_GOVERNANCE.MASKING.PII') = 'date'
            THEN COALESCE(TO_CHAR(DATEADD(day,
                    (MOD(ABS(HASH(TRY_TO_DATE(val), 'bankorg-dob-shift')), 365) + 1)
                    * IFF(MOD(ABS(HASH(TRY_TO_DATE(val), 'bankorg-dob-sign')), 2) = 0, 1, -1),
                    TRY_TO_DATE(val))), '***')
        ELSE '***'
    END;
Snowflake
CREATE MASKING POLICY IF NOT EXISTS MASK_PII_DATE AS (val DATE) RETURNS DATE ->
    CASE
        WHEN val IS NULL THEN NULL
        WHEN CURRENT_ROLE() = 'DBT_ROLE' OR IS_ROLE_IN_SESSION('PII_READER_ROLE') THEN val
        ELSE DATEADD(day,
            (MOD(ABS(HASH(val, 'bankorg-dob-shift')), 365) + 1)
            * IFF(MOD(ABS(HASH(val, 'bankorg-dob-sign')), 2) = 0, 1, -1),
            val)
    END;

Date of birth

DATE_OF_BIRTH is a VARCHAR in RAW, so the string policy masks it there, and the date policy masks it in staging, where it is a DATE. Both shift the date by 1 to 365 days, forward or back. The shift comes from a salted HASH of the date, so the same date gives the same masked date in RAW and in staging and on every query. A value that is not a date returns ***.

Link both policies to the tag. DBT_ROLE gets USAGE on the database and schema and APPLY on the tag, which is all it needs to tag the columns of its models.

Snowflake
ALTER TAG PII SET MASKING POLICY MASK_PII_STRING;
ALTER TAG PII SET MASKING POLICY MASK_PII_DATE;

GRANT USAGE ON DATABASE PRD_GOVERNANCE TO ROLE DBT_ROLE;
GRANT USAGE ON SCHEMA PRD_GOVERNANCE.MASKING TO ROLE DBT_ROLE;
GRANT APPLY ON TAG PRD_GOVERNANCE.MASKING.PII TO ROLE DBT_ROLE;

Tag the RAW columns

The PII columns of the RAW tables carry the tag in the CREATE TABLE statements.

PRD_RAW.CORE_BANKING
CREATE TABLE IF NOT EXISTS CUSTOMERS (
    CUSTOMER_ID        VARCHAR,
    FIRST_NAME         VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'text'),
    LAST_NAME          VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'text'),
    DATE_OF_BIRTH      VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'date'),
    NATIONAL_ID        VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'identifier'),
    EMAIL              VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'email'),
    PHONE              VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'identifier'),
    ADDRESS_LINE       VARCHAR        WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'text'),
    ...
);

The tag references of the three tables list nine columns:

TableColumnTag value
PRD_RAW.CORE_BANKING.CUSTOMERSFIRST_NAMEtext
PRD_RAW.CORE_BANKING.CUSTOMERSLAST_NAMEtext
PRD_RAW.CORE_BANKING.CUSTOMERSDATE_OF_BIRTHdate
PRD_RAW.CORE_BANKING.CUSTOMERSNATIONAL_IDidentifier
PRD_RAW.CORE_BANKING.CUSTOMERSEMAILemail
PRD_RAW.CORE_BANKING.CUSTOMERSPHONEidentifier
PRD_RAW.CORE_BANKING.CUSTOMERSADDRESS_LINEtext
PRD_RAW.CORE_BANKING.ACCOUNTSACCOUNT_NUMBERidentifier
PRD_RAW.CORE_BANKING.TRANSACTIONSCOUNTERPARTY_ACCOUNTidentifier

Tag the staging columns with dbt

dbt replaces the staging views on every run, and a replaced view loses its tags. The macro apply_pii_masking sets the tag again on every column marked pii in the model yml, and it runs as a post-hook of the staging models.

dbt/macros/apply_pii_masking.sql
{% macro apply_pii_masking() -%}
    {%- if execute -%}
        {%- set object_type = 'view' if config.get('materialized') == 'view' else 'table' -%}
        {%- for column in model.columns.values() -%}
            {%- set kind = (column.config.meta or column.meta or {}).get('pii') -%}
            {%- if kind -%}
                alter {{ object_type }} {{ this }} modify column {{ adapter.quote(column.name | upper) }}
                    set tag PRD_GOVERNANCE.MASKING.PII = '{{ kind }}';
            {%- endif -%}
        {%- endfor -%}
    {%- endif -%}
{%- endmacro %}
dbt/dbt_project.yml
models:
  bankorg:
    staging:
      core_banking:
        +materialized: view
        +database: PRD_STAGING
        +schema: CORE_BANKING
        +post-hook: "{{ apply_pii_masking() }}"

The same nine columns are marked in _core_banking__models.yml, with the value of the tag under config: meta: pii.

_core_banking__models.yml
      - name: email
        description: Email address (PII, masked for roles other than DBT_ROLE and PII_READER_ROLE).
        config:
          meta: {pii: email}

Check the masking in RAW

To check the masking I created ANALYST_USER with the role ANALYST_ROLE. The role can read every table and view in PRD_RAW and PRD_STAGING, is not PII_READER_ROLE and does not inherit it, and the user has no secondary roles.

Logged in as ANALYST_USER, I queried the customers in RAW.

ANALYST_USER
SELECT first_name, date_of_birth, national_id, email, phone
FROM PRD_RAW.CORE_BANKING.CUSTOMERS LIMIT 3;
Output of the query as ANALYST_USER.