Chapter 22 · Security, Privacy, Row/Column Policies, Masking, and Least-Privilege Analytics
PII Classification, Purpose Limitation, Retention, Consent, and Right-to-Delete in Historical Warehouses
Classify synthetic PII, bind access to declared purpose and consent policy, execute an auditable erasure workflow, and reconcile privacy actions with historical facts, immutable raw evidence, exports, and backups.
Learning outcomes
Classify direct, contact, pseudonymous, and ordinary business attributes before applying policy.
Separate purpose limitation, consent, retention, and erasure into explicit governable decisions.
Reconcile a privacy deletion with historical warehouse facts without fabricating a universal legal rule.
Handle active raw/export copies and immutable backups as distinct technical cases.
Prove erasure replay safety and restoration behavior with a deletion manifest and tombstone.
Chapter 22 begins from Chapter 21's certified warehouse/domain truth: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit. The canonical fact grain remains one current paid order line. The governed metrics and conformed dimensions are not redefined. This chapter adds identity, authorization, privacy, copy-control, and audit mechanisms around those same assets. A security control that changes revenue merely because a different user queried it is a modeling bug unless the metric contract explicitly defines a security-scoped population.
Runtime executed for this chapter: Python
3.13.5 and SQLite 3.46.1.
Environment: local/in-process, synthetic,
no-cost. Storage: one disposable SQLite file
plus JSON evidence. Security engine: SQLite
does not provide a warehouse-style role/RLS/CLS subsystem, so
the lab implements a deterministic policy evaluator and
secure-view simulation while keeping the policy semantics
vendor-neutral; PostgreSQL/cloud-warehouse row policies are
referenced only as product-specific examples.
Token key: supplied at runtime through
ATLASMART_TOKEN_KEY; no production secret is
embedded in SQL or source.
Time/currency: existing UTC and USD metric
semantics remain unchanged. Privacy: all
names/emails are synthetic. The erasure workflow is a technical
teaching model, not legal advice or a universal retention rule.
1. The problem: reproducible history can conflict with privacy obligations
Chapter 14 treated raw evidence as immutable for replayability. That is an engineering objective, not permission to retain personal data forever. PII classification identifies data whose handling requires stronger controls. Purpose limitation constrains use to approved purposes. Retention defines how long copies remain. Consent is one possible policy/legal signal for particular processing; it is not the only lawful basis in every jurisdiction. A right-to-delete/erasure workflow is a governed process for removing or making personal data unavailable when applicable.
The technical question is how to execute a verified AtlasMart request without destroying financial controls or pretending that backups can always be edited in place.
2. Classify before deciding controls
| Field | Classification in this lab | Default treatment |
|---|---|---|
| customer_name | Direct identifier | Restricted; exclude from ordinary marts |
| Contact identifier | Restricted; masked/denied unless purpose requires clear value | |
| durable_customer_id | Pseudonymous identifier | Restricted; can preserve analytical linkage but remains sensitive |
| segment | Business attribute | Internal, subject to purpose/context |
Classification does not itself grant access. It supplies input to authorization, retention, export, and incident policies.
3. Purpose and consent change allowed use, not historical truth
The marketing policy returns only rows with
marketing_consent=1. That rule controls this
campaign-analysis purpose; it does not delete past paid order
facts or redefine enterprise revenue. Finance does not need
direct identifiers at all. The privacy operator may temporarily
need direct identifiers to execute a verified request. Each
purpose therefore has a different minimum field set.
marketing_campaign_analysis -> token, segment, geography_code, masked_email restriction: no direct name/email; consent evaluatedfinance_reporting -> aggregate revenue/cost/profit restriction: no customer direct identifiers requiredprivacy_request -> direct identifiers only for verified case restriction: case-bound and auditedpipeline_processing -> governed raw/integration fields restriction: non-human service identity; no ad-hoc BI export4. Controlled erasure: what changes and what remains
ERASE-20260921-004before direct identifiers: source_customer_id=C004, customer_name=Dara Studio, email=dara@example.testafter direct identifiers: source_customer_id=NULL, customer_name=[ERASED], email=NULLtoken_vault mapping: removedactive raw generation: rewritten under authorized privacy exception; generation 1 -> 2active export copy: sanitizedimmutable backup: quarantined_pending_expiry (not falsely reported deleted in place)restore-overlay test: PASS; erasure tombstone reapplied before restored data is releasedrerun same case: replay_ignoredwarehouse controls: unchanged at 10 lines / 8 orders / 12 units / 820 / 495 / 325 USDThe facts keep the pseudonymous durable ID so historical measures remain reconcilable. The direct source ID/name/email are removed for C004, the token mapping is deleted, and the active raw generation is rewritten as a deliberate privacy exception. This is an explicit migration from the earlier “immutable raw” operating norm, not a silent contradiction. The manifest stores before/after hashes rather than retaining erased clear text.
5. Backups are a separate lifecycle
An immutable backup is not falsely marked “erased” if it cannot
be rewritten. The lab marks it
quarantined_pending_expiry and records an explicit
retention/legal-policy dependency. If restored, the erasure
tombstone is reapplied before the restored data is released.
Whether a real organization must immediately rewrite, expire,
isolate, or otherwise handle backups depends on applicable law,
controller policy, contractual commitments, technology, and
legal exceptions.
backup status = quarantined_pending_expirytombstone_found = truereleased_name = [ERASED]released_email = NULLrestore_overlay_test = PASS6. Erasure must be idempotent
Privacy workflows can be retried after partial failures. The
same case ID must not create duplicate side effects or resurrect
mappings. The lab records ERASE-20260921-004;
rerunning it returns replay_ignored.
prior=c.execute('SELECT status FROM deletion_manifest WHERE case_id=?',(case_id,)).fetchone()if prior: return 'replay_ignored'7. Reconciliation proves business measures survived the privacy action
| Control | Before | After |
|---|---|---|
| Paid lines | 10 | 10 |
| Orders | 8 | 8 |
| Units | 12 | 12 |
| Revenue | 820 USD | 820 USD |
| Cost | 495 USD | 495 USD |
| Gross profit | 325 USD | 325 USD |
These totals prove that the technical privacy transformation did not corrupt this fixture's measures. They do not prove that retaining the pseudonymous fact key is legally permissible for every use or jurisdiction.
8. Production decision record
Define classification owners, approved purposes, retention schedules, consent/legal-basis handling, verified-request intake, downstream processor/copy inventories, backup/restoration behavior, and evidence requirements before implementing deletion. Security and privacy teams should know where raw, staging, marts, extracts, notebooks, caches, and backups can hold the subject. Legal/controller policy owns the normative decision; engineering owns faithful, auditable execution.
9. Verification checklist
- Confirm C004 direct identifiers exist before the case.
- Execute the case once and verify name/email/source ID are erased.
- Verify the token mapping is removed.
- Verify active raw generation increments and contains erased values.
- Verify the backup is tracked separately, not falsely marked deleted.
- Verify the restore overlay re-applies the tombstone.
- Verify a replay is ignored and warehouse measures remain unchanged.
10. Bridge to Lesson 4
Even correct privacy and secure-view policy can fail when a consumer has another physical access path. Lesson 4 formalizes bypass-path testing across semantic views, raw/staging tables, service identities, and exports.
Knowledge check
Acceptance questions
- Why can raw immutability require an explicit privacy exception?
- Why is a backup tracked separately from a live table?
- What does unchanged revenue prove?
- Why is the deletion manifest hash-based?
Review the answers
1. Replayability is an engineering goal; applicable privacy policy may require direct identifiers to be removed.
2. Immutable/retained backups may have different rewrite and expiry mechanics.
3. The lab preserved measure correctness, not universal legal compliance.
4. It records evidence without storing the erased clear text itself.
Authoritative references
- NIST CSRC — Least privilegeDefines restricting users/processes to the minimum authorizations and resources needed for assigned functions.
- NIST SP 800-207 — Zero Trust ArchitectureResource-centric authentication/authorization and granular least privilege; useful for identity and service-account boundaries.
- NIST SP 1800-35 — Implementing a Zero Trust ArchitectureImplementation-oriented identity/access examples; not a warehouse-specific prescription.
- PostgreSQL documentation — Row Security PoliciesConcrete engine-specific example of row-level policy enforcement; exact syntax/semantics are not universal.
- European Commission — Information for individuals under GDPRExplains data-subject rights including erasure and notes that erasure is not absolute.
- EUR-Lex — Regulation (EU) 2016/679Primary legal text for GDPR; technical examples in this course do not substitute for controller/legal interpretation.
- Python documentation — HMACUsed locally to demonstrate stable keyed pseudonymous tokens; tokenization is not equivalent to anonymization.
- SQLite documentation — CREATE VIEWLocal lab uses views and an application policy evaluator; SQLite is not presented as a native enterprise RLS engine.
11. Lab cleanup/reset
Delete atlasmart_ch22_lab (or your configured
disposable lab directory) and rerun
ch22_lab.py with a synthetic
ATLASMART_TOKEN_KEY. The lab modifies no
repository, cloud, or production resource.