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 ChrisIntroducing 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.
Below are some queries that can be used to audit your Integration Broker configuration. These are useful for troubleshooting and monitoring.
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 1List 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
;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%'
;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