Where should filtering with an Acl be performed?

Let's say I have three tables: users, books, and users_books.

In one of my views, I want to display a list of all the books the current user has access to. A user has access to a book if a row matching a user and a book exists in users_books.

There are (at least) two ways I can accomplish this:

In my fetchAll() method in the books model, execute a join of some sort on the users_books table.
In an Acl plugin, first create a resource out of every book. Then, create a role out of every user. Next, allow or deny users access to each resource based on the users_books table. Finally, in the fetchAll() method of the books model, call isAllowed() on each book we find, using the current user as the role.

I see the last option as the best, because then I could use the Acl in other places in my application. That would remove the need to perform duplicate access checks.

What would you suggest?

Solution

I'd push it all down into the database:

Doing it in the database through JOINs will be a lot faster than filtering things in your PHP.
Doing it in the database will let you paginate things properly without having to jump through hoops like fetching more data than you need (and then fetching even more if you end up throwing too much out).

I can think of two broad strategies you could employ for managing the ACLs.

You could set up explicit ACLs in the database with a single table sort of like this:

id: The id of the thing (book, picture, ...) in question.
id_type: The type or table that id comes from.
user: The user that can look at the thing.

The (id, id_type) pair give you a pseudo-FK that you can use for sanity checking your database and the id_type can be used to select a class to provide the necessary glue to interact the the type-specific parts of the ACLs and add SQL snippets to queries to properly join the ACL table.

Alternatively, you could use a naming convention to attach an ACL sidecar table to each table than needs an ACL. For table t, you could have a table t_acl with columns like:

id: The id of the thing in t (with a real foreign key for integrity).
user: The user the can look at the thing.

Then, you could have a single ACL class that could adjust your SQL given the base table name.

The main advantage of the first approach is that you have a single ACL store for everything so it is easy to answer questions like "what can user X look at?". The main advantage of the second approach is that you can have real referential integrity and less code (through naming conventions) for gluing it all together.

Hopefully the above will help your thinking.