Dynamic Data Masking
also called Query-Time Masking, Policy-Applied Redaction
Redacting a column's value as the query returns it, based on who is asking - so one physical table serves cleared and uncleared audiences, while every object derived from it escapes the rule.
An analyst runs SELECT phone FROM customers and gets ***-***-4417. Her colleague in the fraud team runs
the same query and gets the full number. The table holds one value; the policy decided what each of them
was allowed to see at the moment of the read.
This is the cheapest column-level control in a modern warehouse and the one most often believed stronger than it is. The rule attaches to an object, not to a value, so protection stops at that object's edge. Build a table from it under a privileged identity and the raw value is written out in the clear, with no policy, no error and no finding against the original table.
Why it matters
The alternatives are worse in specific ways. Masking inside each BI tool puts the rule in as many places as there are tools and leaves it absent from every notebook, client and scheduled export. Masking by publishing a redacted copy costs a second pipeline, a second storage bill and a freshness gap. A query-time policy puts one rule where every query passes.
The common access request is not "give me the table", it is "give me the table without the sensitive field", and a policy satisfies that in seconds rather than the 3 to 10 days a copy-and-publish takes.
Implementation patterns
- Attach the policy to a tag, not to a column list. Tag columns as phone, email or national identifier and bind the policy to the tag, so a new table carrying a tagged column inherits the rule on creation.
- Write the policy as a function of the requester's attributes — role, team, purpose on the active grant — not a list of exempt usernames, which goes stale within a quarter.
- Mask to a shape that preserves use: last four digits, domain-only email, date rounded to month. A policy returning a constant breaks dashboards and gets exempted.
- Enumerate the derived-object paths and close them: materialised views, CTAS tables, scheduled and BI extracts refreshed under a service identity. Either that identity is masked too, or the output carries the tag, or the path is blocked.
Industry example
Declarative column masking shipped in mainstream relational engines from 2016, and cloud warehouses added equivalent policy objects over roughly 2020 to 2022, usually bound to column tags so classification drives enforcement. What follows is consistent: the policy lands on base tables quickly because it is a configuration change, and the derived layer is found months later carrying unmasked values, because those objects were built by privileged pipeline identities before anyone tagged them.
Failure scenarios
- Derived-object bypass. A mart exposes the clear value to everyone with access to the mart while the base table's audit log stays clean. This is the dominant real-world failure.
- Masked value used as a join key. Equality survives masking, so a deterministic redaction still links records and can be rebuilt by frequency analysis when the input space is small.
- Policy on the column, nothing on the tag, so the next table from the same source is unprotected.
- Cache effects mistaken for a defect. The predicate makes the query per-identity, warehouse spend rises 20% or more on a busy dashboard, and someone disables the policy to "fix performance".
Trade-offs
| Choose | Gains | Pays |
|---|---|---|
| Query-time policy | One rule, one place, instant to change | Derived objects bypass it; no shared result caching |
| Physically masked copy | No bypass surface at all | Storage, a second pipeline, a freshness gap |
| Tokenisation at ingest | The estate never holds the value | A vault on the path; equality still leaks |
When not to use it
When the entire consuming population is uncleared, publish a masked copy and stop. A policy exists to serve a mixed audience; with a uniform audience it adds a bypass surface and a per-query cost for nothing. This is the commonest over-engineering in column-level security.
Do not use it to meet an obligation that the data not be present in an environment. Masking leaves the value in the table, so minimising what an environment contains is answered by tokenisation or by not loading the column at all.
Interview question
Q: You put a masking policy on national_id across 40 tables and the audit comes back clean. Six
months later a red team reads clear national identifiers from a marketing mart. How did that happen, and
what do you change?
What a strong answer covers: that the mart was built by a pipeline identity the policy exempted, so the value was written out in the clear and the new object carried no policy; that tag-based binding would have applied the rule on creation; and that the audit was clean because it audited the base table, which is the wrong object.
Quick check
Quiz: A masking policy is correct on the base table and a derived mart returns clear values. Which change closes the class of problem rather than this instance? Bind policies to column tags and propagate tags to derived columns, so inheritance happens at creation.
Flashcard: What does a query-time masking policy protect, and what does it not? — The object it is attached to, for identities it does not exempt. Nothing built from that object by a privileged identity.