Design Row level access in a Azure SQL database

Question

I have a requirement to create new SQL database with close to around 10 tables including all fact and dimention tables. This database stores the data for several users from several partner companies.

User Table - UserId, Name, Age, Gender,Company...etc

Transaction tables - TransactionID, TransactionTime,UserId, TransactionSeq,OrderId,StatusId... etc

Orders Table - OrderId,OrderCount,ItemId

Item Table - ItemId,ItemName,ItemType..etc Table4 Table5 .....etc

In my USER table there is a field called Company which identifies which user is in which company . I want to create 5 logins for people from 5 different companies where they should be able to access only their company users data which is identified by column Company in User table. Remember there could me more companies added in future.

We can see from the above table structure that every table does not have Company column in them. Even if I create, its redundant data which I don't want to create.

All the table are connected to Usertable either directly or by joining couple of tables. My question is how do I set up Row level access all the tables in the database where the key column on which the row level access should be set up is only in one table?

In other words if you login from CompanyA userId you should only be able to see the data related to CompanyA in User Table, Transaction table, Orders table etc?

Is row level access the best way to set this up? Considering the fact that there should not be any kind on dataleakage between companies.

There is lot of information like below related to row level security for a single table in the database: https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

I still don't know how to implement it at database level.

Sounds like a weird design, why should transaction be connected to a user and not a company? In my previous life, we had this kind of multi-tenant database and each relevant table contained a Company field which was carefully WHERE:d/joined on (no RLS back in those days), and we're talking 500+ tables, not 10, i don't see why it should be a problem to have something like this here as well. RLS should make your life easier since you don't need to do the WHEREs/JOINs yourself — siggemannen
– siggemannen, Commented Apr 17, 2024 at 9:30
I'll suggest different approach, create schema for each company, same tables. Store company data in related schema. Create user restricted to one schema only. In that way you don't need RLS at all. — Lev Gelman
– Lev Gelman, Commented Apr 17, 2024 at 9:43
A schema per company is a maintenance nightmare - don't do it. — Dale K
– Dale K, Commented Apr 17, 2024 at 9:54
Schema per company would not be possible in my case as I will have some reporting requirements that need me to aggregate data across companies. Having data in multiple schemas might difficult in that instance. — KNP
– KNP, Commented Apr 18, 2024 at 10:36

Alberto Morillo · Accepted Answer · 2024-04-17 14:56:35Z

0

My suggestion is to add the user column on each table so you can create simple functions with less joins between tables as possible to allow those functions to have good performance. This is an example of a function you may create for each table.

ALTER FUNCTION [Security].[fn_RLS_OrderData]
(
  @Comapny int
)
RETURNS TABLE
WITH SCHEMABINDING
AS 
  RETURN
  (
    SELECT 1 as Result
      FROM [Orders] AS d
      INNER JOIN [UsersTable]AS e 
      ON e.[UserLoginContext] = d.UserLogin e.[UserLoginContext] = SUSER_NAME()
      WHERE e.Company   = @Company 
  );

Row-level Security works well with INNER JOINs.

answered Apr 17, 2024 at 14:56

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Design Row level access in a Azure SQL database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related