Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm wondering if it's possible to create a user access table like this. So I have a fact sheet (employee data), I need to give HRBPs access to data based on their roles. Most HRBPs are responsible for single entities, so I list their entity in column "Entity", some HRBPs are responsible for the whole sales sector, that means they should have access to all SALES entities.
When I try to link this table to another table in data modeling, I get an error message as below.
How can I solve the problem?
User access
User Access Table
Solved! Go to Solution.
@Anonymous You could create two RLS roles
RLS which would be used for all people with single entity access
and 'No RLS' role like this:
When you publish the report, put all users responsible for the whole sales sector under this 'No RLS' role, and they will be able to see all the data, as effectively for them the RLS is not applied as the restriction of 1=1 is always true, thus will be seeing all the rows.
Hope this helps
Hi, @Anonymous
If you want super users access data of the entire dataset and not restricted by RLS, you can give them Build permission. And apply RLS to those users who can only access single entity. For more details, you can refer Ways to give Build permission.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-cazheng-msft i think that is incorrect. Build permissions do not override RLS. To override RLS you need to have contributor or better membership in the underlying workspace. And you want to think twice before you do that for regular (management) users.
Hi, @lbendlin
If a user has contributor or better membership in the worksapce, actually he will has build permission to the datasets of the entire workspace. And then RLS will not restrict his acess to the data in the dataset. RLS will not take effect to him.
Best Regards
Caiyun Zheng
Thanks, however, i don't want to add the user to the member group of the space because i'm not sure if that will give the user access to the whole sharepoint site behind the group. There are lots of sensitive info in the sharepoint site which the user should not have access to.
actually no, it won't necessarily slow it down. Being verbose at a lower level is often better than having to apply complex rules.
Test it out and compare the performance for both approaches.
RLS rules apply to the entire table (the "R" in RLS) as opposed to CLS rules that Power BI doesn't support yet.
You can make the rule as complex as you want, even including related tables. Don't go overboard though - RLS is costly from a performance perspective.
You are right that RLS seems to slow down the reports significantly. In addition to creating a customized user acess based on a table. How can I give some super users access to the whole dataset? I don't want to include those super users to the access table because that will add many lines to the table and slow down the filtering speed.
@Anonymous You could create two RLS roles
RLS which would be used for all people with single entity access
and 'No RLS' role like this:
When you publish the report, put all users responsible for the whole sales sector under this 'No RLS' role, and they will be able to see all the data, as effectively for them the RLS is not applied as the restriction of 1=1 is always true, thus will be seeing all the rows.
Hope this helps
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |