Explain Dynamic Data Masking in Databases

62 / 66

Snowflake

Dynamic Data Masking

Conditionally obscure sensitive column values based on the querying user's role.

A masking policy shows different values to different roles, from the same stored data. Nothing is duplicated and nothing is changed on disk.

Dynamic data masking is like a document that shows full detail to a manager but automatically redacts sensitive fields with black bars when the same document is handed to an intern — the underlying page never changes, only what different readers are permitted to see.

Key Concepts

    CREATE MASKING POLICY ssn_mask AS (val STRING) RETURNS STRING ->
      CASE
        WHEN IS_ROLE_IN_SESSION('HR_FULL') THEN val
        ELSE 'XXX-XX-' || RIGHT(val, 4)
      END;

    ALTER TABLE employees MODIFY COLUMN ssn SET MASKING POLICY ssn_mask;

Now the same query returns different results depending on who runs it:

    HR_FULL     ->  123-45-6789
    ANALYST     ->  XXX-XX-6789

Use IS_ROLE_IN_SESSION, not CURRENT_ROLE. CURRENT_ROLE() returns only the role currently active. Someone holding HR_FULL through a role hierarchy while using a different role sees masked data and concludes the policy is broken.

IS_ROLE_IN_SESSIONCURRENT_ROLECURRENT_ROLE()HR_FULL

IS_ROLE_IN_SESSION accounts for inherited roles, which is almost always what you meant.

IS_ROLE_IN_SESSION

It cannot be bypassed. The policy is attached to the column, so it applies everywhere the column is read — directly, through a view, inside a join, in a subquery, from any client.

That is the property that makes it worth using rather than filtering in views: there is no query that gets around it and no view somebody forgot to update.

One policy covers many columns. The same policy can be attached to every matching-typed column across the account:

    ssn_mask -> employees.ssn, contractors.ssn, applicants.ssn

So "PII is masked this way" is one object to review and one to change, which is exactly what an auditor wants to see.

Masking is applied after filtering, and that matters. A user who cannot see the real value can still filter on it:

    SELECT COUNT(*) FROM employees WHERE salary > 2000000;

The masked column still filters on the true value, so a determined user can narrow down individual figures with repeated queries. If that matters, the rows themselves must be restricted with a row access policy — masking alone is not confidentiality.

The tagging shortcut. Rather than attaching policies column by column, tag columns as PII and attach the policy to the tag. Every column carrying that tag, now or later, is masked automatically — which is how this scales past a few tables.

What the interviewer is probing.1. "How does masking differ from a row access policy?" Probing: the distinction. Stalls: "Not much." Moves up: masking changes column values for unauthorised roles; row policies remove rows entirely.

2. "Where is the policy evaluated?" Probing: the mechanism. Stalls: "At load." Moves up: at query time, on the column, so the stored data is unmasked and one copy serves all audiences.

3. "What is the usual policy body?" Probing: the shape. Stalls: "Returns null." Moves up: a CASE on CURRENT_ROLE returning the real value for privileged roles and a masked or hashed value otherwise.

4. "What is a limitation to raise?" Probing: depth. Stalls: "None." Moves up: a masked column used in a join or filter can behave unexpectedly, and masking is not a substitute for not storing data you do not need.