How to filter my Doctrine queries with Symfony ACL

Question

Symfony ACL allows me to grant access to an entity, and then check it:

if (false === $securityContext->isGranted('EDIT', $comment)) {
    throw new AccessDeniedException();
}

However, if I have thousands of entities in the database and the user has access only to 10 of them, I don't want to load all the entities in memory and hydrate them.

How can I do a simple "SELECT * FROM X" while filtering only on the entities the user has access (at SQL level)?

Answer 1

Well there it is: it's not possible.

In the last year I've been working on an alternative ACL system that would allow to filter directly in database queries.

My company recently agreed to open source it, so here it is: http://myclabs.github.io/ACL/

Answer 2

You could have a look into the Doctrine filters. That way you could extend all queries. I have not done this yet and there are some limitations documented. But maybe it helps you. You'll find a description of the ACL database tables here.

UPDATE

Each filter will return a string and all those strings will be added to the SQL queries like so:

SELECT ... FROM ... WHERE ... AND (<result of filter 1> AND <result of filter 2> ...)

Also the table alias is passed to the filter method. So I think you can add Subqueries here to filter your entities.

Answer 3

As pointed out by @gregor in the previous discussion,

In your first query, get a list (with a custom query) of all the object_identity_ids (for a specific entity/class X) a user has access to.

Then, when querying a list of objects for entity/class X, add "IN (object_identity_ids)" to your query.

Matthieu, I wasn't satisfied by replying with more of conjectures (since my conjectures don't add anything valuable to the conversation). So I did some bench-marking on this approach (Digital Ocean 5$/mo VPS).

As expected, table size doesn't matter when using the IN array approach. But a big array size indeed makes things get out of control.

So, Join approach vs IN array approach?

JOIN is indeed better when the array size is huge. BUT, this is assuming that we shouldn't consider the table size. Turns out, in practice IN array is faster - except when there's a large table of objects and the acl entries cover almost every object (see the linked question).

I've expanded on my reasoning on a separate question. Please see When using Symfony's ACL, is it better to use a JOIN query or an IN array query?