luvv to helpDiscover the Best Free Online Tools
Topic 7 of 8

Role Based Access Design

Learn Role Based Access Design for free with explanations, exercises, and a quick test (for BI Developer).

Published: December 24, 2025 | Updated: December 24, 2025

Table of Contents

Why this matters

As a BI Developer, you must ensure the right people see the right data at the right level. Role-based access (RBAC) combined with row-level security (RLS) and column-level security (CLS) protects sensitive fields (PII, salaries), prevents report data leaks, and simplifies audits. Typical tasks include designing access matrices, implementing filters in BI tools, coordinating with data engineers on masking policies, and validating that visuals respect user permissions.

  • Support compliance: enforce least privilege for regulated data.
  • Reduce maintenance: roles are easier to manage than ad‑hoc user exceptions.
  • Improve trust: consistent rules across datasets and reports.

Concept explained simply

RBAC means you group users into roles (like Sales Analyst, Finance Manager). You attach rules to roles instead of individual users. RLS restricts which rows each role can see. CLS restricts which columns each role can see or masks values.

Mental model

Think of a three-layer gate:

  • Gate 1: Role membership (who you are).
  • Gate 2: Row filter (which records you can enter).
  • Gate 3: Column mask (what attributes you can read inside those records).

Default stance: closed gates (deny by default). You explicitly open gates for each role.

Design checklist (quick)
  • List data domains and sensitivity tiers.
  • Define roles by job-to-be-done (not by person names).
  • Map roles to row scopes (regions, subsidiaries, accounts).
  • Map roles to column tiers (Public, Internal, Sensitive, Restricted).
  • Choose enforcement layer(s): warehouse, semantic model, BI tool.
  • Document rules with examples and edge cases.
  • Test with real personas and negative cases.

Step-by-step: design and implementation

Step 1. Inventory & classify data

Tag tables and columns (Public/Internal/Sensitive/Restricted). Identify join keys to apply row filters (RegionID, DepartmentID, CustomerID).

Step 2. Define roles

Start with personas (Sales Rep, Sales Manager, Finance Analyst). Keep names stable even if people change.

Step 3. Map permissions

Create a matrix: roles Ă— data domains. For each role, specify allowed rows and allowed columns or masked columns.

Step 4. Choose enforcement layer

Prefer warehouse policies (masking, secure views) for CLS and cross-tool consistency. Use BI tool RLS for user-friendly row filters and quick iteration.

Step 5. Build entitlements

Use a small entitlement table mapping User/Group → Business attributes (Region, Department). Join it in your model to drive dynamic filters.

Step 6. Implement and test

Implement RLS/CLS rules. Test with persona accounts and negative tests (ensure forbidden data stays hidden).

Step 7. Document and monitor

Record the rules, owners, and review cadence. Add audit queries and a change log.

Worked examples

Example 1 — Power BI RLS by region and department

Goal: Sales role sees only their Regions; Finance role sees all rows but masks PII in the report layer.

  • Create a table UserEntitlements(UserEmail, RegionKey, Department).
  • Relate UserEntitlements to Dimension tables (Region, Department).
  • Define roles:
Role: Sales
Filter on Region: Region[RegionKey] IN VALUES(UserEntitlements[RegionKey])
Filter on Department: 'Employee'[Department] = "Sales"

Role: Finance
(No row filter) — or Department = "Finance" if needed

Use DAX function USERPRINCIPALNAME() to filter UserEntitlements by current user:

UserFilter = UserEntitlements[UserEmail] = USERPRINCIPALNAME()

Attach UserFilter to a security table or use it inside RLS expressions. Mask PII by replacing sensitive measures with conditional logic, or better, rely on warehouse masking for consistency.

Example 2 — Tableau row security with user functions

Goal: Country Managers see only their country.

  • Create a simple mapping table Entitlements(Username, Country).
  • Join Entitlements to your model on Username = USERNAME().
  • Create a data source filter: [Country] = [Entitlements].[Country].

For column restrictions, replace sensitive fields with calculated fields that show NULL or masked value when NOT IsAllowedRole.

Example 3 — Snowflake masking policy for PII (CLS)

Goal: Only roles with PII_READER can see raw email; others see masked.

CREATE OR REPLACE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_READER','SEC_ADMIN') THEN val
    ELSE REGEXP_REPLACE(val, '(^.).+(@.+$)', '\\1***\\2')
  END;

ALTER TABLE PROD.CRM.CUSTOMERS MODIFY COLUMN EMAIL SET MASKING POLICY mask_email;

Combine with RBAC: grant PII_READER to Finance Privacy Officers only.

Example 4 — Looker row access with access_grants
access_grant: region_access { user_attribute: region }

view: orders {
  access_filter: { field: region ; user_attribute: region }
}

Assign user attributes to groups; groups map to roles; roles map to users. Keep default deny by leaving attributes unset for non-authorized users.

Useful patterns

  • Dynamic RLS via bridge table: User → Entitlements (Region/Dept) → Dimensions → Facts.
  • Default deny: if no entitlement row exists, the user sees zero rows.
  • Hierarchies: use parent-child paths (e.g., region hierarchy) to give managers roll-up access.
  • CLS first in warehouse, RLS either in warehouse (secure views) or BI model.
  • Break-glass admin: time-bound elevated access with audit logging.

Who this is for

  • BI Developers implementing secure dashboards and semantic models.
  • Analytics Engineers defining datasets used across tools.
  • Data Stewards reviewing access rules and audits.

Prerequisites

  • Basic SQL (JOINs, WHERE, CASE).
  • Familiarity with one BI tool (Power BI, Tableau, or similar).
  • Understanding of dimensional modeling (facts and dimensions).

Learning path

  1. Classify data and list sensitive columns.
  2. Define roles and write an access matrix.
  3. Build an entitlement table.
  4. Implement RLS in your BI tool or semantic layer.
  5. Add CLS in the warehouse (masking policies or secure views).
  6. Test with personas and add monitoring.

Common mistakes and self-check

  • Granting to users, not roles. Self-check: Can you disable a user without editing rules? Roles should handle it.
  • Orphan rows after joins. Self-check: Do restricted users see fewer totals but still see unrelated rows? Validate joins propagate filters.
  • Inconsistent masking. Self-check: Is PII masked in SQL but exposed via BI extract? Ensure CLS at the source.
  • Allow by default. Self-check: Remove a user's entitlements; they should see zero rows.
  • Hardcoding emails in filters. Self-check: Replace with user functions and entitlement tables.

Exercises

Do these in order. They mirror the tasks below and have solutions you can reveal.

Exercise 1 — Build an access matrix and RLS filters

Design roles SalesAnalyst and FinanceManager. SalesAnalyst: only Department = "Sales" and their Regions. FinanceManager: all departments and regions. Create an entitlement table and write example RLS expressions.

Exercise 2 — Column masking policy

Mark Email and Salary as Sensitive. Implement a masking policy so only role PII_READER sees raw values; others see masked values.

Exercise 3 — Test plan and audit

Create a test plan with at least 5 cases (positive and negative) and example queries to prove enforcement.

Completion checklist:

  • Access matrix documented with roles Ă— domains Ă— sensitivity tiers.
  • Entitlement table designed with user/group identifiers and business keys.
  • RLS expressions drafted and peer-reviewed.
  • CLS masking policy created and attached to columns.
  • Test plan listed with pass/fail criteria and example data.

Practical projects

  • Secure Sales Dashboard: Implement RLS by Region and Department; add CLS for PII fields.
  • Self-service Dataset: Publish a certified dataset with roles and usage docs; include an access matrix page.
  • Audit Pack: Create views or queries that list who can see what (role membership, entitlements, masked columns).

Mini challenge

Your company adds a new subsidiary with unique regions and a DataPrivacyOfficer role who can see masked columns unmasked for audit purposes. Update your access matrix, entitlement model, and policies to include the new subsidiary and role. Tip: avoid duplicating rules—extend entitlements and reuse policies.

Next steps

  • Review your current dashboards and tag sensitive fields.
  • Draft a simple entitlement table and test with one role.
  • Add monitoring: weekly report listing users with elevated roles.

Quick Test is available to everyone; sign in to save your progress.

Practice Exercises

3 exercises to complete

Instructions

Create two roles: SalesAnalyst and FinanceManager. Build an access matrix for Orders, Customers, and Employees tables. SalesAnalyst sees Department = "Sales" and only rows for their Regions from an entitlement table. FinanceManager sees all rows. Write:

  • A sample Entitlements table schema with example rows.
  • Power BI-style RLS expressions (or equivalent pseudocode) for both roles using the current user email.
Expected Output
A roles × domains matrix, an Entitlements(UserEmail, RegionKey, Department) schema with 2–3 example rows, and correct RLS expressions referencing USERPRINCIPALNAME() (or an equivalent user function) and VALUES from Entitlements.

Role Based Access Design — Quick Test

Test your knowledge with 8 questions. Pass with 70% or higher.

8 questions70% to pass
Implementing Column Level SecurityDynamic Security Patterns Basics

Have questions about Role Based Access Design?

AI Assistant

Ask questions about this tool