Forum OpenACS Development: Permissions question.

Posted by Jorge Couchet on 07/27/07 09:29 PM

Hi,

I'm studying the permission system, and I have a doubt related with the forum post: https://openacs.org/forums/message-view?message_id=111076

There appears the following (from Don Baccus): "Right which is why I keep telling people that with the new permissions system, don't call permission_p in your where clause but use the acs_object_party_privilege_map (or whatever it's called) with an "exists" subselect instead".

Well, my doubts are:

1) That advice continues applying?

2) I was watching the source code of: permission::permision_p (from the TCL API), and acs_permission__permission_p (from the PL/SQL API):

- the first executes the query:

***********

select 1 from dual
where exists
( select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = :object_id and ppm.party_id = :party_id and ppm.privilege = :privilege )

***********

- the second executes the query:

***********

return exists (select 1
8 from acs_permissions p, party_approved_member_map m,
9 acs_object_context_index c, acs_privilege_descendant_map h
10 where p.object_id = c.ancestor_id
11 and h.descendant = permission_p__privilege
12 and c.object_id = permission_p__object_id
13 and m.member_id = permission_p__party_id
14 and p.privilege = h.privilege
15 and p.grantee_id = m.party_id)

***********

Why the functions's queries are different?, and which I should use?

Thanks a lot for the help!

Jorge.

2: Re: Permissions question. (response to 1)

Posted by Dave Bauer on 07/27/07 09:42 PM

Use this in the WHERE clause

select 1 from dual
where exists
( select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = :object_id and ppm.party_id = :party_id and ppm.privilege = :privilege )

acs_object_party_privilege_map is a view that takes care of everything.
Using a plpgsql function in the where clause causes the function to be called for EVERY ROW. If you are joining on acs_objects to check permissions this will be quite slow.

3: Re: Permissions question. (response to 1)

Posted by Jorge Couchet on 07/27/07 09:55 PM

Dave, thanks!!.

A doubt (only by curiosity): why the query from the PL/SQL function is different from the query of the TCL function (I assume that they do the same)?

Thanks again.

Jorge.

4: Re: Permissions question. (response to 3)

Posted by Tom Jackson on 07/27/07 11:58 PM

You should probably look at the view definition for acs_object_party_privilege_map. It might just be that the second query represents the view. In general it is better to replace well used joins with a view so they are easier to read and reuse, and so that you avoid confusion and/or bugs. In postgres, views are still handled like the original def for query planning, but maybe the setup is faster, which would be another benefit.

If there is a pl function which does a permission_p check, this should be optimized and then used by the Tcl proc directly. However, if you are looking for a permission as part of a where clause, the view (map) should be used for the reasons Dave gave above, this allows the query planner/optimizer to speed things up.

5: Re: Permissions question. (response to 4)

Posted by Jorge Couchet on 07/28/07 12:22 AM

Tom,

Thanks a lot!.

Indeed, tracking the view, the second query represents it.

Jorge.

6: Re: Permissions question. (response to 1)

Posted by Don Baccus on 07/30/07 07:09 PM

Once upon a time, the longer version of the query in the PL/SQL permission_p executed much more quickly than the more readable query using the abstract view.

I then devoted a lot of time to figuring out how to speed up the permissions system (by a factor of about 10x for both oracle and postgres) by denormalizing some table data, rewriting views, etc.

Because of that work, using the abstract view as recommended by daveb results in query times equivalent to that of the lower-level query in the permission_p PL/SQL procedure.

I didn't bother to rewrite the procedure to use the higher-level view, though. It works as written.

7: Re: Permissions question. (response to 6)

Posted by Jorge Couchet on 07/30/07 07:18 PM

Don,

Thanks! All is very clear now!