Dynamic Security Patterns Basics

Why this matters

As a BI Developer, you will publish dashboards used by different teams, regions, and partners. Dynamic security lets you show each user only the rows and columns they are allowed to see without building many separate reports. Typical tasks include:

• Restricting sales data so each rep sees only their accounts, while managers see their whole team.
• Hiding sensitive columns (e.g., salary, margin) unless the user has a specific role.
• Supporting multi-tenant analytics where each client sees only their own data.
• Granting time-bound access (e.g., contractors see data only during their engagement).

Concept explained simply

Dynamic security applies rules at query time using the identity of the current user (and sometimes their attributes) to filter rows and mask columns. Instead of hardcoding who can see what, you store entitlements in tables and reference them in filter expressions.

Mental model

Think of a movie theater with multiple screenings. Your ticket (user identity and attributes) determines which screen (rows) you can enter and whether you get 2D or 3D glasses (columns revealed or masked). The screening schedule (entitlement tables) is separate from the building (the data model). Changing schedules doesn’t rebuild the theater; you just update the entitlement tables.

Core dynamic security patterns

• User-to-domain bridge: A table mapping users (or groups) to allowed values (e.g., Region, CustomerID, Department). Query filters reference this table.
• Role-based rules: A user’s role controls which rows/columns they see (e.g., Analyst vs. Manager). Often combined with the bridge.
• Hierarchy traversal: Managers inherit subordinates’ scope via a hierarchy (e.g., org chart) using parent-child paths.
• Time-bound access: Entitlements with effective_from/effective_to windows that must include today to apply.
• Column masking: Sensitive columns shown only when conditions are met; otherwise replaced with NULL or a redacted value.
• Default deny: If no entitlement matches, show nothing (and/or mask everything sensitive).

Worked examples

Example 1: Region-based row filtering (SQL + BI function)

Goal: A user only sees rows for regions they’re entitled to.

-- Entitlement table (many-to-many bridge)
security_user_region(user_id, region)

-- Sales fact has column: region
-- Pseudocode filter predicate combining BI identity and SQL join
WHERE sales.region IN (
  SELECT sur.region
  FROM security_user_region sur
  WHERE sur.user_id = CURRENT_USER_ID() -- e.g., USERPRINCIPALNAME()
);

Result: A user with regions {"West","North"} sees only those rows.

Example 2: Manager sees team via hierarchy

Goal: A manager sees all direct and indirect reports’ data.

-- Org table with parent-child
org(user_id, manager_id)

-- Build a recursive set of subordinates
WITH RECURSIVE tree AS (
  SELECT user_id FROM org WHERE manager_id = CURRENT_USER_ID()
  UNION ALL
  SELECT o.user_id FROM org o JOIN tree t ON o.manager_id = t.user_id
)
SELECT * FROM sales s
JOIN accounts a ON s.account_id = a.account_id
WHERE a.owner_user_id IN (
  SELECT user_id FROM tree
) OR a.owner_user_id = CURRENT_USER_ID();

Result: Managers automatically inherit visibility over their teams.

Example 3: Column masking for margin

Goal: Hide margin unless the user has role Finance or the account is assigned to them.

-- Role mapping
security_user_role(user_id, role)

-- Masking expression (pseudocode)
CASE WHEN (
  EXISTS (SELECT 1 FROM security_user_role r
          WHERE r.user_id = CURRENT_USER_ID() AND r.role = 'Finance')
  OR s.owner_user_id = CURRENT_USER_ID()
) THEN s.margin ELSE NULL END AS margin_safe

Result: Margin is available only to finance and account owners; others see NULL.

Example 4: Time-bound access

Goal: Contractors see data only between start and end dates.

security_user_access(user_id, scope_key, start_date, end_date)

WHERE scope_key IN (
  SELECT scope_key
  FROM security_user_access sua
  WHERE sua.user_id = CURRENT_USER_ID()
    AND CURRENT_DATE BETWEEN sua.start_date AND sua.end_date
);

Result: Access automatically starts/ends without manual toggles.

Implementation steps (vendor-agnostic)

1. List security dimensions: Identify the columns you’ll filter on (e.g., Region, CustomerID, Department) and sensitive columns to mask (e.g., Salary, Margin).
2. Design entitlement tables: Create bridge tables like security_user_region, security_user_customer, and security_user_role. Include time windows if needed.
3. Connect identity: Use your BI function for the current user (e.g., a principal name). Normalize everything to a single identity key.
4. Write predicates: Build reusable filter logic (SQL views, BI-level filters, or semantic model policies). Enforce default deny when no match.
5. Mask sensitive columns: Wrap sensitive fields in CASE/IF expressions referencing roles/entitlements.
6. Test edge cases: Users with multiple roles, overlapping scopes, no entitlements, and future-dated access.
7. Audit: Add logging in views or BI usage metrics to track which entitlements get applied.

Who this is for

• BI Developers implementing row/column security in dashboards and semantic models.
• Analytics Engineers designing entitlement tables and secure data models.
• Data Leads needing consistent, auditable access control across reports.

Prerequisites

• Comfort with SQL joins, CASE/IF expressions, and basic date logic.
• Understanding of your BI tool’s user identity function (e.g., a way to get the current user).
• Familiarity with your organization’s roles and data sensitivity requirements.

Exercises

Do this hands-on task to lock in the patterns. The same exercise appears below in the Exercises panel with a worked solution.

Common mistakes and how to self-check

• Hardcoding users in filters: This becomes unmaintainable. Self-check: Are all allow-lists in tables, not in code?
• No default deny: Users without entitlements still see data. Self-check: Simulate a user with no rows in entitlement tables.
• Incorrect AND/OR logic: Combining roles and scopes can unintentionally broaden access. Self-check: Write truth tables for role vs. scope and test both.
• Mismatched identities: Emails vs. IDs don’t align. Self-check: Confirm a single canonical identity key everywhere.
• Forgetting time windows: Access doesn’t expire. Self-check: Include BETWEEN start_date AND end_date in predicates.
• Leaking sensitive columns: Measure uses raw field, not masked field. Self-check: Ensure visuals reference masked expressions.

Practical projects

• Tenant-aware KPI board: Build a dashboard where each client tenant sees only their CustomerIDs; include a Finance-only Profit column masked for others.
• Org-chart access: Implement a manager hierarchy and prove a manager sees all subordinate pipeline, including indirect reports.
• Contractor sandbox: Add time-bound entitlements and demonstrate automatic cut-off after contract end_date.

Learning path

1. Start: Dynamic Security Patterns Basics (this lesson).
2. Then: Build robust entitlement tables and identity normalization.
3. Next: Advanced row policies (multi-attribute rules, exceptions, and overrides).
4. Finally: Auditing, monitoring, and performance tuning of security predicates.

Mini challenge

Write a single predicate (SQL-style) that grants access when ANY of these is true: the user owns the account; the user is a regional manager of the account’s region; the user is Finance and today is within their access window. Then add a masking CASE for cost that only shows for Finance or account owners.

Take the quick test

Ready to check your understanding? Take the quick test below. Note: The test is available to everyone; only logged-in users get saved progress.

Next steps

• Refactor an existing report to replace hardcoded filters with entitlement tables.
• Add default deny and verify with a no-entitlement test user.
• Document the security model (tables, predicates, masking rules) so others can maintain it.