PeopleSoft Page Security

This view will give the clear mapping of the oprid and permission that he has on the set of the pages in particular menu path.

This view also displays ROLENAME and CLASSID through which oprid has the permission to a particular page.

SELECT DISTINCT h.oprid , h.oprdefndesc , g.rolename
, a.classid ,Decode(SUBSTR (b.menugroup , 1 , INSTR (b.menugroup , '&') - 1) SUBSTR (b.menugroup , INSTR (b.menugroup , '&') + 1 , LENGTH (b.menugroup) ) ,'PeopleSoft'
,'PeopleTools' ,SUBSTR (b.menugroup , 1 , INSTR (b.menugroup , '&') - 1) SUBSTR (b.menugroup , INSTR (b.menugroup , '&') + 1 , LENGTH (b.menugroup) )) AS menugroup_label , SUBSTR (b.menulabel , 1 , INSTR (b.menulabel , '&') - 1) SUBSTR (b.menulabel , INSTR (b.menulabel , '&') + 1 , LENGTH (b.menulabel) ) AS menu_label
, a.menuname , c.barname , SUBSTR (c.barlabel , 1 , INSTR (c.barlabel , '&') - 1) SUBSTR (c.barlabel , INSTR (c.barlabel , '&') + 1 , LENGTH (c.barlabel) ) AS bar_label
, SUBSTR (c.itemlabel , 1 , INSTR (c.itemlabel , '&') - 1) SUBSTR (c.itemlabel
, INSTR (c.itemlabel , '&') + 1 , LENGTH (c.itemlabel) ) AS menuitem_label
, c.pnlgrpname AS component , DECODE (a.authorizedactions , 1 , 'Add' , 2 , 'Update/Display'
, 3 , 'Add Update/Display' , 4 , 'Update/Display All' , 5 , 'Add Update/Display All'
, 6 , 'Update/Display Update/Display All' , 7 , 'Add Update/Display Update/Display All'
, 8 , 'Correction' , 9 , 'Add Correction' , 10 , 'Update/Display Correction'
, 11 , 'Add Update/Display Correction' , 12 , 'Update/Display All Correction'
, 13 , 'Add Update/Display All Correction' , 14 , 'Update/Display Update/Display All Correction'
, 15 , 'Add Update/Display Update/Display All Correction' , 129 , 'Add Data Entry'
, 131 , 'Add Update/Display Data Entry' , 135 , 'Add Update/Display Update/Display All Data Entry' , 143 , 'Add Update/Display Update/Display All Correction Data Entry' , 139
, 'Add Update/Display Correction Data Entry' , 133 , 'Add Update/Display All Data Entry'
, 137 , 'Add Correction Data Entry' , 141 , 'Add Update/Display All Correction Data Entry'
, 130 , 'Update/Display Data Entry' , 134 , 'Update/Display Update/Display All Data Entry'
, 138 , 'Update/Display Correction Data Entry' , 142 , 'Update/Display Update/Display All Correction, Data Entry' , 132 , 'Update/Display All Data Entry' , 140 , 'Update/Display All Correction Data Entry' , 136 , 'Correction Data Entry' , 128 , 'Data Entry' ) AS authorized_actions , SUBSTR (d.itemlabel , 1 , INSTR (d.itemlabel , '&') - 1) SUBSTR (d.itemlabel , INSTR (d.itemlabel , '&') + 1 , LENGTH (d.itemlabel) ) AS componentitem_label
, d.pnlname AS pagename , DECODE (a.displayonly , 1 , 'READONLY' , 0
, 'WRITE') AS read_write , c.itemnum , d.subitemnum FROM psauthitem a
, psmenudefn b , psmenuitem c , pspnlgroup d , pspnlgrpdefn e , psroleclass f
, psroleuser g , psoprdefn h WHERE a.menuname = b.menuname AND a.menuname = c.menuname AND a.barname = c.barname AND a.baritemname = c.itemname
AND a.PNLITEMNAME=d.ITEMNAME AND c.itemtype = 5 AND d.pnlgrpname = c.pnlgrpname AND d.market = c.market AND d.pnlgrpname = e.pnlgrpname AND d.market = e.market AND e.pnlgrpname = c.pnlgrpname AND e.market = c.market AND f.classid = a.classid AND f.rolename = g.rolename AND g.roleuser = h.oprid AND h.acctlock = 0