This discussion is archived
3 Replies Latest reply: Nov 26, 2009 12:31 PM by AmandaWalsh RSS

report on group & user access in portal

526435 Newbie
Currently Being Moderated
I created this query that seems to get everything I need. Hope it helps others.

select object_type_name object_type,S.name parent_page ,null page_name, p.privilege, null user_name, gp.dn group_name
from portal.WWSBR_SITES$ S, PORTAL.WWSEC_SYS_PRIV$ p, portal.wwsec_group$ gp
where p.object_type_name ='SITE' -- SITE(page group) / GROUP
and p.grantee_type = 'GROUP'
and P.name = S.id
and p.grantee_group_id = gp.id
union
select object_type_name,S.name ,null, p.privilege, u.name, null
from portal.WWSBR_SITES$ S, PORTAL.WWSEC_SYS_PRIV$ p,wwsec_user u
where p.object_type_name ='SITE' -- SITE(page group) / USER
and p.grantee_type = 'USER'
and P.name = S.id
and p.grantee_user_id = u.id
union
select distinct object_type_name,pg1.name parent_page,pg.name, p.privilege, null user_name, gp.dn group_name
from portal.wwpob_page$ pg, portal.wwpob_page$ pg1, portal.wwsec_group$ gp, PORTAL.WWSEC_SYS_PRIV$ p
where pg1.syspriv_name=p.name
and p.object_type_name ='PAGE' -- PAGE / GROUP
and p.grantee_group_id = gp.id
and p.grantee_type = 'GROUP'
and pg.siteid = pg1.siteid
and pg1.id=1
union
select distinct object_type_name,pg1.name parent_page,pg.name, p.privilege, u.name user_name, null group_name
from portal.wwpob_page$ pg, portal.wwpob_page$ pg1, wwsec_user u, PORTAL.WWSEC_SYS_PRIV$ p
where pg1.syspriv_name=p.name
and p.object_type_name ='PAGE' -- PAGE / USER
and p.grantee_user_id = u.id
and p.grantee_type = 'USER'
and pg.siteid = pg1.siteid
and pg1.id=1
union
select object_type_name,pg1.name parent_page,t.title ,p.privilege, null user_name, gp.dn group_name
from portal.wwv_things t, portal.wwpob_page$ pg1, portal.wwsec_group$ gp, PORTAL.WWSEC_SYS_PRIV$ p,wwsec_user u
where t.siteid = substr(p.name,1,2)
and t.masterthingid = substr(p.name,4,8)
and p.object_type_name ='ITEM' -- ITEM / GROUP
and p.grantee_group_id = gp.id
and p.grantee_type = 'GROUP'
and t.siteid = pg1.siteid
and pg1.id=1
union
select object_type_name,pg1.name parent_page,t.title ,p.privilege, u.name, null
from portal.wwv_things t, portal.wwpob_page$ pg1, PORTAL.WWSEC_SYS_PRIV$ p,wwsec_user u
where t.siteid = substr(p.name,1,2)
and t.masterthingid = substr(p.name,4,8)
and p.object_type_name ='ITEM' -- ITEM / USER
and p.grantee_type = 'USER'
and t.siteid = pg1.siteid
and pg1.id=1
and p.grantee_user_id = u.id
order by 2,1 desc,3
  • 1. Re: report on group & user access in portal
    DinosK Newbie
    Currently Being Moderated
    thanks carp! it does help!
  • 2. Re: report on group & user access in portal
    463808 Newbie
    Currently Being Moderated
    Here is a version that works with Oracle Portal Version: 10.1.4.2.0 (Build: 197) on Oracle Database 10.2.0.3.0 and Oracle Portal Version: 10.1.2.2.0 on Oracle Database 10.1.0.5.0. I changed the last 2 SELECT statements to correctly parse the siteid and masterthingid from the object name.

    select object_type_name object_type
    , S.name parent_page
    , null page_name
    , p.privilege
    , null user_name
    , gp.dn group_name
    from portal.WWSBR_SITES$ S
    , PORTAL.WWSEC_SYS_PRIV$ p
    , portal.wwsec_group$ gp
    where p.object_type_name ='SITE'
    and p.grantee_type = 'GROUP'
    and P.name = S.id
    and p.grantee_group_id = gp.id
    union
    select object_type_name object_type
    , S.name parent_page
    , null page_name
    , p.privilege
    , u.name user_name
    , null group_name
    from portal.WWSBR_SITES$ S
    , PORTAL.WWSEC_SYS_PRIV$ p
    , wwsec_user u
    where p.object_type_name ='SITE'
    and p.grantee_type = 'USER'
    and P.name = S.id
    and p.grantee_user_id = u.id
    union
    select distinct object_type_name object_type
    , pg1.name parent_page
    , pg.name page_name
    , p.privilege
    , null user_name
    , gp.dn group_name
    from portal.wwpob_page$ pg
    , portal.wwpob_page$ pg1
    , portal.wwsec_group$ gp
    , PORTAL.WWSEC_SYS_PRIV$ p
    where pg1.syspriv_name=p.name
    and p.object_type_name ='PAGE'
    and p.grantee_group_id = gp.id
    and p.grantee_type = 'GROUP'
    and pg.siteid = pg1.siteid
    and pg1.id=1
    union
    select distinct object_type_name object_type
    , pg1.name parent_page
    , pg.name page_name
    , p.privilege
    , u.name user_name
    , null group_name
    from portal.wwpob_page$ pg
    , portal.wwpob_page$ pg1
    , wwsec_user u
    , PORTAL.WWSEC_SYS_PRIV$ p
    where pg1.syspriv_name=p.name
    and p.object_type_name ='PAGE'
    and p.grantee_user_id = u.id
    and p.grantee_type = 'USER'
    and pg.siteid = pg1.siteid
    and pg1.id=1
    union
    select object_type_name object_type
    , pg1.name parent_page
    , t.title page_name
    , p.privilege
    , null user_name
    , gp.dn group_name
    from portal.wwv_things t
    , portal.wwpob_page$ pg1
    , portal.wwsec_group$ gp
    , PORTAL.WWSEC_SYS_PRIV$ p
    , wwsec_user u
    where t.siteid = substr(p.name,1,instr(p.name,'/')-1)
    and t.masterthingid = substr(p.name,instr(p.name,'/')+1,length(p.name))
    and p.object_type_name ='ITEM'
    and p.grantee_group_id = gp.id
    and p.grantee_type = 'GROUP'
    and t.siteid = pg1.siteid
    and pg1.id=1
    union
    select object_type_name object_type
    , pg1.name parent_page
    , t.title page_name
    , p.privilege
    , u.name user_name
    , null group_name
    from portal.wwv_things t
    , portal.wwpob_page$ pg1
    , PORTAL.WWSEC_SYS_PRIV$ p
    , wwsec_user u
    where t.siteid = substr(p.name,1,instr(p.name,'/')-1)
    and t.masterthingid = substr(p.name,instr(p.name,'/')+1,length(p.name))
    and p.object_type_name ='ITEM'
    and p.grantee_type = 'USER'
    and t.siteid = pg1.siteid
    and pg1.id=1
    and p.grantee_user_id = u.id
    order by 2, 1 desc, 3;
  • 3. Re: report on group & user access in portal
    AmandaWalsh Explorer
    Currently Being Moderated
    Thankyou for posting your hard work!

    This has been such a great help.