CODEWITHSUNDEEP
sqlt-sqlauthenticationazure-sql-databaseazure-api-apps
LavaHot
LavaHot

Reputation: 381

How to validate user's access to specific rows in a SQL table?

I'm working on a project and I'm new to both web apps and SQL, so bear with me. I'm building an API and I want to make sure that my users only have access to certain rows in a specific table that have a foreign key to their customer id in another table, but have to be validated by user id in another table. (A single Customer has multiple Users and owns multiple Assets. For right now, all of the Customer's Users can access any Asset, but no Customers share an Asset or User.) The way I can think to do this is to do 

SELECT * FROM [Asset] WHERE Id=@AssetId AND CustomerId=(SELECT CustomerId FROM [User] WHERE UserId=@UserId);

This is great, but with many entries in the Asset and User tables, this query could take up a ton of time. This is bad since every request made to my API that needs the Asset data should be doing this check. I could set up an index, and in fact UserId is a Secondary Key in User because it's a unique identifier from the auth provider, but I'm not sure if I should add an index for CustomerId in Asset. The Asset table should grow relatively slowly compared to some other tables (have a messaging record table for auditing purposes), but I'm not sure if that's the right answer, or if there's some simpler answer that's more optimized. Or is this kind of query so fast at scale that I have nothing to worry about?

Upvotes: 1

Views: 418

Answers (1)

AlexB
AlexB

Reputation: 3548

For your particular case, it looks like the perfect context to build a junction table between the User table and the Asset table. Both field together will become the primary key. Individually, AssetId and UserId will be foreign keys.

Let's say the junction table is called AssetUser.

Foreign keys : 

CONSTRAINT [FK_AssetUser_User] FOREIGN KEY ([UserId]) REFERENCES [User]([UserId])

CONSTRAINT [FK_AssetUser_Asset] FOREIGN KEY ([AssetId]) REFERENCES [Asset]([AssetId])

Primary key : 

CONSTRAINT [PK_AssetUser] PRIMARY KEY([AssetId], [UserId]));

You shouldn't worry about scale too much unless you are going to have ALOT of data and/or the performance is critical in your application. If so, you have the option to use hadoop or to migrate to a NoSQL database.

Upvotes: 1

Related Questions