Security Skill

This skill helps implement Row-Level Security (RLS) and Object-Level Security (OLS) for Power BI semantic models.

When to Use This Skill

•Restricting data access by user/role
•Implementing dynamic security based on user identity
•Creating multi-tenant data models
•Protecting sensitive columns with OLS
•Setting up manager/hierarchy security
•Testing security configurations

Security Types

Type	Controls	Use Case
RLS (Row-Level Security)	Which rows users can see	Regional sales reps see only their region
OLS (Object-Level Security)	Which columns/tables users can see	Hide salary column from non-HR users

Row-Level Security (RLS)

TMDL Syntax

tmdl

role 'Sales Representatives'
	modelPermission: read
	lineageTag: <guid>

	tablePermission Sales = 'Sales'[Region] = USERPRINCIPALNAME()

Role Structure

tmdl

role 'RoleName'
	modelPermission: read
	description: 'Description of who this role is for'
	lineageTag: <guid>

	tablePermission TableName = <DAX filter expression>
	tablePermission AnotherTable = <DAX filter expression>

Model Permissions

Permission	Description
`read`	Can read data (standard for RLS)
`administrator`	Full access (no filtering)
`none`	No access

Common RLS Patterns

Static Role (Fixed Filter)

tmdl

/// Role for West region users
role 'West Region'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales = 'Sales'[Region] = "West"

Dynamic Security (User Identity)

tmdl

/// Each user sees only their own data
role 'User Data'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		'Sales'[User Email] = USERPRINCIPALNAME()

Security Table Lookup

tmdl

/// Users mapped to regions in a security table
role 'Region Security'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		CONTAINS(
		    FILTER(
		        'User Region Mapping',
		        'User Region Mapping'[User Email] = USERPRINCIPALNAME()
		    ),
		    'User Region Mapping'[Region], 'Sales'[Region]
		)

Manager Hierarchy

tmdl

/// Managers see their team's data
role 'Manager Security'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		VAR CurrentUser = USERPRINCIPALNAME()
		VAR UserEmployeeID = LOOKUPVALUE(
		    Employees[Employee ID],
		    Employees[Email], CurrentUser
		)
		RETURN
		    PATHCONTAINS(
		        'Sales'[Manager Path],
		        UserEmployeeID
		    )

Multi-Tenant with Tenant ID

tmdl

/// Each tenant sees only their data
role 'Tenant Isolation'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		VAR UserTenant = LOOKUPVALUE(
		    Users[Tenant ID],
		    Users[Email], USERPRINCIPALNAME()
		)
		RETURN
		    'Sales'[Tenant ID] = UserTenant

Multiple Conditions

tmdl

/// Users see data for their region AND department
role 'Region and Department'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		VAR CurrentUser = USERPRINCIPALNAME()
		VAR UserRegion = LOOKUPVALUE(Users[Region], Users[Email], CurrentUser)
		VAR UserDept = LOOKUPVALUE(Users[Department], Users[Email], CurrentUser)
		RETURN
		    'Sales'[Region] = UserRegion &&
		    'Sales'[Department] = UserDept

Date-Based Access

tmdl

/// Users can only see data after their start date
role 'Date Restricted'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		VAR UserStartDate = LOOKUPVALUE(
		    Users[Start Date],
		    Users[Email], USERPRINCIPALNAME()
		)
		RETURN
		    'Sales'[Date] >= UserStartDate

Security Table Design

User-to-Data Mapping Table

tmdl

table 'User Security'
	lineageTag: {{guid}}

	column 'User Email'
		dataType: string
		lineageTag: {{guid}}

	column 'Region'
		dataType: string
		lineageTag: {{guid}}

	column 'Department'
		dataType: string
		lineageTag: {{guid}}

	column 'Access Level'
		dataType: string
		lineageTag: {{guid}}

	partition 'User Security' = m
		mode: import
		source =
			let
			    Source = Table.FromRows({
			        {"user1@company.com", "North", "Sales", "Standard"},
			        {"user2@company.com", "South", "Sales", "Standard"},
			        {"manager@company.com", "ALL", "Sales", "Manager"}
			    }, {"User Email", "Region", "Department", "Access Level"})
			in
			    Source

RLS with Security Table

tmdl

role 'Data Access'
	modelPermission: read
	lineageTag: {{guid}}

	tablePermission Sales =
		VAR CurrentUser = USERPRINCIPALNAME()
		VAR UserAccessLevel = LOOKUPVALUE(
		    'User Security'[Access Level],
		    'User Security'[User Email], CurrentUser
		)
		VAR UserRegion = LOOKUPVALUE(
		    'User Security'[Region],
		    'User Security'[User Email], CurrentUser
		)
		RETURN
		    IF(
		        UserAccessLevel = "Manager" || UserRegion = "ALL",
		        TRUE(),
		        'Sales'[Region] = UserRegion
		    )

Object-Level Security (OLS)

Column-Level Security

OLS hides columns from specific roles. In TMDL:

tmdl

role 'Non-HR Users'
	modelPermission: read
	lineageTag: {{guid}}

	/// Hide salary column
	columnPermission Employees.Salary = none

	/// Hide SSN column
	columnPermission Employees.SSN = none

Table-Level Security

tmdl

role 'External Users'
	modelPermission: read
	lineageTag: {{guid}}

	/// Completely hide internal table
	tablePermission 'Internal Metrics' = false

OLS Permission Values

Permission	Effect
`none`	Column hidden, inaccessible
`read`	Column visible (default)

Combining RLS and OLS

tmdl

/// Role with both row and column restrictions
role 'Limited Access'
	modelPermission: read
	lineageTag: {{guid}}

	/// Row filter: only see own region
	tablePermission Sales = 'Sales'[Region] = LOOKUPVALUE(
	    Users[Region], Users[Email], USERPRINCIPALNAME()
	)

	/// Column filter: hide margin columns
	columnPermission Sales.'Profit Margin' = none
	columnPermission Sales.'Cost' = none

DAX Security Functions

USERPRINCIPALNAME()

Returns the email/UPN of the current user:

dax

USERPRINCIPALNAME()  // Returns "user@company.com"

USERNAME()

Returns the domain\username (less common in cloud):

dax

USERNAME()  // Returns "DOMAIN\username"

CUSTOMDATA()

Returns custom data passed via connection string:

dax

CUSTOMDATA()  // Returns custom parameter value

USEROBJECTID()

Returns the Azure AD Object ID:

dax

USEROBJECTID()  // Returns GUID of user

Testing RLS

In Power BI Desktop

•Modeling tab > View as > Roles
•Select role(s) to test
•Optionally enter "Other user" email
•Click OK to see filtered data

In Power BI Service

•Dataset settings > Security
•Test as role with specific user
•Verify filtered results

Test Measure

Create a measure to debug security:

dax

Security Debug =
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserRegion = LOOKUPVALUE(
    'User Security'[Region],
    'User Security'[User Email], CurrentUser
)
RETURN
    "User: " & CurrentUser & " | Region: " & UserRegion

Performance Considerations

DO

tmdl

/// GOOD: Direct column comparison
tablePermission Sales = 'Sales'[Region] = LOOKUPVALUE(...)

/// GOOD: Use CONTAINS for multiple values
tablePermission Sales = CONTAINS(
    VALUES('User Security'[Region]),
    'User Security'[Region], 'Sales'[Region]
)

DON'T

tmdl

/// BAD: Complex DAX in security filter
tablePermission Sales =
    CALCULATE(
        COUNTROWS(FILTER(...)),  // Avoid complex calculations
        ...
    ) > 0

/// BAD: Iterating over large tables
tablePermission Sales =
    SUMX(LargeTable, ...)  // Slow!

Security Best Practices

Design Principles

•Principle of Least Privilege - Start restrictive, add access
•Single Security Table - Centralize user mappings
•Test All Scenarios - Every role combination
•Document Roles - Clear descriptions of access levels
•Avoid RLS on Aggregated Models - May expose totals

Common Mistakes

Mistake	Problem	Fix
No default restriction	New users see all data	Use catch-all role
Filter only fact table	Dimension data exposed	Filter fact AND dimensions
Complex DAX filters	Poor performance	Simplify with lookup tables
Hardcoded emails	Maintenance nightmare	Use security table
No testing	Security gaps	Test as each role

Security Checklist

• All fact tables have appropriate filters
• Related dimension tables filtered (if needed)
• Security table maintained and current
• Roles tested with real user emails
• Performance acceptable with security applied
• Documentation of role purposes
• No admin role exposed to end users
• OLS applied to sensitive columns

Multiple Roles

When a user belongs to multiple roles, filters combine with OR:

code

User in "North Region" AND "Sales Department"
→ Sees: (Region = "North") OR (Department = "Sales")

To require AND logic, use a single role with combined filter:

tmdl

role 'North Sales'
	tablePermission Sales =
	    'Sales'[Region] = "North" && 'Sales'[Department] = "Sales"

Boundaries and Constraints

DO

•Use USERPRINCIPALNAME() for cloud deployments
•Create a security mapping table for flexibility
•Test with "View as" before deployment
•Filter at the most granular fact table level
•Document each role's intended audience

DO NOT

•Never use RLS as the only security layer
•Don't filter only dimension tables (facts may leak)
•Avoid complex DAX in filter expressions
•Never expose administrator role to users
•Don't rely on RLS for aggregated/composite models

Workflow Integration

After implementing security:

•Test locally - Use "View as Roles" in Desktop
•Document - Describe each role's purpose
•Deploy - Publish to Power BI Service
•Assign - Add users/groups to roles in Service
•Validate - Test with actual user accounts