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
PeopleSoft Simple Web Services (SWS)
Do you need a quick way to create web services?

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.

Read More & Purchase
Contents

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%'
;