We have had several discussions about permissions performance in OACS
4.5, but I just wanted to reiterate that developers should try to
avoid using acs_permissions__permission_p in a WHERE clause of a
query.  I did a first pass performance optimization on my client's
site and by removing acs_permissions__permission_p from several WHERE
clauses, I got a 3-7x improvement on page loads!  I was able to
modify the permissions check so that I called
acs_permissions__permission_p on a significantly reduced set of rows,
but that was due to how I setup the permissions rules for this site.

I'd like to use this thread to collect ideas/code fragments/examples
of how to work around using acs_permissions__permission_p in a WHERE
clause when you absolutely need to do a permissions check in a query.

I'll start by explaining how I setup my permissions checking for my
client's site.  The query that was killing the server performance was
a query which grabbed the most recent forum messages.  Since the site
has several hidden/private members only forums, I had to do
permission checks each time I wanted to display the most recent
messages.  A normal user should not be able to see latest messages in
an administrative forum, etc.

The way I implemented private forums was to set the securty_inherit_p
flag to false for private forums.  Then I would add members to that
forum by granting read permissions (a child privilege of read) on
that forum.  Messages are tied to a forum by a forum message map
table.  Therefore, to see if a user could read a message, I needed to
grab the forum_id and check to see if a user had permissions to view
that forum.

My first query used acs_permissions__permission_p in a WHERE clause.
The performance was very poor because it ran that expensive function
for each row.  :)  To speed up the query, I created a plsql function
which checked whether or not the security_inherit_p flag was set to
true, if it was, then I knew it was a public forum and this user
could view it.  If not, the function ran the
acs_permissions__permission_p function to check permissions, and
returned the appropriate response.  As I mentioned, the speedup was
roughly 3-7x.

Please feel free to comment on my solution (I'm sure there are better
ways to solve the problem) and provide examples of how you solved the
permissions checking problem.  Thanks.

Hmmm...I don't see a drastic restructing of the permissions system in our near future though it may get some fiddling.  I've thought of the separate table for modelling permission inheritance trick, too, and it might help considerably ...

They key, though, is to just avoid calling ad_perrmissions__permission_p in a where clause.

There's a bit of a struggle here in that the idea behind this proc is to abstract the check from the datamodel.  I don't really like the idea of spawning dummy types and having permissions work on them, as then we'd have to worry about the scalability of the acs_object_types structure which itself is subjected to plenty of hierarchical queries.

But in general joining against the "all_object_party_permissions_map" directly and checking for permissions against the view is *much* faster if a lot of rows are involved.  Use the "all" not the "acs_object_party_permission_map" though, as the latter's use of UNION will result in *very* slow queries, too.

In Oracle in particular the optimizer seems to like running permission_p on all rows before using join qualifiers to throw most of them away.  Ugh.  One way around this is to use a subselect to throw away the rows then use permission_p to check against those that survive the subselect.

Oh, right, a privilege isn't a permission isn't a privilege :)  Oops!

Peter,

Which database are you using? Does it make a difference if it Postgresql or Oracle?

This is Postgres (both 7.1 and 7.2)