A housing authority needs caseworkers to retrieve only case rows for their assigned region. Which security feature should the developer implement?
Select an answer to reveal the explanation.
Short Explanation
Row-Level Security is the neighborhood fence for housing cases—each caseworker only walks the rows for their assigned region, even if they query the same table name.
Full Explanation
Row-Level Security with filter predicates keyed to each caseworker’s region assignment is the correct control for a housing authority that must let caseworkers retrieve only case rows for their assigned region. RLS attaches predicate functions to the shared case table so every SELECT—regardless of whether the caseworker queries the base table or a simple view—automatically appends region predicates derived from session context or Entra/user mapping. That yields one physical table with logically isolated neighborhoods of rows, which is exactly the regional fence the stem describes.
Dynamic Data Masking on the case status column only obscures displayed values; it does not stop a caseworker from reading another region’s full case rows, so it fails row isolation by concept. Transparent Data Encryption protects database files and backups at rest but has no notion of region-scoped SELECT results for authorized housing users. A public synonym that points every clerk to the unfiltered base table actively bypasses least privilege and makes cross-region disclosure easier, not harder.
Exam caveat: RLS filter predicates enforce read isolation; block predicates may be required separately when inserts or updates must also be constrained to the caseworker’s region. Predicate logic that incorrectly uses highly privileged ownership chaining or dbo execution context can silently bypass filters—exam and production designs must keep the security policy binding intact and test with non-privileged principals.
Operational check: Create a security policy with an inline predicate that compares the case region to SESSION_CONTEXT or a mapping table of caseworker-to-region assignments. Connect as two caseworkers from different regions and prove each sees only their rows. Attempt a cross-region UPDATE if block predicates are in scope. Monitor for query plan regressions on heavily filtered housing tables and ensure reporting roles that legitimately need citywide visibility use a separate, audited principal excluded from the restrictive policy.