Entity Framework row-level security per user

Question

I'm working on ASP.Net MVC3 web-site with Entity Framework and SQL Server 2008 as a data storage. Also I'm using Repository pattern to concentrate all data-access code in one area.

In my application I have many users, every user can have many Projects. User should only have access to their own projects. At the moment I have this code:

    public IQueryable<Project> All
    {
        get { 
            return context.Projects
                .Where(p => p.Owner.ID == Util.GetCurrentUserID())
                .Select(p=>p); 
        }
    }

    public Project Find(System.Guid id)
    {
        return context.Projects
                .Where(p => p.Owner.ID == Util.GetCurrentUserID())
                .FirstOrDefault();
    }

If you noticed .Where(p => p.Owner.ID == Util.GetCurrentUserID()) is duplicated. And I have quite a few other places where these exact conditions are littered.

Is there a way in DbContext to have this condition always appended automatically to any query going Projects table?

Something like:

public class MyContext : DbContext
{
    public DbSet<Project> Projects
         .Where(p => p.Owner.ID == Util.GetCurrentUserID()) { get; set; }
}

OR

public class MyContext : DbContext
{
    public DbSet<Project> Projects { get {
           // Insert a cast from IQuerieable to DBSet below
       return Projects
              .Where(p => p.Owner.ID == Util.GetCurrentUserID())
              .Select(p => p);
    } 
    set; }
}

UPD while writing the question, realised that the last version can just work - need to try out. Still would like to hear other options for code optimisation and making it more DRY.

Thanks in advance!!

If you found your own solutioin, please post it as an answer, so people with the same problem can find help. — Ricardo Souza
– Ricardo Souza, Commented May 26, 2012 at 23:14
@rcdmk, surely I will. That's like a common theme here, isn't it? — trailmax
– trailmax, Commented May 27, 2012 at 0:25

Mert Akcakaya · Accepted Answer · 2012-05-27 13:34:01Z

4

You can always write an extension method called "WhereProject". Then call the standard "Where" method in your extension method after appending your condition to your predicate.

public static IEnumerable<TSource> WhereProject<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate) where TSource: Project
{
     return source.Where(p=> p.Owner.ID == Util.GetCurrentUserID() && predicate);
}

You can skip predicate if you want or set it as null in the parameter list for default value and then act accordingly if you don't want to use predicate.

This is probably what you want, it is simple:

public static IEnumerable<TSource> WhereProject<TSource>(this IEnumerable<TSource> source) where TSource: Project
{
     return source.Where(p=> p.Owner.ID == Util.GetCurrentUserID());
}

EDIT These solutions does not sound right to me. I think you would want to secure your row data one level higher, in my case it would be the service layer. Consider a method in your service class like this:

public List<Project> GetUserProjects(User user)
{
    return repo.All().Where(p => p.Owner.ID == Util.GetCurrentUserID()).ToList();
}

This way, from the method name, it is very clear what you are doing. It is not responsiblity of a repository to contain your specific logic. It is there to handle your data access only.

edited May 27, 2012 at 13:34

answered May 27, 2012 at 0:45

Mert Akcakaya

3,1792 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

trailmax Over a year ago

yeah, I thought about this approach. It is better then what I have at the moment, but not ideal - a programmer must remember to use WhereProject() in the query. It would be fantastic if I could specify these conditions one level higher.

trailmax Over a year ago

hm.. that's actually what I'm trying to avoid - having another layer on top of repository. Repository is to handle data access only. Row security (imho) belongs to data acess. I understand that is very arguable point. I'll wait for another couple days and start a bounty - would like to hear other opinions on this matter.

trailmax Over a year ago

rethinking your suggestion. I guess you are right, no logic should go in Repository: in case when I want to change data providers, logic will be screwed. I probably should have DataSecurity Layer that does that row filtering on select queries and adds bits of information on write.

trailmax Over a year ago

After poking about for a while, reading different sources, I did come to conclusion that approach with Service layer is the best possible scenario.

Collectives™ on Stack Overflow

Entity Framework row-level security per user

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related