topic Row-Level Security and AD groups in Report Server

Row-Level Security and AD groups

Les111 — Thu, 28 Dec 2023 10:07:43 GMT

I'm using the same security model with dynamic row level security for some reports.

I have 3 roles:

AllItems - allows admin users to see everything. Users are added manually on the row-level security page for each report (I know I could use an AD group for this and may do so but it's only a small number of users).
DirectAndIndirectReports - This is the default role and I have added this with an AD group that contains all users (domain\AllStaff) in the organisation on the row-level security page for each report.
Level1 - This is to give access to users who need to see data for certain groups of staff who they do not manage.

For Level1 I have to add each member on the RLS page for each report. So far I have about 10 people, but this number will grow and soon it will become a problem.

I know I cannot assign the same AllStaff AD group to more than one role but if I create a new AllStaff AD group (e.g. domain\AllStaff2) containing all staff, will this create a conflict because users are in more than one role? If not how would the report server determine which role a user is in if they are in both AllStaff and AllStaff2?

Any thoughts on this or any alternative way of managing multiple roles?

Thanks

Re: Row-Level Security and AD groups

lbendlin — Sat, 30 Dec 2023 02:02:13 GMT

You can have users in multiple roles and you can apply conflicting roles to the same table. The more permissive rules will win over the more restrictive rules.

By the way, dynamic RLS is when you use USERPRINCIPALNAME mappings. your implementation is static RLS.

Re: Row-Level Security and AD groups

Les111 — Tue, 02 Jan 2024 10:09:32 GMT

Does this mean users will see their DirectAndIndirectReports and any additional data they are granted access to as a result of being assigned a value in the Level1 role?

I am using USERPRINCIPLENAME to identify the user, then using a dataset with email addresses matched to data (their direct and indirect reports etc) to filter the output, so I thought this was dynamic RLS.

Re: Row-Level Security and AD groups

lbendlin — Tue, 02 Jan 2024 15:03:46 GMT

You have three roles - that is not something you usually do in dynamic RLS (there you have only one role, and access is controlled via the data model). Of course you can implement a hybrid version but that gets messy quickly.

Re: Row-Level Security and AD groups

Les111 — Tue, 02 Jan 2024 15:14:19 GMT

Yes I was thinking this. The problem is I have a default role which allows users to see their direct and indirect reports. This is based on relationships in a table that has the manager for each staff member (this is the dynamic RLS part I think)

Then there are some people who need access to one area of the organisation but they are not managers of that area (mostly admin staff or HR), so I created a separate table with these permissions based on the organisation hierarchy.

Re: Row-Level Security and AD groups

lbendlin — Tue, 02 Jan 2024 15:33:05 GMT

Managing hierarchical organizations in Power BI security roles - SQLBI

Re: Row-Level Security and AD groups

Les111 — Sat, 06 Jan 2024 15:30:02 GMT

I've now successfully set this up.

I have 2 AD groups, both contain all users in the organisation. This means I don't need to assign permissions to individuals on the report server, I just add the 2 AD groups to one role each in the RLS security page.

One role is based on a hierarchy built from managers and their direct and indirect reports and the other role is based on departments within the organisation, e.g. where the user isn't a manager but needs access to data from a specific department.

These 2 roles have dax filters looking up the USERPRINCIPALNAME and matching it to data in separate datasets. If a user is in one of the datasets they will see data accordingly and if they are in both they will see the appropriate data from both.

Re: Row-Level Security and AD groups

a-walker — Wed, 10 Jan 2024 15:41:46 GMT

Is this a new feature? I have had issues where when a user belongs to multiple roles they do not see anything.

It would be nice for the more permissive rule to win over the restrictive rules, however I have not experienced this.

I am using dynamic USERPRINCIPALNAME() only and members in an Active Directory.

Re: Row-Level Security and AD groups

Les111 — Wed, 10 Jan 2024 15:49:21 GMT

I didn't put any user in 2 roles individually. I created 2 AD groups, both containing all users, then added the AD groups to one role each in the role level security page for the report.

When I tried adding individual users to 2 roles it gave an error, but if you add different AD groups and put the same user in both groups it works.