Row filters, masking and a residency constraint on the cache
An insurer exposes a policy administration view to 1,200 agents in six countries. The view joins a CRM (MySQL), a policy system (Oracle) and a claims API. Requirements: agents see only policies in their own country; agents see only the last four digits of the national ID and no bank account numbers at all; UK underwriters see full bank details, but only for policies above 1,000,000 GBP; the German regulator requires that German policy data is never processed on infrastructure hosted outside the EU; authentication is via two identity providers (Okta in the US, Azure AD in the EU). Today the BI tool connects with one shared svc_reporting account, and a single Denodo role grants SELECT on the whole database.
1. Design the row-level and column-level controls. Explain where each control must live and what would happen if the BI tool enforced them instead.
Read the worked answer
Row-level: a filter on the policy view comparing the policy's country column to the session variable carrying the authenticated user's country attribute. The attribute comes from the IdP - a group or claim resolved at login - not from a lookup table keyed on username inside the view, because that table becomes a tampering target. Expose the view through an interface view with the filter attached, so every consumer inherits it.
Column-level: the base view selects national ID and bank details as-is; a derived view applies masking functions (return the last four characters of the national ID, null the bank account) and is what agents are granted. Underwriters get a separate derived view that reveals bank details, filtered additionally on amount > 1,000,000 GBP and on the underwriter group from the IdP.
Justification for two derived views rather than one dynamic mask: a conditional mask based on group membership inside one expression is harder to test and easy to get wrong when a user belongs to several groups. Two views, two grants, two audit trails.
If the BI tool enforced this instead: a second tool added next quarter would bypass the rules entirely; the Denodo audit log would show clean queries while the data leaked; and a report exported to CSV would carry whatever columns the tool happened to expose. Security at the data layer is the only enforcement point that survives new consumers - which is exactly what an access layer is for.
2. The shared svc_reporting account is in place today. Explain what breaks, how you would migrate, and what could go wrong.
Read the worked answer
What breaks: with one shared account, every session resolves to the same user, so the country attribute is identical for all 1,200 agents and the row filter either returns nothing or returns everything depending on how the service account is profiled. The underwriter view becomes unreachable or universally reachable. Audit records attribute all access to one name, which is unusable evidence for a regulator.
Migration: register both IdPs as authentication sources and map group claims to Denodo roles. Then decide how the end-user identity reaches Denodo. If the BI tool can pass the end-user token (OIDC) or delegate Kerberos, enable that and remove the service account. Where a middleware cannot pass identity, use a signed assertion containing the user and validate the signature in Denodo - never an unsigned header. Run the old and new paths in parallel against a test view that returns the resolved session user, and compare row counts per country with the expected policy counts.
What can go wrong: the group claim names differ between Okta and Azure AD, so a mapping that works for US agents silently fails for German agents and returns zero rows. Token expiry can be longer than a pooled session, so the resolved identity goes stale after a group change - revoke by forcing session re-authentication for privileged roles. And if a group is renamed in the IdP, the Denodo role mapping breaks silently; alert on logins that resolve to no role.
3. The team plans to enable a full cache on the policy view in the Virginia data centre to improve US agent performance. Assess this against the German requirement and list every place EU data could still land outside the EU.
Read the worked answer
Reject the plan as described. Virtualization avoids copies, but caching is a copy: a full cache of the policy view in Virginia materialises German policy records, including national IDs, on US infrastructure. That is processing outside the EU and breaches the constraint regardless of encryption at rest.
The workable design is a regional split. German and EU policies are served by a Virtual DataPort server and cache database inside the EU, and US agents who legitimately need EU policies for a cross-border book are served by that EU server rather than a US replica. If the US dashboard must be fast, cache only the non-EU rows - a filtered derived view with the country predicate applied before caching.
Other places EU data can land outside the EU, each of which needs checking:
- Denodo Embedded MPP or in-memory join spill on a US server: heap dumps and spill files contain row values.
- Diagnostic and Monitoring audit logs, which may store full query text including literals such as a national ID.
- Solution Manager exports and revision control, where test data is often copied from production.
- Cache and metadata backups taken centrally and shipped to a US backup target.
- Scheduler job output, for example an export to CSV staged in a shared file share.
- Support bundles uploaded to a vendor case.
The principle: residency is a property of the whole deployment, and every cache, log, spill file and backup is a data store that must be inventoried.