Hi Folks,
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 dont 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.
