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