Get CSF Key Inside SOA Composite on BPEL

We know that Oracle WSM (Web Services Manager) agents handles the Credential Store very well and is the best approach to calling secure services through client policies. For example, if we need to call a service that is secured by WS-Security Username Token, we can simply attach the “oracle/wss_username_token_client_policy” to the service reference, and then use the “csf-key” binding property to make a reference to some credential, and the OWSM agents will do the will do all the work.

However, many times we face the scenario where the service being called do not follow the standards or use some kind of home-made authentication, even inside the SOAP Body. Furthermore, there are many use cases where it can be useful to get access to a CSF Credential Key inside the BPEL flow, programmatically, to get username and password in hands to assign it to variables and do whatever you need.

It’s possible to do this trick using the “Credential Store Framework API” (CSF API), though a “Java Embedding” activity. There a few steps needed to accomplish this, which are described below:

1 – Prepare your IDE, adding the library “BC4J Security” in the project’s classpath on JDeveloper:
17_image1

17_image3

17_image2

2 – Create a “Java Embedding” activity to retrieve the username and password from the credential store:


try {  
  oracle.security.jps.JpsContextFactory jpsCtxFactory = oracle.security.jps.JpsContextFactory.getContextFactory();  
  oracle.security.jps.JpsContext jpsCtx = jpsCtxFactory.getContext();  
  oracle.security.jps.service.credstore.CredentialStore credStore = jpsCtx.getServiceInstance(oracle.security.jps.service.credstore.CredentialStore.class);  
  oracle.security.jps.service.credstore.PasswordCredential cred = (oracle.security.jps.service.credstore.PasswordCredential)credStore.getCredential("test-cred-map", "test-cred-key");  
  if (cred == null) {  
    System.out.println("Credential not found.");  
  } else {  
    String username = cred.getName();  
    String password = String.valueOf(cred.getPassword());  
    System.out.println("Credential username: " + username);  
    System.out.println("Credential password: " + password);  
  }  
} catch (Exception e) {  
  e.printStackTrace();  
  addAuditTrailEntry(e);  
}

If you want a cleaner code, declare the imports in the beginning of the BPEL file (just before “partnerLinks” tag) and use the simple class names – be careful about the differences in the import statement between BPEL 1.0 and 2.0: http://docs.oracle.com/cd/E23943_01/dev.1111/e10224/bp_java.htm#SOASE87123

3 – Prepare the SOA environment to accept your deployment, adding the “jps-manifest.jar” file in the BPEL compilation classpath “BpelcClasspath”:
17_image4

17_image5

17_image6

17_image7

Use the full qualified path here. The common path for this library is: $DOMAIN_HOME/oracle_common/modules/oracle.jps_11.1.1/jps-manifest.jar

4 – Create your credential for testing the code:

17_image8

17_image9

17_image10

17_image11

5 – Grant permission for your custom code to access your credential map and keys:

5.1 – Edit “system-jazn” file: $DOMAIN_HOME/config/fmwconfig/system-jazn-data.xml

5.2 – Add a grant permission, appending the following tag to the end of “jazn-data/system-policy/jazn-policy”:

<grant>
  <permissions>
    <permission>
      <class>oracle.security.jps.service.credstore.CredentialAccessPermission</class>
      <name>context=SYSTEM,mapName=test-cred-map,keyName=*</name>
      <actions>read</actions>
    </permission>
  </permissions>
</grant>

P.S.: omitting the “grantee” tag, any deployed code will get access to the credential map.

17_image12

6 – Restart both Admin and Managed Servers so “system-jazn” update can take effect;

7 – Test your code and see the magic:

17_image13

The approach shown in this article can be adapted to be used in other FMW technologies, like inside Service Bus (through a “Java Callout”) or some JavaEE application deployed on the WebLogic server.

Export Audit Trail from Database SOA 10g and 11g

For a while I thought that was impossible to query the SOA composite instance audit trail directly from database. Several references on internet say that only SOA Management APIs can understand the format in which this information is stored into SOA Dehydration Store. Based on this, I posted about a java tool which does the audit export based on these APIs: https://gibaholms.wordpress.com/2013/07/18/soa-11g-audit-trail-exporter-tool/

The “SOA 11g Audit Trail Exporter Tool” still very useful due to simple features like the easy-of-use, remote export, no need database access to export (only a valid EM user) and file output. However, I finally found a way to query the audit trail XML directly from Database. The audit trail extraction is very fast and simple, and can be done by anyone with access to the SOAINFRA tables.

Edit 03-12-2015: New package version 1.1 available, with a bug fix to work properly on SOA 10g (thanks FRIEDRICH OBER-HONGSERMEIER for the contribution).

To perform this extraction directly from database, I wrote a simple PL-SQL function that does the Audit Trail tricks. I encapsulated it into a package called SOA_UTIL which can be compiled into a controlled area with some governance:


CREATE OR REPLACE PACKAGE SOA_UTIL AS
  
   -------------------------------------------------------------------------------------------
   -- Written by   : Gilberto Holms (https://gibaholms.wordpress.com/)
   -- Last update  : 07/11/2014
   -- Version      : 1.1
   -- Description  : Utility code to handle Oracle SOA Suite tables
   -- Release notes:
   -- 	1.0: Initial release
   --	1.1: Bug fix to work properly on SOA 10g
   -------------------------------------------------------------------------------------------
  
  FUNCTION GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_CIKEY NUMBER) RETURN CLOB;
  
END SOA_UTIL;

/

CREATE OR REPLACE PACKAGE BODY SOA_UTIL AS
  
   -------------------------------------------------------------------------------------------
   -- Written by   : Gilberto Holms (https://gibaholms.wordpress.com/)
   -- Last update  : 07/11/2014
   -- Version      : 1.1
   -- Description  : Utility code to handle Oracle SOA Suite tables
   -- Release notes:
   -- 	1.0: Initial release
   --	1.1: Bug fix to work properly on SOA 10g
   -------------------------------------------------------------------------------------------
  
  FUNCTION GET_AUDIT_TRAIL(P_SCHEMA_NAME VARCHAR2, P_CIKEY NUMBER) RETURN CLOB
  AS
    V_AUDIT_BLOB BLOB;
    V_AUDIT_CLOB CLOB;

	V_CUR_AUDIT SYS_REFCURSOR;

	TYPE TP_AUDIT_RECORD IS RECORD(
      BLOCK NUMBER(38,0),
      LOG BLOB
	);
    TYPE TP_AUDIT_ARRAY IS TABLE OF TP_AUDIT_RECORD;
    V_AUDIT_ARRAY TP_AUDIT_ARRAY;
    V_AUDIT_COMPLETE BLOB;

	V_BUFFER_LENGTH PLS_INTEGER := 32767;
    V_BUFFER VARCHAR2(32767);
    V_READ_START PLS_INTEGER := 1;
BEGIN

	DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
	DBMS_LOB.CREATETEMPORARY(V_AUDIT_CLOB, TRUE);
	DBMS_LOB.CREATETEMPORARY(V_AUDIT_COMPLETE, TRUE);
	
	OPEN V_CUR_AUDIT FOR 'SELECT BLOCK, LOG FROM ' || P_SCHEMA_NAME || '.AUDIT_TRAIL WHERE CIKEY = :cikey ORDER BY COUNT_ID' USING P_CIKEY;
	FETCH V_CUR_AUDIT BULK COLLECT INTO V_AUDIT_ARRAY;
	CLOSE V_CUR_AUDIT;

	FOR j IN 1..V_AUDIT_ARRAY.COUNT LOOP
		IF j = 1 THEN
			DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
		ELSE
			IF ( V_AUDIT_ARRAY(j).BLOCK = V_AUDIT_ARRAY(j-1).BLOCK ) THEN
				DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
			ELSE
				DBMS_LOB.APPEND (V_AUDIT_COMPLETE, UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB));
				DBMS_LOB.CREATETEMPORARY(V_AUDIT_BLOB, TRUE);
				DBMS_LOB.APPEND (V_AUDIT_BLOB, V_AUDIT_ARRAY(j).LOG);
			END IF;
		END IF;
	END LOOP;

	DBMS_LOB.APPEND (V_AUDIT_COMPLETE, UTL_COMPRESS.LZ_UNCOMPRESS(V_AUDIT_BLOB));
	V_AUDIT_ARRAY.DELETE;

	FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(V_AUDIT_COMPLETE) / V_BUFFER_LENGTH) LOOP
		V_BUFFER := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(V_AUDIT_COMPLETE, V_BUFFER_LENGTH, V_READ_START));
		DBMS_LOB.WRITEAPPEND(V_AUDIT_CLOB, LENGTH(V_BUFFER), V_BUFFER);
		V_READ_START := V_READ_START + V_BUFFER_LENGTH;
	END LOOP;
	
	RETURN V_AUDIT_CLOB;
	
END;
  
END SOA_UTIL;

/

Then you can use this function inside a main query which filters the desired instances. This function works for both SOA 10g and 11g.

Usage example for Oracle SOA Suite 11g:


SELECT 
  CI.COMPOSITE_NAME AS COMPOSITE_NAME,
  CI.CMPST_ID AS INSTANCE_ID,
  CI.CREATION_DATE AS CREATION_DATE,
  CI.STATUS AS STEP,
  SOA_UTIL.GET_AUDIT_TRAIL('FMW11116_SOAINFRA', CI.CIKEY) AS AUDIT_TRAIL_XML
FROM
  SOAINFRA.CUBE_INSTANCE CI
WHERE 
  CI.COMPOSITE_NAME = 'MyComposite'
  AND CI.CREATION_DATE BETWEEN (sysdate - 1) AND (sysdate)
  ORDER BY CI.CREATION_DATE DESC;

Usage example for Oracle SOA Suite 10g:


SELECT 
  CI.PROCESS_ID AS PROCESS_ID,
  CI.CIKEY AS INSTANCE_ID,
  CI.CREATION_DATE AS CREATION_DATE,
  CI.STATUS AS STEP,
  SOA_UTIL.GET_AUDIT_TRAIL('ORABPEL', CI.CIKEY) AS AUDIT_TRAIL_XML
FROM
  ORABPEL.CUBE_INSTANCE CI
WHERE 
  CI.PROCESS_ID = 'MyBPEL'
  AND CI.CREATION_DATE BETWEEN (sysdate - 1) AND (sysdate)
  ORDER BY CI.CREATION_DATE DESC;

Event Details

Edit 03-12-2015: Added Event Details topic.

The audit trail is a large XML containing an array of events. The event XML usually contains all the message and details, like show below:


<event ...>
	<message>...</message>
	<details><![CDATA[ ... ]]></details>
</event>

However, for some very large XML payload, these event details are stored outside this structure, causing the event XML looks like this:


<event ...>
	<message>...</message>
	<details id="0" />
</event>

As you can see above, there is no payload inside the details tag, but there is a detail “id”.

So, if you are parsing the audit trail and you are interested on the details payload, every time you face this situation for some specific instance, you must make a separate call to a table named “AUDIT_DETAILS”, like shown below:


SELECT UTL_COMPRESS.LZ_UNCOMPRESS(BIN) AS AUDIT_DETAILS_XML 
FROM SOAINFRA.AUDIT_DETAILS 
WHERE CIKEY = '162524' -- put the cikey here 
AND DETAIL_ID = 0; -- put the details id here 

I used this technique in the company I work for, and by using the concepts shown in this article, I was able to do a full extract and parsing from SOA 10g and 11g databases getting all the information I needed to my use case. I hope you find it useful too.

SOA 11g Audit Trail Exporter Tool

This project is a simple audit trail exporter tool, made for Oracle SOA Suite 11g (tested on version 11.1.1.6). It works for both BPEL and BPM based SOA composites.

Edit 03-13-2015: Text reviewed.

The SOA “Dehydration Store” stores all instance info and audit trail on some SOAINFRA tables (like AUDIT_TRAIL, AUDIT_DETAILS, XML_DOCUMENT and so on). If you have read privileges to these tables, you can do the export directly from database, as I show in this article: Export Audit Trail from Database SOA 10g and 11g

However, if you don’t have direct access to database or want a simple and controlled way to get the audit information for a specific set of instances, you can do the export using the tool presented here.

In a production environment, as the number of instances increases, the Enterprise Manager interface stuck many times, and can be very slow to access composite instance audit trail. Furthermore, is impossible to find a single instance based on some request payload info.

To get programmatic access to SOA audit information and do much more cool things, Oracle provides the Oracle SOA Suite Infrastructure Management Java API:

http://docs.oracle.com/cd/E28280_01/admin.1111/e10226/soaadmin_apimanage.htm

This project embeds this API, and provides a command-line interface to export instances audit trail, which can be filtered by a date range or the proper instance id. The result is the full audit trail, in form of human readable XML, for every single auditable component of the instances that matches the search filters. Then you can use system tools like “grep” to find a specific instance based on payload content, and see its full execution audit trail.

You can download the SOA Audit Exporter Tool on the link below:

SOAAuditTrailExporter.zip

Usage example:


Full Export:

  java -jar SOAAuditTrailExporter.jar -host=10.2.2.35 -port=8000 -username=weblogic -password=welcome01 -outBaseFolder=/temp/SOAAuditTrailExporter -compositePartition=default -compositeName=MySOAComposite -compositeRevision=1.0

Export By Date:

  java -jar SOAAuditTrailExporter.jar -host=10.2.2.35 -port=8000 -username=weblogic -password=welcome01 -outBaseFolder=/temp/SOAAuditTrailExporter -compositePartition=default -compositeName=MySOAComposite -compositeRevision=1.0 -minCreationDate=07/01/2013 -maxCreationDate=07/03/2013

Export By Instance ID:

  java -jar SOAAuditTrailExporter.jar -host=10.2.2.35 -port=8000 -username=weblogic -password=welcome01 -outBaseFolder=/temp/SOAAuditTrailExporter -compositePartition=default -compositeName=MySOAComposite -compositeRevision=1.0 -instanceId=20158

Enjoy!