Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |