Row-Level Security: Implementation Patterns
Introduction
Row-Level Security (RLS) is the cornerstone of multi-tenant Power BI deployments, ensuring users see only the data they're authorized to access. However, implementing RLS incorrectly can lead to security breaches, performance degradation, or frustrated users unable to access legitimate data.
This comprehensive guide covers proven RLS patterns from simple static roles to complex organizational hierarchies, many-to-many relationships, dynamic security tables, performance optimization techniques, and rigorous testing methodologies. You'll learn how to design maintainable, scalable, and secure RLS implementations that protect sensitive data without compromising user experience.
Prerequisites
- Power BI Desktop (latest version)
- Understanding of DAX fundamentals
- Knowledge of star schema design
- Familiarity with Power BI service deployment
- Azure Active Directory access for testing (optional but recommended)
Understanding Row-Level Security
RLS Fundamentals
RLS Architecture:
Without RLS:
User Query → Power BI Dataset → Returns ALL rows
With RLS:
User Query → Power BI Dataset → Apply RLS Filter → Returns FILTERED rows
RLS Filter applies BEFORE aggregation:
✅ SUM(Sales[Amount]) WHERE Region = "North America"
❌ NOT: SUM(All Sales) THEN filter to "North America"
RLS vs Other Security Mechanisms
| Security Type | Scope | Use Case | Applied At |
|---|---|---|---|
| Row-Level Security (RLS) | Row filtering within tables | Restrict data rows per user/role | Dataset level |
| Object-Level Security (OLS) | Hide entire tables/columns | Sensitive columns (SSN, Salary) | Dataset level |
| Workspace Access | Report/dashboard access | Who can view/edit content | Service level |
| App Audiences | Content sections | Section-based access in apps | App level |
| Dataset Permissions | Dataset connection access | Who can build on dataset | Dataset level |
Best Practice: Use RLS in combination with other mechanisms for defense-in-depth security.
Static vs Dynamic RLS Patterns
Static Roles Pattern
// Role: "Finance Team"
// Applied to 'Sales' table
[Department] = "Finance"
// Role: "North America Sales"
// Applied to 'Sales' table
[Region] = "North America"
// Role: "Executive"
// No filter (sees all data)
TRUE()
Static Role Characteristics:
- ✅ Simple to implement and understand
- ✅ Fast query performance (no lookups)
- ✅ Easy to test
- ❌ Requires creating new role for each variation
- ❌ Doesn't scale (100s of regions = 100s of roles)
- ❌ Manual updates needed when data values change
When to Use Static Roles:
- Small number of distinct filters (< 10 roles)
- Filters based on static business units
- Simple scenarios where users map 1:1 with data segments
Dynamic Security Pattern
// Security Mapping Table Structure:
// SecurityMapping: UserPrincipalName | RegionKey | DepartmentKey | AccessLevel
// Applied to 'Sales' table, filtering by Region
[RegionKey] IN VALUES('SecurityMapping'[RegionKey])
// More robust version with user validation:
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserExists =
CALCULATE(
COUNTROWS('SecurityMapping'),
'SecurityMapping'[UserPrincipalName] = CurrentUser
) > 0
VAR AllowedRegions =
CALCULATETABLE(
VALUES('SecurityMapping'[RegionKey]),
'SecurityMapping'[UserPrincipalName] = CurrentUser
)
RETURN
IF(
UserExists,
[RegionKey] IN AllowedRegions,
FALSE() // Deny access if user not in mapping
)
Dynamic RLS Architecture:
┌─────────────────────────────────────┐
│ SecurityMapping Table │
├─────────────────────────────────────┤
│ UserPrincipalName │ RegionKey │
├───────────────────┼─────────────────┤
│ john@contoso.com │ NA │
│ john@contoso.com │ EMEA │
│ mary@contoso.com │ APAC │
│ exec@contoso.com │ NA │
│ exec@contoso.com │ EMEA │
│ exec@contoso.com │ APAC │
└─────────────────────────────────────┘
↓ Relationship
┌─────────────────────────────────────┐
│ Region Dimension │
├─────────────────────────────────────┤
│ RegionKey │ RegionName │ Country │
├───────────┼────────────┼────────────┤
│ NA │ North America │ USA │
│ EMEA │ Europe, MEA │ UK │
│ APAC │ Asia Pacific │ Japan │
└─────────────────────────────────────┘
↓ Relationship
┌─────────────────────────────────────┐
│ Sales Fact Table │
├─────────────────────────────────────┤
│ OrderID│ RegionKey│ Amount│ Date │
└─────────────────────────────────────┘
Dynamic Role Advantages:
- ✅ Single role scales to unlimited users
- ✅ Security managed via data (can be from SQL, Azure AD)
- ✅ Users can have multiple permitted values
- ✅ Easy to audit (query SecurityMapping table)
- ❌ Slightly more complex to implement
- ❌ Small performance overhead (lookup per query)
Implementing Dynamic Security
// Power Query: Load security mappings from SQL or Azure AD
let
Source = Sql.Database("server", "SecurityDB"),
SecurityTable = Source{[Schema="dbo",Item="UserRegionMapping"]}[Data],
// Transform to expected format
RenamedColumns = Table.RenameColumns(SecurityTable, {
{"Email", "UserPrincipalName"},
{"Region_Code", "RegionKey"}
}),
// Ensure lowercase emails (USERPRINCIPALNAME() returns lowercase)
LowercaseEmail = Table.TransformColumns(RenamedColumns, {
{"UserPrincipalName", Text.Lower, type text}
}),
// Add data validation
RemovedDuplicates = Table.Distinct(LowercaseEmail, {"UserPrincipalName", "RegionKey"})
in
RemovedDuplicates
// Create explicit role named "Dynamic Security"
// Apply this filter to all relevant tables
// Filter for Region dimension:
VAR CurrentUser = USERPRINCIPALNAME()
VAR AllowedRegions =
CALCULATETABLE(
VALUES('SecurityMapping'[RegionKey]),
'SecurityMapping'[UserPrincipalName] = CurrentUser
)
RETURN
'Region'[RegionKey] IN AllowedRegions
// Relationship propagates filter to Sales fact table automatically
Organizational Hierarchy Security
Manager-Based Security Pattern
// Employee Table Structure:
// EmployeeID | EmployeeName | ManagerID | Path
// Build Path in Power Query:
let
Source = Sql.Database("server", "HRDB"),
Employees = Source{[Schema="dbo",Item="Employees"]}[Data],
// Create hierarchy path using PATH function (requires SQL Server 2008+)
// Or build manually with recursive query
AddPath = Table.AddColumn(Employees, "ManagerPath",
each Text.Combine(
List.Transform(
// Recursive function to build path
BuildPath([EmployeeID], Employees),
each Text.From(_)
),
"|"
)
)
in
AddPath
// Alternative: Use PATH functions in DAX (requires parent-child table)
Employees[ManagerPath] =
PATH(Employees[EmployeeID], Employees[ManagerID])
RLS Filter for Manager Hierarchy:
// Applied to 'Sales' table
// Allows manager to see their own data + all subordinates' data
VAR CurrentUser = USERPRINCIPALNAME()
VAR CurrentEmployeeID =
LOOKUPVALUE(
'Employees'[EmployeeID],
'Employees'[Email], CurrentUser
)
VAR CurrentEmployeePath =
LOOKUPVALUE(
'Employees'[ManagerPath],
'Employees'[EmployeeID], CurrentEmployeeID
)
RETURN
PATHCONTAINS(
RELATED('Employees'[ManagerPath]),
CurrentEmployeeID
)
// Explanation:
// - Gets current user's Employee ID
// - Finds their position in org hierarchy
// - Checks if sales record's employee is descendant
// - PATHCONTAINS returns TRUE if CurrentEmployeeID is in the path
Alternative: Simpler Lookup Pattern
// For simpler hierarchies without PATH functions
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserEmployeeID =
LOOKUPVALUE('Employees'[EmployeeID], 'Employees'[Email], CurrentUser)
// Option 1: Direct reports only (one level)
RETURN
'Sales'[SalespersonID] = UserEmployeeID ||
RELATED('Employees'[ManagerID]) = UserEmployeeID
// Option 2: Check against manager mapping table
VAR AllowedEmployees =
CALCULATETABLE(
VALUES('ManagerHierarchy'[EmployeeID]),
'ManagerHierarchy'[ManagerID] = UserEmployeeID
)
RETURN
'Sales'[SalespersonID] IN AllowedEmployees
Testing Hierarchy Security
// Create measure to debug RLS during testing
CurrentUser Debug =
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserEmployeeID =
LOOKUPVALUE('Employees'[EmployeeID], 'Employees'[Email], CurrentUser)
VAR EmployeeCount =
CALCULATE(
COUNTROWS('Employees'),
PATHCONTAINS('Employees'[ManagerPath], UserEmployeeID)
)
RETURN
"User: " & CurrentUser & " | " &
"Employee ID: " & UserEmployeeID & " | " &
"Can See: " & EmployeeCount & " employees"
Many-to-Many Security Relationships
Bridge Table Pattern
Scenario: Users can access multiple regions AND products
Products can be accessed by multiple users
User-Product-Region Bridge:
┌──────────────────────────────────────┐
│ UserSecurityBridge │
├──────────────────────────────────────┤
│ UserPrincipalName│RegionKey│ProductCat│
├──────────────────┼─────────┼──────────┤
│ sales@contoso.com│ NA │ Electronics│
│ sales@contoso.com│ NA │ Furniture │
│ sales@contoso.com│ EMEA │ Electronics│
│ manager@... │ NA │ *All* │
│ manager@... │ EMEA │ *All* │
│ manager@... │ APAC │ *All* │
└──────────────────────────────────────┘
Implementation:
// Applied to Sales table
VAR CurrentUser = USERPRINCIPALNAME()
// Check Region access
VAR AllowedRegions =
CALCULATETABLE(
VALUES('UserSecurityBridge'[RegionKey]),
'UserSecurityBridge'[UserPrincipalName] = CurrentUser
)
// Check Product Category access
VAR AllowedCategories =
CALCULATETABLE(
VALUES('UserSecurityBridge'[ProductCategory]),
'UserSecurityBridge'[UserPrincipalName] = CurrentUser
)
// Check if user has "*All*" wildcard access
VAR HasAllRegions = "*All*" IN AllowedRegions
VAR HasAllProducts = "*All*" IN AllowedCategories
RETURN
(
HasAllRegions ||
RELATED('Region'[RegionKey]) IN AllowedRegions
) &&
(
HasAllProducts ||
RELATED('Product'[Category]) IN AllowedCategories
)
Handling Wildcards and Special Cases
// Support for wildcards like "*", "All", or NULL (= full access)
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserMappings =
FILTER(
'UserSecurityBridge',
'UserSecurityBridge'[UserPrincipalName] = CurrentUser
)
// Check if user has wildcard access
VAR HasWildcard =
COUNTROWS(
FILTER(
UserMappings,
'UserSecurityBridge'[RegionKey] IN {"*", "All", "*All*"} ||
ISBLANK('UserSecurityBridge'[RegionKey])
)
) > 0
// Get allowed regions (excluding wildcards)
VAR AllowedRegions =
CALCULATETABLE(
VALUES('UserSecurityBridge'[RegionKey]),
'UserSecurityBridge'[UserPrincipalName] = CurrentUser,
'UserSecurityBridge'[RegionKey] <> "*",
'UserSecurityBridge'[RegionKey] <> "All",
'UserSecurityBridge'[RegionKey] <> "*All*",
NOT(ISBLANK('UserSecurityBridge'[RegionKey]))
)
RETURN
HasWildcard ||
RELATED('Region'[RegionKey]) IN AllowedRegions
Performance Optimization for RLS
Avoid Expensive Lookups
// ❌ SLOW: Multiple LOOKUPVALUE calls
VAR CurrentUser = USERPRINCIPALNAME()
VAR Region1 = LOOKUPVALUE('SecurityMapping'[Region], 'SecurityMapping'[User], CurrentUser, 'SecurityMapping'[Priority], 1)
VAR Region2 = LOOKUPVALUE('SecurityMapping'[Region], 'SecurityMapping'[User], CurrentUser, 'SecurityMapping'[Priority], 2)
RETURN [RegionKey] IN {Region1, Region2}
// ✅ FAST: Single CALCULATETABLE
VAR CurrentUser = USERPRINCIPALNAME()
VAR AllowedRegions =
CALCULATETABLE(
VALUES('SecurityMapping'[Region]),
'SecurityMapping'[User] = CurrentUser
)
RETURN [RegionKey] IN AllowedRegions
Pre-Calculate Paths and Keys
// Power Query: Pre-calculate hierarchy paths
let
Source = Sql.Database("server", "DB"),
Employees = Source{[Schema="dbo",Item="Employees"]}[Data],
// Build complete manager path as concatenated string
AddManagerPath = Table.AddColumn(Employees, "ManagerPathString",
each BuildPathString([EmployeeID], Employees)), // Custom function
// Or use SQL Server HIERARCHYID if available
// Or use recursive CTE in source query
// Also pre-calculate depth level
AddLevel = Table.AddColumn(AddManagerPath, "Level",
each List.Count(Text.Split([ManagerPathString], "|")))
in
AddLevel
Use Variables to Cache Calculations
// ❌ SLOW: Recalculates USERPRINCIPALNAME() multiple times
RETURN
USERPRINCIPALNAME() IN VALUES('SecurityMapping'[User]) &&
RELATED('Region'[RegionKey]) IN
CALCULATETABLE(
VALUES('SecurityMapping'[Region]),
'SecurityMapping'[User] = USERPRINCIPALNAME() // Calculated again!
)
// ✅ FAST: Calculate once with variable
VAR CurrentUser = USERPRINCIPALNAME()
VAR AllowedRegions =
CALCULATETABLE(
VALUES('SecurityMapping'[Region]),
'SecurityMapping'[User] = CurrentUser
)
RETURN
RELATED('Region'[RegionKey]) IN AllowedRegions
Optimize Security Mapping Table
Security Mapping Table Optimization:
❌ Avoid:
- Text-based keys (use integers)
- Duplicate rows
- Unnecessary columns
- Complex calculated columns
✅ Best Practices:
- Integer surrogate keys
- Indexed in source database
- Minimal columns (User, Key, Level)
- Pre-filtered (remove inactive users)
- Sorted by User for better compression
Testing RLS Thoroughly
Power BI Desktop Testing
Test as Role/User in Power BI Desktop:
1. Modeling tab → Security → Manage roles
2. Create test roles with known filters
3. Modeling tab → View as role
4. Select role + enter test user email
5. Validate visuals show expected data only
Test Cases:
☐ User with single permission
☐ User with multiple permissions
☐ User with NO permissions (should see blank)
☐ User with wildcard/all access
☐ Manager with hierarchy access
☐ User not in security mapping (should deny)
☐ Test each dimension independently
☐ Test combinations of dimensions
Power BI Service Testing
# Create test users in Azure AD for different scenarios
# Add them to dataset roles in Power BI Service
# Test User 1: Limited access (NA region only)
# Test User 2: Manager (can see subordinates)
# Test User 3: Executive (sees all)
# Test User 4: Not in security mapping (should see nothing)
# Share report with test users
Add-PowerBIWorkspaceUser -WorkspaceId "workspace-id" `
-UserPrincipalName "testuser1@contoso.com" `
-AccessRight Viewer
# Have each test user login and verify data visibility
# Check that filters work correctly with slicers
# Verify drill-through and cross-filtering respect RLS
Automated RLS Testing
// Create validation measures for testing
RLS Validation =
VAR CurrentUser = USERPRINCIPALNAME()
VAR ExpectedRowCount = 1000 // Known value for test user
VAR ActualRowCount = COUNTROWS('Sales')
VAR IsValid = ActualRowCount = ExpectedRowCount
RETURN
IF(
IsValid,
"✅ PASS: " & ActualRowCount & " rows",
"❌ FAIL: Expected " & ExpectedRowCount & ", got " & ActualRowCount
)
// Log results for regression testing
Common Testing Pitfalls
⚠️ Common RLS Testing Mistakes:
1. ❌ Testing only in Desktop (different from Service)
✅ Test in Service with real Azure AD users
2. ❌ Not testing null/missing security mappings
✅ Test users NOT in mapping table (should see nothing)
3. ❌ Only testing individual dimensions
✅ Test combinations (Region AND Product filtering)
4. ❌ Not testing relationships
✅ Verify filter propagates through all related tables
5. ❌ Forgetting about calculated tables
✅ Test that virtual tables respect RLS
6. ❌ Not testing wildcards/special values
✅ Test *, NULL, "All" scenarios
7. ❌ Overlooking many-to-many relationships
✅ Verify bidirectional filters work correctly
8. ❌ Not testing performance impact
✅ Compare query times with and without RLS
Advanced RLS Patterns
Time-Based Security
// Users can only see data from their tenure period
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserStartDate =
LOOKUPVALUE('Employees'[StartDate], 'Employees'[Email], CurrentUser)
VAR UserEndDate =
LOOKUPVALUE('Employees'[EndDate], 'Employees'[Email], CurrentUser)
RETURN
'Sales'[OrderDate] >= UserStartDate &&
(ISBLANK(UserEndDate) || 'Sales'[OrderDate] <= UserEndDate)
Conditional RLS by User Attribute
// Different RLS logic for different user types
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserType =
LOOKUPVALUE('SecurityMapping'[UserType], 'SecurityMapping'[Email], CurrentUser)
RETURN
SWITCH(
UserType,
"Sales", [Region] = "NA", // Sales sees only NA
"Manager", [Department] IN {"Sales", "Marketing"}, // Manager sees multiple depts
"Executive", TRUE(), // Executive sees all
FALSE() // Default: no access
)
RLS with Azure AD Groups
// Import Azure AD group membership
let
Source = AzureActiveDirectory.GetUserGroups(),
ExpandedGroups = Table.ExpandListColumn(Source, "Groups"),
// Map groups to regions
AddRegion = Table.AddColumn(ExpandedGroups, "RegionKey",
each if [GroupName] = "Sales-NA" then "NA"
else if [GroupName] = "Sales-EMEA" then "EMEA"
else if [GroupName] = "Sales-APAC" then "APAC"
else null
),
FilteredRows = Table.SelectRows(AddRegion, each [RegionKey] <> null)
in
FilteredRows
Best Practices Checklist
Design Phase
☑ Architecture:
☐ Use dynamic RLS pattern for scalability (not static roles)
☐ Centralize security mapping in single table
☐ Design security table to be managed by HR/Security team
☐ Document RLS logic and maintenance procedures
☐ Plan for wildcards/"All" access patterns
☑ Performance:
☐ Use integer keys instead of text
☐ Pre-calculate paths and hierarchies
☐ Use variables to avoid recalculation
☐ Index security tables in source database
☐ Test with production data volumes
☑ Maintainability:
☐ Use meaningful role names (not "Role1", "Role2")
☐ Store security mappings in database (not hardcoded)
☐ Version control DAX RLS formulas
☐ Document special cases and exceptions
☐ Plan for onboarding/offboarding workflow
Implementation Phase
☑ Development:
☐ Create RLS role in Power BI Desktop
☐ Apply filter to all relevant tables (not just facts)
☐ Test with "View as Role" feature
☐ Validate filter propagation through relationships
☐ Check calculated tables respect RLS
☑ Testing:
☐ Test with multiple user scenarios
☐ Test users NOT in security mapping
☐ Test wildcards and special values
☐ Test hierarchy/organizational scenarios
☐ Performance test with large datasets
☐ Test in Power BI Service (not just Desktop)
☑ Deployment:
☐ Add users/groups to roles in Service
☐ Validate with real users
☐ Monitor query performance
☐ Set up alerting for access issues
☐ Document support procedures
Maintenance Phase
☑ Ongoing:
☐ Quarterly access reviews
☐ Audit security mapping against HR system
☐ Monitor for performance degradation
☐ Update documentation for changes
☐ Train new report creators on RLS requirements
☐ Regression test after dataset updates
Troubleshooting Guide
Issue 1: Users See Blank Reports
Symptoms:
- User opens report and sees no data
- All visuals are empty
- No error message
Diagnosis:
// Create diagnostic measure
User Security Check =
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserInMapping =
CALCULATE(
COUNTROWS('SecurityMapping'),
'SecurityMapping'[UserPrincipalName] = CurrentUser
) > 0
VAR AllowedRegions =
CALCULATETABLE(
VALUES('SecurityMapping'[RegionKey]),
'SecurityMapping'[UserPrincipalName] = CurrentUser
)
RETURN
"User: " & CurrentUser & " | " &
"In Mapping: " & IF(UserInMapping, "Yes", "❌ No") & " | " &
"Regions: " & CONCATENATEX(AllowedRegions, [RegionKey], ", ")
Common Causes & Resolutions:
User not in security mapping table
- Add user to SecurityMapping table
- Or add user's Azure AD group
Email case sensitivity mismatch
- USERPRINCIPALNAME() returns lowercase
- Ensure security table emails are lowercase
RLS filter too restrictive
- Review filter logic
- Check for unintended blank/null filters
Unrelated tables not filtered
- Apply RLS to all dimension tables
- Check relationship directions
Issue 2: Users See Unauthorized Data
Symptoms:
- User sees data they shouldn't
- RLS appears to not be working
Diagnosis:
# Check role membership in Power BI Service
$datasetId = "dataset-id"
$roles = Invoke-PowerBIRestMethod -Url "datasets/$datasetId/roles" -Method Get | ConvertFrom-Json
# Check who is assigned to each role
foreach ($role in $roles.value) {
$members = Invoke-PowerBIRestMethod -Url "datasets/$datasetId/roles/$($role.id)/members" -Method Get | ConvertFrom-Json
Write-Host "Role: $($role.name)"
Write-Host "Members: $($members.value.principalName -join ', ')"
}
Common Causes & Resolutions:
User not assigned to any role
- Assign user/group to appropriate role in Service
- Users without role assignment see ALL data!
RLS not applied to all tables
- Apply filter to dimension tables, not just facts
- Check many-to-many relationships
Bidirectional relationship bypassing RLS
- Review relationship directions
- Set to single-direction where possible
Calculated tables not respecting RLS
- RLS applies to base tables only by default
- Recreate calculated table with RLS-aware logic
Issue 3: Performance Degradation
Symptoms:
- Reports slow after implementing RLS
- Query times 2-10x slower than without RLS
Diagnosis:
Use DAX Studio to analyze query plan:
1. Connect to dataset via XMLA endpoint
2. Run query with Server Timings enabled
3. Review:
- Storage Engine vs Formula Engine time
- Number of Storage Engine queries
- LOOKUPVALUE or FILTER operations
High Formula Engine time = inefficient RLS logic
Optimizations:
- Pre-calculate paths/hierarchies in Power Query
// Move complex logic from DAX to Power Query
AddManagerPath = Table.AddColumn(Employees, "ManagerPath",
each BuildPath([EmployeeID], [ManagerID]))
- Use IN operator instead of OR chains
// ❌ SLOW
RETURN [Region] = "NA" || [Region] = "EMEA" || [Region] = "APAC"
// ✅ FAST
RETURN [Region] IN {"NA", "EMEA", "APAC"}
- Cache user lookups with variables
VAR CurrentUser = USERPRINCIPALNAME() // Calculate once
VAR AllowedKeys = CALCULATETABLE(...) // Calculate once
RETURN [Key] IN AllowedKeys
- Use integer keys instead of text
Text comparison: Slower, larger memory footprint
Integer comparison: Faster, better compression
Issue 4: RLS Works in Desktop but Not Service
Symptoms:
- RLS filters work when testing in Desktop
- Same user sees all data in Service
Common Causes:
Role not assigned in Service
- Desktop "View as" simulates RLS
- Service requires explicit role assignment
Different user identity
- Desktop may use Windows identity
- Service uses Azure AD identity
- Check USERPRINCIPALNAME() value in each
Row-level security not enabled on dataset
- Verify roles exist in Service
- Check Dataset Settings → Security
Resolution:
# Assign users to roles in Service
$datasetId = "dataset-id"
$roleId = "role-id"
$body = @{
identifier = "user@contoso.com"
principalType = "User"
} | ConvertTo-Json
Invoke-PowerBIRestMethod -Url "datasets/$datasetId/roles/$roleId/members" -Method Post -Body $body
RLS Governance and Auditing
Security Mapping Audit
# Regular audit script to validate security mappings
function Audit-RLSSecurityMappings {
param(
[string]$DatasetId,
[string]$HRSystemConnectionString
)
# Get current security mappings from Power BI dataset
$pbiMappings = Invoke-PowerBIRestMethod -Url "datasets/$DatasetId/executeQueries" -Method Post -Body (@{
queries = @(
@{
query = "EVALUATE 'SecurityMapping'"
}
)
} | ConvertTo-Json)
# Get current employees from HR system
$hrEmployees = Invoke-Sqlcmd -ConnectionString $HRSystemConnectionString -Query "
SELECT Email, Department, Region, TerminationDate
FROM Employees
WHERE TerminationDate IS NULL OR TerminationDate > GETDATE()
"
# Compare and identify discrepancies
$orphanedMappings = $pbiMappings | Where-Object {
$_.UserPrincipalName -notin $hrEmployees.Email
}
$missingMappings = $hrEmployees | Where-Object {
$_.Email -notin $pbiMappings.UserPrincipalName
}
# Generate report
Write-Host "=== RLS Security Mapping Audit ==="
Write-Host "Orphaned Mappings (users no longer in HR): $($orphanedMappings.Count)"
Write-Host "Missing Mappings (active users without access): $($missingMappings.Count)"
if ($orphanedMappings.Count -gt 0) {
Write-Host "`n⚠️ Orphaned Mappings (should be removed):"
$orphanedMappings | Format-Table UserPrincipalName, RegionKey
}
if ($missingMappings.Count -gt 0) {
Write-Host "`n⚠️ Missing Mappings (should be added):"
$missingMappings | Format-Table Email, Department, Region
}
}
# Run monthly audit
Audit-RLSSecurityMappings -DatasetId "dataset-id" -HRSystemConnectionString "connection-string"
RLS Design Patterns Comparison
Pattern Selection Matrix
| Scenario | Recommended Pattern | Complexity | Performance | Maintainability |
|---|---|---|---|---|
| 5-10 regions, static assignments | Static Roles | Low | Excellent | Medium |
| Dynamic assignments from DB | Dynamic RLS (Security Table) | Medium | Good | Excellent |
| Organizational hierarchy (manager → reports) | Hierarchical (PATH) | High | Medium | Medium |
| Multi-tenant SaaS application | Customer/Tenant Key | Medium | Good | Good |
| Complex business rules (territory overlap) | Many-to-Many Bridge | Very High | Medium | Medium |
| 1000+ users with 100+ security groups | Azure AD Groups | Low | Excellent | Excellent |
When to Use Each Pattern
Static Roles:
- ✅ Stable security dimensions (<20 roles)
- ✅ Infrequent changes (quarterly or less)
- ✅ Simple business logic (one dimension)
- ❌ Hundreds of roles
- ❌ Frequent organizational changes
Dynamic RLS:
- ✅ Frequent security mapping changes
- ✅ Driven by external system (HR database)
- ✅ Moderate to large user base (50-10,000+ users)
- ✅ Multiple security dimensions (region AND department)
- ❌ Extremely complex business rules (use bridge table)
Hierarchical Pattern:
- ✅ Manager/employee relationships
- ✅ Cascading visibility (manager sees all reports)
- ✅ Organizational structure is key security boundary
- ❌ Performance-critical applications (PATH functions are slower)
- ❌ Frequently changing org structure (PATH recalculation overhead)
Many-to-Many Pattern:
- ✅ Users belong to multiple groups with overlapping permissions
- ✅ Products assigned to multiple categories
- ✅ Complex business rules (territory overlap, matrix organizations)
- ❌ Simple scenarios (over-engineering)
- ❌ Performance-critical dashboards (adds query complexity)
Azure AD Group Pattern:
- ✅ Large organizations with established AD groups
- ✅ Leverage existing IAM infrastructure
- ✅ Centralized access management
- ✅ Audit trail via Azure AD logs
- ❌ Granular data-driven security (use dynamic RLS)
- ❌ External users without Azure AD (use custom security table)
Hybrid Patterns
Scenario: Region-based with Manager Hierarchy
// Combine dynamic RLS with hierarchical logic
[RLS - Regional + Hierarchy] =
VAR CurrentUser = USERPRINCIPALNAME()
// Get user's allowed regions
VAR AllowedRegions =
CALCULATETABLE(
VALUES(SecurityMapping[RegionKey]),
SecurityMapping[UserPrincipalName] = CurrentUser
)
// Get user's employee ID for hierarchy
VAR CurrentEmployeeID =
LOOKUPVALUE(
Employee[EmployeeID],
Employee[Email], CurrentUser
)
// Get all subordinate employees (including self)
VAR SubordinateEmployees =
FILTER(
ALL(Employee[EmployeeID]),
PATHCONTAINS(Employee[ManagerPath], CurrentEmployeeID)
)
// Combine filters: Region OR Subordinate
RETURN
[RegionKey] IN AllowedRegions
|| [EmployeeID] IN SubordinateEmployees
Scenario: Customer Portal with Time-Based Access
// Time-limited customer access pattern
[RLS - Customer Portal] =
VAR CurrentUser = USERPRINCIPALNAME()
// Get customer ID and access dates
VAR CustomerID = LOOKUPVALUE(CustomerPortalUsers[CustomerID], CustomerPortalUsers[Email], CurrentUser)
VAR AccessStart = LOOKUPVALUE(CustomerPortalUsers[AccessStartDate], CustomerPortalUsers[Email], CurrentUser)
VAR AccessEnd = LOOKUPVALUE(CustomerPortalUsers[AccessEndDate], CustomerPortalUsers[Email], CurrentUser)
// Ensure access is still valid
VAR HasActiveAccess = AccessStart <= TODAY() && AccessEnd >= TODAY()
// Filter data
RETURN
Sales[CustomerID] = CustomerID
&& HasActiveAccess
RLS Testing Framework
# Comprehensive RLS testing script
function Test-RLSImplementation {
param(
[string]$DatasetId,
[string]$WorkspaceId,
[array]$TestUsers
)
$testResults = @()
foreach ($user in $TestUsers) {
Write-Host "Testing RLS for: $($user.Email)"
# Impersonate user and query data
$body = @{
queries = @(
@{
query = "
EVALUATE
SUMMARIZECOLUMNS(
'Region'[RegionName],
'Customer'[CustomerName],
""Total Sales"", SUM('Sales'[Amount])
)
ORDER BY [Total Sales] DESC
"
}
)
serializerSettings = @{
includeNulls = $false
}
impersonatedUserName = $user.Email
} | ConvertTo-Json -Depth 5
try {
$result = Invoke-PowerBIRestMethod `
-Url "datasets/$DatasetId/executeQueries" `
-Method Post `
-Body $body | ConvertFrom-Json
$rowCount = $result.results[0].tables[0].rows.Count
$totalSales = ($result.results[0].tables[0].rows | Measure-Object -Property "[Total Sales]" -Sum).Sum
$testResults += [PSCustomObject]@{
User = $user.Email
ExpectedRegion = $user.ExpectedRegion
RowsReturned = $rowCount
TotalSales = $totalSales
Status = if ($rowCount -eq $user.ExpectedRowCount) { "✅ PASS" } else { "❌ FAIL" }
}
}
catch {
$testResults += [PSCustomObject]@{
User = $user.Email
ExpectedRegion = $user.ExpectedRegion
RowsReturned = 0
TotalSales = 0
Status = "❌ ERROR: $($_.Exception.Message)"
}
}
}
return $testResults
}
# Define test cases
$testUsers = @(
@{Email = "manager@contoso.com"; ExpectedRegion = "North America"; ExpectedRowCount = 150},
@{Email = "rep.europe@contoso.com"; ExpectedRegion = "Europe"; ExpectedRowCount = 80},
@{Email = "rep.asia@contoso.com"; ExpectedRegion = "Asia"; ExpectedRowCount = 60}
)
# Execute tests
$results = Test-RLSImplementation -DatasetId "dataset-id" -WorkspaceId "workspace-id" -TestUsers $testUsers
$results | Format-Table -AutoSize
Performance Benchmarking
# Measure RLS performance impact
function Measure-RLSPerformance {
param(
[string]$DatasetId,
[string]$BaselineQuery,
[array]$TestUsers
)
# Baseline (no RLS)
Write-Host "Running baseline query (no RLS)..."
$baselineStart = Get-Date
$baseline = Invoke-PowerBIRestMethod -Url "datasets/$DatasetId/executeQueries" -Method Post -Body (@{
queries = @(@{query = $BaselineQuery})
} | ConvertTo-Json)
$baselineEnd = Get-Date
$baselineDuration = ($baselineEnd - $baselineStart).TotalMilliseconds
Write-Host "Baseline: $baselineDuration ms"
# Test with RLS for each user
$rlsResults = foreach ($user in $TestUsers) {
Write-Host "Testing RLS for $($user.Email)..."
$rlsStart = Get-Date
$body = @{
queries = @(@{query = $BaselineQuery})
impersonatedUserName = $user.Email
} | ConvertTo-Json
$result = Invoke-PowerBIRestMethod -Url "datasets/$DatasetId/executeQueries" -Method Post -Body $body
$rlsEnd = Get-Date
$rlsDuration = ($rlsEnd - $rlsStart).TotalMilliseconds
[PSCustomObject]@{
User = $user.Email
Duration_ms = $rlsDuration
Overhead_ms = $rlsDuration - $baselineDuration
Overhead_Pct = [math]::Round((($rlsDuration / $baselineDuration) - 1) * 100, 2)
}
}
# Summary
$avgOverhead = ($rlsResults | Measure-Object -Property Overhead_Pct -Average).Average
Write-Host "`n=== Performance Impact Summary ==="
Write-Host "Baseline (no RLS): $baselineDuration ms"
Write-Host "Average RLS Overhead: $([math]::Round($avgOverhead, 2))%"
if ($avgOverhead -lt 10) {
Write-Host "✅ Performance: EXCELLENT (< 10% overhead)"
} elseif ($avgOverhead -lt 25) {
Write-Host "⚠️ Performance: ACCEPTABLE (10-25% overhead)"
} else {
Write-Host "❌ Performance: NEEDS OPTIMIZATION (> 25% overhead)"
}
return $rlsResults
}
# Run performance benchmark
$query = "EVALUATE SUMMARIZECOLUMNS('Product'[Category], ""Sales"", SUM('Sales'[Amount]))"
$perfResults = Measure-RLSPerformance -DatasetId "dataset-id" -BaselineQuery $query -TestUsers $testUsers
$perfResults | Format-Table -AutoSize
Key Takeaways
- Dynamic RLS scales better than static roles for most enterprise scenarios
- Test thoroughly in both Desktop and Service with real user accounts
- Performance matters: Use variables, pre-calculate paths, prefer integer keys
- Security mapping is data: Store in database, version control, audit regularly
- Apply RLS to all tables: Not just facts, include all related dimensions
- Many-to-many requires bridge tables: Plan carefully for complex scenarios
- Organizational hierarchies need PATH functions: Or pre-calculate in Power Query
- Monitor and audit: Quarterly reviews, automated validation, performance tracking
Next Steps
- Audit current RLS implementation (if exists) or design new architecture
- Create SecurityMapping table in source database
- Implement dynamic RLS pattern for primary security dimensions
- Build comprehensive test plan with multiple user scenarios
- Test in Power BI Service with real Azure AD users
- Measure performance impact and optimize as needed
- Document RLS logic and maintenance procedures
- Schedule quarterly security mapping audits
- Train report creators on RLS design patterns
- Implement monitoring for RLS-related access issues
Additional Resources
- Power BI Row-Level Security
- Dynamic RLS Patterns
- Object-Level Security
- Security Best Practices
- USERPRINCIPALNAME Function
- PATH Functions in DAX
Secure. Test. Monitor. Audit.