Querying the Portal tables for a list of Secured Items

This is useful in producing audit reports and during migrations. It will print a list of all the group privileges assigned to the various objects in portal (pages, items, etc). You can easily update this to include user privileges, although most of our privileges were granted to groups rather than users. This also includes an additional join (p.name=t.filename) that allows you to retrieve the values for some custom object types as well.

select p.object_type_name,p.owner,p.name,p.grantee_group_id,g.id,g.name group_name, p.privilege,t.id,t.masterthingid, t.itemtype,t.title,t.title_link,t.description,t.author,t.filename,t.creator,t.createdate,t.updator,t.updatedate,t.version
from portal.wwsec_sys_priv$ p, portal.wwsec_group$ g , portal.wwv_things t
where p.grantee_group_id=g.id
and g.name not in ('AUTHENTICATED_USERS','PORTAL_ADMINISTRATORS','DBA','PORTAL_DEVELOPERS')
and( p.name = t.filename or
(p.name LIKE t.siteid||'%'
and p.name LIKE '%'||to_char(t.masterthingid)
)
)
and t.iscurrentversion=1
order by p.object_type_name,t.itemtype, p.name, g.name