Dynamic Row level security for multiple column values

[Anonymous]

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

[v-shex-msft]

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)

RLS with UserName()

Regards,

Xiaoxin Sheng

[Anonymous]

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

[v-shex-msft]

HI @Anonymous ,

Can you please provide the some sample data for these two tables? 

Regards,

Xiaoxin Sheng

[Anonymous]

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

[v-shex-msft]

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