How to learn Implementing Row Level Security for Data Security Row Column Level in BI Developer for free

Why this matters

As a BI Developer, you ship dashboards and datasets that many people access. Without Row Level Security (RLS), users could see data they shouldn’t — customer records, salaries, sales by other regions, or restricted projects. RLS lets you restrict rows based on who is querying, so each person sees only what they’re allowed to see.

Protect sensitive business data while enabling broad self-service analytics.
Reduce the need to clone datasets per audience.
Simplify governance with a single, secure semantic model.

Concept explained simply

RLS is a rule that says “only rows matching this condition are visible to this user.” The condition can use attributes like user email, role, department, or a mapping table of entitlements.

Mental model

Imagine every query automatically adds a hidden WHERE clause that matches the user’s permissions. For example: WHERE region IN (regions the user is allowed to see). If a row doesn’t match, it behaves as if it doesn’t exist for that user.

Key properties of good RLS

Deny-by-default: If uncertain, show nothing.
Centralized entitlements: One source of truth mapping users to allowed data.
Dynamic: No manual updates when people move teams.
Auditable: Easy to check who can see what.
Testable: You can impersonate users safely.

Core patterns

Predicate-based RLS: A rule that filters rows by a condition (e.g., country = user_country).
Entitlement-table join: Maintain a table listing (principal, data_key) pairs; join to filter.
Role-based filters in BI tools: Define roles/expressions that use the viewer’s identity.

Worked examples

Example 1 — SQL entitlement table (generalized)

Goal: Only show orders from regions a user is entitled to.

-- Entitlements: which user can see which region
CREATE TABLE entitlements (
  principal_id VARCHAR,  -- e.g., user email or group id
  region       VARCHAR
);

-- Fact table
CREATE TABLE orders (
  order_id INT,
  region   VARCHAR,
  amount   NUMERIC
);

-- RLS concept: filter orders by a join to entitlements for the current principal
-- Pseudocode for a secured view (replace CURRENT_PRINCIPAL with your platform’s function)
CREATE OR REPLACE VIEW orders_secured AS
SELECT o.*
FROM orders o
JOIN entitlements e
  ON e.region = o.region
WHERE e.principal_id = CURRENT_PRINCIPAL();

Queries against orders_secured only return rows where the principal has matching entitlements. Deny-by-default is achieved because rows without matching entitlements are filtered out.

Testing this example

Insert sample entitlements for two users.
Impersonate or set CURRENT_PRINCIPAL to each user.
SELECT * FROM orders_secured and verify regions match the entitlement table for that user.

Example 2 — BI role filter (semantic model)

Many BI tools support row filters referencing the current viewer. You define a role and an expression that uses the logged-in user identity.

-- Pseudocode logic for a model filter on a Region dimension:
Region[Region] IN VALUES(Entitlements[Region])
AND Entitlements[Principal] = CURRENT_USER()

Attach the role to your dataset/model, and ensure users access the model with their identity. The model automatically restricts rows across all visuals — not just individual charts.

Tips for this approach

Reference a single Entitlements table used by all models.
Avoid hard-coded user emails in filters; rely on a table and CURRENT_USER().
Keep your grain consistent: Region vs Country vs Store.

Example 3 — Hierarchical org security (manager sees team)

Scenario: A manager can see their own data and their direct/indirect reports. Use a parent-child org table and expand it for entitlements.

-- org_hierarchy: employee -> manager 
CREATE TABLE org_hierarchy (
  employee_id VARCHAR,
  manager_id  VARCHAR
);

-- Build a manager_to_employee mapping (transitive) into entitlements
-- (This can be materialized in ETL)
CREATE TABLE entitlements AS
SELECT m.manager_id AS principal_id,
       e.employee_id AS data_key
FROM org_hierarchy e
JOIN RECURSIVE_EXPAND(e.employee_id, e.manager_id) AS h -- pseudo recursive expansion
  ON ...
-- Then filter facts by employee_id IN (data_key for CURRENT_PRINCIPAL)

Result: Managers see their team’s rows; individual contributors see only their own.

Implementation checklist

Define data domains and access rules (who should see what, at what grain).
Create a single Entitlements table: principal_id, data_key (and optionally scope, start/end dates).
Implement deny-by-default logic via joins or policies.
Use the platform’s identity function (e.g., current user) in filters.
Test with user impersonation for edge cases.
Add indexes/cluster keys on entitlement join columns to keep queries fast.
Set up monitoring: number of rows returned by role, unexpected full scans, policy bypass attempts.

Exercises

Do these in order. Use sample data or adapt to your environment.

Exercise 1 — Build a secured view with entitlements

Mirror of Exercise ex1 below.

Steps

Create orders and entitlements tables.
Insert data for at least two users and two regions.
Create a secured view that filters by CURRENT_PRINCIPAL.
Test by impersonating each user and verifying allowed rows.

Exercise 2 — Add dynamic RLS to a BI model

Mirror of Exercise ex2 below.

Steps

Load Orders, Region, and Entitlements into a model.
Define a role that filters Region by Entitlements for CURRENT_USER.
Validate with a simple table visual and user impersonation.

Self-check checklist

Deny-by-default: Users see nothing if not in entitlements.
Central table: No hard-coded users in filters.
Impersonation tests: At least three identities tested.
Edge cases: User with multiple regions, user with none, manager hierarchy.
Performance: Queries still fast after RLS is enabled.

Common mistakes and how to self-check

Putting filters only on visuals instead of the model. Fix: Implement model-level RLS so all visuals inherit it.
Hard-coding user emails in rules. Fix: Use an entitlement table and CURRENT_USER.
Inconsistent grain between entitlements and facts. Fix: Align grain (e.g., both at Region) or map via conformed dimensions.
No deny-by-default. Fix: Ensure unmatched users get zero rows.
Skipping tests for power users/admins. Fix: Impersonate them; verify least privilege still holds.
Performance regressions from many-to-many joins. Fix: Index keys, pre-aggregate entitlement expansions, or materialize secured views.

Practical projects

Single-model security: Add RLS to a sales dataset using a Region entitlement table; document your rules.
Org hierarchy: Build dynamic manager access (team rollup) and a test matrix (manager, IC, contractor).
Audit & monitoring: Create a small report showing row counts returned per role/user and alert on anomalies.

Learning path

Understand identity: what user attribute your platform exposes (email, ID, groups).
Model entitlements: design principal-to-data mappings at the correct grain.
Implement in SQL/warehouse first, then in the BI model.
Add tests and impersonation scripts.
Harden and monitor performance and coverage.

Who this is for

BI Developers building shared datasets/dashboards.
Analytics Engineers designing secure semantic layers.
Data Stewards/Governance partners defining access rules.

Prerequisites

Comfort with SQL JOINs and dimension modeling.
Basic knowledge of your BI tool’s role/security features.
Access to a test environment where you can impersonate users.

Next steps

Complete the exercises, then take the Quick Test below.
Apply RLS to a real, non-critical dataset in your environment.
Document your entitlement model and rollout plan.

Mini challenge

Design RLS for a dataset where some users see their stores, district managers see all stores in their district, and HQ analysts see all. Implement with a single entitlement structure and deny-by-default. Validate with three impersonated users.

Quick Test

Take the quiz to check your understanding. Everyone can take it for free; logged-in users will have their progress saved automatically.

Implementing Row Level Security

Table of Contents