Where should I filter records?

Question

I'm designing a course management system which has different kinds of users, including sysadmin, branch manager and branch user. As you might expect, the sysadmin can manage all records, the branch manager can manage all records from its branch and the branch user can only manage its records.

My question is, where should this filtering be done? Should I do it at the DAL layer? Or just return all records from the DAL and then do the filtering at some other layer? I've been looking everywhere for best practices regarding authorization but I haven't found anything that explains this aspect clearly.

Thanks.

Answer 1

In general, selection of database records, for whatever reason, should be done by the database --- that's it's job.

Further,

• You don't want unnecessary records wasting bandwidth between your database server and client machine.
• You don't want unnecessary records taking up memory on your client machine.
• You don't want unauthorized records in memory on your client machine where they could be hacked.

Answer 2

The implementation-level answer to this question will depend heavily on your tools. Will this be .NET, Java, PHP, Python?

Your DAL should ONLY be concerned with returning and committing data to a data store. Filtering such as you describe is best handled in the middle-tier, with the business logic. Most queries will occur in the context of the logged-in user so that the appropriate data is returned, so keep this in mind as you design the system.

UPDATE:

Given .NET and NHibernate, NHibernate is your ORM/DAL. The DAL should ideally be a Repository implementation to allow you to get data in and out of your database.

Design a domain model to handle the relationships between User, Role, and Branch. Each entity has relationships to the other entities. The "records" for those branches must also be modeled as entities. Once your relationships are established in your model, you can design methods on each entity that retrieve related entities. You might consider this model to be a sort of ViewModel or Controller. Its job is to explicitly implement the relationship between the entities, creating an abstraction "above" the one provided by NHibernate.