Today we will describe PeopleSoft Delivered Common Queries and SQL queries with PeopleTools stables that related to security, component structure and pages, this is very useful to get information or help us to write our own
Main Menu > PeopleTools > Security > Common Queries
Here are more examples:
To find the user profile if employee ID is known, use the query:
SELECT OPRID FROM PSOPRDEFN WHERE EMPLID = '<Employee ID>'
To find all the permission lists used by a menu, or all the roles and user profiles that can access the menu, if the menu name is known, use the query:
SELECT A.ROLENAME, A.CLASSID, B.ROLEUSER, C.MENUNAME FROM PSROLECLASS A, PSROLEUSER B, PSAUTHITEM C WHERE A.ROLENAME=B.ROLENAME AND A.CLASSID=C.CLASSID AND C.MENUNAME = '<Menu Name>'
To find all users who have access to a particular page, and from what permission lists, use the query:
SELECT DISTINCT OPRID, OPRCLASS FROM PSOPRCLS WHERE OPRCLASS IN (SELECT DISTINCT CLASSID FROM PSAUTHITEM WHERE PNLITEMNAME = '<Page Name>,)
To find the roles and permission lists associated with a particular user, use the query:
SELECT RU.ROLEUSER AS OPRID, RU.ROLENAME AS ROLE, RU.CLASSID AS PERMISSION_LIST FROM PSROLEUSE RU INNER JOIN PSROLECLASS RC ON RU.ROLENAME = RC.ROLENAME WHERE RU.ROLEUSER = 'oprid' ORDER BY ROLE, PERMISSION_LIST
Note: Replace the 'oprid' value in the line RU.ROLEUSER = 'oprid' with the Operator ID of the user you are interested in (e.g. PS).
To find the permission lists having access to a given component, use the query:SELECT CLASSID FROM PSAUTHITEM WHERE BARITEMNAME = '<Component Name>'
To find all the roles with access to a given component, use the query:SELECT ROLENAME,CLASSID FROM PSROLECLASS WHERE CLASSID IN (SELECT CLASSID FROM PSAUTHITEM WHERE BARITEMNAME = '<Component Name>')
To find all the permission lists having access to a given web library, use the query:SELECT CLASSID FROM PSAUTHWEBLIBVW WHERE MENUNAME = '<Web library Name> (e.g. 'WEBLIB_CTI')
To find navigation to a page, do the following:
1. Find the PORTAL_OBJNAME in Peopletools > Portal > View Menu Item Details.
2. Run the below query and search for the PORTAL_OBJNAME.
Menu path will give navigation of the desired Page.
SELECT DISTINCT(PORTAL_OBJNAME), PORTAL_URI_SEG2 AS 'COMPONENT' FROM PSPRSMDEFN WHERE PORTAL_URI_SEG2 IN (SELECT PNLGRPNAME FROM PSPNLGROUP WHERE PNLNAME = '<Page Name>')
To find the records containing a specific field if the field name is known, use the query:SELECT DISTINCT RECNAME, FIELDNAME FROM PSRECFIELD WHERE FIELDNAME = '<Field Name>'
To find all the records and fields used by a page, use the query:SELECT RECNAME, FIELDNAME FROM PSPNLFIELD WHERE PNLNAME = '<Page Name>'
To find the pages that reference a record and field if RECNAME and FIELDNAME are known, use the query:SELECT PNLNAME FROM PSPNLFIELD WHERE RECNAME = '<Record Name>' AND FIELDNAME = '<Field Name>'
To find all the fields used in a component, use the query:SELECT CP.PNLGRPNAME, CP.MARKET, CP.DESCR, CPG.PNLNAME, CPG.ITEMLABEL, PG.DESCR,PGF.FIELDTYPE, PGF.LBLTEXT, PGF.RECNAME, PGF.FIELDNAME FROM PSPNLGRPDEFN CP, PSPNLGROUP CPG,PSPNLDEFN PG,PSPNLFIELD PGF WHERE CP.PNLGRPNAME = 'JOB_DATA' AND CP.MARKET = 'GBL' AND CP.PNLGRPNAME = CPG.PNLGRPNAM AND CP.MARKET = CP.MARKET AND PG.PNLNAME = CPG.PNLNAMEAND PGF.PNLNAME = PG.PNLNAME
To find all the records used in a component, use the query:SELECT DISTINCT CP.PNLGRPNAME, CP.MARKET, CP.DESCR, PGF.RECNAME, R.RECDESCR, R.RECTYPE FROM PSPNLGRPDEFN CP,PSPNLGROUP CPG,PSPNLDEFN PG,PSPNLFIELD PGF,PSRECDEFN R WHERE CP.PNLGRPNAME = 'JOB_DATA' AND CP.MARKET = 'GBL' AND CP.PNLGRPNAME = CPG.PNLGRPNAME AND CP.MARKET = CP.MARKET AND PG.PNLNAME = CPG.PNLNAME AND PGF.PNLNAME = PG.PNLNAME AND PGF.RECNAME = R.RECNAME
To find the indexes that have been built on a table, use the query:SELECT * FROM USER_INDEXES WHERE TABLE_NAME = '<Table Name>'
Main Menu > PeopleTools > Security > Common Queries
Here are more examples:
To find the user profile if employee ID is known, use the query:
SELECT OPRID FROM PSOPRDEFN WHERE EMPLID = '<Employee ID>'
To find all the permission lists used by a menu, or all the roles and user profiles that can access the menu, if the menu name is known, use the query:
SELECT A.ROLENAME, A.CLASSID, B.ROLEUSER, C.MENUNAME FROM PSROLECLASS A, PSROLEUSER B, PSAUTHITEM C WHERE A.ROLENAME=B.ROLENAME AND A.CLASSID=C.CLASSID AND C.MENUNAME = '<Menu Name>'
To find all users who have access to a particular page, and from what permission lists, use the query:
SELECT DISTINCT OPRID, OPRCLASS FROM PSOPRCLS WHERE OPRCLASS IN (SELECT DISTINCT CLASSID FROM PSAUTHITEM WHERE PNLITEMNAME = '<Page Name>,)
To find the roles and permission lists associated with a particular user, use the query:
SELECT RU.ROLEUSER AS OPRID, RU.ROLENAME AS ROLE, RU.CLASSID AS PERMISSION_LIST FROM PSROLEUSE RU INNER JOIN PSROLECLASS RC ON RU.ROLENAME = RC.ROLENAME WHERE RU.ROLEUSER = 'oprid' ORDER BY ROLE, PERMISSION_LIST
Note: Replace the 'oprid' value in the line RU.ROLEUSER = 'oprid' with the Operator ID of the user you are interested in (e.g. PS).
To find the permission lists having access to a given component, use the query:SELECT CLASSID FROM PSAUTHITEM WHERE BARITEMNAME = '<Component Name>'
To find all the roles with access to a given component, use the query:SELECT ROLENAME,CLASSID FROM PSROLECLASS WHERE CLASSID IN (SELECT CLASSID FROM PSAUTHITEM WHERE BARITEMNAME = '<Component Name>')
To find all the permission lists having access to a given web library, use the query:SELECT CLASSID FROM PSAUTHWEBLIBVW WHERE MENUNAME = '<Web library Name> (e.g. 'WEBLIB_CTI')
To find navigation to a page, do the following:
1. Find the PORTAL_OBJNAME in Peopletools > Portal > View Menu Item Details.
2. Run the below query and search for the PORTAL_OBJNAME.
Menu path will give navigation of the desired Page.
SELECT DISTINCT(PORTAL_OBJNAME), PORTAL_URI_SEG2 AS 'COMPONENT' FROM PSPRSMDEFN WHERE PORTAL_URI_SEG2 IN (SELECT PNLGRPNAME FROM PSPNLGROUP WHERE PNLNAME = '<Page Name>')
To find the records containing a specific field if the field name is known, use the query:SELECT DISTINCT RECNAME, FIELDNAME FROM PSRECFIELD WHERE FIELDNAME = '<Field Name>'
To find all the records and fields used by a page, use the query:SELECT RECNAME, FIELDNAME FROM PSPNLFIELD WHERE PNLNAME = '<Page Name>'
To find the pages that reference a record and field if RECNAME and FIELDNAME are known, use the query:SELECT PNLNAME FROM PSPNLFIELD WHERE RECNAME = '<Record Name>' AND FIELDNAME = '<Field Name>'
To find all the fields used in a component, use the query:SELECT CP.PNLGRPNAME, CP.MARKET, CP.DESCR, CPG.PNLNAME, CPG.ITEMLABEL, PG.DESCR,PGF.FIELDTYPE, PGF.LBLTEXT, PGF.RECNAME, PGF.FIELDNAME FROM PSPNLGRPDEFN CP, PSPNLGROUP CPG,PSPNLDEFN PG,PSPNLFIELD PGF WHERE CP.PNLGRPNAME = 'JOB_DATA' AND CP.MARKET = 'GBL' AND CP.PNLGRPNAME = CPG.PNLGRPNAM AND CP.MARKET = CP.MARKET AND PG.PNLNAME = CPG.PNLNAMEAND PGF.PNLNAME = PG.PNLNAME
To find all the records used in a component, use the query:SELECT DISTINCT CP.PNLGRPNAME, CP.MARKET, CP.DESCR, PGF.RECNAME, R.RECDESCR, R.RECTYPE FROM PSPNLGRPDEFN CP,PSPNLGROUP CPG,PSPNLDEFN PG,PSPNLFIELD PGF,PSRECDEFN R WHERE CP.PNLGRPNAME = 'JOB_DATA' AND CP.MARKET = 'GBL' AND CP.PNLGRPNAME = CPG.PNLGRPNAME AND CP.MARKET = CP.MARKET AND PG.PNLNAME = CPG.PNLNAME AND PGF.PNLNAME = PG.PNLNAME AND PGF.RECNAME = R.RECNAME
To find the indexes that have been built on a table, use the query:SELECT * FROM USER_INDEXES WHERE TABLE_NAME = '<Table Name>'
