Testing Security With User Scenarios

Who this is for

• BI Developers and Analytics Engineers implementing row-level and column-level security.
• Data Analysts who review dashboards and need to validate access boundaries.
• Team leads responsible for sign-off on secure BI releases.

Prerequisites

• Basic understanding of Row-Level Security (RLS) and Column-Level Security (CLS).
• Ability to map users to roles or groups in your BI tool.
• Familiarity with your data model (facts, dimensions, relationships).

Why this matters

Real BI work often requires proving that only the right people see the right data. You may need to:

• Demonstrate to auditors that managers cannot see other regions' payroll.
• Confirm that external partners only see their own accounts and a limited set of metrics.
• Ensure sensitive columns (e.g., PII, salary) are hidden or masked for most users.

User-scenario testing simulates how real people use dashboards, filters, drilldowns, and exports—catching leaks that unit tests or a quick “looks fine” glance might miss.

Concept explained simply

Row-level security hides or reveals specific rows based on who is viewing. Column-level security hides or masks certain columns. Testing with user scenarios means acting like each type of user and verifying what they can do across common actions (view, filter, drill, export).

Mental model

Think of your BI app as a building with rooms (dashboards), doors (filters, drillthrough), and windows (exports, subscriptions). RLS decides which rooms you can enter. CLS decides which objects in the room you can see. Scenario testing walks through the building as each persona and tries every door and window.

Practical steps

1. Define personas: List roles like Sales Rep, Sales Manager, Finance Analyst, Contractor. Note allowed regions and columns.
2. Map personas to data rules: For each persona, specify row filters (e.g., region IN [EMEA]) and column rules (e.g., salary masked).
3. Create a test matrix: For each persona, list typical actions: open dashboard, apply filters, drillthrough, view details tables, export, subscribe.
4. Use real-like test data: Include edge cases: NULL region, unassigned accounts, small groups (1–2 rows), cross-region managers.
5. Run positive and negative tests: Positive: user sees what they should. Negative: user cannot see what they shouldn’t—even via drill/export.
6. Record evidence: Screenshot or note row counts, visible columns, filter states, and export outcomes.
7. Fix, retest, regression: Patch filters or roles; re-run the matrix; keep it for future releases.

Worked examples

Example 1: Healthcare RLS + CLS

• Scenario: Doctors can see only their patients. Admins can see all. Patient SSN is visible only to Admins.
• Test: Log in as Doctor A. Confirm that patients of Doctor B never appear, even with global filters cleared. Check columns: SSN column should be hidden. Try export or drillthrough: still no SSN.
• Expected: Only Doctor A’s patient rows; SSN hidden. As Admin: all rows; SSN visible.

Example 2: Sales by Region with Dynamic RLS

• Scenario: Sales Rep sees only their assigned region; Sales Manager sees all regions they oversee (EMEA + APAC).
• Test: As Rep (EMEA), switch filters to APAC; ensure no rows show. As Manager (EMEA + APAC), verify both regions appear; AMER remains hidden.
• Expected: Rep: EMEA only; Manager: EMEA + APAC; exports respect the same rows.

Example 3: Finance Salary Masking

• Scenario: Finance Analyst can see headcount totals but not individual salaries; HR Admin sees salary details.
• Test: As Finance Analyst, open detailed table and export to CSV; confirm salary column is masked/absent. Check aggregated widgets for small groups (N < 3) to avoid re-identification.
• Expected: No raw salaries for Finance Analyst anywhere; add a small-N suppression rule to prevent privacy leakage.

Exercise

Complete the exercise below, then check your work with the solution. Use the checklist to self-validate.

Exercise 1: Design a scenario test plan

You have a Retail dataset: FactSales (OrderID, CustomerID, Region, Amount, Discount, Date), DimUser (UserID, Role, RegionsAllowed), DimCustomer (CustomerID, Tier, Email). Rules:

• Sales Rep: rows in RegionsAllowed; cannot see Customer Email; can see Amount and Discount.
• Regional Manager: multiple regions; cannot see Customer Email; can see Amount and Discount.
• Marketing Contractor: only Tier and aggregated Amount; no Discount, no Customer Email, no drillthrough to details.

Create a test matrix with positive and negative cases, including export and drillthrough attempts.

Common mistakes

• Testing with an over-privileged account: You won’t see leaks. Use role-accurate test accounts.
• Assuming exports inherit all rules: Some tools need explicit settings. Always test exports and subscriptions.
• Forgetting small-N risk: Aggregates over tiny groups can reveal identities. Add suppression thresholds.
• Unassigned or NULL regions: Orphan records may leak. Decide policy (exclude or assign) and test it.
• Bi-directional or many-to-many relationships: Can widen visibility unexpectedly. Validate with targeted cases.
• Not testing drillthrough/detail: Even if cards look fine, detail tables might leak columns.

Practical projects

• Build a reusable test matrix template for your team with standard personas, actions, and evidence fields.
• Implement small-N suppression on at least one dashboard and verify it with scenario tests.
• Create a regression pack: 10–15 must-pass cases that you re-run before each release.

Learning path

1. Refresh RLS/CLS concepts and how they map to roles/groups.
2. Design a persona catalog and a standard test matrix.
3. Run scenario tests on a simple model, then on a complex, relationship-heavy model.
4. Introduce edge-case data (NULLs, small groups, cross-region users) and retest.
5. Automate parts of evidence capture where possible (screenshots, row counts).

Next steps

• Finish the exercise and compare with the provided solution.
• Take the quick test below to confirm understanding.
• Schedule a peer review: trade personas with a teammate and test each other’s dashboards.

Mini challenge

Design a 1-page test matrix for an e-commerce dashboard with roles: Customer Support (only their assigned tickets), Vendor (only their products), and Exec (all data but no PII). Include at least two negative tests per role covering drillthrough and export.