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