skip to content

What is dynamic data masking in a relational database — where the engine rewrites column values as they are read — and how does it differ from storing the data already redacted or tokenised?

level: juniorimportance: must knowfreq 50%

answer

  1. stored value untouched, projection transformed
  2. policy on column vs CASE in a view
  3. partial / full / hash / format-preserving
  4. static masking = masked copy for non-prod
  5. read-write round-trip persists the mask

basics

~20 s

Dynamic masking keeps the true value stored and applies a masking function at query time based on who is asking, so privileged roles still see the original. Write-time redaction or tokenisation changes what is stored, so the original cannot leak from that table at all.

solid answer

~50 s

**Dynamic (on-read) masking** leaves the real value in the table and transforms it in the result set: a card number returns as `**** **** **** 1234` for a support role and unchanged for a fraud analyst. The rule is declared once beside the column as a masking policy, or emulated with a view containing a CASE on the caller's role, so every client inherits it without application changes. **Write-time redaction** changes what is stored — you persist only the last four digits. **Tokenisation** stores a surrogate whose real value lives in a separate vault. Both are irreversible from that table, which is the point: a compromised query path cannot leak what is not there. Masking is a presentation control that reduces casual exposure for people already allowed to query the table. Redaction and tokenisation are storage decisions. Masking never removes the need to restrict who may read the column.

code

sql · 8 lines
sql
CREATE VIEW customer_support AS
SELECT id,
       name,
       CASE WHEN pg_has_role(current_user, 'pii_reader', 'MEMBER')
            THEN ssn
            ELSE 'XXX-XX-' || right(ssn, 4)
       END AS ssn
FROM customer;

go deeper

for a junior

Say clearly that the true value is still stored and only the returned value changes, and contrast with redaction that changes what is stored.

for a middle

Add how it is implemented (column policy or role-aware view), name the common masking functions, and mention the read-then-write hazard.

for a senior

Frame it as a defence-in-depth presentation control, pair it with column privileges, and separate dynamic masking from static masking for non-production copies.

for a principal

Discuss where redaction should live across the estate — not storing, tokenising, or masking — and the operational cost of each, including auditability of who is exempt.

## The core idea Dynamic data masking means stored bytes are untouched but the value a session receives is transformed by a masking function chosen by *who is asking*. The same query from two roles returns different strings for the same row. Application code does not change; the rule sits beside the data. ## Four places redaction can live 1. **On read (dynamic masking).** Real value stored; masked in the projection per role. Reversible for exempt roles. 2. **On write (redaction/truncation).** Store only `1234`. Irreversible, smallest blast radius, but the data is gone for every future use case. 3. **Tokenisation.** Store a surrogate; a separate vault maps token to value. The database never holds the secret; joins still work because the token is stable. 4. **Static masking.** Produce a masked *copy* — the standard way to build non-production datasets. The clone contains no real values, so it can live under weaker controls. ## Typical masking functions Full mask (fixed literal), partial mask (keep last four), default-by-type (zero, epoch date, empty string), NULL, hash, random value from the same domain, and format-preserving masks that keep length and character classes so downstream validation still passes. ## How it is expressed Engines that support it attach a policy to a column: the policy body is an expression over the column value plus session context (current role, group membership), and the engine substitutes it during projection. Where no policy feature exists, the same effect is built with a view that applies CASE on the current role, plus revoking direct SELECT on the base table. ## What it is good for Cutting accidental exposure for staff who legitimately query the table, keeping full values off support screens and ad-hoc tooling, and shrinking the population that ever sees raw PII without maintaining a parallel schema. It is uniform: every driver, BI tool and console session gets the same treatment because enforcement is in the engine, not in one application. ## What it is not It is not an authorization boundary. Predicates and joins usually evaluate against the *raw* value, so a masked reader can still probe. Highly privileged roles and the object owner typically bypass policies. And it does not reduce what an attacker who steals the data files gets — the true values are still stored. ## The classic bug A masked reader who also has UPDATE can read `***-**-1234`, edit an unrelated field, and write the whole row back — persisting the mask as the real value and destroying data. Masked roles should be read-only, or writes must never round-trip masked columns.

  • A support agent reads a masked SSN, edits the phone number on the same screen, and saves. What can go wrong?
    If the application sends the whole row back, it writes the masked string into the real SSN column and the true value is lost. Masked roles should have no UPDATE on the masked column, or the write path must send only changed fields. This is one of the most common production incidents caused by on-read masking.
  • How would you build a test dataset from production without exposing PII?
    Use static masking: run an extract that applies masking functions during the copy so the clone physically contains no real values. Keep the mapping deterministic where referential integrity matters, and never rely on dynamic masking for this — a dynamic policy protects only the query path, while a restored dump of the real database carries the raw data.

Like a photocopy with a marker through the account number: the original in the filing cabinet is unchanged, and whoever holds the cabinet key still reads it.

saying these in an interview costs you the question

  • Calling dynamic masking encryption — the stored value is plaintext
  • Assuming a masked column is safe to expose to untrusted or ad-hoc query access
  • Thinking masking protects stolen backups or data files
  • Giving a masked role UPDATE rights on the masked column
  • Believing masking always applies — owners and superusers usually bypass it

context

resolving links…