Hi,
I am building a crm application that is using a multi-tenant single database (Azure SQL). I am trying to find the most efficient way to use the server side actions and database queries to ensure data is secure.
Current I have the database setup with the below as each user can only be assigned to one account, but one account can have multiple companies. I want to make sure that each user can only see the companies assigned to their account. I’m currently using the company ID as the unique id across all other tables.
User Table
userID (used as the identity for the security provider)
accountID
Account Table
accountID
userID
companyID
Company Table
companyID
accountID
When making a query I am joining the IDs together but getting stuck on adding the right condition to ensure only the correct Company data is being returned. I’ve tried using the userID equals identity from the security provider but doesn’t work.
Is it possible to make a database query to the user’s account details and get the companyID then pass this value to the next database query as a variable and set the condition to equal the companyID on the tables witht he data I’m quering?
I can’t seem to find how to use multiple queries in a single server side action.
Hope you can help, as I feel like I’m missing a simple logic step that ones someone says it, it’ll make total sense.
Last updated: