13

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

Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261
  • 1
    Have you looked at this SO answer? http://stackoverflow.com/questions/9652755/best-way-to-manage-user-group-object-permissions-with-symfony2 – am_ Feb 26 '13 at 20:03
  • Did you try to use [createQuery](http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#dql-select-clause) method? – Idan Yadgar Feb 25 '13 at 13:43

3 Answers3

3

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/

Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261
  • 1
    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. – gregor May 23 '14 at 16:20
  • 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). – Matthieu Napoli May 24 '14 at 11:32
  • not true, WHERE IN () should be fast - http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in . 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. – gregor May 26 '14 at 14:57
  • 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. – Matthieu Napoli May 26 '14 at 15:01
  • 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. – Matthieu Napoli May 26 '14 at 15:02
  • @MatthieuNapoli "which (when given many ids) would be very inefficient " --- IN works. Just pass a list of ids (not another query). – galeaspablo Jul 16 '16 at 20:02
  • "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 @gregor has addressed i.e. query_packet_size. – galeaspablo Jul 16 '16 at 20:05
  • 1
    @galeaspablo 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. – Matthieu Napoli Jul 17 '16 at 17:09
  • 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. – galeaspablo Jul 17 '16 at 22:39
1

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

Benchmark

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?

Community
  • 1
  • 1
galeaspablo
  • 869
  • 5
  • 15
-1

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.

stofl
  • 2,950
  • 6
  • 35
  • 48
  • 2
    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. – Matthieu Napoli Mar 15 '14 at 09:36