RLS - row level security and OR operator

[po]

Hi,

We have following situation

3 tables

sales table

distributor_number, seller_number, sales_qty

1                                1                     10

1                                 4                     3

2                                 1                   6

8                                 8                     12

seller table

seller_name, seller_number

   joe bloggs   1

   jill bloggs     2

   jack bloggs   8

distributor_table

distributor_name  distributor_number

 joe bloggs            1

 jill bloggs              2

jack bloggs             8

Have set up RLS sucht that a role Joe Bloggs they shoudl see any products they either sell or distribute in which case the Joe Bloggs user should see these 3 rows from the sales table

sales table

distributor_number, seller_number, sales_qty

1                                1                     10

1                                 4                     3

2                                 1                   6

However, they only see the first one as looks like RLS works on ensuring they are both a distributor and seller.

How can we best implemnet RLS to ensure it bahves like an OR operator i.e. set up RLS where either distributor OR seller is Joe Bloggs?

This is as simplified example as have surrogate keys and other attributes on our sales table thus cannot apply RLS to the fact table itself and must be applied to the dimension tables.

It looks like OR(Seller[NAME]="Joe Bloggs", Distributor[NAME]="Joe Bloggs")

Thanks

[v-luwang-msft]

Hi @po ,

You can apply RLS to the main table (sales table) instead of the RLS table and check like this:

CONTAINS (
    distributor_table,
    distributor_table[distributor_name], "joe bloggs",
    distributor_table[distributor_number], 'sales table'[distributor_number]
)
    || CONTAINS (
        'seller table',
        'seller table'[seller_name], "joe bloggs",
        'seller table'[seller_number], 'sales table'[seller_number]
    )

You could download my pbix file if you need.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[v-luwang-msft · Accepted solution]

Hi  @po ,

You can apply RLS to the main table (sales table) instead of the RLS table and check like this:

CONTAINS (
    distributor_table,
    distributor_table[distributor_name], "joe bloggs",
    distributor_table[distributor_number], 'sales table'[distributor_number]
)
    || CONTAINS (
        'seller table',
        'seller table'[seller_name], "joe bloggs",
        'seller table'[seller_number], 'sales table'[seller_number]
    )

You could download my pbix file if you need.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[po]

Hi,

Just one question though wehn have table with just dimension then see then all.

Would therefore also need RLS applied on the dimension table.

Thanks

[amitchandak]

@po , If you create two roles and assign both two the user, it will work like OR. In a single role, it will work like AND unless a statement of Or was possible to add.

[po]

Thanks for the info all

Looks like can use OR operator in RLS.

Also like the solution with CONTAINS and the associated .pbx file.