Appropriate spot for security evaluations - business logic or data access

Pardon the length here...hopefully I didn't go overboard...

I'm in the process of working on my first production MVC application and I'm trying to stick to DDD principles in the process. I've run into some questions related to how to deal with the security requirements of the application and thought I'd see if the SO community could offer some best-practice suggestions.

Domain Information

To use a simplified explanation, this application will have AffiliateCompanies, Users, and Customers.

• AffiliateCompanies are hierarchal, so one affiliate can sign up and be tied to the activities of another affiliate. The root is the main company providing the products/services.
• Users all belong to an Affiliate entity.
• Customers are organizati开发者_开发知识库ons to which the products/services are sold. Affiliates are assigned to customers such that it is possible for two hierarchically-unrelated affiliates to split a Customer.

Security Information

Rights to perform certain actions in the application will be determined based on an ACL-type of arrangement. Each User object has a property that is a collection of SystemAccessRules that determine what actions they can perform and what the scope of their permissions are (their own objects, their affiliate's objects, or their entire hierarchy's objects). Users can also belong to roles, which themselves have that same collection of SystemAccessRules.

As a result, if a user logs in and wants to see a list of "their" customers, the list could be comprised of customers they are individually assigned to, customers anyone in their affiliate organization is assigned to, or customers anyone in their organization or any of their child affiliate organizations are assigned to.

Database Considerations

DDD aside, at some point the storage strategy has to come into play. In this simple scenario, the tables align with the objects above (including a roles table), with a few support tables to support the relationships between the objects:

• AffiliateCustomers - this table allows for a many-to-many relationship between affiliates and customers by storing the PK of each entity as a pair of FKs that are themselves a composite PK for this table.
• ACL - this table stores the security information, specifically the subject of the entry (either a user or a role), the action in question (e.g. "CreateCustomer"), the permission (allow or deny), and a scope (their own stuff, their organization's, or their network's).

The Question...Finally

I'm using a combination of repositories and services. I'm trying to keep business logic in the services and out of the repositories or database, but due to the security design here, a simple request for the list of "their" customers could be immensely burdensome, especially as the data set grows. I was trying to use Linq where possible, but this architecture seems not to be very suited. As I see it, here are my choices:

1. Accept the requesting user as an argument for service methods (or determine it by context), and have the service method populate a list through multiple queries to Linq repository. This would require pulling the list of customers, then iterating through each customer to issue another query to pull the ACL data, then using that data to filter the first list based on permissions. The hierarchy issue would require some fancy Linq footwork (like this), if it's possible at all.

   Even if the hierarchy issue could be made to work, it seems like this solution won't perform very well...

2. Accept the requesting user as an argument, but pass it and the required permission (e.g. "View Customers") to the repository in order to retrieve appropriate data from the database through a stored procedure that would use several EXISTS clauses in a CTE query that could account for the hierarchical nature of the data and the need to check for role and user security.

   This pushes a fair amount of logic to the database, which seems very anti-DDD and generally bad.

I'm leaning more toward the second option, but that may be because in my past projects that's how I've done it. I'm not even sure if my design overall is on the right track (in the past the permission declarations were done using bit flags, so it was even easier to do the DB query using bitwise operator).

Has anyone been in similar situations, and if so can you comment on the performance and maintainability of the solution you pursued? I want to stick to high-minded programming principles, but not at the expense of simplicity and common sense.

Have you considered using the specification pattern to pass your business rules down to your data access layer?

The service constructs a specification tree which it passes to the repository. The repository converts the specification into an Expression<Func<Customer, bool>> which it passes to IQueryable<Customer>.Where(...). When the repository materialises the collection, e.g. by calling ToList(), the business rules are translated into SQL and executed on the database server.

Last time I checked, LINQ to SQL didn't support CTEs, so you may need to use a view to flatten the hierarchy.