avatarSusheel Aakulu

Summary

The website content outlines two methods for implementing Combined Row Level Security (RLS) and Column Level Security (CLS) in PowerBI to control data access based on user personas.

Abstract

The provided content discusses two distinct approaches to enhance data security in PowerBI by combining Row Level Security (RLS) with Column Level Security (CLS). The first method involves creating a new table of authorized users and using DAX expressions to mask or reveal sensitive data based on user roles. This approach requires the maintenance of an authorized users table and the creation of multiple measures for different sensitive columns. The second method leverages Field Parameters and DAX to dynamically control the visibility of sensitive data in reports, which simplifies the management of user access without the need for an authorized users table. Both methods aim to ensure that users, such as employees and supervisors, only see the data relevant to their roles, with the second method offering a more streamlined approach to managing multiple columns and user groups.

Opinions

  • The author prefers Method 2 for its ease of implementation and maintenance, as well as its ability to handle multiple audience groups without creating separate authorized user tables.
  • Method 1 is recognized for its simplicity and the fact that user access control is contained within PowerBI.
  • The cons of Method 1 include its limitation to a single audience group and the additional effort required to maintain the authorized users table.
  • Method 2 is favored for its ability to satisfy multiple columns masking with a single field parameter and multiple managed roles, as well as for not showing the CLS field/measure to the end user, adding an extra layer of security.
  • A noted disadvantage of Method 2 is the need to create and maintain multiple Azure Active Directory (AAD) groups.
  • The author advises against using fields or metrics intended for CLS in slicers, indicating a best practice for implementing CLS.

Combined Row Level Security and Column Level Security in PowerBI

Two ways of implementing RLS and CLS both combined in PowerBI

Method 1 : By Introducing a new table and combination of DAX. Output: The column/field will be masked and visible in the visual based on user persona with RLS

Method 2: By utilizing fieldparameters and combination of DAX. Output: The column/field will be hidden/not included in the visual based on user persona with RLS

Method 2 is my preferred approach, comment down below, which one you would prefer :)

Use case: Employee and Supervisor, 2 user personas

User Persona 1 : Employee: Scenario 1: Visual with Data Type Text in a Table Visual for CLS:

  • When an employee accesses the PBI report, RLS should be applied to the report and employees must see only their respective records. Also, CLS should be applied and the “STI Eligible field” should be masked.

Scenario 2: Visual with Data Type Numeric in a Bar Graph for CLS:

  • When an employee accesses the PBI report, RLS should be applied to the report and employees must see only their respective records. Also, CLS should be applied and the “TM_Count” measure should be masked.

User Persona 2: Supervisor: Scenario 1 & 2: For both data types of Text and Numeric, - The Supervisor should see unmasked information and their respective assigned Employees information in the report. - Also, signed in Supervisor should see their respective information in the report.

Method 1 : Authorized Users Table and DAX

Pre-requisites CLS : For Data Type Text — Secnario 1

Data Model Overview:

Create a new table named Authorized User:

  • This table contains a list of users who should see the PII columns un masked

For Example:

Create a dummy masked column: A new dummy column must be created in the table that contains a PII column either from source or via DAX in PowerBI. [PII: Personally identifiable information — Column with Sensitive Data, required to be Masked]

For Example, dummy column created in PowerBI: STL Eligible column here is a PII column

A new dummy calculated column within PowerBI is created as below

DAX used to create this column:

STI (MASKED) = REPT(“*”, 7)

Create a New Measure: The below measure helps to view PII unmasked column based on a condition if the signed in user’s info is existing in the Authorized User Table via DAX function IF, Contains & UserPrincipalName()

If the signed in user’s info is not existing in the Authorized User Table, then it will return the dummy masked column as output

STI (PII) = 
VAR showVal = IF(
 CONTAINS(‘Authorized Users’,’Authorized Users’[User],USERPRINCIPALNAME()),
 1,0)
RETURN IF(showVal=1,
 VALUES(‘TM Org Info’[STI Eligible]), 
 VALUES(‘TM Org Info’[STI (MASKED)]
))

Pre-requisites CLS : For Data Type Numeric — Measure— Secnario 2

Authorized User Table needs to be created [As explained above]

Create a New Measure: The below measure helps to view PII unmasked measure based on a condition if the signed in user’s info is existing in the Authorized User Table via DAX function IF, Contains & UserPrincipalName().

If the signed in user’s info is not existing in the Authorized User Table, then it will return -1 as output

TM_COUNT (PII) = IF(
 CONTAINS(‘Authorized Users’, ‘Authorized Users’[User], USERPRINCIPALNAME()),
 COUNT(DYN_TM_FULL_COMPLETIONS[Team Member ID]),
 -1)

Pre-requisites RLS

Create Roles in PowerBI based on use case

Outputs:

Combination of CLS and RLS: Data Type Text in a Table Visual

When an Employee access the report

RLS is applied and only Employee Records are displayed. CLS is applied and STI (PII) column is masked

When a Supervisor access the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and STI (PII) column is unmasked masked

Combination of CLS and RLS: Data Type numeric in a Bar Graph

When an Employee accesses the report

RLS is applied and only Employee records are displayed. CLS is applied and TM_Count(PII) measure is masked to -1

When a supervisor accesses the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and TM_Count(PII) measure is unmasked masked.

Pro’s of this approach

  • Easy to Implement
  • User Access Control is within PowerBI to show Masked and Unmasked information with the help of DAX and Authorized Users Table

Con’s of this approach

  • Works for one audience group with one Authorized Table For example: - If there is a use case to showcase 2 Audience Groups, 2 masked columns differently, then we need to create 2 different Authorized Users Tables for 2 different scenarios
  • Maintenance of Authorized table is an additional activity
  • Need to create multiple measure for multiple PII columns

To overcome these Con’s we can look at Method 2

Method 2: CLS + RLS via Fields Parameters & DAX

Pre-requisites CLS : For Data Type Text — Secnario 1

Create a new Fields Parameter

Parameter text PII = {
    ("STI Eligible", NAMEOF('TM Org Info'[STI Eligible]), 0),
    ("Supervisor Email Address", NAMEOF('TM Org Info'[Supervisor Email Address]), 1)
}

The above DAX generates a New Field Parameter which contains STI Eligible and Supervisor Email Address.

Use the created Parameter filed in the required visuals where masking is required

Create 2 new Managed Roles For Example:

The Managed Roles + Parameters combined together helps with showcasing and hiding the fields in the visual, which acts as CLS

Pre-requisites CLS : For Data Type Numeric — Measure— Secnario 2

Create a new Fields Parameter

Parameter numeric PII = {
    ("TM_COUNT (PII)", NAMEOF('All Measures'[TM_COUNT (PII)]), 0)
}

The above DAX generates a New Field Parameter which contains TM_Count (PII) Measure.

Use the created Parameter filed in the required visuals where masking is required

Create a new Managed Role

Pre-requisites RLS

Add additional conditions to the existing Managed Roles For Example: Visual with Data Type Text:

Visual with Data Type Numeric [Measure]:

Add respective user Persona AAD groups for the dataset in the security setting within PowerBI Service

Outputs:

Combination of CLS and RLS: Data Type Text in a Table Visual

When an Employee access the report

RLS is applied and only Employee Records are displayed. CLS is applied and STI (PII) column is not included in the visual hence not visible

When a Supervisor access the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and STI (PII) column is included and visible

Combination of CLS and RLS: Data Type numeric in a Bar Graph

When an Employee accesses the report

RLS is applied and only Employee records are displayed. CLS is applied and TM_Count(PII) measure is not included in the visual hence not visible

When a supervisor accesses the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and TM_Count(PII) measure is included and visible

Pro’s of this approach

  • Overcomes the Con of CLS+RLS via Authorized User Table and DAX method, satisfies multiple columns masking with one field Parameter and Multiple Managed Roles
  • No need to create and maintain Authorised User table
  • Easy to Add/Remove users from AAD groups
  • CLS Field/Measure is not shown to end user at all. Adds additional layer of security and control

Con’s of this approach

  • Create and maintain multiple AAD Groups
  • * Note: Fields/Metrics used for CLS is not recommended to be used in Slicer.
Power Bi
Powerbi Online Training
Power Bi Tutorials
Power Bi Desktop
Power Bi Step By Step
Recommended from ReadMedium