Every Door Was Locked

Wednesday 9 September, 11:40. A retail group is evaluating us for a white-label partnership and their security questionnaire is 214 questions long. We are fine until 7.3:

“How many individuals in your organisation are able to identify a natural person from your analytical data store?”

The honest first answer is 4. Six months ago we ran a masking project: email, phone, full_name, street and birth_date on raw_customer carry a policy tag, and exactly four principals hold the Fine-Grained Reader role — three data engineers and me. Everything downstream reads a SHA-256 hash. The mart the analysts live in is literally called mart_orders_anon.

Before answering, I asked our senior analyst to try to break it using only the access she already has. She came back in an afternoon with a spreadsheet of 283,916 customers — name, postal address, birth year — 94.64% of everyone we have. She did not touch a tagged column. Nothing she ran appears in an audit log as anything other than allowed.

The real answer to 7.3 is 96: the 31 people with warehouse access, and 65 more who hold only a BI licence and can do it without touching the warehouse at all.

BigQuery gives you nine masking rules. The choice looks like a slider from readable to safe. It is not: it is a choice about what the column can still be used for, and Google publishes the trade-off in a column of its own documentation called joinability.

Two sentences decide most of this lesson. “SHA-256 is a deterministic hashing function; an initial value always resolves to the same hash value.” And the security note on the same row: the rule is rated moderate because it is “susceptible to rainbow table attacks, known-plaintext attacks, and statistical analysis.”

A deterministic mask is not a redaction. It is a foreign key with the label filed off.

There is a rule with no such property — Random hash, salted per query. It is also the one rule that “is only supported with data policies that are set on columns, not policy tags.” The safe option cannot be attached to the mechanism we govern with: a taxonomy tags a concept once, centrally, and the price is that the concept can only ever be masked the joinable way.

The review’s argument was “can SHA-256 be reversed?” It cannot, and it does not need to be. An email address is not a secret with 256 bits in it; it is a name, in one of about six formats, at one of about eight providers. You do not reverse the hash, you enumerate the inputs.

Attacker’s dictionary: the 100 commonest first names × the 140 commonest surnames × 8 consumer providers × 4 name-derived templates, with the birth-year suffix swept across 54 years and the digit a provider appends when a mailbox is taken. 9,408,000 candidate strings. Hashing all of them recovered 170,174 of 300,000 addresses — 56.72% — in about seven seconds of single-threaded Python.

Scaled honestly: a realistic German list of 3,000 first names and 30,000 surnames makes the space 41,040,000,000 strings — about four hours on one core, in Python. A GPU does SHA-256 at billions per second. Assume a deterministic hash of a human identifier is free to reverse.

And it is the expensive path that adds almost nothing. Of the 170,174 addresses recovered, only 8,291 belong to people who were not already identifiable by the next rung — 2.76% of the customer base. The review spent its whole afternoon on the rung that matters least.

You cannot post a parcel to a hash. Every commerce company has a table with a real name and a real street address in it, because a carrier needs one, and that table is never called “the customer table”, so nobody tags it. Ours is raw_shipping_label. The anonymised mart carries order_id, because you cannot analyse orders without it, and order_id is not personal data by any definition anyone would write down.

select l.recipient_name, l.street, l.postcode,
       o.birth_year, o.plz3, count(*) as orders, sum(o.price_cents)/100 as spend
from   mart_orders_anon      o
join   raw_shipping_label    l using (order_id)
group  by 1,2,3,4,5

One join identifies 99.19% of everyone who has ever bought from us. The support-ticket row is the one to sit with: a policy tag protects a column, not a substring of a free-text column, and roughly two in five people who write to us include their own address in the message. There is no masking rule for prose.

We are a marketplace, so much of our data is published by us on purpose. Every one of the 642,299 sold items has a listing page with the seller’s public handle, city, other items and sold history. Any row in the seller-payout mart carries listing_id, and listing_id is a URL. That re-identifies 23,084 sellers — every seller with a sale — and requires no account, no query and no employment.

Our items have quantity one. A unique item is a natural key. A shop selling 4,000 identical white T-shirts leaks nothing when it publishes one; a shop where every listing is one object publishes a join key with every page.

Strip the mart further — drop the hash, drop order_id, drop everything that looks like an id — and you are left with what the analysts asked for: postcode district, birth year, gender, and what people bought.

GDPR Recital 26 names this: identifiability is tested by “all the means reasonably likely to be used, such as singling out … by the controller or by another person”, weighing “the costs of and the amount of time required for identification.” A GROUP BY costs nothing.

The textbook fix is k-anonymity. Here is its bill, for k = 5:

To get every buyer into a group of five, 800 postcode districts collapse into 8 regions. That is not a privacy setting; that is the deletion of the regional demand analysis the table was built for. The privacy knob and the usefulness knob are the same knob.

A report cell is an aggregate, and an aggregate is safe — unless it is over one row.

Across the three, 87,276 distinct customers (29.09% of the register, 33.76% of buyers) appear in at least one cell describing exactly one order. A real one from the data: week 7, district 044, category coats, 1 order, €11.28 — a size-34 coat bought by one person born in 1989, and one join away she has a name.

The brand report is the worst, and for a nameable reason: about 6% of listings carry a brand string a seller typed by hand. The free-text field nobody governs is the field that singles people out. 65 people can read these reports with no warehouse account at all.

Ordered by effort, cheapest first — and there is no relationship at all between effort and result. The rung the review argued about is by far the most expensive and reaches fewer people than a single join.

The honest scorecard, because the answer is not that masking is theatre:

That third row is the one to keep. Every rung requires intent. Masking converted an accident into an attack, which is genuinely valuable — most real breaches at our size are accidents. What it could never do is reduce the population who can identify a customer, because that population was never gated on the column.

You cannot access-control an inference. A permission system decides who may read a value. Identity is not a value in our data; it is a property of the combination, and combinations are what analysts are paid to make.

A policy tag lives on a column of a table. A view has no schema of its own, so a tag resolves through it at query time — but CREATE TABLE AS SELECT does not copy column-level policy-tag metadata. dbt’s adapter repo carries an open request to fix exactly this: “BigQuery policy tags set at a base layer propagate to views … but do not propagate to table materializations … downstream tables end up without tags unless every model re-specifies policy_tags in schema YAML.”

raw_customer → stg_customer → int_customer_enriched ‖ dim_customer → mart_orders_anon → rpt_*
   TAGGED         view/TAGGED       view/TAGGED       ‖   no tag        no tag           no tag
                                                      ↑ the tag frontier

Of 41 models (20 views, 21 tables), 26 carry a column derived from a tagged one; 7 are protected and 19 hold untagged copies, 11 of them a direct identifier.

This is Lesson 21’s question wearing a compliance hat: the answer is fully determined statically, before anything runs, and nothing in the build computes it. And note what decides where the frontier falls — the materialisation setting. Somebody changed dim_customer from a view to a table to make a dashboard faster, in a pull request about latency, and silently ended the protection for everything downstream. Lesson 16’s bundled change in a new coat.

1 · Control the output, not the column. A minimum group size on what leaves the warehouse is the only measure that touches all five rungs. Applied to the regional report, the arithmetic is the argument:

So the recommendation is not “add a threshold”. It is: publish at region × month, and route the district-level question through a named process.

2 · Count the population, not the permissions. 31 people have row-level access to a customer-grain table. The two jobs that genuinely need one identified customer at a time — support lookup, fraud investigation — are lookups, and a lookup belongs in an application with an audit trail and a reason field, not in a dataset grant that also permits select *.

3 · Break the join keys, not just the values. A shared deterministic pseudonym is a foreign key. Give consumers that do not need to join back a salted, per-consumer pseudonym; keep order_id out of anything called anon.

4 · Make the tag frontier a build step. Tags are not inherited, so inheritance has to be computed: walk the column graph from the tagged sources and fail the build when a model produces a derived column with no tag declared.

5 · Write down what the data is for. Every rung above is legal, logged and allowed. The only thing separating the analyst measuring returns from the analyst assembling a spreadsheet of addresses is purpose, and purpose is the one thing the stack records nowhere.

1. Which tables that we do not call “customer tables” contain a name or an address, and who can read them? Start with logistics, support, payouts and anything a vendor sends back.
2. What masking rule did we choose, and is it joinable? If it is a plain hash we have a pseudonym, not a redaction — say “pseudonymised” out loud; it is a word with legal consequences.
3. Which of our models carry a column derived from a tagged one, and can anything compute that list? If the answer is a person’s memory, the frontier is wherever that memory stopped.
4. Do any dashboards show cells with a handful of rows behind them? A minimum group size is the only control that reaches people with no warehouse account.
5. When someone looks up one customer, do we record why? Purpose is the only signal that separates these five rungs from ordinary work.

1. Generate 50,000 people: first name from a list of 100, surname from a list of 150, postcode district, birth year, email as first.last@provider. Store sha256(email) beside the address.
2. Write the dictionary loop — every first name × surname × provider, hashed, looked up. Print the share recovered and the time. Fifteen lines.
3. Make a shipping-label table (order_id, recipient_name) and join it to your “anonymised” orders on order_id. Compare with step 2.
4. Drop every identifier and run select count(*) from (select plz3, birth_year, gender, count(*) n from … group by 1,2,3) where n = 1. That number is how many people your anonymous table names.
5. Build one report at district × week × category, count the cells with one order behind them, rebuild at region × month and count again. You have measured the trade you are making.

Access control decides who may read a value. Nobody needs to read a value to know who someone is — they need any combination that is unique, and in a marketplace where every item is one object, almost every combination is. Control the resolution of what leaves, and record the purpose of anything that leaves at row grain; the column permission is the smallest part of the job.

This is the fourteenth failure shape the series has collected, and the first that is not about a wrong answer at all. Events (06–10), the constant (11), the drift (12), the unwatched (13), the reversible (14), the unreproduced (15), the bundled (16), the transient (17), the extremal (18), the referential (19), the plural (20), the remote (21), the absent (22) — and now the permitted. Every earlier shape is a story about a check that did not fire. Here every check fired correctly, every policy was enforced as written, and every access in the audit log reads allowed, because each one was. The control was specified over the wrong object: it governs columns, and the thing we meant to protect is an inference. No amount of correct enforcement fixes a specification that names the wrong noun.

Vocabulary

One deterministic generator (no random seed, no wall-clock input) builds 300,000 customers and 642,299 orders over 90 days (€24,953,108.18 of GMV, average order €38.84), then measures each rung against that population. The artifact ships the generator as an appendix; that copy was extracted from the rendered HTML, re-run in a clean directory and diffed key by key — 13 top-level keys, 256 scalar values, 0 differences, with the three wall-clock timings declared unstable and excluded.

• The dictionary attack is actually run — 9,408,000 SHA-256 computations against the real hashes, not an estimate. Seconds vary run to run; the candidate count and the 56.72% coverage reproduce exactly.
• The join, the singling-out count, the report cells and the grain ladder were re-run as SQL in DuckDB and asserted equal to the Python answers: 256,423 identified by the join, 23,386 singled out on three columns, 20,039 cells of one, 3,360 cells at region × month, GMV to the cent.
• Asserted in the generator: the survivor buckets are disjoint and sum with the cracked count to 300,000; the singling-out ramp is monotone; a coarser grain always passes more cells; no container path leaks into the shipped source.
• The masking-rule table, the joinability column, the SHA-256 determinism sentence, the rainbow-table rating and the “Random hash … not policy tags” restriction are quoted from Google’s BigQuery documentation. The non-inheritance of policy tags through CREATE TABLE AS SELECT is quoted from the open dbt-adapters issue requesting it. GDPR wording is Recital 26 and Art. 4(5).
• Two inputs are declared inventories rather than measurements: the 41-model project and the principal counts (4 / 31 / 96). The name dictionary is deliberately small, which makes mailbox collisions more common than reality; the attacker’s digit sweep absorbs them, and a real attacker has a far larger list, so 56.72% is if anything a low estimate.

Previously: 11 which layer a rule belongs in · 13 what leaves the warehouse and comes back · 14 the erasure registry and the copy map · 19 the identity graph itself · 20 who gets a view instead of the fact table · 21 what depends on this column.