PII leaking into an analytics dataset
A financial services firm ingests customer records into MaxCompute. A data engineer grants a broad analyst role SELECT on a curated table so the analytics team can build dashboards. During a review, the security team finds that the curated table includes national ID numbers and full phone numbers in plaintext, and that three analysts exported query results to local CSV files last month. The firm has a policy that PII must be masked for anyone outside a named 'PII-authorized' group, and all exports must be logged and approved. Dashboards currently reference the plaintext columns directly. The engineer argues the table is 'internal only' so masking is unnecessary.
1. Assess the engineer's 'internal only' argument and identify the control gaps.
Read the worked answer
The argument fails on multiple counts:
- 'Internal' is not a data classification. Policy defines PII exposure by who can see it (PII-authorized group), not by network location. Analysts outside that group had SELECT, so policy was violated regardless of 'internal.'
- No column-level protection: plaintext national ID and phone were exposed to a broad role. The correct control is column-level masking/desensitization so non-authorized users see masked values while authorized users see real values.
- Uncontrolled export: three analysts exported CSVs. There is no evidence of approval or logging, so data left the governed boundary. This is the highest-severity gap because it's irreversible.
- Dashboards coupled to plaintext columns: even if masking is added, dashboards reading raw columns will break or, worse, be built by users who then need the plaintext grant.
- Least privilege failure: a single broad analyst role replaced per-purpose access.
Root cause: access was granted at table granularity to a broad role with no classification-driven masking and no export governance.
2. Design the remediation in priority order, distinguishing immediate containment from durable fixes.
Read the worked answer
Immediate containment (hours):
- Revoke the broad analyst SELECT on the curated table; replace with access via a masked view so dashboards keep working.
- Identify and quarantine the exported CSVs; notify per incident process.
- Audit who accessed the PII columns in the last period.
Durable fixes (days–weeks): 4. Column-level masking: apply masking policies to national ID and phone so only the PII-authorized group sees plaintext. Implement as a view or label-based masking so the base table stays protected even if new grants are added. 5. Grant model: stop granting on base tables. Grant on masked views per purpose; keep the PII-authorized group small and reviewed. 6. Export governance: require approval and log all exports; alert on unapproved export attempts. Restrict local download for roles that don't need it. 7. Dashboard migration: repoint dashboards to the masked view; where a dashboard genuinely needs full values, it must run under an authorized service identity, not a human analyst. 8. Classification and review cadence: tag PII columns, add a periodic access review, and make 'internal only' an explicit anti-pattern in the data-handling standard.
Order matters: contain the live exposure before redesigning grants, because the CSV exports are already out.
3. A product manager says masking will 'break analytics' because aggregations need exact phone numbers. How do you respond?
Read the worked answer
Challenge the premise. Aggregations rarely need exact PII values:
- Counts/distinct counts work on masked or hashed values if the mask is deterministic (e.g., consistent tokenization) so group-by still groups correctly. A random mask would break distinct counts - choose a format-preserving or tokenized mask for join/group keys.
- Prefix/region analysis (e.g., phone area code) can be served by a derived, non-identifying column (area code only) rather than the full number.
- If a genuine need exists, it should be scoped: a named authorized group, a purpose-bound view, and logged access - not broad plaintext SELECT.
Offer to profile the actual dashboard queries to show which columns are truly needed. Usually the 'need' is a small number of derived attributes. Propose a masked view plus derived columns as the default, with an exception process for the rare authorized case. This satisfies policy without blocking analytics, and it removes the incentive to export raw data.