Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I need to implement RLS.
I have 2 tables (below). Table 1 has all the users with their emails and Title. Table 2 has data which needs to be presented based on RLS, however some records can be viewed by multiple users based on their Titles. I can create a realtionship between those 2 tables either by ID, Username, or Title. Thanks in advance.
Table 1:
ID | Name | Username | Title |
1 | John | john@xyz.com | Manager |
2 | Jerry | jerry@xyz.com | Director |
3 | Mary | mary@xyz.com | Manger |
4 | Sue | sue@xyz.com | Analyst |
5 | Alex | alex@xyz.com | Developer |
Table 2
Rowid | Who can View | Col1 | Col2 |
101 | Manager/Director | ||
102 | Manager/Director | ||
103 | Manager/Director | ||
104 | Manager/Director | ||
105 | Analyst / Developer | ||
106 | Everyone | ||
107 | Everyone | ||
108 | Everyone | ||
109 | Everyone | ||
110 | Only Director | ||
111 | Only Director | ||
112 | Only Director | ||
113 | Only Manager |
@sabeensp - You could have a bridge table that contains a row for each combination of Table2 RowID and Title. Then, Table1 will filter the Bridge table and the Bridge table will filter Table2.
Hope this helps,
Nathan
@Anonymous Data table has over 10M rows creating mutiple combination would be hard, but thanks for the tip.
Is the RLS defined row-by-row for 10M rows? Or is there some other attribute that can be exploited?
@AnonymousOnly Title of the employee
The Bridge table could instead be the distinct combinations of Who can View and Title. Then use those two columns in the relationships.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |