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.
Hi All,
Trying to create a scenario based on the issue i am facing.
I have a table with many columns. There are 2 column values based on which i would like to create dynamic RLS
1. Category , column values ( Fruit, Vegetable)
2. Name, column values ( Orange, Apple, Tomato)
If i need to create a static RLS for the above scenario, i will have to create 5 roles and add users to respective roles so that they could see only that data on login.
Assuming i had many more names it would be a task to create more roles. Is there a way i can apply Dynamic RLS to this situation?
Thanks in advance.
Regards,
Sandy
HI @Anonymous ,
I think you need to grouping these table records and create a bridge table to link and mapping those tables. (category, name)
After these, you can create relationship from user table to bridge table and enable RLS based on username function.(notice: turn on 'apply security filter in both direction' option)
Regards,
Xiaoxin Sheng
So would the Bridge table be something like this. and to which column would i create a relationship to?
Column 1 Column 2
Fruit User 1
Vegetable User 2
Apple User 3
Orange User 3
HI @Anonymous ,
Can you please provide the some sample data for these two tables?
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
Thanks for checking on this. Below is the scenario for dynamic RLS
Bill should have access to all Category that has Vegetable
Mark should have access to all Category that has Fruit
Tom must have access to all Name that has Cabbage
Steve must have access to all Name that has Tomato
I was unable to attach the sample file so pasting below
EID | Category | Name |
1 | Vegetable | Cabbage |
2 | Fruit | Apple |
3 | Vegetable | Brinjal |
4 | Fruit | Orange |
5 | Vegetable | Beans |
6 | Fruit | Banana |
7 | Vegetable | Potato |
8 | Fruit | Kiwi |
9 | Vegetable | Cabbage |
10 | Fruit | Apple |
11 | Vegetable | Cabbage |
12 | Vegetable | Cabbage |
13 | Vegetable | Cabbage |
14 | Vegetable | Tomato |
15 | Vegetable | Tomato |
HI @Anonymous ,
You can refer to following step to mapping these tables.
Steps:
1. Create 'User Category' table with 'User id' and category(vegetable,fruit).
2. Create 'Category Name' table with category and names.
3. Extract category values from above two table and remove duplicate records to create 'Category' table with unique category values.
4. Use 'Category' field to link to 'User Category', 'Category Name' based on category field.
5. Use 'user id' field to link 'User' table and 'User Category' Table.
6. Setup RLS on category table and use current username to find out correspond 'user id', then use it to lookup category value from 'user category' table.
Regards,
Xiaoxin Sheng
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |