How to Set Up Row-Level Security in Power BI
Demo of static and dynamic security implementation
Business intelligence depends on the ability to provide the information necessary to make informed decisions. But not all information is meant for all eyes. The ability to control or restrict access to sensitive information in data models and reports is hugely important.
It allows you to answer questions such as how do I secure access to published reports? How do I restrict visibility of data so that my CEO can see the whole picture, but a department head or regional sales managers will only see results pertaining to their department or regions? How do I ensure that published data conforms with legal or regulatory requirements?
Row-level security is one technique Power BI modelers and report writers can use to resolve these questions. By correctly structuring datasets and using Active Directory (AD) security groups, row-level security can enforce data protection at the BI tool level.
In this webinar recording we discuss and demo how to implement row-level security and restrict access for given users. You will learn
- How to set up static row-level security with groups
- How to set up dynamic row-level security using DAX
Data is a valuable and sensitive resource, learn how to protect it.
Microsoft Power BI
Stephen’s background in BI, operations and finance spans 30 years. During that time, he has led numerous business analytics initiatives using Microsoft data tools and has watched the platform evolve from his initial use of version 1 of SQL Server and Microsoft Access. He brings a deep familiarity with the various capabilities and components of the Microsoft BI platform and understanding of how to best leverage them.
- Power BI security introduction
- Power BI is an online SaaS offering built on Azure
- Web front end cluster
- Back-end cluster
- Premium offers dedicated, provisioned and partitioned instance
- Azure active directory for account authentication and management
- User accounts
- Security groups
- Distribution lists
- Data storage and movement
- A Power BI dataset is a collection of data that you import or connect to, it can be at rest or in process
- With DirectQuery, source data is not stored in Power BI
- User authentication to data sources
- With each data source, users connect based on their login, they access the data with those credentials
- When sharing, access is based on whether the source supports RLS
- Non-RLS data sources
- Original credentials supplied during connection are applied to access or display the data
- RLS capable data sources
- Datasets using Import and DirectQuery
- On-premises data gateway can enforce RLS on some sources
- What is role-level security?
- A security mechanism that filters the records from a table based on the authorization context of the current user that is logged in.
- Filters limit what data is shown in a visualization
- A special type of role filter is built into Power BI to use for row-level security
- A role is typically a job function or geographic location
- Sometimes people play more than one role, they can be assigned to multiple roles
- Example: A project manager might need access to sales and engineering data, so they’re assigned to both roles
- Static role filters
- Static Role Filters are based on dimension(s) of the data in the dataset and are a hardcoded value
- Location, Job Title, Department
- Static Role Filter examples:
- Location – Users in the USA Role should only see rows of data where Country is set to USA
- Department – People in the HR Role can see payroll data but others cannot
- Static Role Filters are defined in the Power BI report
- Users are assigned to Roles in Power BI service at the dataset level
- Example of static role filter
- Dynamic role filters
- Often, user and role assignments are already maintained in another system and you won’t want to duplicate that assignment in Power BI service
- We can pull that assignment data into our dataset along with the rest of the data
- Dynamic Role Filters are based on relating the Power BI service user to data source user data to enforce row-level security
- We need some new DAX functions to help us out…
- DAX user functions
- The username from Power BI in the format domain\username
- The username from Power BI in the format firstname.lastname@example.org
- Your source data will need the user names in one of these two formats to mesh correctly with Power BI
- Example of dynamic role filter
- Hierarchical role filters
- Sometimes you need to implement RLS in a conditional way, some users are restricted in one way, other users are restricted in another way
- Hierarchal data is an example of when you might need to use this technique
- Typical hierarchies are management levels or geographic
- To solve this, we will filter on the hierarchy table and apply conditional filters
- To implement this, we need two more DAX functions…
- Example of hierarchical role filter
- Does not answer the question “Who can see which reports?”
- That is permissions and sharing related
- RLS only limits data access in published reports being viewed by users in Power BI service
- Only applies to viewer member
- Users with access to dataset can download and access without restrictions
- RLS within overall security framework
- Which users need access to what information?
- What is overall data governance approach?
- What combination of import, DirectQuery and live connection?
- Where to implement security?
- How to maintain access lists?
- Non-RLS data sources