Applying Row Level Security (RLS) for multi-tenant DB

Posts   
 
    
andreash
User
Posts: 5
Joined: 03-Feb-2021
# Posted on: 11-Jun-2021 10:29:34   

We have implemented Row Level Security (RLS) in Azure SQL Database for isolating the data of each Tenant following the example here https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15#MidTier

For integrating this with LLBLGen we have the following code

public DataAccessAdapterBase Get(decimal tenantId)
{
       var adp = new DataAccessAdapter();
       adp.KeepConnectionOpen = true;
       adp.ActiveRecoveryStrategy = new SqlAzureRecoveryStrategy();
       var sql = $"EXEC sp_set_session_context @key=N'TenantId', @value={tenantId}, @read_only=1;";
       adp.ExecuteSQL(sql);
       return adp;
}

Is this approach secure? Is it safe to set the session this way for each adapter?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Jun-2021 10:41:49   

As far as I can see, this is safe from LLBLGen Pro side. Most importantly to be connected to the database by a user principal that has deny update on the TenantID column.

andreash
User
Posts: 5
Joined: 03-Feb-2021
# Posted on: 11-Jun-2021 10:49:05   

Thanks for the feedback Walaa. The concern you are sharing is valid. We control this through a Security Policy.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jun-2021 09:37:24   

I'd use parameters and not embed values in the querystring. https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_executing_plain_sql.htm#executing-a-statement-with-executesql I know 'tenantid' is likely a number and not coming from the outside and no harm done, but doing so here, likely also means you're doing it elsewhere too. Just never embed values in the query string, ever, no exceptions. This way you won't run the risk of sql injection through the plain sql api.

Additionally, you need to close the connection and dispose the adapter manually now. Especially that last part might be overlooked in this case, so keep an eye out for that. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
andreash
User
Posts: 5
Joined: 03-Feb-2021
# Posted on: 12-Jun-2021 09:42:00   

Thanks Otis for the feedback!