Securing a Data Lake with Row-Level and Column-Level Controls
A healthcare insurer stores claims in Azure Data Lake Storage Gen2. Analysts in three regions (US, EU, APAC) query through Synapse serverless SQL pools. EU analysts must not see US patient identifiers, and APAC analysts must see only their region's rows. The current design grants Storage Blob Data Reader at the container level to all analysts. An audit found that EU analysts could read US files. The compliance team requires least privilege and auditable access.
1. Why is container-level RBAC insufficient, and what layered controls would you apply?
Read the worked answer
Container-level Storage Blob Data Reader grants read to every blob, so EU analysts can read US files. Use POSIX ACLs or Azure RBAC scoped to folders that map to region and sensitivity. Enable hierarchical namespace and set ACLs per region folder, granting each analyst group only its region. For column-level masking, use Synapse serverless views with CASE expressions or Dynamic Data Masking where supported. For row-level, create views filtered by SUSER_NAME() or a security predicate table. Also enable storage diagnostic logs and Synapse auditing to meet the audit requirement.
2. How would you implement row-level security for APAC analysts in serverless SQL?
Read the worked answer
Create a mapping table of user to allowed region, then a view over the claims external table that joins to the mapping and filters WHERE region = (SELECT region FROM mapping WHERE user_name = SUSER_NAME()). Grant SELECT on the view only, not on the underlying external table. Because serverless SQL does not support native RLS predicates on external tables, the view is the enforcement point. Test with EXECUTE AS USER to confirm each analyst sees only APAC rows. Document that direct file access must also be blocked via ACLs, since views do not protect raw files.
3. What monitoring proves compliance to auditors?
Read the worked answer
Enable Azure Storage diagnostic logs (Read/Write) sent to Log Analytics, Synapse workspace auditing to a storage account, and Microsoft Defender for Storage. Query logs for cross-region access attempts and failed ACL checks. Produce a periodic report of who accessed which region folder. Retain logs per policy (e.g., 1 year). Alert on any access outside the user's assigned region.