DB as Authentication Provider For Human Task

There could be a scenario when a proper LDAP is not available to store users/groups for weblogic/SOA human task application etc. And this scenario is very much possible. In such situations weblogic SQL Authenticator comes to the rescue. SQL authenticator is provided to use database as authentication provider.

Listed down here are the steps to configure database as authentication provider using SQL Authenticator. Note that these steps are based on document available at -

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


1. Create tables that will store the user & group details with below scripts -


CREATE TABLE USERS (
U_NAME VARCHAR(200) NOT NULL,
U_PASSWORD VARCHAR(50) NOT NULL,
U_DESCRIPTION VARCHAR(1000))
;
ALTER TABLE USERS
ADD CONSTRAINT PK_USERS
PRIMARY KEY (U_NAME)
;
CREATE TABLE GROUPS (
G_NAME VARCHAR(200) NOT NULL,
G_DESCRIPTION VARCHAR(1000) NULL)
;
ALTER TABLE GROUPS
ADD CONSTRAINT PK_GROUPS
PRIMARY KEY (G_NAME)
;
CREATE TABLE GROUPMEMBERS (
G_NAME VARCHAR(200) NOT NULL,
G_MEMBER VARCHAR(200) NOT NULL)
;
ALTER TABLE GROUPMEMBERS
ADD CONSTRAINT PK_GROUPMEMS
PRIMARY KEY (
G_NAME,
G_MEMBER
)
;
ALTER TABLE GROUPMEMBERS
ADD CONSTRAINT FK1_GROUPMEMBERS
FOREIGN KEY ( G_NAME )
REFERENCES GROUPS (G_NAME)
ON DELETE CASCADE

Populate this tables with some users and groups.

2. Create a datasource of the database having user and group tables.


3. Create SQL Autenticator. For this first goto security realm > myrealm > Providers and click New to create a new authentication provider as shown in the image.

4. Select the newly created provider and set the control flag to SUFFICIENT. Set the control flag of "DefaultAuthenticator" to OPTIONAL if you want to login BPM worklist application with database users.

5. Save changes and click Provider Specific tab. Enter the datasource name created earlier in step 2. Ensure that the following values are selected -

Plaintext Passwords Enabled: select

Data Source Name: <datasource name>

Group Membership Searching:unlimited

Max Group Membership Search Level: 0

Password Style Retained: select

Password Algorithm: SHA-1

Password Style: PLAINTEXT

6. Reorder the authentication provider so that DB provider comes at top.

7. Restart the server & managed servers and verify the db users and groups appear in "Users & Groups" tab.

8. Change the default realm from jazn.com to myrealm. In EM console and goto System Mbean browser Application Defined Mbeans > oracle.as.soainfra.config > Server  > WorkflowIdentityConfig > human-workflow > WorkflowIdentityConfig.ConfigurationType > jazn.com

Invoke the setRealmName operation with value "myrealm".

9. Download the workflow-120-SQLIdentityProvider.zip from Note 1194815.1. the link of which is provided above. Unzip it and copy dbprovider.jar to MW_HOME/user_projects/domains/{soa_domain}/lib

10. Shutdown weblogic server.

11. Navigate to HOME/user_projects/domains/{domain_name}/config/fmwconfig and take backup of jps-config.xml. 

12. Edit jps-config.xml to update following -

a) Add below xml fragment in serviceProviders element.


<serviceProvider type="IDENTITY_STORE" name="custom.provider" class="oracle.security.jps.internal.idstore.generic.GenericIdentityStoreProvider">
<description>Custom IdStore Provider</description>
</serviceProvider>

b) Add below xml fragment in serviceInstances element.


<serviceInstance name="idstore.custom" provider="custom.provider" location="dumb">
<description>Custom Identity Store Service Instance</description>
<property name="idstore.type" value="CUSTOM"/>
<property name="ADF_IM_FACTORY_CLASS" value="org.sample.providers.db.DBIdentityStoreFactory"/>
<property name="DB_SERVER_NAME" value="localhost"/>
<property name="DB_SERVER_PORT" value="1521"/>
<property name="DB_DATABASE_NAME" value="XE"/>
<property name="ST_SECURITY_PRINCIPAL" value="Admin"/>        
<property name="ST_SECURITY_CREDENTIALS" value="welcome1"/>
</serviceInstance>

c) Change <serviceInstanceRef ref="idstore.ldap"/> to <serviceInstanceRef ref="idstore.custom"/>.

13. Restart server and you should be able to login to worklist application with the user configured in the database

Dehydration Store Tables

Some tables of SOA dehydration stores and details are available at below Oracle documentation -

http://docs.oracle.com/cd/E23943_01/core.1111/e10108/bpel.htm#r12c1-t12

Also pasting the contents here for ready reference -

Table Name	Table Description
audit_trail	Stores the audit trail for instances. The audit trail viewed in Oracle BPEL Control is created from an XML document. As an instance is processed, each activity writes events to the audit trail as XML.
audit_details	Stores audit details that can be logged through the API. Activities such as an assign activity log the variables as audit details by default. Audit details are separated from the audit_trail table due to their large size. If the size of a detail is larger than the value specified for this property, it is placed in this table. Otherwise, it is placed in the audit_trail table.
cube_instance	Stores process instance metadata (for example, the instance creation date, current state, title, and process identifier)
cube_scope	Stores the scope data for an instance (for example, all variables declared in the BPEL flow and some internal objects that help route logic throughout the flow).
dlv_message	Stores incoming (invocation) and callback messages upon receipt. This table only stores the metadata for a message (for example, current state, process identifier, and receive date).
dlv_subscription	Stores delivery subscriptions for an instance. Whenever an instance expects a message from a partner (for example, the receive or onMessage activity) a subscription is written out for that specific receive activity.
document_ci_ref	Stores cube instance references to data stored in the xml_document table.
document_dlv_msg_ref	Stores references to dlv_message documents stored in the xml_document table.
wftask	Stores tasks created for an instance. The TaskManager process keeps its current state in this table.
work_item	Stores activities created by an instance. All activities in a BPEL flow have a work_item table. This table includes the metadata for the activity (current state, label, and expiration date (used by wait activities)).
xml_document	Stores all large objects in the system (for example, dlv_message documents). This table stores the data as binary large objects (BLOBs). Separating the document storage from the metadata enables the metadata to change frequently without being impacted by the size of the documents.
Headers_properties	Stores headers and properties information.