PeopleSoft

--SQL to check access to Menu-Component-Page accessible to user through which Role and PL

SELECT DISTINCT RTRIM(F.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.MENUNAME) , RTRIM(B.PNLGRPNAME) , RTRIM(D.PNLNAME) ,
A.DISPLAYONLY, CASE A.AUTHORIZEDACTIONS
WHEN 0 THEN 'Authorized without Any Action'
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 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'
WHEN 139 THEN 'Add Update/Display Correction Data Entry' END "Access"
FROM PSAUTHITEM A, PSMENUITEM B, PSCLASSDEFN C, PSPNLGROUP D , PSROLECLASS E , PSROLEUSER F
WHERE A.MENUNAME = B.MENUNAME
AND A.BARNAME = B.BARNAME
AND A.BARITEMNAME = B.ITEMNAME
AND D.ITEMNAME = A.PNLITEMNAME
AND A.CLASSID = C.CLASSID
AND B.PNLGRPNAME = D.PNLGRPNAME
AND A.CLASSID = E.CLASSID
AND E.ROLENAME = F.ROLENAME
AND B.PNLGRPNAME = 'PC_AM_EXPRESS'
AND D.PNLNAME = 'PC_AM_EXPRESS3'
AND F.ROLEUSER = 'TK31108' ORDER BY 1,2;

SELECT TRIM(F.ROLEUSER) ,   TRIM(G.OPRDEFNDESC) , TRIM(D.ROLENAME) , TRIM(A.CLASSID) , TRIM(A.MENUNAME) , TRIM(C.PNLGRPNAME) ,
TRIM(C.PNLNAME) ,   A.DISPLAYONLY , TRIM(E.PAGEACCESSDESCR)
FROM PSAUTHITEM A,
PS_ACLCOMPONENT_V2 B ,
PSPNLGROUP C ,
PSROLECLASS D ,
PSPGEACCESSDESC E ,
PSROLEUSER F ,
PSOPRDEFN G
WHERE A.CLASSID         = D.CLASSID
AND A.MENUNAME          = B.MENUNAME
AND A.BARNAME           = B.BARNAME
AND A.BARITEMNAME       = B.BARITEMNAME
AND B.PNLGRPNAME      = C.PNLGRPNAME
AND A.PNLITEMNAME       = C.ITEMNAME
AND A.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS
AND D.ROLENAME          = F.ROLENAME
AND F.ROLEUSER          = G.OPRID
AND C.PNLGRPNAME        = 'TRN_IDENTIFY'
ORDER BY 1,2,3,4;

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

--SQL to check access to Menu-Component-Page accessible through which PL (which may be assigned to a role or not)

SELECT DISTINCT RTRIM(A.CLASSID) , RTRIM(A.MENUNAME) , RTRIM(B.PNLGRPNAME) , RTRIM(D.PNLNAME) ,
A.DISPLAYONLY, CASE A.AUTHORIZEDACTIONS
WHEN 0 THEN 'Authorized without Any Action'
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 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'
WHEN 139 THEN 'Add Update/Display Correction Data Entry' END "Access"
FROM PSAUTHITEM A, PSMENUITEM B, PSCLASSDEFN C, PSPNLGROUP D
WHERE A.MENUNAME = B.MENUNAME
AND A.BARNAME = B.BARNAME
AND A.BARITEMNAME = B.ITEMNAME
AND D.ITEMNAME = A.PNLITEMNAME
AND A.CLASSID = C.CLASSID
AND B.PNLGRPNAME = D.PNLGRPNAME
AND B.PNLGRPNAME = 'PC_AM_EXPRESS'
AND D.PNLNAME = 'PC_AM_EXPRESS3' ORDER BY 1,2;

SELECT TRIM(D.ROLENAME) , TRIM(A.CLASSID) , TRIM(A.MENUNAME) , TRIM(C.PNLGRPNAME) , TRIM(C.PNLNAME) , A.DISPLAYONLY , TRIM(E.PAGEACCESSDESCR)
FROM PSAUTHITEM A,
PS_ACLCOMPONENT_V2 B ,
PSPNLGROUP C ,
PSROLECLASS D ,
PSPGEACCESSDESC E
WHERE A.CLASSID         = D.CLASSID
AND A.MENUNAME          = B.MENUNAME
AND A.BARNAME           = B.BARNAME
AND A.BARITEMNAME       = B.BARITEMNAME
AND B.PNLGRPNAME = C.PNLGRPNAME
AND A.PNLITEMNAME       = C.ITEMNAME
AND A.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS
AND C.PNLNAME           = 'CS_DOC_MAINT'
ORDER BY 1,2,3,4;

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

--SQL to check access of Application Designer on PeopleTools tab in PL

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME LIKE 'APPLICATION_DESIGNER%';

--App Des Object Definition Access

SELECT DISTINCT RTRIM(C.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.MENUNAME) , RTRIM(D.OBJNAME)
FROM PSAUTHITEM A , PSROLECLASS B , PSROLEUSER C , PS_APP_DES_OBJ_VW D
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.MENUNAME LIKE 'APPLICATION_DESIGNER%'
AND A.BARNAME = D.OBJNAME
AND A.CLASSID = 'ALLPAGES' ORDER BY 1,2;

--Tools Permissions Access

SELECT DISTINCT RTRIM(C.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.MENUNAME) , RTRIM(D.OBJNAME)
FROM PSAUTHITEM A , PSROLECLASS B , PSROLEUSER C , PS_APP_DES_TLS_VW D
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.MENUNAME LIKE 'APPLICATION_DESIGNER%'
AND A.BARNAME = D.OBJNAME
AND A.CLASSID = 'ALLPAGES' ORDER BY 1,2;

--Miscellaneous Permissions Access

SELECT DISTINCT RTRIM(C.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.MENUNAME) , RTRIM(D.OBJNAME)
FROM PSAUTHITEM A , PSROLECLASS B , PSROLEUSER C , PS_APP_DES_MISC_VW D
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.MENUNAME LIKE 'APPLICATION_DESIGNER%'
AND A.BARNAME = D.OBJNAME
AND A.CLASSID = 'ALLPAGES' ORDER BY 1,2;

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

--SQL to check access of Data Mover on PeopleTools tab in PL

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME = 'DATA_MOVER';

SELECT DISTINCT RTRIM(C.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.MENUNAME)
FROM PSAUTHITEM A , PSROLECLASS B , PSROLEUSER C
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.MENUNAME = 'DATA_MOVER' ORDER BY 1,2;

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

--SQL to check access of Definition Security Access on PeopleTools tab in PL

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME = 'OBJECT_SECURITY';

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

--SQL to check access of Query Access on PeopleTools tab in PL

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME = 'QUERY';

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

--SQL to check access of Performance Monitor PPMI Access on PeopleTools tab in PL

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME = 'PERFMONPPMI';

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

--SQL to check access of Realtime Event Notification Permissions (RENServer Permission) on PeopleTools tab in PL

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME LIKE 'REN%';

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

Common SQL For Data Archival

SELECT ARCH_SEC_GEN, ARCH_SEC_EDIT, ARCH_SEC_RUN, ARCH_SEC_PRGE FROM PS_ARCH_SECURITY WHERE ARCH_CLASS = 'ALLPAGES';

--SQL to check access of Generate SQL on PeopleTools tab in PL

SELECT ARCH_SEC_GEN FROM PS_ARCH_SECURITY WHERE ARCH_CLASS = 'ALLPAGES';

--SQL to check access of Edit SQL on PeopleTools tab in PL

SELECT ARCH_SEC_EDIT FROM PS_ARCH_SECURITY WHERE ARCH_CLASS = 'ALLPAGES';

--SQL to check access of Run SQL on PeopleTools tab in PL
SELECT ARCH_SEC_RUN FROM PS_ARCH_SECURITY WHERE ARCH_CLASS = 'ALLPAGES';

--SQL to check access of Purge Audit on PeopleTools tab in PL
SELECT ARCH_SEC_PRGE FROM PS_ARCH_SECURITY WHERE ARCH_CLASS = 'ALLPAGES';

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

--SQL to check access of Process Group Permission

SELECT * FROM PSAUTHPRCS WHERE CLASSID = 'ALLPAGES';

SELECT DISTINCT RTRIM(C.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.PRCSGRP)
FROM PSAUTHPRCS A , PSROLECLASS B , PSROLEUSER C
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.CLASSID = 'ALLPAGES' ORDER BY 1,2;

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

--SQL to check access of Process Profile Permission

SELECT * FROM PSPRCSPRFL WHERE CLASSID = 'ALLPAGES';

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

--SQL to check access of Component Interface Permissions

SELECT * FROM PSAUTHBUSCOMP WHERE CLASSID = 'ALLPAGES';

SELECT DISTINCT RTRIM(C.ROLENAME) , RTRIM(A.CLASSID) , RTRIM(A.BCNAME)
FROM PSAUTHBUSCOMP A , PSROLECLASS B , PSROLEUSER C
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND A.CLASSID = 'ALLPAGES'
AND A.BCNAME = '' ORDER BY 1,2;

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

--SQL to check access of Web Libraries

SELECT * FROM PSAUTHITEM WHERE CLASSID = 'ALLPAGES' AND MENUNAME LIKE 'WEBLIB_%';

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

--SQL to check access of Web Services

SELECT DISTINCT A.CLASSID , C.IB_SERVICENAME FROM PSAUTHWS A , PSOPERATION B , PSSERVICEOPR C WHERE A.IB_OPERATIONNAME = B.IB_OPERATIONNAME AND B.IB_OPERATIONNAME = C.IB_OPERATIONNAME

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

--SQL to check access for Personalization Tab

SELECT DISTINCT A.CLASSID , A.OPTN_CATEGORY_LVL , B.OPTN_CATEGORY_GRP FROM PSAUTHOPTN A , PSUSEROPTNDEFN B WHERE A.OPTN_CATEGORY_LVL = B.OPTN_CATEGORY_LVL AND A.USEROPTN = B.USEROPTN

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

--SQL to Check access of Tree and Access Group Permissions through a PL

SELECT * FROM PS_SCRTY_ACC_GRP;

SELECT E.ROLENAME,
D.CLASSID,
A.TREE_NAME ,
B.PARENT_NODE_NAME ,
B.TREE_NODE ,
B.TREE_NODE_TYPE
FROM PSTREEDEFN A ,
PSTREENODE B,
PS_SCRTY_ACC_GRP C ,
PSROLECLASS D ,
PSROLEUSER E
WHERE A.TREE_NAME = B.TREE_NAME
AND A.TREE_NAME    = C.TREE_NAME
AND C.ACCESS_GROUP = B.PARENT_NODE_NAME
AND C.CLASSID      = D.CLASSID
AND D.ROLENAME     = E.ROLENAME
AND E.ROLEUSER     = ''
AND B.PARENT_NODE_NAME = '';

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

--SQL to Check access of Query profile access through a PL

SELECT * FROM PS_SCRTY_QUERY;

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

--SQL to Check access of Mass Change Template access through a PL

SELECT * FROM PS_MC_OPR_SECURITY;

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

--SQL to check access to route control profile to an user

SELECT ROLEUSER , ROLENAME , RTE_CNTL_PROFILE FROM PS_RTE_CNTL_RUSER;

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

To find all the folders which are hidden from Portal Navigation.

SELECT * FROM PSPRSMSYSATTRVL WHERE PORTAL_NAME = 'EMPLOYEE' AND PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
AND PORTAL_REFTYPE = 'F' ;

SELECT A.PORTAL_OBJNAME,B.PORTAL_LABEL,B.PORTAL_PRNTOBJNAME,C.PORTAL_LABEL
FROM PSPRSMSYSATTRVL A,PSPRSMDEFN B,PSPRSMDEFN C WHERE A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
AND B.PORTAL_REFTYPE = 'F' AND A.PORTAL_NAME = B.PORTAL_NAME
AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME
AND B.PORTAL_NAME = C.PORTAL_NAME
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME

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

To find all the content references which are hidden from Portal Navigation.

SELECT * FROM PSPRSMSYSATTRVL WHERE PORTAL_NAME = 'EMPLOYEE' AND PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
AND PORTAL_REFTYPE = 'C';

SELECT A.PORTAL_OBJNAME,
B.PORTAL_LABEL,
B.PORTAL_PRNTOBJNAME,
C.PORTAL_LABEL,
B.PORTAL_URI_SEG1 ,
B.PORTAL_URI_SEG2
FROM PSPRSMSYSATTRVL A,
PSPRSMDEFN B,
PSPRSMDEFN C
WHERE A.PORTAL_NAME      = 'EMPLOYEE'
AND A.PORTAL_ATTR_NAM    = 'PORTAL_HIDE_FROM_NAV'
AND B.PORTAL_REFTYPE     = 'C'
AND A.PORTAL_NAME        = B.PORTAL_NAME
AND A.PORTAL_OBJNAME     = B.PORTAL_OBJNAME
AND B.PORTAL_NAME        = C.PORTAL_NAME
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME
AND B.PORTAL_URI_SEG1 || B.PORTAL_URI_SEG2 = 'STRUCTURE_PROCUREMENT_OPTIONSAP_WB_DSPLY_CFG';

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

To check if Password Control is turned on...

If result of SQL is more than 0 that means "Password Expiration" days are mentioned

SELECT PSWDEXPIRESDAYS FROM PSSECOPTIONS

No. of attempts for wrong password

SELECT LOGINATTEMPTS FROM PSSECOPTIONS

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

To Check the navigation of a component

SELECT DISTINCT S.PNLGRPNAME,S.ITEMLABEL,P.PNLITEMNAME,P.DISPLAYONLY,
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(RTRIM(E.PORTAL_LABEL),' >> '),RTRIM(D.PORTAL_LABEL)),' >> '), RTRIM(C.PORTAL_LABEL)), ' >> '), RTRIM(B.PORTAL_LABEL)), ' >> '),RTRIM(A.PORTAL_LABEL))
FROM PSAUTHITEM P, PSROLECLASS Q, PSPGEACCESSDESC R,PS_ACLCOMPONENT_V2 S, PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME = B.PORTAL_NAME
AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME = C.PORTAL_NAME
AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME = D.PORTAL_NAME
AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
WHERE Q.CLASSID = P.CLASSID
AND P.MENUNAME=S.MENUNAME
AND S.BARITEMNAME=P.BARITEMNAME
AND S.BARNAME=P.BARNAME
AND P.AUTHORIZEDACTIONS = R.AUTHORIZEDACTIONS
AND A.PORTAL_URI_SEG1=P.MENUNAME
AND A.PORTAL_URI_SEG2=S.PNLGRPNAME
AND S.PNLGRPNAME ='BUS_UNIT_OPT_BI'
ORDER BY 1,2,3,4;

SELECT DISTINCT 'Root > ' || RTRIM(REVERSE (SYS_CONNECT_BY_PATH(REVERSE (PORTAL_LABEL), ' > ')), ' > ') PATH
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
AND PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT' START WITH
PORTAL_URI_SEG2 = 'HEB_1099CHK_CMP'
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME;

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