Security

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

Row-Level Security (RLS)

TMDL Syntax

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

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

Role Structure

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

Common RLS Patterns

Static Role (Fixed Filter)

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

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

Dynamic Security (User Identity)

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

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

Security Table Lookup

/// 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

/// 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

/// 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

/// 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

/// 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

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

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:

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

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

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

OLS Permission Values

Combining RLS and OLS

/// 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:

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

USERNAME()

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

USERNAME()  // Returns "DOMAIN\username"

CUSTOMDATA()

Returns custom data passed via connection string:

CUSTOMDATA()  // Returns custom parameter value

USEROBJECTID()

Returns the Azure AD Object ID:

USEROBJECTID()  // Returns GUID of user

Testing RLS

In Power BI Desktop

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

In Power BI Service

1. Dataset settings > Security
2. Test as role with specific user
3. Verify filtered results

Test Measure

Create a measure to debug security:

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

Performance Considerations

DO

/// 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

/// 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

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

Common Mistakes

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:

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

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

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:

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