Row-level and masking policy bypass through a shared service account
A regional bank exposes a customer_360 view (customer_id, full_name, national_id, email, region, risk_score, balance) as a REST data service used by three channel applications, and directly over JDBC by internal analysts.
Requirements: support agents see national_id masked to the last four characters and only customers in their own region; risk analysts see the full national_id but only region = 'EMEA'; a data-science service account needs all regions with national_id irreversibly hashed.
Today the team maintains three derived views, each with a hard-coded WHERE clause and a masking expression in a selection, and the REST service points at one of them. An audit found three problems: an agent retrieved unmasked national_id by connecting a JDBC client straight to the base view; the data-science account also receives live balance; and a phone column added to the base view last month appeared unmasked in the REST JSON. Roughly 9% of rows have region set to NULL, and the masking CASE expression returns the original value for those rows.
1. Explain why the current design leaks, and describe the control placement that fixes it. Include how two roles on one user combine.
Read the worked answer
Why it leaks. All three controls live on derived views. Security attached to a derived view protects exactly that view - every other access path to the same base view is unprotected. That is precisely what the audit found: the agent went round the derived view over JDBC and got raw data. The same structure produced the second finding: the data-science masking was implemented in a different view from the one that strips balance, so the two restrictions never combined.
Where the controls belong. Push them down to the base view (or the database object) so every consumer inherits them:
- Column controls: attach a masking policy to the base view's
national_id(andbalance) rather than a selection expression. The policy is applied by the server at execution time regardless of who queries and how. - Row controls: attach row-filter policies keyed to roles (
AGENT,RISK_ANALYST) on the base view, so the region restriction follows the user everywhere. - Tag-driven policies: tag
national_idwith a PII classification and bind the masking policy to the tag. New columns can then be classified and inherit protection without anyone editing views.
Combination. A user holding two roles gets the union of row filters (OR), not the intersection - an analyst who is both AGENT-EMEA and RISK_ANALYST sees the union of those regions. Masking policies are the opposite: the most restrictive applicable mask wins. Test this explicitly with a two-role account before go-live; it is the classic surprise in an audit.
2. The REST service authenticates with one fixed service account. What does that mean for the row filters, and what are the options?
Read the worked answer
It defeats row-level security entirely. With a single service account, VDP sees the same principal for every request, so every row filter resolves to the same predicate and every caller gets the same rows. Region-based access becomes a fiction: the agent in APAC and the agent in EMEA receive identical payloads, and the audit trail names the service account rather than the human. Worse, the service account necessarily needs broad privileges (all regions), so the API is effectively running as a superuser on behalf of everyone.
Options, best first:
- Identity passthrough. Configure the data service to propagate the end-user identity - OAuth2/OIDC bearer token validated by VDP, Kerberos constrained delegation, or HTTP Basic passthrough - and map token claims or directory groups to Denodo roles. Row filters then resolve per user, and the logs name the human.
- Per-application roles. If the three channel apps genuinely have different entitlements and cannot forward user identity, issue one account per application with a dedicated role and accept that the row filter is per application, not per user. Document that explicitly; do not pretend it is per-user security.
- Token-scoped parameters. Have the calling app pass region as a parameter validated against a signed token. This is fragile - it moves the enforcement into the client - and should only be a stopgap.
Whatever you choose, remove the all-region privileges from the shared account, otherwise a single leaked credential exposes the entire customer base.
3. Address the NULL-region masking flaw and the new `phone` column leak. What controls prevent the next new column from leaking?
Read the worked answer
The NULL flaw is a default-allow bug. The CASE expression returns the original value when region IS NULL, so 9% of the customer base - likely the highest-risk segment, since missing region often means incomplete onboarding - is served unmasked. Two changes: make masking unconditional on the column (mask is the default, unmask is an explicit, audited exception), and make the row filter fail closed. A row filter of the form region = <user region> already excludes NULL rows, which is the safe outcome; if business rules require NULL-region rows to be visible to a supervisor role, express that as a second policy on that role - never as an IS NULL escape hatch inside the general expression.
The phone leak is a shape problem, not a policy problem. The REST resource was defined with SELECT *-style semantics, so any column added to the base view automatically appears in the JSON. Fix the service to project an explicit column list, or better, publish an interface view that declares the contract - then a new base column cannot reach the API until someone deliberately adds it. Publishing an interface view also protects downstream applications from breaking when columns are dropped or retyped.
Preventing recurrence. Classify at onboarding: tag new PII columns and bind masks to tags so protection is the default rather than a task someone remembers. Add a pre-promotion check in the deployment process - an impact analysis over consumers of the base view plus a diff of exposed columns between revisions - and a regression test that asserts the masked value for a NULL-region row and the absence of phone from the JSON payload. Denodo's policy simulation against a test principal is a cheap way to prove the mask before promotion.