3 Replies Latest reply: Nov 26, 2009 2:31 PM by AmandaWalsh RSS

    report on group & user access in portal

    526435
      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
          thanks carp! it does help!
          • 2. Re: report on group & user access in portal
            463808
            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
              Thankyou for posting your hard work!

              This has been such a great help.