Object Level Security "Reverse" Role

[JustDavid]

Hello,

I've went through several youtube video and googling on how to specifically hide certain columns from users from creating roles.

However, after research, it seems that i have to manually input the users based on the roles, in order for those users to unable see the columns that I don't want them to see.

But this is not what I want. In fact, I want the reverse.

What I like to achieve is that ONLY specific people who's on the role can see ALL data, and if user's who's not in the role cannot see certain columns.

To put it into context, says I creata a role called 'HR'. And I have a selected invididuals, i.e. Mary, Jane and John who is in this 'HR' role. Everyone in the company has access to the power bi report. Thus, if it's Mary, Jane and John, they are able to see all columns (i.e. Employee Name and Salary). Any other person who have access to the report, except Mary, Jane and John, although able to see the report, but unable to see the employee name and salary.

How do I go about doing it?

Is there a way to have the role to be NOT EQUAL?

createOrReplace

	role NOT EQUAL HR    (original is  role HR)
		modelPermission: read

		tablePermission 'Table Name'
			columnPermission 'Employee Name' = read
			columnPermission 'Salary' = read

		annotation PBI_Id = some number and text

[AmiraBedh]

Hello !

Thank you for posting on MS Fabric community.

You need to create 2 AAD groups :

HR_SeeSensitive : members: Mary, Jane, John

AllEmployees: all other users

Then create 2 model roles via tabular editor / TOM :

• HR : modelPermission: Read and leave the sensitive columns at read or default so HR sees everything

• NonHR : modelPermission: Read andset the sensitive columns to none so they’re invisible to this role

{
  "name": "NonHR",
  "modelPermission": "read",
  "tablePermissions": [
    {
      "name": "Employee",
      "columnPermissions": [
        { "name": "Employee Name", "metadataPermission": "none" },
        { "name": "Salary",        "metadataPermission": "none" }
      ]
    }
  ]
}

then publish and map groups to roles in the service

• map HR_SeeSensitive to HR role

• map AllEmployees to NonHR role

[JustDavid]

@AmiraBedh thank you for replying.

Am a newbie in this RLS/OLS so do not really follow what you're doing here.

First of all, I do not have Tabular Editor as when I try to download, it is no longer free. Also, need to submit ticket to IT to have it installed.

However, I realized that Power BI built-in TMDL seems to work like Tabular.

Secondly, can I assume that when you said "create 2 AAD groups" you're referring to roles in Power BI under 'Modeling' / 'Manage roles'?

Secondly, on your code where you showed the TMDL, you have a section for 

"name": "Employee",

May I know is this where I need to input the employees email address?

If it is, if I have multiple emails of employees to input, how do I go about doing it? 

EDIT:

I have tried doing what you've told me without the complex, and it doesn't work to my expectation.

1st, I create 2 roles:

• HR
• All Employees

Under HR, this is what I have set on TMDL

createOrReplace

	role HR
		modelPermission: read

		tablePermission 'factTable'
			columnPermission 'Employee Name' = read
			columnPermission 'Salary' = read

		annotation PBI_Id = 28235dc3b86e485bbe8108f6a2b214fd

Under All, this is what I have set on TMDL

createOrReplace

	role 'All Employees'
		modelPermission: read

		tablePermission 'factTable'
			columnPermission 'Employee Name' = none
			columnPermission 'Salary' = none

		annotation PBI_Id = e7eac03c32ad4c90937cfb78ddbad2e2

I upload it to my workspace and then under 'Security', I have set Mary, Jane and John to be in HR. The other employees (hundreds of them), I DID NOT put them under 'All Employees' role.

However, when I test view with 'All Employee', the matrix table that I have setup is giving me an error (screenshot below).

What did I do wrong here?

I was expecting that at least, 'All Employee' user can see other columns in the matrix table, except the 'Employee Name' and 'Salary' columns that I had "hide" via OLS

[AmiraBedh]

Hello Davis !

I am glad to help, we all learning here 🙂

With OLS, hidden columns don’t just disappear from the fields pane they’re not addressable at all so any visual or measure that use them will fail for users in the deny role.

OLS is allow by default which means to deny columns to everyone except HR, you must assign a deny role to everyone.

You need an AAD group that includes everyone who uses the report and add it under the dataset security tab and map it to your all employees role.

If you need more help please tell me.

[JustDavid]

So if I understand you correctly, I have to list all employees except Mary, Jane and John to the 'All Amployee'?

If I were to do that, would these employees be able to see the matrix table that I created, with an exception of the columns that I have hindered them from vieweing?

[AmiraBedh]

Exactly ! non HR users will see the visual as long as the matrix don’t reference the hidden columns and if the matrix uses employee name or salary or any measure that reads them you will have that error because the all employees role since OLS makes those columns non addressable.

[JustDavid]

Is there a way then for non HR users to be able to see the matrix table that I've created with the restricted columns "built-in", in a sense they are not able to see the "columns" without giving that error?

[v-aatheeque]

Hi @JustDavid 

If you have applied built-in restrictions on certain columns, non-HR users won’t be able to access them directly, which can sometimes cause errors in matrix visuals. To avoid this, you can either:

• Remove the restricted columns from the matrix entirely for non-HR users.

• Use role-based logic to conditionally display blank (e.g : Restricted) in those columns for unauthorized users.

Reference : Object-Level Security (OLS) with Power BI - Microsoft Fabric | Microsoft Learn

Hope this helps,
Thank You. 

[v-aatheeque]

Hi @JustDavid 


We’d like to check if you were able to go through the previous response to your issue. Please let us know if you need further clarification we’ll do our best to support you.