Author Info

Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris

Looking for pain-free PeopleSoft web services? 😀

PeopleSoft Simple Web Services (SWS)

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Audit Queries for Integration Broker

Below are some queries that can be used to audit your Integration Broker configuration. These are useful for troubleshooting and monitoring.

Node with No Authentication Type

Find Active Nodes that do NOT have any authentication type.

Generally this should NOT return any rows.
If you run this in a DPK/DEMO environment, you will see that Oracle delivers many that violate this.

You may consider adding Node: WADL_NODE, WSDL_NODE to this but typically if you need an external integration partner to pull a WSDL or WADL you would want to secure it.

See the Nodes chapter chapter for more information.

SELECT A.MSGNODENAME, A.DESCR, A.ACTIVE_NODE, A.AUTHOPTN, A.LOCALNODE, A.NODE_TYPE, A.USERID
  FROM PSMSGNODEDEFN A

  WHERE  A.MSGNODENAME NOT IN ('ANONYMOUS')
     -- No Authentication
     AND A.AUTHOPTN = 'N'
     -- Active Nodes Only
     AND A.ACTIVE_NODE = '1'
  ORDER BY 1

Node and Operations with No Security

List NODEs that have no authentication and the web services that can be executed. This is useful for finding nodes that are open to the world. This is a good starting point for securing your system.

This will likely produce some false positives. Why? Because the nodes and routings for the service operation (if NOT REST) may also block the execution of the service operation. For example, if you have a node that is open to the world, but the routing for the service operation is only for a specific node, then the service operation is not open to the world. This query will not take that into account. However, in these cases you should tighten up the security on the node.

This should return a very small list if any at all.

-- Find Web Service Open to the world with no authentication
SELECT
  N.MSGNODENAME,
  N.DESCR,
  N.ACTIVE_NODE,
  N.AUTHOPTN,
  N.LOCALNODE,
  N.NODE_TYPE,
  N.USERID,
  B.ROLENAME,
  C.CLASSID,
  WSA.IB_OPERATIONNAME,
  WSA.AUTHORIZEDACTIONS
FROM
  PSMSGNODEDEFN N,
  PSOPRDEFN A,
  PSROLEUSER B,
  PSROLECLASS C,
  PSAUTHWS WSA
WHERE
-- No Authentication
  N.AUTHOPTN = 'N'
-- Active Nodes Only
  AND N.ACTIVE_NODE = '1'
  -- Join in the Node User to the OPRID Table
  AND N.USERID = A.OPRID
  AND A.OPRID = B.ROLEUSER
  AND B.ROLENAME = C.ROLENAME
  AND C.CLASSID = WSA.CLASSID
  -- Unlocked Users OOnly
  AND A.ACCTLOCK = 0
  ORDER BY N.MSGNODENAME, WSA.IB_OPERATIONNAME
  ;

Service Operations with No Logging

Status: Work in Progress

select O.IB_OPERATIONNAME, O.*, R.*

From PSOPERATION O , PSIBRTNGDEFN R

WHERE O.IB_OPERATIONNAME = O.IB_OPERATIONNAME
AND
-- SYNC
O.RTNGTYPE = 'S'

AND O.IB_OPERATIONNAME NOT LIKE 'CI%'
;

List Users that have access to Web Services

  SELECT OD.OPRID, RU.ROLENAME, RC.CLASSID, WS.IB_OPERATIONNAME FROM PSOPRDEFN OD, 
  PSROLEUSER RU,
  PSROLECLASS RC,
  PSAUTHWS WS
  WHERE
  OD.OPRID = ru.ROLEUSER
  AND RU.ROLENAME = RC.ROLENAME
  AND RC.CLASSID = WS.CLASSID
  AND OD.ACCTLOCK = 0
  -- AND OD.OPRID NOT IN ('PS', 'PSAPPS')
  ;

Cross Reference of Service Operations to Roles and Permission List

Get a listing of what Permission List (CLASSID) and Role are granting service operation access.

SELECT DISTINCT A.IB_OPERATIONNAME, B.ROLENAME, A.CLASSID
  FROM PSAUTHWS A, PSROLECLASS B
  -- change or remove this based on what you are researching.
  WHERE A.IB_OPERATIONNAME LIKE 'PERSON%'
     AND A.CLASSID = B.CLASSID
  ORDER BY 1, 2, 3

No Shared Node Users

There should not be shared node users unless that node user has zero permissions. It is god practice to have a distinct OPRID for each node. This is especially true for any node that accepts inbound traffic.

EXPECT: Zero Rows

  SELECT USERID, COUNT(1) 
  FROM PSMSGNODEDEFN 
  WHERE ACTIVE_NODE = 1
  GROUP BY USERID
  HAVING COUNT(1) > 1;

Finding Application Package to Service Operation Mapping

If you have a root PeopleCode Application Package that you want to find the Service Operation it is associated with, you can use the following query. This is useful for finding the root package of a service operation.

SELECT * fROM PSOPERATIONAC
-- Application Package Root Name
WHERE PACKAGEROOT = 'PERSON_BASIC_FULLSYNC'
;