Row Level Security filters NULL values on relation

[pyskaty]

I'm looking for solution to my problem. I have 3 tables with data from all of my customers (multitenant):

Drivers (id, name, clientId, ... ), Vehicles (id, name, clientId, ...) and rides (Id, vehicleId, driverId, clientId, ...). 

On top of that I have build several reports and embed them in my application.

I implement row level security by clientId -> when customer displays reports in my application I filter above tabels by clientId. And everything works fine exect one scenario. 

When I have rides where driverId is null (which is completely correct from business point of view) RLS filters that rows (through driver to rides relation).

How to implement it so rows in Rides that have driverId = null won't be filtered??? 

Many thanks 

[v-lili6-msft]

hi  @pyskaty 

For your case, you could try this way as below:

I assume that this is a simple sample data, and you use [Name] for RLS

Driversrides

for your case, add a calculated column in rides table 

Name for rls = RELATED(Drivers[Name])

Now define RLS use this new column [Name for rls] instead of [Name]

[Name for rls] = "A"||[Name for rls] =blank()

of course you need adjust the relationship as below:

Result:

Regards,

Lin

[Tad17]

Hey @pyskaty 

Easiest solution is to replace the "null' with a value or word to include in the filter.

If this helps please kudo.

If this solves your problem please accept it as a solution.

[amitchandak]

Not sure what exact condition, but can be handled like ?

if(not(isblank([driverId]),security filter)

[pyskaty]

The trick is that I'm not filtering Rides table by driverId, only by clientId. Rows from Rides tabel are filtered because of relation between Rides and Drivers tabel (drivers are also filtered by clientId and then applied to Rides table - where 'NULL' driver didn't exists so it filters Rides where driverId is null).

I'm considering creating shadow driver for every klient and insert its Id for Rides that normaly have NULLa. But maybe there is some other, simpler solution.