How to implement Object Level Security for the Measures in Power BI

How to implement Object Level Security for the Tables, Columns and Measures in Power BI

The Object-Level Security (OLS) in Power BI is a powerful feature that enables granular control over data access, ensuring that sensitive information is visible only to authorized users. OLS operates at both the table and column levels, allowing for precise data security configurations.

Key Aspects of OLS:

By implementing OLS, you can manage permissions at both the table and column levels, providing a robust layer of security for your data.

Table-Level Security: OLS restricts access to entire tables within a dataset. This is particularly useful for hiding sensitive data sets or information that is only relevant to specific user roles.

Column-Level Security: OLS also enables restrictions at the column level. This means that certain columns within a table can be hidden from users who do not have the appropriate permissions.

Object-Level Security (OLS) vs Row Level Security (RLS):

Both OLS and RLS helps to manage and control data access, ensuring that users see only the data they are authorized to view. Here is the key difference between RLS and OLS.

Object-Level Security (OLS):

The OLS manages access to entire table or specific columns within a table or dataset. It hides or restricts visibility of these objects based on user roles, ensuring sensitive information is only seen by authorized users.

• Controls access to entire tables or specific columns.
• Hides or restricts visibility of these objects based on user roles.

Row-Level Security (RLS):

RLS controls access to specific rows within a table. It filters data at the row level based on user attributes or roles, allowing users to see only the data relevant to their permissions.

• Controls access to specific rows within a table.
• Filters data at the row level based on user attributes or roles.

Implementing OLS in Power BI:

To implement Object-Level Security (OLS) in Power BI, define security roles directly in Power BI, and then use Tabular Editor to manage access to the tables and columns by applying the appropriate filters and permissions.

Example (Scenario):
Let's assume the Power BI data model is structured as shown below.

The relationships are as per below:

The Measure are as per below:

Net Revenue = SUM(fact_Sales_Orders[Revenue])

Quantity Sold = SUM(fact_Sales_Orders[Quantity_Sold])

Total Quantity = CALCULATE( [Quantity Sold], ALL(dim_Date))

Stock Value = SUM(fact_Inventory[Stock_Valuation])

Now we will see on how to implement the OLS on a Table, Column and the Measure level as discussed below.

1) OLS on a Table Object Level:
To implement the OLS on any Model Object (Table or Column or Measure) level, we need to define a Security Role in Power BI.

In this example, I have defined role as "OLS_Model_Objects", with no DAX Logic and it is not required to define on any dataset.

We will use this Security Rule "OLS_Model_Objects" in Tabular Editor to manage access to the tables and columns by applying the appropriate filters and permissions.

Implementing OLS for the table: fact_Inventory:

First Open the Power BI Data Model with Tabular Editor tool to manage the OLS permissions.

Next select the table fact_Inventory then go to Object Level Security property under Section of [Translations, Perspectives, Security].

Next set the OLS_Model_Objects security value as "None". This property will have the values as "Default", "Read", "None".

The "Default" is the default option, enable all the default permissions on the selected Object.

The "Read" option provides the read permissions on the selected Object.

The "None" option will restrict the access to that Object for the users mapped to the respective Security Role (OLS_Model_Objects).

Finally Save Changes to the Model and Test the Role.

Result: Before OLS: 

 

Result: After OLS:

As we know, after implementing OLS on fact_Inventory, the table and its dependent columns and measures will be hidden for the users mapped to the role OLS_Model_Objects.
Any visuals depending on the table fact_Inventory will be broken.

Since the [Stock Value] measure is depending on fact_Inventory[Stock_Valuation], the Visuals using that measure in the Report will be broken with Error as shown below:

Notes:

Please note that we cannot implement the OLS on a Table that breaks the relationships in the Data Model.

For example, from the above Model, dim_Customer is accessing the fact_Sales_Returns via fact_Sales_Orders. In this case, we can't implement OLS on fact_Sales_Orders.

If you do so, we will get the following Error.

2) OLS on a Column Object Level:

We can implement the OLS at a Column Object level using same role OLS_Model_Objects as explained below.

Implementing OLS for the table Column: fact_Sales_Orders[Quanity_Sold]:

First Open the Power BI Data Model with Tabular Editor tool to manage the OLS permissions.

Next select the column [Quanity_Sold] from table fact_Sales_Orders then go to Object Level Security property under Section of [Translations, Perspectives, Security].

Next set the OLS_Model_Objects security value as "None". This property will have the values as "Default", "Read", "None".

Finally Save Changes to the Model and Test the Role.

Result: Before OLS:

Result: After OLS:
After the OLS, the column [Quantiy_Sold] from the table fact_Sales_Orders will be hidden for the users. The below associated Measures will also be hidden. Hence the dependent visuals will be broken in the report.

Quantity Sold = SUM(fact_Sales_Orders[Quantity_Sold])

Total Quantity = CALCULATE( [Quantity Sold], ALL(dim_Date))

3) OLS on a Measure Object Level:

We cannot directly implement the OLS on the Measures. However, we can achieve this using a dummy table object.

Here is a temporary table created to implement OLS for measures. This table can be hidden.

OLS_Security_Enabler = FILTER({BLANK()}, FALSE())

Next, we need to create a measure as OLS_Flag with below logic, which will always return 1.

OLS_Flag = (COUNTROWS(VALUES(OLS_Security_Enabler[Value]))<1)*1

Next, we need to use this OLS_Flag measure in the main measures for which we want to apply OLS, as shown below:

Quantity Sold =

VAR _OLS_Enable = [OLS_Flag]

VAR _Qnty_Sold = SUM(fact_Sales_Orders[Quantity_Sold])

RETURN

IF(_OLS_Enable=1, _Qnty_Sold)

Stock Value =

VAR _OLS_Enable = [OLS_Flag]

VAR _Stock_Val = SUM(fact_Inventory[Stock_Valuation])

RETURN

IF(_OLS_Enable=1, _Stock_Val)

Implementing OLS for the table Column: OLS_Security_Enabler[Value]:

First Open the Power BI Data Model with Tabular Editor tool to manage the OLS permissions.

Next select the column [Value] from table OLS_Security_Enabler then go to Object Level Security property under Section of [Translations, Perspectives, Security].

Next set the OLS_Model_Objects security value as "None". 

Note:
In this case, we set the Object level security, either to the table OLS_Security_Enabler or to the column [Value].

Since this table is having only 1 Column, entire table will be hidden when column is hidden.

Result: Before OLS:

Result: After OLS:

Though we implemented the [OLS_Flag] logic only for the measures [Stock Value], [Quantity Sold], the [Total Quantity] measure also will be hidden as it depends on [Quantity Sold].

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------