CODEWITHSUNDEEP
c#asp.net.netlinq-to-sql
Dean
Dean

Reputation: 4594

How can I restrict users to only edit their data with Linq To SQL?

Background: I've been working on a small proof-of-concept web application in ASP.NET 4.0 with C# and I've been using Linq To SQL because it's quick and easy. I've now got to restrict one user from seeing another user's data in the same database. So I've added an 'account' column to most of my tables and I want to check that the current user is a member of the same account as the data they are viewing/editing before allowing them access.

One possible solution I'm not really happy with: I could simply but painfully go through all of my Linq To SQL queries and add an if statement to do the check like this:

MyDataContext DB = new MyDataContext();
//get the current user's information from the DB
USER myUser = DB.USERs.Where(j => j.USR_EMAIL == User.Identity.Name).FirstOrDefault();
//get the object to edit
MYOBJECT myObject= DB.MYOBJECTs.Where(j => j.ID == IdFromQueryString).FirstOrDefault();
//compare the user's account with the object's account they are now trying to view
if(myUser.AccountID == myObject.AccountID)
{
    //display the object
}
else
{
    //display error message
}

This would be a lot of work to do throughout the entire application and not to mention the repetition and opportunity for mistakes. It will also be painful to maintain and if I forget to add this for a query I will leave a security hole open.

The Question: Is there a way to do this once for each table and it automatically checks every time I do a query with Linq To SQL? OR Is there a better way to go about this?

Upvotes: 2

Views: 845

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294187

You should had used the Repository pattern and not access directly Contest.Users, but always access Repository.Users. Then you could had change the repository to expose Users as from u in InternalContext.Users where u.AccountId = currentAccountId select u thus enforcing the scope. Coincidentally this technique is called a scope in Rails...

The next best thing is to use DataLoadOptions to associate a lambda filter with your entities, see How to: Filter at the DataContext Level (LINQ to SQL). You would need to add this to every context you create. Again, refactoring the code to obtain the data context from a method (or better, implement a repository pattern...) would help a lot.

Finally you could go on the back end and implement this via updateable views and context_info() but I would strongly discourage this.

PS. I hope you made the account_id the leftmost key in every clustered index on the tables, and made the entity id primary key non-clustred, did you? Your performance will tank otherwise.

Upvotes: 3

mnsr
mnsr

Reputation: 12437

Sorry, do you have AccountID's available for both objects? If so, is there any reason why you can't join them on their Accountid?

var query = from o in DB.MYOBJECTs.Where(w => w.ID == IdFromQueryString)
            join u in DB.USERs.Where(w => w.USR_EMAIL = User.Identity.Name) on o.AccountID equals u.AccountID
            select o;

Upvotes: 1

Philip Kendall
Philip Kendall

Reputation: 4314

Two possible approaches:

  • Use stored procedures to access the data rather than direct queries; give the users permission only to these stored procedures (ie no general read access to the database) and have the stored procedures enforce the access restrictions.
  • Proxy all access to the database through a separate service - the service is then the only thing with general access to the database and can enforce the access restrictions.

These aren't fundamentally too dissimilar from your current strategy of wrapping every query, but may be easier to manage.

Upvotes: 0

Related Questions