# Security

> Implements Row-Level Security (RLS) and Object-Level Security (OLS) for Power BI models. Use for data access control, dynamic security, and multi-tenant scenarios.

- **Type:** Skill
- **Install:** `agentstack add skill-kpbray-power-bi-agent-skills-security`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [kpbray](https://agentstack.voostack.com/s/kpbray)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [kpbray](https://github.com/kpbray)
- **Source:** https://github.com/kpbray/power-bi-agent-skills/tree/master/.github/skills/security

## Install

```sh
agentstack add skill-kpbray-power-bi-agent-skills-security
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

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

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

### Role Structure

```tmdl
role 'RoleName'
	modelPermission: read
	description: 'Description of who this role is for'
	lineageTag: 

	tablePermission TableName = 
	tablePermission AnotherTable = 
```

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

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:

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

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

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

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

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [kpbray](https://github.com/kpbray)
- **Source:** [kpbray/power-bi-agent-skills](https://github.com/kpbray/power-bi-agent-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-kpbray-power-bi-agent-skills-security
- Seller: https://agentstack.voostack.com/s/kpbray
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.