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)?

Andreaandreana

Did you try to use createQuery method? – Sanctimony

Have you looked at this SO answer? #9653255 – Breazeale

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/

Andreaandreana

There is a workaround though. One can list the ACE for a SecurityIdentity and the UserSecurityIdentity and then inject the WHERE clause to the query without joining, just the ids. – Monthly

Yes but that's a query like that WHERE id IN (1, 2, 3, …), which (when given many ids) would be very inefficient :( But you are right that's worth mentioning. Also, that would be 2 DB queries instead of one (with a JOIN). – Andreaandreana

not true, WHERE IN () should be fast - dev.mysql.com/doc/refman/5.0/en/… . Also the limit to the number of IDs is dependant on the allowed query packet size, which by default is 16MB (increasable to 1GB), enough for at least 1 million IDS in the query (I think). Number of queries depends on where you store the ACL rules - could be in a file or memcached - so 1 query all together. – Monthly

At my work most of the filtering done that way would end up with 1,000 to 10,000 IDs in the WHERE … IN (…) clause (in a table containing possibly millions on rows). That's not good. I agree that it's not a problem with low volumes of data. – Andreaandreana

But when you think realistically about it, there's a reason you use JOIN instead of 2 queries and a WHERE IN. The same reason applies here. – Andreaandreana

@MatthieuNapoli "which (when given many ids) would be very inefficient " --- IN works. Just pass a list of ids (not another query). – Proofread

"there's a reason you use JOIN" --- Yes there is a reason you use JOIN... because you need other fields from another table. WHY? IN's implementation is pretty good in modern RDBMS implementations. Such that you will be using binary search (time complexity log[n]). In a table of 100,000,000,000 entries, log(n) = 11... The only concern would be the length of your list, which @Monthly has addressed i.e. query_packet_size. – Proofread

@Proofread as I said, the problem is if your list contains 1,000, 10,000 or even 1M IDs… Take your log(n) and multiply it by that. And I'm not sure that would be efficient (or would even work) to do a first query to get 1M IDs, and then another query with 1M IDs in the IN clause. – Andreaandreana

Hey @MatthieuNapoli I've taken a couple of hours to analyze this, since I'm evaluating my ACL approach for a similar situation. I've added another answer to this question, and created a separate question to address the IN vs JOIN for this specific case. In any case, thanks for your work in the open source ACL system. I'm having a look at it. – Proofread

Answer 2

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?

Proofread

Answer 3

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.

Valenzuela

Thanks for your answer, I didn't see it. Doctrine filter don't help because they don't allow to JOIN with a table, they only allow to add SQL in the WHERE clause. – Andreaandreana