Row-Level/Column-Level Security, Dynamic Masking, Tokenization, and Policy Placement

Learning outcomes

1. The problem: the secure view masks email, but the analyst can still query raw

Marketing needs customer segments and geography for campaign analysis. It does not need clear-text names or emails. A row-level policy decides which records are visible; a column-level policy decides which attributes are accessible. Masking returns a transformed representation, such as a***@example.test. Tokenization replaces an identifier with a stable surrogate whose mapping/key is separately protected. These controls solve different problems.

The declared consumer is marketing analytics. The relevant customer state includes marketing consent. The failure mode is subtle: a secure view can be perfectly designed and still useless if the same role retains direct access to the underlying restricted table.

2. Row and column policy are not synonyms

-- Product-neutral policy intent; NOT executed in SQLite as native RLS/roles.-- Exact syntax varies by warehouse.ROLE marketing_analyst:  ALLOW SELECT secure_customer_view  DENY  SELECT raw_customer  COLUMNS token, segment, geography_code, masked_email  ROWS marketing_consent = trueROLE privacy_operator:  ALLOW SELECT_PII, ERASE_PII ON dim_customer/raw_customer  DENY  ADMIN ON security_catalogROLE pipeline_writer (service identity):  ALLOW INSERT raw_customer, UPSERT dim_customer  DENY  BI export and security administration

3. Executed secure-view result

marketing_campaign_analysis secure rowscustomer_token              segment      geography   masked_emailtok_eb060e042b673d546dc8    Mid-Market   G-NORTH     a***@example.testtok_e59219f7c6b135fc7599    Consumer     G-EAST      b***@example.testtok_b3c2ecea1fc59fbca6d1    Enterprise   G-EAST      c***@example.testD-CUST-004 is excluded because marketing_consent=0.D-CUST-005 is excluded because marketing_consent=0.

The lab has five customer identities. Three consented synthetic customers are visible to the marketing policy; C004 and the inferred C005 are excluded. Clear names are absent. The token is stable for the lab key, which enables joins across approved datasets without exposing the original durable ID to the consumer.

4. Tokenization is pseudonymization, not a magic deletion primitive

The token is an HMAC of the durable customer ID using a runtime key. If someone gains both the protected mapping/key and surrounding data, the token may remain linkable. Treating it as anonymous simply because it looks random is unsafe. The lab stores a token_vault mapping for approved internal use, then deletes the C004 mapping during the privacy workflow.

def token(durable_id):    return 'tok_'+hmac.new(KEY.encode(),durable_id.encode(),hashlib.sha256).hexdigest()[:20]# KEY comes from ATLASMART_TOKEN_KEY; it is not embedded in source.

5. Controlled failure: masking works, authorization still fails

alice.marketing -> prod.secure_customer_view SELECT = ALLOWreturned email = a***@example.testalice.marketing -> prod.raw_customer SELECT = ALLOWraw row contains clear customer_name + emailsecurity conclusion = BYPASS FOUND

This is why a masked query result is not evidence that the user cannot retrieve the unmasked value elsewhere. The repair removes the analytical role's direct raw grant and tests the effective permission again.

c.execute("DELETE FROM permission WHERE role='marketing_analyst' AND resource='prod.raw_customer' AND action='SELECT'")assert authorize(c,'alice.marketing','prod.raw_customer','SELECT')[0]=='DENY'

6. Where should policy live?

Policy can exist in a warehouse table policy, secure view, semantic layer, BI tool, API, or export process. The closer the enforcement is to the governed data boundary, the fewer alternate routes exist. Higher layers may still add purpose-aware UX, but they should not be the only barrier when users can query lower layers. A production system may also need object-storage policies, staging schemas, notebook credentials, and service-account restrictions. The exact feature names and syntax vary by engine.

7. Performance and query semantics

Row filters and masking expressions can change plans or prevent some optimizations in certain products, but this chapter reports no invented overhead. Benchmark the same authorized result under representative scale/cache/concurrency before tuning. Never weaken policy merely to preserve a benchmark number without an explicit risk decision.

8. Verification checklist

1. Confirm marketing returns three policy-approved rows.
2. Confirm no clear customer_name is returned.
3. Confirm masked emails are deterministic for the fixture.
4. Confirm the initial raw bypass is detected.
5. Remove the raw grant and assert direct raw SELECT becomes DENY.
6. Verify finance and ETL roles still retain only their required actions.

9. Production judgment and rollback

Use row policies for population boundaries, column policy for attribute boundaries, masking for controlled presentation, and tokens for controlled linkage. None substitutes for privilege review. Version policy definitions, test them with named identities, and keep a rollback route that restores the previous reviewed policy—not a blanket grant. Chapter 3's dimensional keys and Chapter 20's metric semantics remain unchanged.

10. Bridge to Lesson 3

Row/column controls answer what an authorized consumer can see. Lesson 3 asks a different question: what personal data should exist at all, for what purpose, for how long, under what consent/legal policy, and what happens when a verified deletion request meets historical facts, raw evidence, exports, and backups.

Knowledge check

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.