I just found out a way to work around it: create a materialized view.
create materialized view acs_privilege_helper
refresh on commit
as
select p.privilege, h.privilege as parent_privilege
from acs_privileges p, acs_privilege_hierarchy h
where p.privilege=h.child_privilege(+)
The resulting object acs_privilege_helper can be used like a table and the connect by works just fine on it.
Any objections on adding this view to acs-kernel? Drawbacks:
1) slight performance hit when inserting new privileges - since this happens almost never it's not a problem.
2) A maintainability nuisance: I had to grant the oracle user the right to create materialized view like this before I could create it:
grant create materialized view to myuser;
I wonder how this is set by default in normal oracle installations and if it would bother everyone upgrading.
Benefit: a beautiful permission grant page ...
