Error handling using EDN-reuse code -Soa 11g

In my project we had used EDN event delivery network in SOA 11g for error handling.

We created an Error event and with a error message structure .

Eg: HelloPlatformErrorEvent, so I will be listing down the entries that you will need to implement error handling across the processes .The code is reusable and will take care skeletal common error handling.

The good thing with using EDN in errorhandling is we can have multiple consumers consuming the event and do different processing. Since EDN is fully supported in SOA 11g , the overheads of publish and subscribe /configurations will not be bothering us. We need to create an event and consumers can receive events .So if we can have multiple consumer bpel’s each doing different functionality ,one can write a file,one process writing to DB, once sending notifications. So we have plugged out the error handling/logging mechanism out of the processes. Consumers can be enables/disabled separately based on requirement.

Steps for reusing the event driven error event codes

Here for example the

event  :  HelloPlatformErrorEvent,

edl file : HelloPlatformErrorEvent

Message structure :HelloPlatformErrorEvent.xsd

Since it’s used across composites, the file will uploaded/accessed from MDS.

Composite.xml

·         Add to imports

<import namespace="http://schemas.oracle.com/events/edl/HelloPlatformErrorEvent"

          location="oramds:/apps/HelloPlatform/Events/edl/HelloPlatformErrorEvent.edl"

          importType="edl"/>

·         Add to component (bpel)

    <business-events>

      <publishes xmlns:pub1="http://schemas.oracle.com/events/edl/HelloPlatformErrorEvent"

                 name="pub1:HelloPlatformErrorEventMessage"/>

    </business-events>

. bpel file

·         Add to namespaces

         xmlns:bperrmsg="http://xmlns.oracle.com/HelloPlatform/ErrorMessage/V1"

         xmlns:errorEvent="http://schemas.oracle.com/events/edl/HelloPlatformErrorEvent"

·         Add to imports

<import namespace="http://xmlns.oracle.com/HelloPlatform/ErrorMessage/V1"

          location="oramds:/apps/HelloPlatform/Events/schema/HelloPlatformErrorEvent.xsd"

          importType="http://www.w3.org/2001/XMLSchema"/>

·          Add to Variables

<variable name="PublishHelloPlatformErrorEvent"

              element="bperrmsg:HelloPlatformErrorEventMessage"/>

·         Add inside catchall

The assign values will vary based on your message structure

       

<sequence name="ErrorSequence">

  <assign name="assignErrorEvent">

          <copy>

            <from>ora:getFaultName()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:ErrorDetails/bperrmsg:ErrorCode</to>

          </copy>

          <copy>

            <from>ora:getConversationId()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:InstanceDetails/bperrmsg:ConversationId</to>

          </copy>

          <copy>

            <from>ora:getInstanceId()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:InstanceDetails/bperrmsg:InstanceId</to>

          </copy>

          <copy>

            <from>ora:getCompositeName()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:InstanceDetails/bperrmsg:CompositeName</to>

          </copy>

          <copy>

            <from>ora:getProcessId()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:InstanceDetails/bperrmsg:ProcessName</to>

          </copy>

          <copy>

            <from>ora:getFaultAsString()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:ErrorDetails/bperrmsg:ErrorText</to>

          </copy>

          <copy>

            <from>'Error occured in TaskExecutionFetchPendingTasksProcess'</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:ErrorDetails/bperrmsg:ErrorMessage</to>

          </copy>

          <copy>

            <from>true()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:ErrorHandlerOptions/bperrmsg:PersistMsgOnDB</to>

          </copy>

          <copy>

            <from>true()</from>

            <to>$PublishHelloPlatformErrorEvent/bperrmsg:ErrorHandlerOptions/bperrmsg:SendNotification</to>

          </copy>

        </assign>       

<invoke name="invokePublishHelloPlatformErrorEvent"

                bpelx:eventName="errorEvent:HelloPlatformErrorEventMessage"

                inputVariable="PublishHelloPlatformErrorEvent"

                bpelx:invokeAsDetail="no"/>

        <exit name="ProcessExit"/>

      </sequence>

   

That’s it for today. Thanks to Ram,Ravi for the coming up this model.

Custom SQL authenticator configurations on weblogic -Part1

I had a scenario where we were not able to authenticate users against LDAP and access to LDAP was not provided. So the next option was to get export of the user data and have a custom SQL authentication configured on the weblogic server.

So we had a person table with all user tables. But there were couple of issues when we tried to configure custom sql authentication. In our scenario the user gets automatically authenticated in the J2ee application, so the authentication to Human worklist application was not required, so we had the option of hardcoding a single password so that its easy for j2ee web app to call worklist api to access tasks based the user id alone.

The steps for configuring the custom sql authenticator can be found in reference blogs listed below.

Issue#1

Since the PERSON table didn’t have password column, I tried assigning the query select welcome1 from dual query to retrieve the password. So for all users it will return welcome1 as password. But it didnt work.

<wls:sql-get-users-password>SELECT select ‘welcome1’ from dual </wls:sql-get-users-password>

Error stackTrace

------------------------------

<Jan 15, 2013 9:19:53 AM IST> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

<Jan 15, 2013 9:22:39 AM IST> <Error> <Security> <BEA-000000> <[Security:090759]A SQLException occurred while retrieving password informatio

n

java.sql.SQLException: Invalid column index

        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:6336)

        at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:6306)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:303)

        at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:908)

        at weblogic.security.providers.authentication.shared.DBMSSQLRuntimeQueryImpl.passwordStringQuery(DBMSSQLRuntimeQueryImpl.java:88)

        Truncated. see log file for complete stacktrace

Issue #2

I tried using PERSON and SOA_PASSWORD tables with emailaddress and Password column respectively .Email address from PERSON and password from SOA_PASSWORD table.

This also throws error

Error Stacktrace

--------------------------

java.sql.SQLException: Invalid column index

        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:6336)

        at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:6306)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:303)

        at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:908)

        at weblogic.security.providers.authentication.shared.DBMSSQLRuntimeQueryImpl.passwordStringQuery(DBMSSQLRuntimeQueryImpl.java:88)

        Truncated. see log file for complete stacktrace

This works properly.

Conclusion :-- User and Password queries have to be on same table.

Solution

----------

finally created a VIEW -- SOA_PASSWORD_VIEW with à  SELECT person.EMAILADDRESS, 'welcome1' FROM PERSON person;

CREATE OR REPLACE FORCE VIEW "SOA_PASSWORD_VIEW" ("EMAILADDRESS", "PASSWORD") AS   SELECT person.EMAILADDRESS, 'welcome1' FROM PERSON person;

Sample queries you add in config.xml

Code snippet from config.xml

--------------------------------------------------------------

<wls:sql-get-users-password>SELECT PASSWORD FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS = ?</wls:sql-get-users-password>

        <wls:sql-user-exists>SELECT EMAILADDRESS FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS = ?</wls:sql-user-exists>

        <wls:sql-list-member-groups>SELECT ROLES_NAME FROM PERSON_ROLE WHERE PERSON_EMAILADDRESS = ?</wls:sql-list-member-groups>

        <wls:sql-list-users>SELECT EMAILADDRESS FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS LIKE ? and rownum &lt; 10</wls:sql-list-users>

        <wls:sql-list-groups>SELECT NAME FROM ROLE WHERE NAME LIKE ? and rownum &lt; 10</wls:sql-list-groups>

        <wls:sql-group-exists>SELECT NAME FROM ROLE WHERE NAME = ?</wls:sql-group-exists>

        <wls:sql-is-member>SELECT PERSON_EMAILADDRESS FROM PERSON_ROLE WHERE ROLES_NAME = ? AND PERSON_EMAILADDRESS = ?</wls:sql-is-member>

        <wls:password-style>PLAINTEXT</wls:password-style>

        <wls:sql-create-user>INSERT INTO SOA_PASSWORD_VIEW (EMAILADDRESS,PASSWORD) VALUES ( ? , ? )</wls:sql-create-user>

        <wls:sql-remove-user>DELETE FROM SOA_PASSWORD_VIEW WHERE EMAILADDRESS = ?</wls:sql-remove-user>

        <wls:sql-remove-group-memberships>DELETE FROM PERSON_ROLE WHERE PERSON_EMAILADDRESS = ? OR ROLES_NAME = ?</wls:sql-remove-group-memberships>

        <wls:sql-set-user-password>UPDATE SOA_PASSWORD_VIEW SET PASSWORD = ?</wls:sql-set-user-password>

        <wls:sql-create-group>INSERT INTO ROLE(NAME) VALUES ( ? )</wls:sql-create-group>

        <wls:sql-add-member-to-group>INSERT INTO PERSON_ROLE (ROLES_NAME,PERSON_EMAILADDRESS) VALUES( ?, ?)</wls:sql-add-member-to-group>

        <wls:sql-remove-member-from-group>DELETE FROM PERSON_ROLE WHERE ROLES_NAME = ? AND PERSON_EMAILADDRESS = ?</wls:sql-remove-member-from-group>

        <wls:sql-remove-group>DELETE FROM ROLE WHERE NAME = ?</wls:sql-remove-group>

        <wls:sql-remove-group-member>DELETE FROM PERSON_ROLE WHERE ROLES_NAME = ?</wls:sql-remove-group-member>

        <wls:sql-list-group-members>SELECT PERSON_EMAILADDRESS FROM PERSON_ROLE WHERE ROLES_NAME = ? AND PERSON_EMAILADDRESS LIKE ? and rownum &lt; 10</wls:sql-list-group-members>

      </sec:authentication-provider>

This basically covers the initial DB setup and issues faced while configuring the Custom SQL authenticator. Thanks to Bhupinder,Ram and Venkat for helping with this configuration

Resources:

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1499679.1&h=Y

http://biemond.blogspot.in/2008/12/using-database-tables-as-authentication.html

http://orasoa.blogspot.in/2010/06/sqlauthenticator-and-human-worklist.html

http://middlewaremagic.com/weblogic/?p=5476