SQL Limiting read and write to certain views

Question

I have a database where each user can create an account and save user specific details. Naturally, I want each user to only be able to modify things that are linked to his account.

My strategy was to create a SQL login and user for each account, create a view for each table they can have access to and grant them execute on a few procedures.

The views are created like this:

using (var cmd = new SqlCommand("CREATE VIEW [<viewName>] AS SELECT * FROM <TableName> WHERE ProfileID = '" + <UserId> + "'", con))
{
    cmd.ExecuteNonQuery();       
}

I tested the view and found that user would only see/select items linked to their ID but the problem is when I tried to execute a procedure using another users ID, that user was able to modify data that was not in his view; data that "belongs" to another user.

So my questions are:

1. Is this a correct approach? It feels a bit off for some reason. My idea is based off of this:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/granting-row-level-permissions-in-sql-server

2. How can I make sure that users can only edit rows that are contained in their view? Do I have to deny the user something before I grant him the views and executes?

Thank you.

Answer 1

This is not a correct approach.

ASP.NET website usually uses SQL login that has a full read/write access to the whole database (or at least to the part of database used by your application).

Mind that it does not mean that all users on your website can do anything with the data. The web user is not SQL login, the web user has access only to the functions you write and allow him to run. You need to limit users' access in your ASP.NET controller's code, checking their permission before executing SELECT or UPDATE in your (C#, VB.NET) code.

Here is an example how it should be done.