How to Set Up Row-Level Security for Billing Data in Azure Synapse or Power BI

Row-Level Security (RLS) is essential when sharing Azure billing and cost data across your organization. It ensures that users only see data relevant to their department, project, or cost center—preventing unauthorized access to sensitive financial information. This guide covers implementing RLS in both Azure Synapse Analytics and Power BI, enabling secure, multi-tenant cost reporting.

Prerequisites

Before implementing Row-Level Security, ensure you have:

• Azure Synapse Analytics workspace (for Synapse-based RLS) or Power BI Pro/Premium license
• Cost Management export configured and writing to Azure Storage (CSV or Parquet format)
• Billing data loaded into Synapse dedicated SQL pool or Power BI dataset
• User mapping data that defines which users can see which resources (departments, subscriptions, resource groups, tags)
• Administrative access to Synapse SQL pools or Power BI workspace
• Understanding of your organization's access model (department-based, subscription-based, tag-based, etc.)

Understanding Row-Level Security Concepts

Row-Level Security works by filtering data based on the identity of the user viewing the report. Key concepts:

• Security predicates: Filters applied automatically based on user context
• Security policies: Rules that define which rows users can access
• User mapping tables: Reference tables that map users to allowed values (departments, cost centers, etc.)
• Dynamic security: Filters that use functions like USERPRINCIPALNAME() or USERNAME() to determine access

Architecture Options

Option 1: Tag-Based Security Model

Users see resources tagged with their department/cost center:

User: [email protected] → Department: Engineering
Shows all resources with tag: Department=Engineering

Option 2: Subscription-Based Security Model

Users see specific Azure subscriptions they manage:

User: [email protected] → Subscriptions: [sub-123, sub-456]
Shows all costs from those subscriptions

Option 3: Resource Group-Based Security Model

Users see specific resource groups:

User: [email protected] → Resource Groups: [rg-prod-web, rg-prod-api]
Shows costs from those resource groups only

Method 1: Implementing RLS in Azure Synapse Analytics

Step 1: Create User Mapping Table

Create a table that defines user access permissions:

-- Create user mapping table
CREATE TABLE dbo.UserAccessMapping
(
    UserPrincipalName NVARCHAR(255) NOT NULL,
    Department NVARCHAR(100),
    SubscriptionId NVARCHAR(100),
    ResourceGroup NVARCHAR(255),
    TagKey NVARCHAR(100),
    TagValue NVARCHAR(100)
);

-- Insert sample mappings
INSERT INTO dbo.UserAccessMapping
(UserPrincipalName, Department, SubscriptionId)
VALUES
('[email protected]', 'Engineering', 'abc123-def456-ghi789'),
('[email protected]', 'Marketing', 'xyz987-uvw654-rst321'),
('[email protected]', 'Finance', NULL); -- Finance sees all

-- Create index for performance
CREATE NONCLUSTERED INDEX IX_UserAccessMapping_UserPrincipalName
ON dbo.UserAccessMapping (UserPrincipalName);

Step 2: Create Security Predicate Function

Create an inline table-valued function that returns filtered rows:

-- Security function for department-based access
CREATE FUNCTION dbo.fn_SecurityPredicateDepartment(@Department NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS AccessResult
    WHERE
        -- Finance department sees all data
        @Department IN (
            SELECT Department
            FROM dbo.UserAccessMapping
            WHERE UserPrincipalName = USER_NAME()
        )
        OR
        -- Or if user is in Finance department (sees all)
        EXISTS (
            SELECT 1
            FROM dbo.UserAccessMapping
            WHERE UserPrincipalName = USER_NAME()
              AND Department = 'Finance'
        );
GO

For subscription-based security:

CREATE FUNCTION dbo.fn_SecurityPredicateSubscription(@SubscriptionId NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS AccessResult
    WHERE
        @SubscriptionId IN (
            SELECT SubscriptionId
            FROM dbo.UserAccessMapping
            WHERE UserPrincipalName = USER_NAME()
        )
        OR
        -- Admin group sees all subscriptions
        IS_MEMBER('db_owner') = 1;
GO

Step 3: Create Security Policy

Apply the security policy to your billing data table:

-- Create security policy for billing data
CREATE SECURITY POLICY BillingDataSecurityPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicateDepartment(Department)
ON dbo.AzureCostData
WITH (STATE = ON);

For subscription-based:

CREATE SECURITY POLICY BillingDataSecurityPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicateSubscription(SubscriptionId)
ON dbo.AzureCostData
WITH (STATE = ON);

Step 4: Test the Security Policy

Test with different users:

-- Connect as [email protected]
SELECT
    Date,
    SubscriptionName,
    ResourceGroup,
    Department,
    SUM(Cost) AS TotalCost
FROM dbo.AzureCostData
GROUP BY Date, SubscriptionName, ResourceGroup, Department;
-- Should only see Engineering department data

-- Check what you can access
SELECT * FROM dbo.UserAccessMapping WHERE UserPrincipalName = USER_NAME();

Step 5: Grant Permissions

Grant users SELECT permission (RLS filters will apply automatically):

-- Create database user from Azure AD user
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

-- Grant read access to billing table
GRANT SELECT ON dbo.AzureCostData TO [[email protected]];
GRANT SELECT ON dbo.UserAccessMapping TO [[email protected]];

-- Repeat for other users

Method 2: Implementing RLS in Power BI

Step 1: Create User Mapping Table in Data Model

Import or create a UserAccessMapping table in Power BI:

Option A: Import from Azure SQL/Synapse

1. Get Data > Azure > Azure SQL Database
2. Connect to your Synapse SQL pool
3. Import the UserAccessMapping table

Option B: Create manually in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("base64...", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserPrincipalName = _t, Department = _t]),

    // Or create from scratch
    UserMapping = Table.FromRecords({
        [UserPrincipalName = "[email protected]", Department = "Engineering"],
        [UserPrincipalName = "[email protected]", Department = "Marketing"],
        [UserPrincipalName = "[email protected]", Department = "Finance"]
    })
in
    UserMapping

Step 2: Create Relationships

Create relationships between tables:

1. Go to Model view in Power BI Desktop
2. Create relationship: UserAccessMapping[Department] → AzureCostData[Department]
3. Set cross-filter direction to Both or Single (depending on your model)

Step 3: Create RLS Roles

1. Go to Modeling tab > Manage Roles

2. Click Create to create a new role (e.g., "DepartmentBasedAccess")

3. Add DAX filter expression:

Department-Based Security:

[Department] IN VALUES(UserAccessMapping[Department])
    && UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()

Subscription-Based Security:

[SubscriptionId] IN
    CALCULATETABLE(
        VALUES(UserAccessMapping[SubscriptionId]),
        UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
    )

Tag-Based Security (using Tags column from cost data):

VAR UserDepartment =
    CALCULATETABLE(
        VALUES(UserAccessMapping[Department]),
        UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
    )
VAR TagFilter = "Department=" & UserDepartment
RETURN
    CONTAINSSTRING([Tags], TagFilter)

Multi-Level Security (Department + Finance sees all):

VAR UserDept =
    CALCULATETABLE(
        VALUES(UserAccessMapping[Department]),
        UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
    )
VAR IsFinance = UserDept = "Finance"
RETURN
    IF(
        IsFinance,
        TRUE(),  // Finance sees all
        [Department] = UserDept
    )

Step 4: Test RLS in Power BI Desktop

1. Go to Modeling > View as
2. Select the role to test (e.g., "DepartmentBasedAccess")
3. Click Other user and enter test email: [email protected]
4. Verify that only Engineering department data appears

Step 5: Publish and Assign Users to Roles

1. Publish report to Power BI Service
2. Go to workspace in Power BI Service
3. Click ... next to dataset > Security
4. Add users/groups to roles:
   • Add [email protected] to "DepartmentBasedAccess" role
   • Add security groups for easier management
5. Click Save

Step 6: Configure Dynamic RLS (Advanced)

For truly dynamic security without manual user assignment:

Create single role with dynamic filtering:

-- Apply to AzureCostData table
VAR CurrentUser = USERPRINCIPALNAME()
VAR AllowedDepartments =
    CALCULATETABLE(
        VALUES(UserAccessMapping[Department]),
        UserAccessMapping[UserPrincipalName] = CurrentUser
    )
RETURN
    [Department] IN AllowedDepartments
    || CurrentUser = "[email protected]"  // Admin override

Assign ALL users to this single role—filtering happens automatically based on USERPRINCIPALNAME().

Method 3: Tag-Based RLS Implementation

For environments using Azure tags extensively:

Step 1: Extract Tags from Cost Data

Tags in cost export are often in JSON format. Parse them in Power Query:

let
    Source = AzureBlob.Contents("..."),
    ParsedCsv = Csv.Document(Source, [Delimiter=",", Encoding=65001]),

    // Add custom column to parse tags
    ParsedTags = Table.AddColumn(ParsedCsv, "DepartmentTag", each
        let
            TagsJson = [Tags],
            ParsedJson = try Json.Document(TagsJson) otherwise null,
            DeptValue = try ParsedJson[Department] otherwise null
        in
            DeptValue
    )
in
    ParsedTags

Step 2: Create RLS on Parsed Tag Column

Apply RLS to the extracted tag column:

-- RLS expression on AzureCostData table
[DepartmentTag] IN
    CALCULATETABLE(
        VALUES(UserAccessMapping[Department]),
        UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
    )

Advanced Scenarios

Scenario 1: Manager Hierarchy (Managers See Team Data)

Create user hierarchy table:

CREATE TABLE dbo.UserHierarchy
(
    ManagerEmail NVARCHAR(255),
    EmployeeEmail NVARCHAR(255)
);

-- Security function for hierarchical access
CREATE FUNCTION dbo.fn_SecurityPredicateHierarchy(@Department NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS AccessResult
    WHERE
        -- User can see their own department
        @Department IN (
            SELECT Department FROM dbo.UserAccessMapping
            WHERE UserPrincipalName = USER_NAME()
        )
        OR
        -- Manager can see team members' departments
        @Department IN (
            SELECT ua.Department
            FROM dbo.UserHierarchy uh
            INNER JOIN dbo.UserAccessMapping ua ON uh.EmployeeEmail = ua.UserPrincipalName
            WHERE uh.ManagerEmail = USER_NAME()
        );
GO

Scenario 2: Time-Based Access (Temporary Permissions)

Add time-based constraints:

CREATE TABLE dbo.UserAccessMapping
(
    UserPrincipalName NVARCHAR(255),
    Department NVARCHAR(100),
    ValidFrom DATE,
    ValidTo DATE
);

-- Security function with time constraints
CREATE FUNCTION dbo.fn_SecurityPredicateTimeBased(@Department NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS AccessResult
    WHERE
        @Department IN (
            SELECT Department
            FROM dbo.UserAccessMapping
            WHERE UserPrincipalName = USER_NAME()
              AND GETDATE() BETWEEN ValidFrom AND ValidTo
        );
GO

Scenario 3: Multi-Attribute Security (Department + Environment)

Filter on multiple attributes:

-- Power BI RLS expression
VAR UserDept =
    CALCULATETABLE(
        VALUES(UserAccessMapping[Department]),
        UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
    )
VAR UserEnv =
    CALCULATETABLE(
        VALUES(UserAccessMapping[Environment]),
        UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
    )
RETURN
    [Department] IN UserDept
    && [Environment] IN UserEnv

Best Practices

Security Best Practices

1. Principle of Least Privilege: Start with no access and explicitly grant permissions
2. Use Security Groups: Map Azure AD groups to roles instead of individual users
3. Audit Access Regularly: Review UserAccessMapping quarterly
4. Test Before Publishing: Always test RLS with "View as" before publishing
5. Document Security Model: Maintain documentation of who should see what

Performance Best Practices

1. Index User Mapping Tables: Create indexes on UserPrincipalName columns
2. Minimize RLS Complexity: Avoid complex DAX with multiple CALCULATE functions
3. Use Synapse for Large Datasets: For 10M+ rows, implement RLS in Synapse instead of Power BI
4. Pre-filter Data: If possible, filter data at source before applying RLS
5. Monitor Query Performance: Use DAX Studio to analyze RLS query performance

Maintenance Best Practices

1. Automate User Mapping: Sync UserAccessMapping from HR system or Azure AD
2. Version Control: Keep RLS expressions in source control
3. Handle User Departures: Implement process to remove access when users leave
4. Test After Changes: Re-test RLS whenever cost data schema changes
5. Use Deployment Pipelines: Use Power BI deployment pipelines for consistent RLS across environments

Common Pitfalls to Avoid

1. Not Testing with Real Users: "View as" only simulates—test with actual user accounts
2. Circular Dependencies: Avoid relationships that create circular references with RLS tables
3. NULL Values: Account for NULL departments/subscriptions in RLS expressions
4. Cross-Filter Direction Issues: Set correct cross-filter direction in relationships
5. Forgetting Admin Override: Always include admin bypass in RLS expressions
6. Case Sensitivity: Email addresses may be case-sensitive depending on authentication
7. Premium vs Pro: Dynamic RLS requires Power BI Premium or Premium Per User

Troubleshooting

Users See No Data

Symptom: Report is blank after applying RLS

Solutions:

• Verify user is added to role in Power BI Service security settings
• Check UserAccessMapping contains entry for user's email
• Test email format matches USERPRINCIPALNAME() exactly (check case, domain)
• Verify relationships are configured correctly
• Check that Department/Subscription values match between tables

Diagnostic Query (Synapse):

-- Check what user can see
SELECT USER_NAME() AS CurrentUser;
SELECT * FROM dbo.UserAccessMapping WHERE UserPrincipalName = USER_NAME();

-- Disable RLS temporarily to verify data exists
ALTER SECURITY POLICY BillingDataSecurityPolicy WITH (STATE = OFF);
SELECT TOP 10 * FROM dbo.AzureCostData;
ALTER SECURITY POLICY BillingDataSecurityPolicy WITH (STATE = ON);

Performance Issues

Symptom: Reports load slowly after adding RLS

Solutions:

• Add indexes to UserAccessMapping table
• Simplify DAX expressions (avoid nested CALCULATE)
• Move RLS to Synapse if dataset is very large
• Use DirectQuery instead of Import mode
• Pre-aggregate data before applying RLS

Performance Testing Query:

-- Check execution plan
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT
    Department,
    SUM(Cost) AS TotalCost
FROM dbo.AzureCostData
GROUP BY Department;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

RLS Not Filtering in Power BI Service

Symptom: RLS works in Desktop but not in Service

Solutions:

• Republish dataset (not just report)
• Re-add users to roles after republishing
• Check workspace permissions (users need at least Viewer role)
• Verify Power BI Premium/PPU if using dynamic RLS
• Clear browser cache and refresh report

Users See Wrong Data

Symptom: Users see data from other departments

Solutions:

• Check for multiple role assignments (user might be in wrong role)
• Verify UserAccessMapping doesn't have duplicate/incorrect entries
• Test RLS expression with different user emails
• Check for admin accounts that bypass RLS
• Review cross-filter direction on relationships

Diagnostic DAX:

-- Create measure to show current user
CurrentUser = USERPRINCIPALNAME()

-- Create measure to show allowed departments
AllowedDepartments =
    CONCATENATEX(
        CALCULATETABLE(
            VALUES(UserAccessMapping[Department]),
            UserAccessMapping[UserPrincipalName] = USERPRINCIPALNAME()
        ),
        [Department],
        ", "
    )

Next Steps

After implementing Row-Level Security:

1. Set up automated user mapping sync from Azure AD or HR system
2. Create admin dashboard to monitor RLS effectiveness and access patterns
3. Implement audit logging to track who accesses billing data
4. Document security model for compliance and onboarding
5. Train report consumers on what data they can see and why
6. Integrate with Azure RBAC for consistent permissions across Cost Management and reports

Related Resources

• How to View Azure Billing Data in Azure Synapse or Power BI
• How to Visualize Azure Billing Data in Power BI
• Microsoft Documentation: Row-Level Security in Power BI
• Microsoft Documentation: Row-Level Security in Azure Synapse