Chapter 13 — Data systems

Lesson map

Lesson 13 published. Map, example, EX037.

flowchart TD
  N0["Learning outcomes"]
  N1["13.1 Data minimization"]
  N2["13.2 Authorization, not injection recipes"]
  N3["13.3 Synthetic data"]
  N4["13.3b Row-level and column-level controls"]
  N5["13.1b Masking pitfalls"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6
  N6 --> EX["Worked example"]
  EX --> Q["Exercise EX037"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["13.1 Data minimization"]
  N2["13.2 Authorization, not injection recipes"]
  N3["13.3 Synthetic data"]
  N4["13.3b Row-level and column-level controls"]
  N5["13.1b Masking pitfalls"]
  N6["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> N6 --> Q["EX037"]

[Learning outcomes]
    |
    v
[13.1 Data minimization]
    |
    v
[13.2 Authorization, not injection recipes]
    |
    v
[13.3 Synthetic data]
    |
    v
[13.3b Row-level and column-level controls]
    |
    v
[13.1b Masking pitfalls]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX037]

Learning outcomes

You will (1) scope database assessment without extracting personal data; (2) test authorization at the query layer; (3) design synthetic datasets for labs.

13.1 Data minimization

Authorized database work uses synthetic rows or masked copies. Exporting production personal data into a student laptop is a privacy incident, not a finding. The ROE must state: no SELECT of columns tagged personal, no full-table dumps, row limits and a destroy-by date for any temporary extract.

Row limits and destroy-by dates turn temporary extracts into controlled objects rather than souvenirs. Tagging personal columns gives the query tool something to refuse automatically. A student laptop with a production dump is a breach waiting for a lost bag. Sam's ROE checklist fails the kickoff if any of those clauses is missing.

13.2 Authorization, not injection recipes

The engineering question is usually "can role R read table T?" Test that with the granted role. Teaching SQL injection payloads is out of this book's safety boundary; if injection is in scope for a professional engagement, it is under a separate method line and disclosure plan, not a homework exercise.

Role tests answer the client's real question: who among their staff can see which tables. Payloads belong outside this course because they teach harm without teaching the engineering of access control. When a professional ROE includes injection, it is a named method with its own disclosure path, never a lab exercise. Sam files authorization gaps as findings about roles and grants.

13.3 Synthetic data

Generate deterministic synthetic customers with faker-like rules or fixed seeds so labs reproduce. Never copy a production backup into the course repository.

Synthetic case — Riverstone Transit. Sam needed fare-product rows for a report test. Ops provided a synthetic snapshot with product ids and prices only — no passenger names — and a 48-hour destroy tag.

Fixed seeds mean two students and a CI job see the same customers and the same failing checks. Production backups bring privacy duty and irreversible exposure if the repo leaks. Sam generates data in the lab setup script and commits only the generator, never a dump.

13.4 Errors and limits

Masking that leaves unique rare values can still identify people. Prefer synthetic generation over imperfect masking when teaching.

A rare disease code or an unusual job title can re-identify someone even after names are stripped. Synthetic rows avoid that argument entirely for teaching. When a client still demands masked extracts, Sam waits for privacy sign-off and keeps the files on client-controlled hosts and records the destroy-by date in the engagement log. Teaching labs never receive even masked production rows from any client.

13.3b Row-level and column-level controls

Modern warehouses offer row and column policies. Assessment questions become: does role R see column C? Does row filter F apply? Test with the role itself. Document the policy engine version; "we ran SELECT *" against an admin role proves little about analyst exposure.

Admin SELECT * shows what an admin sees, which is the wrong question for analyst exposure. Running as role R with the policy engine's version recorded makes the result match production behaviour. Column denies and row filters fail differently, so both need their own checks. Sam stores the role name on every such finding.

13.1b Masking pitfalls

Replacing names with initials or shuffling rare zip codes still leaves quasi-identifiers. For teaching and most assessments, synthetic generation with a fixed seed is simpler and safer than arguing about imperfect masks. If the host insists on masked production extracts, require a privacy review and keep the extract off student laptops.

Quasi-identifiers combine: a birth year, a depot and a rare job title can point to one staff member. Fixed-seed synthesis sidesteps the debate and still stresses authorization logic. Masked extracts that must be used stay off personal devices and under the privacy review's retention rules. Sam prefers to demonstrate the same finding on synthetic rows whenever the client allows it.

Exercises

• EX037 — ROE data clause. Write a data-handling clause for a Postgres assessment. Rubric: 3 = no PII columns, row limit, no dumps, destroy-by, synthetic preferred; 2 = three; 1 = one; 0 = none.
• EX038 — Authz test. How do you test whether role analyst can read passengers without using an injection technique? Rubric: 3 = connect as analyst, attempt SELECT, record grant/deny; 2 = idea without role; 1 = vague; 0 = injection.
• EX039 — Synthetic. Give three fields safe for a synthetic fare table and two that must never appear in the course dataset. Rubric: 3 = e.g. product_id, price, zone vs name, card_number; 2 = partial; 1 = weak; 0 = none.

Synthesis

Minimize data, test roles, synthesize for teaching. The database is not a trophy store.

Continue studying — chapter 13 of 50

← Chapter 12 (draft) · Course chapter index · Chapter 14 (draft) →