PeopleSoft Security Queries

Query to find the list of users having access to component, action along with Navigation details

 

SELECT DISTINCT roleclass.rolename,
authitem.menuname,
authitem.menulabel, 
authitem.baritemname,
authitem.itemlabel,
DECODE(authitem.DISPLAYONLY, 0, 'N', 1, 'Y') AS "Display_Only",
CASE authitem.AUTHORIZEDACTIONS
    WHEN 1 THEN 'Add'
    WHEN 2 THEN 'Update/Display'
    WHEN 3 THEN 'Add, Update/Display'
    WHEN 4 THEN 'Update/Display All'
    WHEN 5 THEN 'Add, Update/Display All'
    WHEN 6 THEN 'Update/Display, Update/Display All'
    WHEN 7 THEN 'Add, Update/Display, Update/Display All'
    WHEN 8 THEN 'Correction'
    WHEN 9 THEN 'Add, Correction'
    WHEN 10 THEN 'Update/Display, Correction'
    WHEN 11 THEN 'Add, Update/Display, Correction'
    WHEN 12 THEN 'Update/Display All, Correction'
    WHEN 13 THEN 'Add, Update/Display All, Correction'
    WHEN 14 THEN 'Update/Display, Update/Display All, Correction'
    WHEN 15 THEN 'Add, Update/Display, Update/Display All, Correction'
    ELSE 'SPECIAL' END AS "Authorized_Actions",
authitem.PNLGRPNAME,
CASE InStr(nav.PATH_TO_COMPONENT, 'Root>', 1)
  WHEN 0 THEN nav.PATH_TO_COMPONENT
  ELSE SubStr(nav.PATH_TO_COMPONENT, InStr(nav.PATH_TO_COMPONENT, 'Root>', 1) + 5, length(nav.PATH_TO_COMPONENT) - InStr(nav.PATH_TO_COMPONENT, 'Root>', 1) + 5)
  END AS navigation
from
(SELECT
DISTINCT a.CLASSID, a.MENUNAME, b.MENULABEL , a.BARITEMNAME,c.itemlabel ,
a.DISPLAYONLY,                                                                               
a.AUTHORIZEDACTIONS,
c.PNLGRPNAME
FROM PSAUTHITEM a, PSMENUDEFN b, PS_ACLCOMPONENT_V2 c
where a.MENUNAME = b.MENUNAME
AND a.menuname =  c.menuname
AND a.barname = c.barname
AND a.baritemname = c.baritemname) authitem ,
psroleclass roleclass,
(SELECT a.PORTAL_URI_SEG2 as component
, E.PORTAL_LABEL || '>' || D.PORTAL_LABEL || '>' ||C.PORTAL_LABEL ||
'>' || B.PORTAL_LABEL || '>' || A.PORTAL_LABEL as PATH_TO_COMPONENT
, A.PORTAL_URLTEXT
, A.PORTAL_ISPUBLIC as Active
, A.LASTUPDOPRID
, A.LASTUPDDTTM
, A.PORTAL_URI_SEG2
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D, PSPRSMDEFN
E
WHERE 1=1
AND A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME(+)
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME(+)
AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME(+)
AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME(+)
AND (E.PORTAL_NAME IS NULL OR E.PORTAL_NAME = 'EMPLOYEE')
AND (A.PORTAL_NAME = 'EMPLOYEE' OR A.PORTAL_NAME IS NULL )
AND (B.PORTAL_NAME = 'EMPLOYEE' OR B.PORTAL_NAME IS NULL )
AND (C.PORTAL_NAME = 'EMPLOYEE' OR C.PORTAL_NAME IS NULL )
AND (D.PORTAL_NAME = 'EMPLOYEE' OR D.PORTAL_NAME IS NULL )
) nav
WHERE  roleclass.rolename =
AND authitem.classid = roleclass.classid
AND  authitem.PNLGRPNAME =  nav.PORTAL_URI_SEG2(+)
ORDER BY rolename, menuname, baritemname

 

***********************************************************************

Below are the SQLs to find the Peoplecode and SQL objects which are  created or modified by you but not included in any of your project.

 

 

--------------------------------------------------

-- Selects Peoplecode not inserted in a project

--------------------------------------------------

SELECT

CASE A.OBJECTID1

when 1 then 'Record Peoplecode'

when 3 then 'Menu Peoplecode'

when 9 then 'Page Peoplecode'

when 10 then 'Component level Peoplecode'

when 60 then 'Message Peoplecode'

when 74 then 'Component Interface Peoplecode'

when 87 then 'Subscription Peoplecode'

END AS  OBJect_Type

,

A.OBJECTVALUE1 || '.' || A.OBJECTVALUE2 || '.' || A.OBJECTVALUE3 || '.' || A.OBJECTVALUE4 || '.' || A.OBJECTVALUE5 || '.' || A.OBJECTVALUE6  || '.' || A.OBJECTVALUE7

AS EXTENDED_OBJ_NAME

FROM PSPCMPROG    A 

 , PSPCMTXT B -- join to pspcmtxt to filter out reverted changes (back to blank)

WHERE  LASTUPDOPRID =

AND  A.OBJECTID1 || A.OBJECTVALUE1 || A.OBJECTID2 ||A.OBJECTVALUE2 || A.OBJECTID3 || A.OBJECTVALUE3 || A.OBJECTID4 || A.OBJECTVALUE4

NOT IN

(

SELECT OBJECTID1 || OBJECTVALUE1 || OBJECTID2 || OBJECTVALUE2 || OBJECTID3 || OBJECTVALUE3 || OBJECTID4 || OBJECTVALUE4

FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

and i.objecttype in (8,9,39,40,42,43,44,46,47,48)

)

AND a.objectid1                               = b.objectid1

AND a.objectvalue1                        = b.objectvalue1

AND a.objectid2                               = b.objectid2

AND a.objectvalue2                        = b.objectvalue2            

AND a.objectid3                               = b.objectid3

AND a.objectvalue3                        = b.objectvalue3

AND a.objectid4                               = b.objectid4

AND a.objectvalue4                        = b.objectvalue4

AND a.objectid5                               = b.objectid5

AND a.objectvalue5                        = b.objectvalue5

AND a.objectid6                               = b.objectid6

AND a.objectvalue6                        = b.objectvalue6

AND a.objectid7                               = b.objectid7

AND a.objectvalue7                        = b.objectvalue7

AND  A.OBJECTID1 NOT IN   (66,104)

;    

  

 

 

--------------------------------------------------

-- Selects App Engine Peoplecode not inserted in a project

--------------------------------------------------

SELECT

DISTINCT

'Application Engine Peoplecode',

A.OBJECTVALUE1 || '.' || A.OBJECTVALUE2 || '.' || A.OBJECTVALUE3 || '.' || A.OBJECTVALUE4 || '.' || A.OBJECTVALUE5 || '.' || A.OBJECTVALUE6  || '.' || A.OBJECTVALUE7

FROM PSPCMPROG    A 

WHERE  LASTUPDOPRID =

AND   A.OBJECTID1|| A.OBJECTVALUE1|| A.OBJECTID2||A.OBJECTVALUE2||A.OBJECTVALUE3||A.OBJECTVALUE4||A.OBJECTVALUE5  

 NOT IN

(

SELECT I.OBJECTID1 ||I.OBJECTVALUE1 || I.OBJECTID2 ||REPLACE(I.OBJECTVALUE2,' ','')

 FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

and objecttype =43

)

and A.OBJECTID1 = 66

;    

 

--------------------------------------------------

-- Selects App Package Peoplecode not inserted in a project

--------------------------------------------------

SELECT

'Application Package Peoplecode'

, A.OBJECTVALUE1 || '.' || A.OBJECTVALUE2

 FROM PSPCMPROG    A 

WHERE  LASTUPDOPRID =

AND   A.OBJECTID1|| A.OBJECTVALUE1|| A.OBJECTID2||A.OBJECTVALUE2

NOT IN

(

SELECT I.OBJECTID1 ||I.OBJECTVALUE1 || I.OBJECTID2 || I.OBJECTVALUE2

 FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

and objecttype =58

)

and A.OBJECTID1 = 104

;    

 

 

 

--------------------------------------------------

-- Selects SQL Objects not inserted in a project

--------------------------------------------------

SELECT 

CASE WHEN SQLTYPE = 0 THEN 'SQL Object '

WHEN SQLTYPE = 1 THEN 'App Engine Step '

WHEN SQLTYPE = 2 THEN 'Record View'

WHEN SQLTYPE = 6 THEN 'Application Engine XSLT'

END AS OBJECTTYPE      ,

SQLID

FROM PSSQLDEFN

WHERE   LASTUPDOPRID =

AND  SQLID

NOT IN

(

SELECT OBJECTVALUE1

FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

AND OBJECTTYPE in (30)

);