skip to content

Why is a column whose values are masked on read not equivalent to a column the user is not authorized to read? Describe concretely how someone querying a table with masked values can still learn the underlying data.

level: seniorimportance: must knowfreq 45%

answer

  1. mask applied at projection, predicates see raw
  2. equality probe = existence oracle
  3. count(*) + ranges = binary search
  4. ORDER BY leaks full ranking
  5. owners, replicas, backups bypass policies

basics

~20 s

Masking changes what is returned, not what the engine computes on. Predicates, joins, ORDER BY and aggregates still evaluate against the raw value, so a user can confirm guesses, binary-search ranges and rank rows. Real authorization means the value never participates in the query at all.

solid answer

~50 s

A masking policy rewrites the **projection**. Everything else — WHERE, JOIN, GROUP BY, ORDER BY, aggregates — still runs on the true value. That makes a masked column an oracle: - **Equality probing:** `WHERE ssn = '123-45-6789'` returns a row or not, confirming a guess. Iterate a candidate list and you extract data. - **Range search:** `WHERE salary > 200000` plus `count(*)` binary-searches an individual's salary in about twenty queries. - **Ordering:** ORDER BY on the masked column reveals the full ranking even though values are hidden. - **Aggregates:** `avg(salary)` over a slice narrowed to one person is that person's salary. - **Joins and side channels:** joining on the raw value links identities; unique-violation errors and error messages add more. So masking defends against shoulder-surfing and accidental exposure by people already trusted with the table. If a principal must not know the value, revoke access to the column — or do not store it there.

code

sql · 5 lines
sql
-- confirm a guess: the predicate runs on the raw value
SELECT count(*) FROM employee WHERE id = 42 AND salary > 200000;

-- rank everyone without ever displaying a salary
SELECT id, name FROM employee ORDER BY salary DESC;

go deeper

for a junior

State the key fact: masking changes what is displayed, and a WHERE clause still compares the real value.

for a middle

Give at least two concrete inference techniques — equality probing and range binary search — and name revoking access as the real fix.

for a senior

Frame the threat model explicitly, cover aggregate and ordering leakage plus bypass paths, and pair masking with privileges, query-surface limits and auditing.

for a principal

Position masking within a disclosure-control strategy: what is stored at all, tokenisation, aggregate group-size policy, detection, and which compliance claims you are willing to make about it.

## Where the mask is applied In every mainstream implementation, a masking policy is an expression evaluated when the column is *output*. The optimizer still sees the underlying column for predicate evaluation, index lookup, join keys and aggregation. That single fact is the whole answer: masking hides the display, not the computation. ## The inference toolkit **Equality oracle.** Any predicate on the masked column tells you whether a value exists. With a candidate list — a leaked breach dump, a checksum-constrained ID space, a salary band — existence answers reconstruct the data. Even without candidates, `LIKE` prefixes walk the value character by character. **Comparison and binary search.** For numeric or date columns, `count(*)` under range predicates locates a value to full precision in logarithmically many queries. Twenty round trips resolve a salary to the nearest currency unit. **Ordering.** ORDER BY on the masked column, with an unmasked identifier in the projection, yields the exact rank order of every subject. For many attacks rank is as useful as value. **Aggregation.** Aggregates are computed pre-mask, so min, max, sum and avg over a narrow slice leak the value directly. Classic statistical-database attacks apply: two overlapping aggregates differing by one row reveal that row. **Join and correlation.** Joining the masked column to another table on the raw value links records without ever displaying the value — enough to re-identify a supposedly anonymous dataset. **Side channels.** Unique-constraint violations on insert confirm existence. Type-cast and division-by-zero errors can be coerced into embedding values in messages. Index-driven timing differences leak selectivity. And a CSV export, a materialised copy, or a query run by a role exempt from the policy simply returns the truth. **Bypass by design.** Object owners, superusers, replication roles and backup processes typically ignore masking policies. So does anyone reading the data files, a physical backup, or a logical replication stream — masking lives in the SQL layer only. ## What follows for design 1. **Never use masking as the only control against a hostile principal.** Its correct threat model is "trusted employee who should not casually see PII on a support screen", not "attacker with ad-hoc SQL". 2. **Pair it with real restriction.** Revoke SELECT on the column or table and expose a view without it. Then no predicate can reference what is not in the namespace. 3. **Deny ad-hoc SQL over masked columns.** If access is only a parameterised application query that never accepts a predicate on the sensitive column, the oracle narrows considerably. 4. **Constrain the query surface.** Aggregate-only access with minimum-group-size rules, forbidding ORDER BY on the column, and rate limiting are the statistical-disclosure controls this problem has always required. 5. **Audit and detect.** Log queries that filter on masked columns; a burst of equality probes is a strong signal. 6. **Prefer not storing it.** Tokenisation or write-time truncation removes the oracle entirely because the value is not in the relation. ## How to say it in an interview "Masking is a projection-time transformation, so it is a usability and blast-radius control, not an authorization boundary. Anyone who can write predicates against the column can extract it by inference. Authorization is column privileges, restricted views, or not storing the value at all."

  • Which controls actually close the inference channel?
    Removing the column from the principal's namespace: revoke SELECT on it and expose a view without it, so no predicate can name it. Failing that, restrict the query surface — no ad-hoc SQL, parameterised queries that never filter on the column, minimum aggregation group sizes, and rate limiting. Auditing predicate use on sensitive columns turns the residual risk into a detection problem.
  • Does dynamic masking help at all against someone who steals a backup?
    No. Masking is applied by the SQL execution layer at projection time; a physical backup, a data-file copy, or a logical replication stream carries the true values. Protecting stolen media is the job of encryption at rest, and protecting the copy path is the job of privileges and auditing.

Like blacking out an answer on a quiz sheet while still letting someone ask 'is it higher than 40?' as often as they like — the redaction is intact and the answer is theirs anyway.

saying these in an interview costs you the question

  • Treating a masking policy as a substitute for revoking column access
  • Assuming predicates and joins see the masked value
  • Forgetting aggregates are computed before masking
  • Believing masking protects backups, replicas or file-level copies
  • Ignoring that owners and superusers usually bypass policies

context

resolving links…