Layer-Design: where to check permissions for database reads/updates?

I think, the key is to think about what exactly you mean when you say that the DAO approach "decreases reusability". If your requirement to enforce user access rights is universal to all applications of your DAO, then doing this at the DAO level actually increases reusability rather than reducing it: everybody using the DAO would be able to benefit from these checks rather than having to implement them on their own.

You can make the user id an implicit parameter to make these methods more friendly to upstream user. You could also make it return a Try (or, perhaps, an Either) to address your concern about distinguishing between a missing and an inaccessible object cases:

case class UserId(id: Int)def findCalById(id: Int)(implicit user: UserId): Future[Try[Option[Calendar]]] = ???

Then, the caller could do something like this:

implicit val currentUser = UserId(request.getUserId)dao.findCalById(request.getCalendarId).map {     case Failure(IllegalAccessException()) => "You are not allowed to use this calendar"    case Return(None) => "Calendar not found"    case Return(Some(cal)) => calendarToString(cal)}

On the other hand, if there are possible cases where the DAO would be used without a user context (an "admin" application perhaps), then you might consider either subclassing it to provide the access control to your "regular applications", or, perhaps, simply making an additional role that would allow a user access to all calendars regarding of ownership, and then using that "superuser" in your admin application.

I would not worry about the cost of having to load the object before checking the access (even if the object is really expensive to load, it should be a rare enough occurrence that someone tries to access an object he does not own). I think, a stronger argument against the service layer approach is exactly reusability and modularity of the code: the very existence of the DAO class with a public interface suggests that it can, at least potentially, be reused by more than one component. It seems silly having to require all such components implement their own access checks, especially, considering that such contract would not be enforceable: there is no way to make sure that somebody, who decides to use your DAO class a couple of years from now, will remember about this requirement (or care to read the comment). If you are producing a layer for accessing the database anyway, you might as well make it useful for something.

I use the second approach. I will go only from an architectural point of view which I feel is more important than a usually small cost of query.

Some reasons include:

1. It is cleaner to have all validations/verifications in a single place. The code has a structure. There will be cases when some validations can not be performed in the DAO layer. And then it becomes ad-hoc, what are the validations go into the service layer and what in the DAO layer.

2. The method findCalById should only return Calendar by id using an id. It is more reusable. What if tomorrow you need a functionality that an admin can see all the calendars irrespective of the owner. You will end up writing another query for this feature. It will be more easy to add this check in a service layer.

3. Assuming someday you have another data store which returns the record, then you end up having validations in multiple places. It will not happen if there is a service layer to do the validations. The service layer will not change as it will not care from where the record comes.

4. Onboarding a new colleague becomes easier. Assuming a new guy who specializes in the DB domain starts to work with you. He will be more productive if he is only concerned with the records that the DB should return forgetting about how the application uses this data. (separation of concern applies in real life too :)).

You have raised a very interesting question here. As others have already emphasized, correct approach depends on the meaning of your question.

how to distinguish between a calendar that does not exist and an existing calendar that cannot be accessed by the current user?

When you start thinking about implementing the filtering logic in DAO vs in Services, you effectively start solving the multi-tenancy problem, which probably is already solved. This sounds like multi-tenancy because you think how to isolate the data between different users that use the same application. The problem is narrowed down since you want to have the shared database - that's a given (cannot change). You also mention that data is not always isolated (admin can see what everybody else see in their isolation). If so, then the implementation place is actually irrelevant, and for each use case you can have a different choice - whichever makes sense for that specific use case. It would matter if you didn't have mixed use cases though, but you do have (admin vs regular user). So, your multi-tenancy is not that complex - it's just use-case-based.

One thing that I see going awkward in this conversation is - you consider your database in separation from your application, which actually defeats the purpose of the database. Your database is an application's database. I also saw signs of considering your data access logic in separation from other layers, which actually makes your data access a different application, but it's not - all layers together form your application. It's this holistic view of the application that makes the implementation place irrelevant [in this very specific case].

Here is how I see this for several use cases that can exist in your application:

• User is authenticated, which decides access rights for calendars per authenticated user (it can be either "View only user's calendars", or it can be "View all calendars").
• User accesses screen for viewing only user's calendars - user's calendars are displayed. If you think Admin should also use this same screen, then you can either map different service for admin, or call different DAO method for admin, or pass different parameters to DAO for admin.
• User accesses screen for viewing all calendars (well, this page probably is protected for only Admin's access). Then display all calendars. If you think regular user should also use this same screen, then you can either map different service for user, or call different DAO method for user, or pass different parameters to DAO for user.