Forum Discussion

Post Prodigy

4 years ago

Solved

RLS - row level security and OR operator

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

Need Help

v-luwang-msft

4 years ago

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

5 Replies

v-luwang-msft
Community Support
4 years ago
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
  Post Prodigy
  4 years ago
  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
Super User
4 years ago
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
  Post Prodigy
  4 years ago
  Thanks for the info all
  
  Looks like can use OR operator in RLS.
  
  Also like the solution with CONTAINS and the associated .pbx file.

v-luwang-msft

Community Support

4 years ago

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]
    )