Row Level Security Basics

Why this matters

Row-Level Security (RLS) lets each viewer see only the rows they are allowed to see, without duplicating reports. As a Data Analyst, you will:

• Publish one dashboard for all regions, yet ensure each manager sees only their region.
• Protect confidential client or department data in multi-tenant reports.
• Reduce manual work and risk from creating many filtered copies of the same report.

Concept explained simply

RLS filters the dataset at query time based on who is viewing. Think of a giant spreadsheet with a transparent filter automatically applied per user.

Mental model

Imagine three cards:

• Identity card: who is the viewer?
• Entitlements card: which rows is this identity allowed to see?
• Filter card: logic that keeps only allowed rows.

When a user opens the dashboard, the tool uses the identity card to look up allowed rows (entitlements card) and applies a filter (filter card) to the dataset.

Key terms

• Static RLS: fixed filters by role (e.g., "Role = North only").
• Dynamic RLS: filters depend on data in an entitlement table (user-to-rows mapping).
• Entitlements table: a table listing which user (or group) can see which values.
• Deny-by-default: users see nothing unless explicitly permitted.

How to implement (tool-agnostic steps)

1. Identify the security key: choose the column to secure (e.g., Region, Department, ClientID).
2. Build entitlements: create a table mapping each user (or group) to allowed keys. Columns often include UserId/Email, AllowedKey, and optionally Tenant/Department.
3. Relate entitlements to data: connect AllowedKey to the fact/dimension table key.
4. Write the security filter: ensure the tool filters rows to those that exist in the entitlements table for the current user.
5. Test with sample identities: try known users and confirm only permitted rows appear.
6. Set deny-by-default: if a user has no entitlements, they see zero rows.

Worked examples

Example 1: Dynamic region-based RLS via entitlement join (generic SQL)

Goal: Each user sees only Sales rows for their regions.

-- Entitlements table (UserRegions): user_email, region
-- Example: ("maria@co", "West"), ("maria@co", "Central")

-- Pseudo-SQL logic used by semantic layer at query time
SELECT s.*
FROM sales s
JOIN user_regions ur
  ON ur.region = s.region
WHERE ur.user_email = SESSION_USER();

Result: "maria@co" sees only West and Central sales.

Example 2: Dynamic RLS with semantic filter expression (Power BI-style)

Tables: Sales(Region,...), UserRegions(UserEmail, Region)

-- Role filter on table 'UserRegions'
UserRegions[UserEmail] = USERPRINCIPALNAME()

-- Role filter on table 'Sales'
Sales[Region] IN VALUES(UserRegions[Region])

This keeps Sales rows whose Region is in the current user's allowed regions. Users without a matching row in UserRegions see nothing (deny-by-default).

Example 3: Multi-tenant + department RLS (exists-style filter)

Goal: A user must match both TenantID and Department to see a row.

-- Entitlements: (UserId, TenantID, Department)
-- Data: FactTable has TenantID, Department

-- Pseudo SQL using semi-join logic
SELECT f.*
FROM fact_table f
WHERE EXISTS (
  SELECT 1
  FROM entitlements e
  WHERE e.user_id = SESSION_USER_ID()
    AND e.TenantID = f.TenantID
    AND e.Department = f.Department
);

Result: Access is granted only when both keys match. This prevents cross-tenant leakage.

Common mistakes and how to self-check

• Using OR instead of AND for multi-key security, unintentionally widening access. Self-check: Confirm a user assigned to Tenant A and Dept Finance cannot see Tenant B Finance or Tenant A HR.
• Not deny-by-default. Self-check: Remove a user's entitlements; verify they see zero rows.
• Relying on front-end filters only. Self-check: Export or API queries should still return zero unauthorized rows.
• String-parsing entitlements like "West;Central" and splitting on the fly. Self-check: Replace with one row per permission in an entitlement table.
• Missing relationships or wrong direction. Self-check: Ensure the security key in entitlements links correctly to the data key used in filters.
• Performance issues from high-cardinality keys. Self-check: Index keys, use numeric IDs, and validate query plans if available.

Exercises (hands-on)

These mirror the Exercises section below. Complete them here, then check the expected outputs and solutions in the exercise details.

Exercise 1: Build a dynamic region allow-list

1. Create an entitlements table with rows: ("maria@co","West"), ("maria@co","Central"), ("lee@co","East").
2. Link entitlements to your data on Region.
3. Write a dynamic filter that uses the current user identity to keep only matching regions.
4. Test as "maria@co" and confirm only West and Central appear.

• Checklist: entitlements exist; relationship works; deny-by-default holds if user has no entitlements.

Exercise 2: Deny-by-default and test users

1. Ensure that if no entitlement exists for a user, zero rows return.
2. Create a test user "kai@co" with no entitlements and verify they see nothing.
3. Add entitlement ("kai@co","North"). Verify they now see only North.

• Checklist: unknown user = 0 rows; adding one entitlement = only those rows.

Practical projects

• Secure Sales Dashboard: Add dynamic RLS by Region and validate with three test users.
• Client Health Report: Implement multi-tenant security with TenantID and confirm no cross-tenant visibility.
• Department KPIs: Combine Department and Location as a composite security key and benchmark performance before/after indexing.

Who this is for

• Data Analysts designing or publishing dashboards for multiple audiences.
• BI Developers and Analytics Engineers responsible for secure data access.

Prerequisites

• Comfort with joins/relationships in your BI tool or SQL.
• Basic understanding of your platform’s user identity function (e.g., session user or principal name).

Learning path

1. Understand RLS concepts (identity, entitlements, filter).
2. Implement static RLS roles, then move to dynamic entitlements.
3. Add multi-key constraints (e.g., Tenant + Department).
4. Scale and optimize (indexes, numeric keys, minimal cardinality).

Mini challenge

Implement dynamic RLS for a KPI table where access requires both TenantID and Region, and only for the current month. Hint: combine an EXISTS-style entitlement check with a date filter using the month boundary from your date table.

Next steps

• Run the Quick Test to confirm understanding. Available to everyone; only logged-in users get saved progress.
• Extend your entitlements to cover new business rules (e.g., product line).
• Review your model for deny-by-default and indexing.