Peoplesoft Page Security View

Peoplesoft Page Security View.

 

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

 

-Posted by

Ganesh A.M

 

 

0 comments: