Security Queries

User ID Specific Queries

Query Name	Descr
PT_SEC_USER_APPDES_ACCESS	User ID App. Des. Access
PT_SEC_USER_CREF	User ID CRef Access
PT_SEC_USER_CREF_MENU_CMP_MKT	User CRef  Menu Cmp Mkt Access
PT_SEC_USER_CREF_PORTAL	User ID CRef Portal Access
PT_SEC_USER_CRF_PRT_MN_CMP_MKT	User Crf Prt Mn Cmp Mkt Access
PT_SEC_USER_PAGE	User ID page access
PT_SEC_USER_PERM_LIST	Users and Permission Lists
PT_SEC_USER_PTACCESS	User ID PeopleTools Access
PT_SEC_USER_ROLES	User's Roles
PT_SEC_USER_ROLE_PLIST_PAGE	User ID, Role, P List, Pgs
PT_SEC_USER_SIGNON_TIME	User ID Signon Times
PT_SEC_USER_WS_OP	User ID-Web Service Operation

'User IDs Permission List

(To which Permission Lists does this User ID belong?)'

SELECT DISTINCT A.CLASSID

  FROM PSROLECLASS A, PSROLEUSER B, PSOPRDEFN C

  WHERE ( A.ROLENAME = B.ROLENAME

     AND C.OPRID = B.ROLEUSER

     AND C.OPRID = :1)

'User IDs Roles

(To which Roles does this User ID belong?)'

SELECT A.ROLENAME

  FROM PSROLEUSER A, PSOPRDEFN B

  WHERE ( A.ROLEUSER = B.OPRID

     AND B.OPRID = :1)

'User IDs Page Access

(Which pages can this User ID access?)'

SELECT D.MENUNAME, D.BARNAME, D.BARITEMNAME, F.PNLNAME, E.PAGEACCESSDESCR, D.DISPLAYONLY

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSAUTHITEM D, PSPGEACCESSDESC E, PSPNLGROUP F, PSMENUITEM G

  WHERE ( B.ROLENAME = C.ROLENAME

     AND D.MENUNAME = G.MENUNAME

     AND D.BARNAME = G.BARNAME

     AND D.BARITEMNAME = G.ITEMNAME

     AND F.PNLGRPNAME = G.PNLGRPNAME

     AND A.OPRID = C.ROLEUSER

     AND B.CLASSID = D.CLASSID

     AND F.ITEMNAME = D.PNLITEMNAME

     AND A.OPRID = :1

     AND D.MENUNAME NOT IN ('APPLICATION_DESIGNER','CLIENTPROCESS','DATA_MOVER','IMPORT_MANAGER','OBJECT_SECURITY','QUERY')

     AND D.MENUNAME NOT LIKE 'WEBLIB%'

     AND D.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS

     AND 'PeopleSoft Administrator' NOT IN (SELECT H.ROLENAME

  FROM PSROLEUSER H

  WHERE H.ROLEUSER = A.OPRID))

UNION

SELECT I.MENUNAME, I.BARNAME, I.ITEMNAME, K.PNLNAME, L.PAGEACCESSDESCR, 0

  FROM PSMENUITEM I, PSPNLGRPDEFN J, PSPNLGROUP K, PSPGEACCESSDESC L

  WHERE ( J.PNLGRPNAME = I.PNLGRPNAME

     AND K.PNLGRPNAME = I.PNLGRPNAME

     AND J.ACTIONS = L.AUTHORIZEDACTIONS

     AND 'PeopleSoft Administrator' IN (SELECT M.ROLENAME

  FROM PSROLEUSER M

  WHERE M.ROLEUSER = :1))

  ORDER BY 1, 2, 3, 4

'User IDs Misc. PeopleTools Access

(Can this User ID access Application Designer, Client Process, Data Mover, Import Manager, Definition Security or Query?)'

SELECT D.MENUNAME

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSAUTHITEM D

  WHERE ( B.ROLENAME = C.ROLENAME

     AND A.OPRID = C.ROLEUSER

     AND B.CLASSID = D.CLASSID

     AND A.OPRID = :1

     AND D.MENUNAME IN ('CLIENTPROCESS','APPLICATION_DESIGNER','DATA_MOVER','IMPORT_MANAGER','OBJECT_SECURITY','QUERY')

     AND 'PeopleSoft Administrator' NOT IN (SELECT E.ROLENAME

  FROM PSROLEUSER E

  WHERE E.ROLEUSER = A.OPRID))

UNION

SELECT F.MENUNAME

  FROM PSAUTHITEM F

  WHERE ( F.MENUNAME IN ('QUERY','CLIENTPROCESS','DATA_MOVER','IMPORT_MANAGER','OBJECT_SECURITY','APPLICATION_DESIGNER')

     AND 'PeopleSoft Administrator' IN (SELECT G.ROLENAME

  FROM PSROLEUSER G

  WHERE G.ROLEUSER = :1))

  ORDER BY 1

'User IDs Defintion Type Access

(Which Defintion Types can this User ID access?)'

SELECT F.OBJNAME, E.OBJNAME

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSAUTHITEM D, PS_APPDES_OBJ_PERM E, PS_APP_DES_OBJECTS F

  WHERE ( A.OPRID = :1

     AND A.OPRID = C.ROLEUSER

     AND B.ROLENAME = C.ROLENAME

     AND B.CLASSID = D.CLASSID

     AND D.MENUNAME = 'APPLICATION_DESIGNER'

     AND D.BARNAME <> ' '

     AND D.BARNAME = F.OBJNAME

     AND E.DESCR = F.DESCR

     AND 'PeopleSoft Administrator' NOT IN (SELECT J.ROLENAME

  FROM PSROLEUSER J

  WHERE J.ROLEUSER = A.OPRID)

     AND D.AUTHORIZEDACTIONS = E.ACCESS_CD

     AND D.AUTHORIZEDACTIONS = (SELECT MAX( G.AUTHORIZEDACTIONS)

  FROM PSAUTHITEM G

  WHERE G.CLASSID IN (SELECT I.CLASSID

  FROM PSROLEUSER H, PSROLECLASS I

  WHERE H.ROLENAME = I.ROLENAME

     AND H.ROLEUSER = C.ROLEUSER

     AND G.MENUNAME = 'APPLICATION_DESIGNER'

     AND G.BARNAME = D.BARNAME)))

UNION

SELECT N.OBJNAME, M.OBJNAME

  FROM PS_APPDES_OBJ_PERM M, PS_APP_DES_OBJECTS N

  WHERE ( M.DESCR = N.DESCR

     AND M.ACCESS_CD = '4'

     AND M.APP_DES_CATEGORY IN ('1','2','3')

     AND 'PeopleSoft Administrator' IN (SELECT O.ROLENAME

  FROM PSROLEUSER O

  WHERE O.ROLEUSER = :1))

'User IDs Signon Times

(What are the valid signon times for this User ID?)'

SELECT D.DESCR, D.SIGNON_START_TIME, D.SIGNON_END_TIME

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSSIGNON_VW D

  WHERE ( B.ROLENAME = C.ROLENAME

     AND A.OPRID = C.ROLEUSER

     AND B.CLASSID = D.CLASSID

     AND A.OPRID = :1

     AND 'PeopleSoft Administrator' NOT IN (SELECT E.ROLENAME

  FROM PSROLEUSER E

  WHERE E.ROLEUSER = A.OPRID))

UNION

SELECT F.DESCR, '00:00', '23:59'

  FROM PSSIGNON_VW F

  WHERE ( 'PeopleSoft Administrator' IN (SELECT G.ROLENAME

  FROM PSROLEUSER G

  WHERE G.ROLEUSER = :1))

'User IDs Roles, Permission Lists, and Page Access

(What access to Roles, Permission Lists and Pages has been granted for this User ID?)'

SELECT C.ROLENAME, D.CLASSID, D.MENUNAME, D.BARNAME, D.BARITEMNAME, D.PNLITEMNAME, E.PAGEACCESSDESCR, D.DISPLAYONLY

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSAUTHITEM D, PSPGEACCESSDESC E

  WHERE ( B.ROLENAME = C.ROLENAME

     AND A.OPRID = C.ROLEUSER

     AND B.CLASSID = D.CLASSID

     AND A.OPRID = :1

     AND D.MENUNAME NOT IN ('APPLICATION_DESIGNER','CLIENTPROCESS','DATA_MOVER','IMPORT_MANAGER','OBJECT_SECURITY','QUERY')

     AND D.MENUNAME NOT LIKE 'WEBLIB%'

     AND D.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS)

UNION

SELECT 'PeopleSoft Administrator', 'PSADMIN', F.MENUNAME, F.BARNAME, F.ITEMNAME, H.PNLNAME, I.PAGEACCESSDESCR, 0

  FROM PSMENUITEM F, PSPNLGRPDEFN G, PSPNLGROUP H, PSPGEACCESSDESC I

  WHERE ( G.PNLGRPNAME = F.PNLGRPNAME

     AND H.PNLGRPNAME = F.PNLGRPNAME

     AND G.ACTIONS = I.AUTHORIZEDACTIONS

     AND 'PeopleSoft Administrator' IN (SELECT J.ROLENAME

  FROM PSROLEUSER J

  WHERE J.ROLEUSER = :1))

'User IDs Content Reference Access

(What access to Content References has been granted for this User ID?)'

SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSOPRDEFN C, PSROLEUSER D, PSROLECLASS E

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND B.PORTAL_PERMNAME = E.CLASSID

     AND C.OPRID = D.ROLEUSER

     AND D.ROLENAME = E.ROLENAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.OPRID = :1

     AND A.PORTAL_NAME = :2

     AND 'PeopleSoft Administrator' NOT IN (SELECT F.ROLENAME

  FROM PSROLEUSER F

  WHERE F.ROLEUSER = C.OPRID))

UNION

SELECT G.PORTAL_LABEL, G.PORTAL_OBJNAME

  FROM PSPRSMDEFN G

  WHERE ( G.PORTAL_NAME = :2

     AND G.PORTAL_REFTYPE = 'C'

     AND G.PORTAL_CREF_USGT = 'TARG'

     AND G.PORTAL_URI_SEG1 <> ' '

     AND G.PORTAL_URI_SEG2 <> ' '

     AND G.PORTAL_URI_SEG3 <> ' '

     AND 'PeopleSoft Administrator' IN (SELECT H.ROLENAME

  FROM PSROLEUSER H

  WHERE H.ROLEUSER = :1))

  ORDER BY 1

'User IDs Content Reference (includes Portal) Access

(What access to Content References (includes Portal) has been granted for this User ID?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSOPRDEFN C, PSROLEUSER D, PSROLECLASS E

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND B.PORTAL_PERMNAME = E.CLASSID

     AND C.OPRID = D.ROLEUSER

     AND D.ROLENAME = E.ROLENAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.OPRID = :1

     AND 'PeopleSoft Administrator' NOT IN (SELECT F.ROLENAME

  FROM PSROLEUSER F

  WHERE F.ROLEUSER = C.OPRID))

UNION

SELECT G.PORTAL_NAME, G.PORTAL_LABEL, G.PORTAL_OBJNAME

  FROM PSPRSMDEFN G

  WHERE ( G.PORTAL_REFTYPE = 'C'

     AND G.PORTAL_CREF_USGT = 'TARG'

     AND G.PORTAL_URI_SEG1 <> ' '

     AND G.PORTAL_URI_SEG2 <> ' '

     AND G.PORTAL_URI_SEG3 <> ' '

     AND 'PeopleSoft Administrator' IN (SELECT H.ROLENAME

  FROM PSROLEUSER H

  WHERE H.ROLEUSER = :1))

  ORDER BY 1, 2

'User IDs Content Reference (includes Menu, Component and Market) Access

(What access to Content References (includes Menu, Component and Market) has been granted for this User ID?)'

SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSOPRDEFN C, PSROLEUSER D, PSROLECLASS E

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND B.PORTAL_PERMNAME = E.CLASSID

     AND C.OPRID = D.ROLEUSER

     AND D.ROLENAME = E.ROLENAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND C.OPRID = :1

     AND A.PORTAL_NAME = :2

     AND 'PeopleSoft Administrator' NOT IN (SELECT F.ROLENAME

  FROM PSROLEUSER F

  WHERE F.ROLEUSER = C.OPRID))

UNION

SELECT G.PORTAL_LABEL, G.PORTAL_OBJNAME, G.PORTAL_URI_SEG1, G.PORTAL_URI_SEG2, G.PORTAL_URI_SEG3

  FROM PSPRSMDEFN G

  WHERE ( G.PORTAL_REFTYPE = 'C'

     AND G.PORTAL_CREF_USGT = 'TARG'

     AND G.PORTAL_URI_SEG1 <> ' '

     AND G.PORTAL_URI_SEG2 <> ' '

     AND G.PORTAL_URI_SEG3 <> ' '

     AND G.PORTAL_NAME = :2

     AND 'PeopleSoft Administrator' IN (SELECT H.ROLENAME

  FROM PSROLEUSER H

  WHERE H.ROLEUSER = :1))

  ORDER BY 1

'User IDs Content Reference (includes Portal, Menu, Component and Market) Access

(What access to Content References (includes Portal, Menu, Component and Market) has been granted for this User ID?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSOPRDEFN C, PSROLEUSER D, PSROLECLASS E

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND B.PORTAL_PERMNAME = E.CLASSID

     AND C.OPRID = D.ROLEUSER

     AND D.ROLENAME = E.ROLENAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND C.OPRID = :1

     AND 'PeopleSoft Administrator' NOT IN (SELECT F.ROLENAME

  FROM PSROLEUSER F

  WHERE F.ROLEUSER = C.OPRID))

UNION

SELECT G.PORTAL_NAME, G.PORTAL_LABEL, G.PORTAL_OBJNAME, G.PORTAL_URI_SEG1, G.PORTAL_URI_SEG2, G.PORTAL_URI_SEG3

  FROM PSPRSMDEFN G

  WHERE ( G.PORTAL_REFTYPE = 'C'

     AND G.PORTAL_CREF_USGT = 'TARG'

     AND G.PORTAL_URI_SEG1 <> ' '

     AND G.PORTAL_URI_SEG2 <> ' '

     AND G.PORTAL_URI_SEG3 <> ' '

     AND 'PeopleSoft Administrator' IN (SELECT H.ROLENAME

  FROM PSROLEUSER H

  WHERE H.ROLEUSER = :1))

  ORDER BY 1, 2

'User IDs Web Service Operation Access

(What access to Web Service Operations has been granted for this User ID?)'

SELECT D.IB_SERVICENAME, D.IB_OPERATIONNAME

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSAUTHWS_VW2 D

  WHERE ( B.ROLENAME = C.ROLENAME

     AND A.OPRID = C.ROLEUSER

     AND B.CLASSID = D.CLASSID

     AND D.AUTHORIZEDACTIONS = 4

     AND A.OPRID = :1

     AND 'PeopleSoft Administrator' NOT IN (SELECT E.ROLENAME

  FROM PSROLEUSER E

  WHERE E.ROLEUSER = A.OPRID))

UNION

SELECT F.IB_SERVICENAME, F.IB_OPERATIONNAME

  FROM PSAUTHWS_VW2 F

  WHERE ( 'PeopleSoft Administrator' IN (SELECT G.ROLENAME

  FROM PSROLEUSER G

  WHERE G.ROLEUSER = :1))

Roles

Query Name	Descr
PT_SEC_ROLE_ALIASES	Role Aliases
PT_SEC_ROLE_CLASS	Classes assigned to role.
PT_SEC_ROLE_CREF	Role Content Reference Access
PT_SEC_ROLE_CREF_MENU_CMP_MKT	Role CRef Menu Cmp Mkt Access
PT_SEC_ROLE_CREF_PORTAL	Role CRef Portal Access
PT_SEC_ROLE_CRF_PRT_MN_CMP_MKT	Role Crf Prt Mn Cmp Mkt Access
PT_SEC_ROLE_PAGES	Pages that a Role has Access
PT_SEC_ROLE_SIGNONTIME	Role Signon Times
PT_SEC_ROLE_USERS	Users assigned to Role
PT_SEC_ROLE_WS_OP	Role - Web Service Operations

'Roles User IDs

(Which User IDs are assigned to this Role - including both static and dynamic?)'

SELECT A.ROLEUSER

  FROM PSROLEUSER A

  WHERE ( A.ROLENAME = :1)

'Roles Permission Lists

(To which Permission Lists does this Role belong?)'

SELECT A.CLASSID

  FROM PSROLECLASS A

  WHERE ( A.ROLENAME = :1)

'Roles Page Access

(Which pages can this Role access?)'

SELECT DISTINCT B.MENUNAME, B.BARNAME, B.BARITEMNAME, D.PNLNAME, C.PAGEACCESSDESCR, B.DISPLAYONLY

  FROM PSROLECLASS A, PSAUTHITEM B, PSPGEACCESSDESC C, PSPNLGROUP D, PSMENUITEM E

  WHERE ( A.CLASSID = B.CLASSID

     AND D.PNLGRPNAME = E.PNLGRPNAME

     AND B.MENUNAME = E.MENUNAME

     AND B.BARNAME = E.BARNAME

     AND B.BARITEMNAME = E.ITEMNAME

     AND D.ITEMNAME = B.PNLITEMNAME

     AND A.ROLENAME = :1

     AND B.MENUNAME NOT IN ('APPLICATION_DESIGNER','CLIENTPROCESS','DATA_MOVER','IMPORT_MANAGER','OBJECT_SECURITY','QUERY','PERFMONPPMI')

     AND B.MENUNAME NOT LIKE 'WEBLIB%'

     AND B.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS)

  ORDER BY 2

'Roles Content Reference Access

(Which access to Content References has been granted for this Role?)'

SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.ROLENAME = :1

     AND A.PORTAL_NAME = :2)

  ORDER BY 1

'Roles Content Reference (includes Portal) Access

(Which access to Content References (includes Portal) has been granted for this Role?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.ROLENAME = :1)

  ORDER BY 1, 2

'Roles Content Reference (includes Menu, Component and Market) Access

(Which access to Content References (includes Menu, Component and Market) has been granted for this Role?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.ROLENAME = :1)

  ORDER BY 1, 2

'Roles Content Reference (includes Portal, Menu, Component and Market) Access

(Which access to Content References (includes Portal, Menu, Component and Market) has been granted for this Role?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.ROLENAME = :1)

  ORDER BY 1, 2

                                                                           

'Roles Web Service Operation Access

(Which access to Web Service Operations has been granted for this Role?)'

SELECT DISTINCT B.IB_SERVICENAME, B.IB_OPERATIONNAME

  FROM PSROLECLASS A, PSAUTHWS_VW2 B

  WHERE ( A.CLASSID = B.CLASSID

     AND A.ROLENAME = :1)

Permission Lists

Query Name	Descr
PT_SEC_PLIST_ALIASES	Permission List Aliases
PT_SEC_PLIST_APPDES_ACCESS	Perm List's App Designer Acces
PT_SEC_PLIST_CREF	Permission List CRef Access
PT_SEC_PLIST_CREF_MENU_CMP_MKT	PList CRef Menu Cmp Mkt Access
PT_SEC_PLIST_CREF_PORTAL	PList CRef Portal Access
PT_SEC_PLIST_CR_PRT_MN_CMP_MKT	PList CR Prt Mn Cmp Mkt Access
PT_SEC_PLIST_PAGES	Pages the Perm List can Access
PT_SEC_PLIST_PTACCESS	Perm List's PeopleTools Access
PT_SEC_PLIST_ROLES	Roles assigned to Perm List
PT_SEC_PLIST_SIGNON_TIME	Perm List Signon Times
PT_SEC_PLIST_USERS	Users assigned to Perm List
PT_SEC_PLIST_WS_OP	Web Service in Permission List

'Permission Lists User IDs

(Which User IDs are assigned to this Permission List?)'

SELECT C.ROLEUSER

  FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C

  WHERE ( A.CLASSID = B.CLASSID

     AND B.ROLENAME = C.ROLENAME

     AND A.CLASSID = :1)

'Permission Lists Roles

(Which Roles are assigned to this Permission List?)'

SELECT B.ROLENAME

  FROM PSCLASSDEFN A, PSROLECLASS B

  WHERE ( A.CLASSID = B.CLASSID

     AND A.CLASSID = :1)

'Permission Lists Page Access

(Which pages can this Permission List access?)'

SELECT B.MENUNAME, B.BARNAME, B.BARITEMNAME, B.PNLITEMNAME, C.PAGEACCESSDESCR, B.DISPLAYONLY

  FROM PSCLASSDEFN A, PSAUTHITEM B, PSPGEACCESSDESC C

  WHERE ( A.CLASSID = B.CLASSID

     AND A.CLASSID = :1

     AND B.BARITEMNAME > ' '

     AND B.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS)

'Permission Lists Signon Times

(What are the valid signon times for this Permission List?)'

SELECT DISTINCT A.DESCR, A.SIGNON_START_TIME, A.SIGNON_END_TIME

  FROM PSSIGNON_VW A

  WHERE ( A.CLASSID = :1)

'Permission Lists Definition Type Access

(Which Definition Types can this Permission List access?)'

SELECT DISTINCT D.DESCR, C.OBJNAME

  FROM PSCLASSDEFN A, PSAUTHITEM B, PS_APPDES_OBJ_PERM C, PS_APP_DES_OBJECTS D

  WHERE ( A.CLASSID = B.CLASSID

     AND B.MENUNAME = 'APPLICATION_DESIGNER'

     AND A.CLASSID = :1

     AND B.BARNAME <> ' '

     AND C.DESCR = D.DESCR

     AND D.OBJNAME = B.BARNAME

     AND B.AUTHORIZEDACTIONS = C.ACCESS_CD)

  ORDER BY 1, 2

'Permission Lists Misc. PeopleTool Access

(Can this Permission List access Application Designer, Client Process, Data Mover, Import Manager, Object Security or Query?)'

SELECT DISTINCT B.MENUNAME

  FROM PSCLASSDEFN A, PSAUTHITEM B

  WHERE ( A.CLASSID = B.CLASSID

     AND ( B.MENUNAME = 'CLIENTPROCESS'

     OR B.MENUNAME = 'DATA_MOVER'

     OR B.MENUNAME = 'IMPORT_MANAGER'

     OR B.MENUNAME = 'APPLICATION_DESIGNER'

     OR B.MENUNAME = 'OBJECT_SECURITY'

     OR B.MENUNAME = 'QUERY')

     AND A.CLASSID = :1)

'Permission Lists Content Reference Access

(Which Content References can this Permission List access?)'

SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.CLASSID = :1

     AND A.PORTAL_NAME = :2)

  ORDER BY 1

'Permission Lists Content Reference (includes Portal) Access

(Which Content References (includes Portal) can this Permission List access?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.CLASSID = :1)

  ORDER BY 1, 2

'Permission Lists Content Reference (includes Menu, Component and Market) Access

(Which Content References (includes Menu, Component and Market) can this Permission List access?)'

SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.CLASSID = :1

     AND A.PORTAL_NAME = :2)

  ORDER BY 1

'Permission Lists Content Reference (includes Portal, Menu, Component and Market) Access

(Which Content References (includes Portal, Menu, Component and Market) can this Permission List access?)'

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3

  FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C

  WHERE ( A.PORTAL_REFTYPE = 'C'

     AND A.PORTAL_CREF_USGT = 'TARG'

     AND A.PORTAL_NAME = B.PORTAL_NAME

     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE

     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME

     AND C.CLASSID = B.PORTAL_PERMNAME

     AND A.PORTAL_URI_SEG1 <> ' '

     AND A.PORTAL_URI_SEG2 <> ' '

     AND A.PORTAL_URI_SEG3 <> ' '

     AND C.CLASSID = :1)

  ORDER BY 1, 2

'Permission Lists Web Service Operation Access

(Which Web Service Operations can this Permission List access?)'

SELECT A.IB_SERVICENAME, A.IB_OPERATIONNAME

  FROM PSAUTHWS_VW2 A

  WHERE ( A.CLASSID = :1

     AND A.AUTHORIZEDACTIONS = 4)

More Queries

Query Name	Descr
PT_SEC_ACCESSLOG_DAY	Access activity by day
PT_SEC_ACCESSLOG_USER	User ID's Access Activity
PT_SEC_APPDES_OBJECTS	PLists with the App Des object
PT_SEC_APPDES_OBJECTS_USERS	Users with the App Des object
PT_SEC_CURUSER_LIST	List of Active Users
PT_SEC_MENU_PLIST	Perm Lists with this Menu
PT_SEC_MENU_USERS	User IDs with access to menu
PT_SEC_PAGES_PLIST	Perm Lists with access to page
PT_SEC_PTLS_PLIST	Perm Lists with the PT Tool
PT_SEC_PTLS_USERS	Users with the PT Tool
PT_SEC_REN_PLIST	REN Security by Perm List
PT_SEC_REN_USER	User REN Access

--PT_SEC_MENU_PLIST    Perm Lists with this Menu

SELECT DISTINCT B.CLASSID

  FROM PSMENUDEFN A, PSAUTHITEM B

  WHERE ( A.MENUNAME = B.MENUNAME

     AND A.MENUNAME = :1)

--PT_SEC_MENU_USERS    User IDs with access to menu

SELECT E.OPRID

  FROM PSMENUDEFN A, PSAUTHITEM B, PSROLECLASS C, PSROLEUSER D, PSOPRDEFN E

  WHERE ( A.MENUNAME = B.MENUNAME

     AND A.MENUNAME = :1

     AND B.CLASSID = C.CLASSID

     AND D.ROLEUSER = E.OPRID

     AND C.ROLENAME = D.ROLENAME

     AND 'PeopleSoft Administrator' NOT IN (SELECT F.ROLENAME

  FROM PSROLEUSER F

  WHERE F.ROLEUSER = E.OPRID))

UNION

SELECT G.ROLEUSER

  FROM PSROLEUSER G

  WHERE ( G.ROLENAME = 'PeopleSoft Administrator')

  ORDER BY 1

--PT_SEC_PAGES_PLIST    Perm Lists with access to page

SELECT DISTINCT A.CLASSID

  FROM PSAUTHITEM A, PSPNLDEFN B

  WHERE ( B.PNLNAME = :2

     AND A.PNLITEMNAME <> ''' '''

     AND A.PNLITEMNAME = B.PNLNAME)

--PT_SEC_APPDES_OBJECTS    PLists with the App Des object

SELECT DISTINCT A.CLASSID

  FROM PSAUTHITEM A

  WHERE ( A.BARNAME = :1)

--PT_SEC_APPDES_OBJECTS_USERS    Users with the App Des object

SELECT D.OPRID

  FROM PSAUTHITEM A, PSROLECLASS B, PSROLEUSER C, PSOPRDEFN D

  WHERE ( A.BARNAME = :1

     AND A.CLASSID = B.CLASSID

     AND B.ROLENAME = C.ROLENAME

     AND D.OPRID = C.ROLEUSER

     AND 'PeopleSoft Administrator' NOT IN (SELECT E.ROLENAME

  FROM PSROLEUSER E

  WHERE E.ROLEUSER = D.OPRID))

UNION

SELECT F.ROLEUSER

  FROM PSROLEUSER F

  WHERE ( F.ROLENAME = 'PeopleSoft Administrator')

  ORDER BY 1

--PT_SEC_PTLS_PLIST        Perm Lists with the PT Tool

SELECT DISTINCT A.CLASSID

  FROM PSAUTHITEM A, PSROLECLASS B

  WHERE ( A.CLASSID = B.CLASSID

     AND A.MENUNAME = :2)

--PT_SEC_PTLS_USERS    Users with the PT Tool

SELECT D.OPRID

  FROM PSAUTHITEM A, PSROLECLASS B, PSROLEUSER C, PSOPRDEFN D

  WHERE ( A.CLASSID = B.CLASSID

     AND A.MENUNAME = :2

     AND B.ROLENAME = C.ROLENAME

     AND D.OPRID = C.ROLEUSER

     AND 'PeopleSoft Administrator' NOT IN (SELECT E.ROLENAME

  FROM PSROLEUSER E

  WHERE E.ROLEUSER = D.OPRID))

UNION

SELECT F.ROLEUSER

  FROM PSROLEUSER F

  WHERE ( F.ROLENAME = 'PeopleSoft Administrator')

  ORDER BY 1

--PT_SEC_ACCESSLOG_DAY    Access activity by day

SELECT A.OPRID, A.LOGIPADDRESS, TO_CHAR(CAST((A.LOGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(CAST((A.LOGOUTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')

  FROM PSACCESSLOG A

  WHERE ( TO_CHAR(((A.LOGINDTTM ) + ( 0)),'YYYY-MM-DD') >= TO_CHAR(TO_DATE(:1,'YYYY-MM-DD'),'YYYY-MM-DD')

     AND TO_CHAR(((A.LOGINDTTM ) + ( -1)),'YYYY-MM-DD') < TO_CHAR(TO_DATE(:2,'YYYY-MM-DD'),'YYYY-MM-DD'))

--PT_SEC_ACCESSLOG_USER    User ID's Access Activity

SELECT A.LOGIPADDRESS, TO_CHAR(CAST((A.LOGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), TO_CHAR(CAST((A.LOGOUTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')

  FROM PSACCESSLOG A

  WHERE ( A.OPRID = :1)

--PT_SEC_CURUSER_LIST    List of Active Users

SELECT A.OPRID, A.LOGIPADDRESS, TO_CHAR(CAST((A.LOGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), ROUND((CAST(( CAST(SYSTIMESTAMP AS TIMESTAMP)) AS DATE) - CAST((TO_TIMESTAMP(TO_CHAR(CAST((A.LOGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD-HH24.MI.SS.FF')) AS DATE)) * 1440, 0)

  FROM PSACCESSLOG A

  WHERE ( A.LOGINDTTM = A.LOGOUTDTTM

     AND A.PT_SIGNON_TYPE = '1')

  GROUP BY  A.OPRID,  A.LOGIPADDRESS,  TO_CHAR(CAST((A.LOGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')

  ORDER BY 3 DESC

--PT_SEC_REN_PLIST    REN Security by Perm List

SELECT B.BARNAME

  FROM PSCLASSDEFN A, PSAUTHITEM B

  WHERE ( A.CLASSID = B.CLASSID

     AND A.CLASSID = :1

     AND B.MENUNAME = 'REN')

--PT_SEC_REN_USER    User REN Access

SELECT D.BARNAME

  FROM PSOPRDEFN A, PSROLECLASS B, PSROLEUSER C, PSAUTHITEM D

  WHERE ( B.ROLENAME = C.ROLENAME

     AND A.OPRID = C.ROLEUSER

     AND B.CLASSID = D.CLASSID

     AND A.OPRID = :1

     AND D.MENUNAME = 'REN'

     AND 'PeopleSoft Administrator' NOT IN (SELECT E.ROLENAME

  FROM PSROLEUSER E

  WHERE E.ROLEUSER = A.OPRID))

UNION

SELECT H.OBJNAME

  FROM PS_APPDES_OBJ_PERM F, PS_APP_DES_OBJECTS H

  WHERE ( F.DESCR = H.DESCR

     AND F.APP_DES_CATEGORY = H.APP_DES_CATEGORY

     AND F.APP_DES_CATEGORY = '4'

     AND F.ACCESS_CD = '4'

     AND 'PeopleSoft Administrator' IN (SELECT G.ROLENAME

  FROM PSROLEUSER G

  WHERE G.ROLEUSER = :1))

PeopleSoft Component Access

The following SQL returns a list of PeopleSoft users with access to a specific Component (as defined in Application Designer), along with the maximum level of access they possess.

Input Parameters:

PNLGRPNAME: Name of Component being researched. To determine the name of a Component while logged into PeopleSoft, use the -J command

SELECT E.PNLGRPNAME "Component Name",

 A.OPRID "OprId",

 A.OPRDEFNDESC "Name",

 F.DEPTNAME "HR Department",

 F.WORK_PHONE "Work Phone",

 DECODE(MAX(D.DISPLAYONLY),0,'N',

 'Y') "Display Only",

 CASE

 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),1) > 0 THEN 'Y'

 ELSE 'N'

 END "Add",

 CASE

 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),2) > 0 THEN 'Y'

 ELSE 'N'

 END "Update",

 CASE

 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),4) > 0 THEN 'Y'

 ELSE 'N'

 END "Update All",

 CASE

 WHEN BITAND(MAX(D.AUTHORIZEDACTIONS),8) > 0 THEN 'Y'

 ELSE 'N'

 END "Correction",

 CASE

 WHEN MAX(D.AUTHORIZEDACTIONS) > 15 THEN 'Y'

 ELSE 'N'

 END "Special"

 FROM PSOPRDEFN A,

 PSROLEUSER B,

 PSROLECLASS C,

 PSAUTHITEM D,

 PSMENUITEM E,

 PS_EMPLOYEES F

 WHERE A.OPRID = B.ROLEUSER

 AND B.ROLENAME = C.ROLENAME

 AND C.CLASSID = D.CLASSID

 AND D.MENUNAME = E.MENUNAME

 AND D.BARNAME = E.BARNAME

 AND D.BARITEMNAME = E.ITEMNAME

 AND A.EMPLID = F.EMPLID

 AND E.PNLGRPNAME = :PNLGRPNAME

 AND F.EMPL_STATUS = 'A'

 GROUP BY E.PNLGRPNAME,A.OPRID,A.OPRDEFNDESC,F.DEPTNAME,

 F.CIS_FIN_DEPT_DESCR,F.WORK_PHONE

 ORDER BY A.OPRID;

Component Interface Access

The following SQL statement identifies who has full access to Component Interfaces, and the Permission Lists/Roles to which they are tied:

SELECT A.ROLEUSER,

 A.ROLENAME,

 B.CLASSID,

 C.CLASSDEFNDESC,

 D.BCNAME

 FROM SYSADM.PSROLEUSER A,

 SYSADM.PSROLECLASS B,

 SYSADM.PSCLASSDEFN C,

 SYSADM.PSAUTHBUSCOMP D

 WHERE B.ROLENAME = A.ROLENAME

 AND C.CLASSID = B.CLASSID

 AND D.CLASSID = C.CLASSID

 AND D.BCMETHOD = 'Create'

 AND D.AUTHORIZEDACTIONS = 4;

 

 

 

PT_SEC_PLIST_USERS --  Users assigned to Perm List Row User ID	PT_SEC_PLIST_ROLES --  Roles assigned to Perm List Row Role Name
SELECT C.ROLEUSER   FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C   WHERE ( A.CLASSID = B.CLASSID      AND B.ROLENAME = C.ROLENAME      AND A.CLASSID = :1)	SELECT B.ROLENAME   FROM PSCLASSDEFN A, PSROLECLASS B   WHERE ( A.CLASSID = B.CLASSID      AND A.CLASSID = :1)

 

 

SELECT C.ROLEUSER,B.ROLENAME,A.CLASSID

  FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C

  WHERE ( A.CLASSID = B.CLASSID

     AND B.ROLENAME = C.ROLENAME

     AND A.CLASSID = :1)

 

 

PT_SEC_ROLE_CRF_PRT_MN_CMP_MKT --  Role Crf Prt Mn Cmp Mkt Access Row Portal Name Content Reference Label Content Reference Name Menu Component Market	PT_SEC_PLIST_CR_PRT_MN_CMP_MKT --  PList CR Prt Mn Cmp Mkt Access Row Portal Name Content Reference Label Content Reference Name Menu Component Market
SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3   FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.ROLENAME = :1)   ORDER BY 1, 2	SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3   FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.CLASSID = :1)   ORDER BY 1, 2

 

SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2 Component, A.PORTAL_URI_SEG3
  FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C
  WHERE ( A.PORTAL_REFTYPE = 'C'
     AND A.PORTAL_CREF_USGT = 'TARG'
     AND A.PORTAL_NAME = B.PORTAL_NAME
     AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE
     AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME
     AND C.CLASSID = B.PORTAL_PERMNAME
     AND A.PORTAL_URI_SEG1 <> ' '
     AND A.PORTAL_URI_SEG2 <> ' '            -- Component
     AND A.PORTAL_URI_SEG3 <> ' '
     AND C.ROLENAME = :1)
  ORDER BY 1, 2

 

PT_SEC_ROLE_CREF_MENU_CMP_MKT --  Role CRef Menu Cmp Mkt Access Row Content Reference Label Content Reference Name Menu Component Market	PT_SEC_PLIST_CREF_MENU_CMP_MKT --  PList CRef Menu Cmp Mkt Access Row Content Reference Label Content Reference Name Menu Component Market
SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3   FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.ROLENAME = :1      AND A.PORTAL_NAME = :2)   ORDER BY 1	SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME, A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3   FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.CLASSID = :1      AND A.PORTAL_NAME = :2)   ORDER BY 1

 

 

 

PT_SEC_ROLE_CREF --  Role Content Reference Access Row Content Reference Label Content Reference Name SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME   FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.ROLENAME = :1      AND A.PORTAL_NAME = :2)   ORDER BY 1

PT_SEC_PLIST_CREF --  Permission List CRef Access Row Content Reference Label Content Reference Name SELECT A.PORTAL_LABEL, A.PORTAL_OBJNAME   FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.CLASSID = :1      AND A.PORTAL_NAME = :2)   ORDER BY 1

 

 

 

PT_SEC_ROLE_CREF_PORTAL --  Role CRef Portal Access Row Portal Name Content Reference Label Content Reference Name	PT_SEC_PLIST_CREF_PORTAL --  PList CRef Portal Access Row Portal Name Content Reference Label Content Reference Name
SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME   FROM PSPRSMDEFN A, PSPRSMPERM B, PSROLECLASS C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.ROLENAME = :1)   ORDER BY 1, 2	SELECT A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME   FROM PSPRSMDEFN A, PSPRSMPERM B, PSCLASSDEFN C   WHERE ( A.PORTAL_REFTYPE = 'C'      AND A.PORTAL_CREF_USGT = 'TARG'      AND A.PORTAL_NAME = B.PORTAL_NAME      AND A.PORTAL_REFTYPE = B.PORTAL_REFTYPE      AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME      AND C.CLASSID = B.PORTAL_PERMNAME      AND A.PORTAL_URI_SEG1 <> ' '      AND A.PORTAL_URI_SEG2 <> ' '      AND A.PORTAL_URI_SEG3 <> ' '      AND C.CLASSID = :1)   ORDER BY 1, 2

 

 

 

 

 

 

 

 

 

 

 

 

 

PT_SEC_PLIST_PAGES				--  Pages the Perm List can Access
Row	Menu Name	Bar Name	Bar Item		Panel Item	Authorized Actions	Display

 

 

PT_SEC_PLIST_SIGNON_TIME

--  Perm List Signon Times

Row       Descr    Start Time          End Time

 

PT_SEC_PLIST_APPDES_ACCESS		--  Perm List's App Designer Acces
Row	Definition Type		Access Perm.

 

 

PT_SEC_PLIST_PTACCESS	--  Perm List's PeopleTools Access
Row	Menu Name

 

 

PT_SEC_PLIST_WS_OP		--  Web Service in Permission List
Row	Service		Operation