Row Level security with a bridge table

[Anonymous]

I am implementing Row Level Security using a bridge table.  The problem is that USERNAME() or USERPRINCIPALNAME() only return the email address of the logged in user.  Is there a similar function that returns the AD Groups that the logged user belongs to?  That is what we want to use for RLS (AD Groups instead of email addresses).

Our Bridge table basically has these columns: 

ADGroup                   CenterID

GeneralUsers                 A

CenterAUser                  A

GeneralUsers                 B

CenterBUser                  B

And it is connected to the main table on the CenterID.  The relationship between those 2 tables is many to many (an AD Group can have access to several Centers, so there is a row for each ocurrence, and also a Center can be accessed by one or many AD Groups.

   Any help?

[MattAllington · Accepted solution]

The way you describe only works with email addresses. There is an alternate approach where you create different roles in Desktop that don't use USERNAME() etc. You can then manually assign AD Groups to these roles in the service. There is one off setup, but thereafter it is just a matter of managing the members of the groups. 

[Icey · Accepted solution]

Hi @Anonymous ,

Just like what @MattAllington said, you can create Roles like so:

Then, publish it to Power BI Service and set security.

Or, you can consider to refer to this post: 

   Dynamic Row Level Security is easy with Active Directory Security Groups.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thank @Icey and @MattAllington

I'm new to Power BI, so I could follow the first 2 steps Icey mentioned:

but when I got to "Publish it to the Power BI service and establish security" here's what I see:

As you can see, I don't have the "User", "Groups", etc. options in the menu on the left.

Also, I don't know how to get to the Row-Level Security page.

Can you guide me on how to get there? maybe I'm posting it wrong.

Thank you

[Icey · Accepted solution]

Hi @Anonymous ,

Just like what @MattAllington said, you can create Roles like so:

Then, publish it to Power BI Service and set security.

Or, you can consider to refer to this post: 

   Dynamic Row Level Security is easy with Active Directory Security Groups.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thank @Icey and @MattAllington

I'm new to Power BI, so I could follow the first 2 steps Icey mentioned:

but when I got to "Publish it to the Power BI service and establish security" here's what I see:

As you can see, I don't have the "User", "Groups", etc. options in the menu on the left.

Also, I don't know how to get to the Row-Level Security page.

Can you guide me on how to get there? maybe I'm posting it wrong.

Thank you

[Icey · Accepted solution]

Hello @VickyCastaneda ,

See this document to manage security in your model.

---

To manage security in the data model, you'll want to do the following.

1. Select the ellipse (...) for a dataset.

2. Select Security.

   

This will take you to the RLS page to add members to a role that you created in Power BI Desktop. Only dataset owners will see Available Security. If the dataset is in a group, only administrators in the group will see the security option.

You can only create or modify roles in Power BI Desktop.

---

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[MattAllington · Accepted solution]

The way you describe only works with email addresses. There is an alternate approach where you create different roles in Desktop that don't use USERNAME() etc. You can then manually assign AD Groups to these roles in the service. There is one off setup, but thereafter it is just a matter of managing the members of the groups.