{"kind":"AgentDefinition","metadata":{"namespace":"community","name":"power-bi-security-rls-best-practices","version":"0.1.0"},"spec":{"agents_md":"---\ndescription: 'Comprehensive Power BI Row-Level Security (RLS) and advanced security patterns implementation guide with dynamic security, best practices, and governance strategies.'\napplyTo: '**/*.{pbix,dax,md,txt,json,csharp,powershell}'\n---\n\n# Power BI Security and Row-Level Security Best Practices\n\n## Overview\nThis document provides comprehensive instructions for implementing robust security patterns in Power BI, focusing on Row-Level Security (RLS), dynamic security, and governance best practices based on Microsoft's official guidance.\n\n## Row-Level Security Fundamentals\n\n### 1. Basic RLS Implementation\n```dax\n// Simple user-based filtering\n[EmailAddress] = USERNAME()\n\n// Role-based filtering with improved security\nIF(\n    USERNAME() = \"Worker\",\n    [Type] = \"Internal\",\n    IF(\n        USERNAME() = \"Manager\",\n        TRUE(),\n        FALSE()  // Deny access to unexpected users\n    )\n)\n```\n\n### 2. Dynamic RLS with Custom Data\n```dax\n// Using CUSTOMDATA() for dynamic filtering\nVAR UserRole = CUSTOMDATA()\nRETURN\n    SWITCH(\n        UserRole,\n        \"SalesPersonA\", [SalesTerritory] = \"West\",\n        \"SalesPersonB\", [SalesTerritory] = \"East\",\n        \"Manager\", TRUE(),\n        FALSE()  // Default deny\n    )\n```\n\n### 3. Advanced Security Patterns\n```dax\n// Hierarchical security with territory lookups\n=DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(\n    DimUserSecurity[SalesTerritoryID], \n    DimUserSecurity[UserName], USERNAME(), \n    DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey]\n)\n\n// Multiple condition security\nVAR UserTerritories = \n    FILTER(\n        UserSecurity,\n        UserSecurity[UserName] = USERNAME()\n    )\nVAR AllowedTerritories = SELECTCOLUMNS(UserTerritories, \"Territory\", UserSecurity[Territory])\nRETURN\n    [Territory] IN AllowedTerritories\n```\n\n## Embedded Analytics Security\n\n### 1. Static RLS Implementation\n```csharp\n// Static RLS with fixed roles\nvar rlsidentity = new EffectiveIdentity(\n    username: \"username@contoso.com\", \n    roles: new List\u003cstring\u003e{ \"MyRole\" },\n    datasets: new List\u003cstring\u003e{ datasetId.ToString()}\n);\n```\n\n### 2. Dynamic RLS with Custom Data\n```csharp\n// Dynamic RLS with custom data\nvar rlsidentity = new EffectiveIdentity(\n    username: \"username@contoso.com\",\n    roles: new List\u003cstring\u003e{ \"MyRoleWithCustomData\" },\n    customData: \"SalesPersonA\",\n    datasets: new List\u003cstring\u003e{ datasetId.ToString()}\n);\n```\n\n### 3. Multi-Dataset Security\n```json\n{\n    \"accessLevel\": \"View\",\n    \"identities\": [\n        {\n            \"username\": \"France\",\n            \"roles\": [ \"CountryDynamic\"],\n            \"datasets\": [ \"fe0a1aeb-f6a4-4b27-a2d3-b5df3bb28bdc\" ]\n        }\n    ]\n}\n```\n\n## Database-Level Security Integration\n\n### 1. SQL Server RLS Integration\n```sql\n-- Creating security schema and predicate function\nCREATE SCHEMA Security;\nGO\n\nCREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))\n    RETURNS TABLE\nWITH SCHEMABINDING\nAS\n    RETURN SELECT 1 AS tvf_securitypredicate_result\nWHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';\nGO\n\n-- Applying security policy\nCREATE SECURITY POLICY SalesFilter\nADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)\nON sales.Orders\nWITH (STATE = ON);\nGO\n```\n\n### 2. Fabric Warehouse Security\n```sql\n-- Creating schema for Security\nCREATE SCHEMA Security;\nGO\n\n-- Creating a function for the SalesRep evaluation\nCREATE FUNCTION Security.tvf_securitypredicate(@UserName AS varchar(50))\n    RETURNS TABLE\nWITH SCHEMABINDING\nAS\n    RETURN SELECT 1 AS tvf_securitypredicate_result\nWHERE @UserName = USER_NAME()\nOR USER_NAME() = 'BatchProcess@contoso.com';\nGO\n\n-- Using the function to create a Security Policy\nCREATE SECURITY POLICY YourSecurityPolicy\nADD FILTER PREDICATE Security.tvf_securitypredicate(UserName_column)\nON sampleschema.sampletable\nWITH (STATE = ON);\nGO\n```\n\n## Advanced Security Patterns\n\n### 1. Paginated Reports Security\n```json\n{\n    \"format\": \"PDF\",\n    \"paginatedReportConfiguration\":{\n        \"identities\": [\n            {\"username\": \"john@contoso.com\"}\n        ]\n    }\n}\n```\n\n### 2. Power Pages Integration\n```html\n{% powerbi authentication_type:\"powerbiembedded\" path:\"https://app.powerbi.com/groups/00000000-0000-0000-0000-000000000000/reports/00000000-0000-0000-0000-000000000001/ReportSection\" roles:\"pagesuser\" %}\n```\n\n### 3. Multi-Tenant Security\n```json\n{\n  \"datasets\": [\n    {\n      \"id\": \"fff1a505-xxxx-xxxx-xxxx-e69f81e5b974\",\n    }\n  ],\n  \"reports\": [\n    {\n      \"allowEdit\": false,\n      \"id\": \"10ce71df-xxxx-xxxx-xxxx-814a916b700d\"\n    }\n  ],\n  \"identities\": [\n    {\n      \"username\": \"YourUsername\",\n      \"datasets\": [\n        \"fff1a505-xxxx-xxxx-xxxx-e69f81e5b974\"\n      ],\n      \"roles\": [\n        \"YourRole\"\n      ]\n    }\n  ],\n  \"datasourceIdentities\": [\n    {\n      \"identityBlob\": \"eyJ…\",\n      \"datasources\": [\n        {\n          \"datasourceType\": \"Sql\",\n          \"connectionDetails\": {\n            \"server\": \"YourServerName.database.windows.net\",\n            \"database\": \"YourDataBaseName\"\n          }\n        }\n      ]\n    }\n  ]\n}\n```\n\n## Security Design Patterns\n\n### 1. Partial RLS Implementation\n```dax\n// Create summary table for partial RLS\nSalesRevenueSummary =\nSUMMARIZECOLUMNS(\n    Sales[OrderDate],\n    \"RevenueAllRegion\", SUM(Sales[Revenue])\n)\n\n// Apply RLS only to detail level\nSalesperson Filter = [EmailAddress] = USERNAME()\n```\n\n### 2. Hierarchical Security\n```dax\n// Manager can see all, others see their own\nVAR CurrentUser = USERNAME()\nVAR UserRole = LOOKUPVALUE(\n    UserRoles[Role], \n    UserRoles[Email], CurrentUser\n)\nRETURN\n    SWITCH(\n        UserRole,\n        \"Manager\", TRUE(),\n        \"Salesperson\", [SalespersonEmail] = CurrentUser,\n        \"Regional Manager\", [Region] IN (\n            SELECTCOLUMNS(\n                FILTER(UserRegions, UserRegions[Email] = CurrentUser),\n                \"Region\", UserRegions[Region]\n            )\n        ),\n        FALSE()\n    )\n```\n\n### 3. Time-Based Security\n```dax\n// Restrict access to recent data based on role\nVAR UserRole = LOOKUPVALUE(UserRoles[Role], UserRoles[Email], USERNAME())\nVAR CutoffDate = \n    SWITCH(\n        UserRole,\n        \"Executive\", DATE(1900,1,1),  // All historical data\n        \"Manager\", TODAY() - 365,     // Last year\n        \"Analyst\", TODAY() - 90,      // Last 90 days\n        TODAY()                       // Current day only\n    )\nRETURN\n    [Date] \u003e= CutoffDate\n```\n\n## Security Validation and Testing\n\n### 1. Role Validation Patterns\n```dax\n// Security testing measure\nSecurity Test = \nVAR CurrentUsername = USERNAME()\nVAR ExpectedRole = \"TestRole\"\nVAR TestResult = \n    IF(\n        HASONEVALUE(SecurityRoles[Role]) \u0026\u0026 \n        VALUES(SecurityRoles[Role]) = ExpectedRole,\n        \"PASS: Role applied correctly\",\n        \"FAIL: Incorrect role or multiple roles\"\n    )\nRETURN\n    \"User: \" \u0026 CurrentUsername \u0026 \" | \" \u0026 TestResult\n```\n\n### 2. Data Exposure Audit\n```dax\n// Audit measure to track data access\nData Access Audit = \nVAR AccessibleRows = COUNTROWS(FactTable)\nVAR TotalRows = CALCULATE(COUNTROWS(FactTable), ALL(FactTable))\nVAR AccessPercentage = DIVIDE(AccessibleRows, TotalRows) * 100\nRETURN\n    \"User: \" \u0026 USERNAME() \u0026 \n    \" | Accessible: \" \u0026 FORMAT(AccessibleRows, \"#,0\") \u0026 \n    \" | Total: \" \u0026 FORMAT(TotalRows, \"#,0\") \u0026 \n    \" | Access: \" \u0026 FORMAT(AccessPercentage, \"0.00\") \u0026 \"%\"\n```\n\n## Governance and Administration\n\n### 1. Automated Security Group Management\n```powershell\n# Add security group to Power BI workspace\n# Sign in to Power BI\nLogin-PowerBI\n\n# Set up the security group object ID\n$SGObjectID = \"\u003csecurity-group-object-ID\u003e\"\n\n# Get the workspace\n$pbiWorkspace = Get-PowerBIWorkspace -Filter \"name eq '\u003cworkspace-name\u003e'\"\n\n# Add the security group to the workspace\nAdd-PowerBIWorkspaceUser -Id $($pbiWorkspace.Id) -AccessRight Member -PrincipalType Group -Identifier $($SGObjectID)\n```\n\n### 2. Security Monitoring\n```powershell\n# Monitor Power BI access patterns\n$workspaces = Get-PowerBIWorkspace\nforeach ($workspace in $workspaces) {\n    $users = Get-PowerBIWorkspaceUser -Id $workspace.Id\n    Write-Host \"Workspace: $($workspace.Name)\"\n    foreach ($user in $users) {\n        Write-Host \"  User: $($user.UserPrincipalName) - Access: $($user.AccessRight)\"\n    }\n}\n```\n\n### 3. Compliance Reporting\n```dax\n// Compliance dashboard measures\nUsers with Data Access = \nCALCULATE(\n    DISTINCTCOUNT(AuditLog[Username]),\n    AuditLog[AccessType] = \"DataAccess\",\n    AuditLog[Date] \u003e= TODAY() - 30\n)\n\nHigh Privilege Users = \nCALCULATE(\n    DISTINCTCOUNT(UserRoles[Email]),\n    UserRoles[Role] IN {\"Admin\", \"Manager\", \"Executive\"}\n)\n\nSecurity Violations = \nCALCULATE(\n    COUNTROWS(AuditLog),\n    AuditLog[EventType] = \"SecurityViolation\",\n    AuditLog[Date] \u003e= TODAY() - 7\n)\n```\n\n## Best Practices and Anti-Patterns\n\n### ✅ Security Best Practices\n\n#### 1. Principle of Least Privilege\n```dax\n// Always default to restrictive access\nDefault Security = \nVAR UserPermissions = \n    FILTER(\n        UserAccess,\n        UserAccess[Email] = USERNAME()\n    )\nRETURN\n    IF(\n        COUNTROWS(UserPermissions) \u003e 0,\n        [Territory] IN SELECTCOLUMNS(UserPermissions, \"Territory\", UserAccess[Territory]),\n        FALSE()  // No access if not explicitly granted\n    )\n```\n\n#### 2. Explicit Role Validation\n```dax\n// Validate expected roles explicitly\nRole-Based Filter = \nVAR UserRole = LOOKUPVALUE(UserRoles[Role], UserRoles[Email], USERNAME())\nVAR AllowedRoles = {\"Analyst\", \"Manager\", \"Executive\"}\nRETURN\n    IF(\n        UserRole IN AllowedRoles,\n        SWITCH(\n            UserRole,\n            \"Analyst\", [Department] = LOOKUPVALUE(UserDepartments[Department], UserDepartments[Email], USERNAME()),\n            \"Manager\", [Region] = LOOKUPVALUE(UserRegions[Region], UserRegions[Email], USERNAME()),\n            \"Executive\", TRUE()\n        ),\n        FALSE()  // Deny access for unexpected roles\n    )\n```\n\n### ❌ Security Anti-Patterns to Avoid\n\n#### 1. Overly Permissive Defaults\n```dax\n// ❌ AVOID: This grants full access to unexpected users\nBad Security Filter = \nIF(\n    USERNAME() = \"SpecificUser\",\n    [Type] = \"Internal\",\n    TRUE()  // Dangerous default\n)\n```\n\n#### 2. Complex Security Logic\n```dax\n// ❌ AVOID: Overly complex security that's hard to audit\nOverly Complex Security = \nIF(\n    OR(\n        AND(USERNAME() = \"User1\", WEEKDAY(TODAY()) \u003c= 5),\n        AND(USERNAME() = \"User2\", HOUR(NOW()) \u003e= 9, HOUR(NOW()) \u003c= 17),\n        AND(CONTAINS(VALUES(SpecialUsers[Email]), SpecialUsers[Email], USERNAME()), [Priority] = \"High\")\n    ),\n    [Type] IN {\"Internal\", \"Confidential\"},\n    [Type] = \"Public\"\n)\n```\n\n## Security Integration Patterns\n\n### 1. Azure AD Integration\n```csharp\n// Generate token with Azure AD user context\nvar tokenRequest = new GenerateTokenRequestV2(\n    reports: new List\u003cGenerateTokenRequestV2Report\u003e() { new GenerateTokenRequestV2Report(reportId) },\n    datasets: datasetIds.Select(datasetId =\u003e new GenerateTokenRequestV2Dataset(datasetId.ToString())).ToList(),\n    targetWorkspaces: targetWorkspaceId != Guid.Empty ? new List\u003cGenerateTokenRequestV2TargetWorkspace\u003e() { new GenerateTokenRequestV2TargetWorkspace(targetWorkspaceId) } : null,\n    identities: new List\u003cEffectiveIdentity\u003e { rlsIdentity }\n);\n\nvar embedToken = pbiClient.EmbedToken.GenerateToken(tokenRequest);\n```\n\n### 2. Service Principal Authentication\n```csharp\n// Service principal with RLS for embedded scenarios\npublic EmbedToken GetEmbedToken(Guid reportId, IList\u003cGuid\u003e datasetIds, [Optional] Guid targetWorkspaceId)\n{\n    PowerBIClient pbiClient = this.GetPowerBIClient();\n\n    var rlsidentity = new EffectiveIdentity(\n       username: \"username@contoso.com\",\n       roles: new List\u003cstring\u003e{ \"MyRole\" },\n       datasets: new List\u003cstring\u003e{ datasetId.ToString()}\n    );\n    \n    var tokenRequest = new GenerateTokenRequestV2(\n        reports: new List\u003cGenerateTokenRequestV2Report\u003e() { new GenerateTokenRequestV2Report(reportId) },\n        datasets: datasetIds.Select(datasetId =\u003e new GenerateTokenRequestV2Dataset(datasetId.ToString())).ToList(),\n        targetWorkspaces: targetWorkspaceId != Guid.Empty ? new List\u003cGenerateTokenRequestV2TargetWorkspace\u003e() { new GenerateTokenRequestV2TargetWorkspace(targetWorkspaceId) } : null,\n        identities: new List\u003cEffectiveIdentity\u003e { rlsIdentity }\n    );\n\n    var embedToken = pbiClient.EmbedToken.GenerateToken(tokenRequest);\n\n    return embedToken;\n}\n```\n\n## Security Monitoring and Auditing\n\n### 1. Access Pattern Analysis\n```dax\n// Identify unusual access patterns\nUnusual Access Pattern = \nVAR UserAccessCount = \n    CALCULATE(\n        COUNTROWS(AccessLog),\n        AccessLog[Date] \u003e= TODAY() - 7\n    )\nVAR AvgUserAccess = \n    CALCULATE(\n        AVERAGE(AccessLog[AccessCount]),\n        ALL(AccessLog[Username]),\n        AccessLog[Date] \u003e= TODAY() - 30\n    )\nRETURN\n    IF(\n        UserAccessCount \u003e AvgUserAccess * 3,\n        \"⚠️ High Activity\",\n        \"Normal\"\n    )\n```\n\n### 2. Data Breach Detection\n```dax\n// Detect potential data exposure\nPotential Data Exposure = \nVAR UnexpectedAccess = \n    CALCULATE(\n        COUNTROWS(AccessLog),\n        AccessLog[AccessResult] = \"Denied\",\n        AccessLog[Date] \u003e= TODAY() - 1\n    )\nRETURN\n    IF(\n        UnexpectedAccess \u003e 10,\n        \"🚨 Multiple Access Denials - Review Required\",\n        \"Normal\"\n    )\n```\n\nRemember: Security is layered - implement defense in depth with proper authentication, authorization, data encryption, network security, and comprehensive auditing. Regularly review and test security implementations to ensure they meet current requirements and compliance standards.","description":"Comprehensive Power BI Row-Level Security (RLS) and advanced security patterns implementation guide with dynamic security, best practices, and governance strategies.","import":{"commit_sha":"541b7819d8c3545c6df122491af4fa1eae415779","imported_at":"2026-05-18T20:05:35Z","license_text":"MIT License\n\nCopyright GitHub, Inc.\n\nPermission is hereby granted, free of charge, to any person obtaining a copy\nof this software and associated documentation files (the \"Software\"), to deal\nin the Software without restriction, including without limitation the rights\nto use, copy, modify, merge, publish, distribute, sublicense, and/or sell\ncopies of the Software, and to permit persons to whom the Software is\nfurnished to do so, subject to the following conditions:\n\nThe above copyright notice and this permission notice shall be included in all\ncopies or substantial portions of the Software.\n\nTHE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR\nIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,\nFITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE\nAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER\nLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,\nOUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE\nSOFTWARE.","owner":"github","repo":"github/awesome-copilot","source_url":"https://github.com/github/awesome-copilot/blob/541b7819d8c3545c6df122491af4fa1eae415779/instructions/power-bi-security-rls-best-practices.instructions.md"},"manifest":{}},"content_hash":[131,251,222,122,108,222,221,163,20,249,156,191,238,114,121,166,224,243,53,253,144,130,178,40,101,119,229,21,160,72,120,81],"trust_level":"unsigned","yanked":false}