Practice — Medium · The Leak Hunt
Skills focus. RLS leaks created, hunted from the reader's side, and repaired
— plus the dynamic-RLS pattern read for comprehension.
Ungraded. Work on a COPY of your file; part 1 deliberately breaks things.
Part 1 — Manufacture the leak
- On the copy: delete the returns → stores relationship. (This is the L04 Medium break, now with a security consequence.)
- View as Midwest Manager and audit every page like an external security tester: list which visuals narrow and which leak. Write the leak list before looking at Model view.
- Repair the relationship; re-audit; confirm the leak list empties. Delete the copy.
Part 2 — Read the dynamic pattern
You will not build dynamic RLS this week, but you must be able to read it. Given: the employees table (165 rows: EmployeeID, Region, Email…) and this role filter on it:
[Email] = USERPRINCIPALNAME()- Trace the mechanism in writing: a manager signs in → the function returns their sign-in email → the filter keeps their employee row → and then what must be true of the model for that one row to narrow the sales fact to their region?
- Our employees table relates to stores (many-to-one, employees→stores). Does the filter flow from a filtered employees table to sales? Think carefully about direction — employees is on the MANY side. What does this tell you about what a dynamic-RLS mapping table needs to be, structurally?
Reflection
- Part 1's leak was invisible on the revenue card and loud on the returns card. What general auditing rule follows for View-as testing?
- Part 2's structural snag is subtle and real. In one sentence: why can't our employees table, as currently wired, serve as the dynamic-RLS mapping table?