Chapter 25 Oracle ERP

The Oracle ERP resource adapter is defined in the com.waveset.adapter.OracleERPResourceAdapter class. This adapter supports Oracle E-Business Suite (EBS).

Identity Manager also provides an Oracle resource adapter that supports Oracle databases. For detailed information about this adapter, see Chapter 24, Oracle.

Adapter Details

Resource Configuration Notes

None

Identity Manager Installation Notes

The Oracle ERP resource adapter is a custom adapter. You must perform the following steps to complete the installation process:

Installing the Oracle ERP Resource Adapter

1. To add an Oracle resource to the Identity Manager resources list, you must add the following value in the Custom Resources section of the Configure Managed Resources page.

   com.waveset.adapter.OracleERPResourceAdapter

2. If you are connecting to Oracle Real Application Clusters (RAC) using a thin driver, specify a value in the following format in the Connection URL on the Resource parameters page:

   jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=host01)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host02)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host03)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=PROD)))

3. If you are using the JDBC thin driver in an environment that does not use Oracle Real Application Clusters, copy the JAR file that contains the JDBC thin driver classes to the $WSHOME$/WEB-INF/lib directory. The JAR file must be compatible with the JDK version of your application server.

4. If you are using a different driver, specify the driver and connection URL on the Resource Parameters page.

   The Oracle ERP adapter supports Oracle E-Business Suite (EBS) version 11.5.9 without further modification; however, the following additional changes are required to support EBS version 11.5.10 and 12:

5. Delete the responsibilities account attribute from the schema map and add the directResponsibilities and indirectResponsibilities attributes.

6. Add the following properties to the FormRef attribute to any Oracle ERP user form :

   • RESOURCE_NAME. Specifies the ERP resource name

     • VERSION. Specifies the version of the ERP resource. Allowed values are 11.5.9, 11.5.10, 12.

     • RESP_DESCR_COL_EXISTS. Defines whether the description column exists in the fnd_user_resp_groups_direct table. This property is required if Version is 11.5.10 or 12. Allows values are TRUE and FALSE.

       For example, the Tabbed User Form may need to be modified in a manner similar to the following to support EBS version 12.

       <FormRef name=’Oracle ERP User Form’> <Property name=’RESOURCE_NAME’ value=’Oracle ERP R12’/> <Property name=’VERSION’ value=’12’/> <Property name=’RESP_DESCR_COL_EXISTS’ value=’TRUE’/> </FormRef>

Usage Notes

This section describes resource parameters that are applicable for the Oracle ERP adapter, including

• Oracle Applications User Management Security

• Oracle Client Encryption Types

• Oracle Client Encryption Level

• Oracle E-Business Suite (EBS) Admin User Responsibility

• Adding Securing Attributes

• Enabling Users

• Auditing Responsibilities

• Using Resource Actions

Oracle Applications User Management Security

User security is controlled at three levels within Oracle Applications:

• Functional security, which controls user access to the individual menus and menu options within the system.

• Data security, which controls what specific data objects a user can operate on.

• Role Based Access Control (RBAC), which allows for the creation of roles, to which responsibilities and permissions can be assigned.

The Oracle ERP adapter supports functional security only. Therefore, the adapter cannot list create, update, or delete Oracle data objects, object instances, or instance sets. Nor does the adapter create or manage role objects, role hierarchies or role categories.

Oracle Client Encryption Types

This parameter can contain a list of valid Oracle support encryption algorithm names, such as RC4_56 or RC4_128. If this list is empty, all algorithms supported by Oracle for that Oracle release will be available. The client/server will negotiate on which of these algorithms to use based on Oracle Client Encryption Level setting.

The Oracle Server must also be configured to support this type of encryption.

For a more information about the supported algorithms, refer to the Oracle Advanced Security Administrator’s Guide. See the SQLNET.ENCRYPTION_TYPES_CLIENT section for a list of valid values for the thin JDBC client.

Oracle Client Encryption Level

This value determines the level of security that the server/client negotiates and enforces. The default value, if left blank, is ACCEPTED. The valid values are REJECTED, ACCEPTED, REQUESTED and REQUIRED. For more details about the use of this parameter, refer to the Oracle Advanced Security Administrator’s Guide and the SQLNET.ENCRYPTION_CLIENT values.

You must also configure the Oracle Server to support this type of encryption.

Oracle E-Business Suite (EBS) Admin User Responsibility

This value determines the EBS Responsibility used by the Identity Manager Oracle EBS Admin user to call the EBS application initialization routine. A list of valid responsibilities can be found in the fnd_responsibility_vl table. Also refer to the Oracle EBS documentation for more information.

If the Identity Manager Oracle EBS Admin user has a valid EBS system account and has a responsibility that matches the value of this parameter, the Oracle session created during connection enables the users’ actions to be audited using the Oracle EBS auditing mechanism. For example, the created_by and the last_updated_by fields of the fnd_user table objects will be updated correctly with the user ID of the Identity Manager Oracle EBS Admin user.

Adding Securing Attributes

The securingAttrs account attribute supports the Securing Attributes feature in Oracle E-business Suite. To configure Securing Attributes from the Identity Manager Create User page, perform the following steps:

Configuring Securing Attributes from the Create User Page

1. Select the Add Securing Attribute checkbox.

2. Enter a search pattern to narrow the choices of available attributes in the Enter Securing Attribute Search Pattern text box. Use the % character as a wild card. Then click the Load Securing Attributes button. This will load the attributes into the Oracle Securing Attributes select box.

3. Select an attribute from the drop-down menu, and it will be added to the Securing Attributes table.

   You can remove securing attributes by selecting the attribute to be removed from the table and clicking the Remove Selected Securing Attribute button.

Enabling Users

Enabling an Oracle EBS user requires the value of the owner attribute to be specified. The value CUST is used by default unless the value is specifically added to the Enable form and sent through the Enable view. The following code example changes the default owner to MYOWNER:

<Field name=’resourceAccounts.currentResourceAccounts[MyOracleERP].
attributes.owner’ type=’string’>
   <Display class=’Text’>
      <Property name=’title’ value=’Owner’/>
   </Display>
   <Default>
      <s>MYOWNER</s>
   </Default>
</Field>

Returning User Responsibilities

The listResourceObjects call can be used to return a user’s responsibilities and other Oracle EBS objects. The following table provides information about the supported object types.

The following code sample adds a field to the user form that returns active responsibilities. You must replace USER_NAME and RESOURCE_NAME with valid values. auditorResps may be replaced with responsibilities, directResponsibilities, or indirectResponsibilites

<Field name=’respNames’ type=’string’>
   <Display class=’Text’>
      <Property name=’title’ value=’Oracle ERP Responsibilities’/>
   </Display>
   <Expansion>
      <invoke name=’listResourceObjects’ class=’com.waveset.ui.FormUtil’>
         <ref>display.session</ref>
         <s>auditorResps</s>
         <s>RESOURCE_NAME</s>
         <map>
            <s>id</s>
            <s>USER_NAME</s>
            <s>activeRespsOnly</s>
            <s>true</s>
            <s>attrsToGet</s>
            <list>
               <s>name</s>
            </list>
         </map>
         <s>null</s>
      </invoke>
   </Expansion>
</Field>

Auditing Responsibilities

To audit the sub-items (such as forms and functions) of responsibilities assigned to users, add the auditorObject to the schema map. auditorObject is a complex attribute that contains a set of responsibility objects. The following attributes are always returned in a responsibility object:

• responsibility

• userMenuNames

• menuIds

• userFunctionNames

• functionIds

• formIds

• formNames

• userFormNames

• readOnlyFormIds

• readWriteOnlyFormIds

• readOnlyFormNames

• readOnlyUserFormNames

• readWriteOnlyFormNames

• readWriteOnlyUserFormNames

• functionNames

• readOnlyFunctionNames

• readWriteOnlyFunctionNames

readOnly and ReadWrite attributes are identified by querying the PARAMETERS column in the fnd_form_functions table for one of the following:

• QUERY_ONLY=YES

• QUERY_ONLY="YES"

• QUERY_ONLY = YES

• QUERY_ONLY = "YES"

• QUERY_ONLY=Y

• QUERY_ONLY="Y"

• QUERY_ONLY = Y

• QUERY_ONLY = "Y"

If the Return Set of Books and/or Organization resource parameter is set to TRUE, the following attributes are also returned:

• setOfBooksName

• setOfBooksId

• organizationalUnitName

• organizationalUnitId

With the exception of the responsibility, setOfBooksName, setOfBooksId, organizationalUnitId, and organizationalUnitName attributes, the attribute names match account attribute names that may be added to the schema map. The account attributes contain an aggregate set of values that are assigned to the user. The attributes that are contained in the responsibility objects are specific to the responsibility.

The auditorResps[] view provides access to the responsibility attributes. The following form snippet returns all the active responsibilities (and their attributes) assigned to a user .

<defvar name=’audObj’>
   <invoke name=’get’>
      <ref>accounts[Oracle ERP 11i VIS].auditorObject</ref>
   </invoke>
</defvar>
<!-- this returns list of responsibility objects -->
<defvar name=’respList’>
   <invoke name=’get’>
      <ref>audObj</ref>
      <s>auditorResps[*]</s>
   </invoke>
</defvar>

For example:

• auditorResps[0].responsibility returns the name of the first responsibility object.

• auditorResps[0].formNames returns the formNames of the first responsibility object.

Using Resource Actions

The Oracle ERP adapter supports resource actions. To enable these actions, you must supply scripts written in Javascript or BeanShell. The adapter calls these scripts before or after performing the following provisioning actions:

• create Before/After Action

• update Before/After Action

• delete Before/After Action

• enable Before/After Action

• disable Before/After Action

• getUser After Action

Every action script receives an actionContext map, as defined by the java.util.Map class. The possible map content varies for each action.

Scripts should never close the JDBC Connection that is passed to them. The adapter automatically closes the connection at the appropriate time.

See Chapter 50, Adding Actions to Resources for more information about implementing resource actions. Example scripts are provided in $WSHOME/sample/OracleERPActions.xml.

create Before/After Action

The actionContext map passed to the action contains the following entries:

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may also add appropriate strings to the errors key. The presence of any items in the errors List is considered a creation failure.

update Before/After Action

The actionContext map passed to the action contains the following entries:

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered an update failure.

delete Before/After Action

The actionContext map passed to the action contains the following entries:

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add appropriate strings to the errors key. The presence of any items in the errors List is considered a deletion failure.

enable Before/After Action

The actionContext map passed to the action contains the following entries:

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

disable Before/After Action

The actionContext map passed to the action contains the following entries:

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, the script may add the appropriate strings to the errors key. The presence of any items in the errors List is considered a failure.

getUser After Action

The getUser action is useful in situations where you need to retrieve additional custom account attributes from the database beyond those retrieved by the standard adapter. To enable this action, specify the name of this resource action by setting the resource parameter labeled GetUser After Action.

The actionContext map passed to the action contains the following entries:

Error Handling

Any throw from within the script is considered a failure.

If the script encounters any errors, it may add appropriate strings to the errors key. The presence of any items in the errors List is considered a fetch failure.

Security Notes

This section provides information about supported connections and privilege requirements.

Supported Connections

Identity Manager can use one of the following drivers to communicate with the Oracle adapter:

• JDBC thin driver

• JDBC OCI driver

• Third-party drivers

Since the Oracle Application stored procedures require unencrypted passwords to be passed to some of the stored procedures used for provisioning, you should implement encrypted communications between Identity Manager and the Oracle application resource.

Please read the Oracle publication Oracle Advanced Security Administrators Guide and your JDBC driver’s documentation to validate the level of support for encryption that your specific version of Oracle RDBMS and driver provides.

Oracle EBS Permissions

Oracle E-Business Suite requires access to the following tables and stored procedures.

The administrator must be able to run the select command for all tables. In addition, the administrator must be able to update the apps.fnd_user table.

The adapter might access additional tables and stored procedures. Refer to the Oracle E-business Suite documentation for additional information.

Oracle states that the Oracle EBS system, including the fnd_user_pkg stored procedures, were designed to be used to administer the ORACLE EBS system as the APPS user. Oracle does NOT recommend creating an alternate administrative user. However, if you need to manage Oracle EBS with a user other than APPS, contact Oracle for guidance.

The alternate administrative user must be granted the same access as the APPS user has to all Oracle data, including tables, views, and stored procedures.

The user will also need synonyms set up so the user will have access to the tables that the APPS user has access to. If a different user is used and the appropriate grants and synonyms have not been created for the user, the following error might be encountered:

Error: ORA-00942: table or view does not exist

Add the appropriate grants and synonyms to correct the error. A sample SQL*Plus script is located in the following directory:

$WSHOME/sample/other/CreateLHERPAdminUser.oracle.

You can modify this script as necessary and use it to create an alternative Oracle EBS Admin user. Usage instructions are documented in the comments at the beginning of the script.

For pass-through authentication only, authority is needed to run the following SQL command:

create or replace function wavesetValidateFunc1 (username IN varchar2, 
  password IN varchar2)
RETURN varchar2 IS ret_val boolean;
BEGIN ret_val := apps.FND_USER_PKG.ValidateLogin(username, password);
IF ret_val = TRUE THEN RETURN ’valid’;
ELSE RETURN NULL;
END IF;
END wavesetValidateFunc1;

Provisioning Notes

The following table summarizes the provisioning capabilities of this adapter. The adapter does not issue any direct table updates during any supported provisioning operation.

Account Attributes

Default Attribute

The following table lists the default Oracle ERP account attributes. All attributes are optional.

Additional Attributes

The Oracle ERP adapter allows you to add several read-only attributes that Identity Manager can use to audit changes to responsibilities. The values returned in the auditorResps attribute are the active responsibilities for that user. Except for auditorObject, all other attributes listed in the following table are aggregates of each responsibility’s sub-items, minus any menu and function exclusions that may exist.

The auditorObject attribute may be added as well. See Auditing Responsibilities for details about this atttribute.

The following table lists attributes that may be added to the schema map.

The Oracle ERP adapter can support any additional custom attributes by using before and after actions for create and update, and by using a custom getUser action. See Using Resource Actions for more information.

Resource Object Management

Identity Manager supports the following native objects:

Identity Template

$accountId$

Sample Forms

Built-In

None

Also Available

OracleERPUserForm.xml

Troubleshooting

Use the Identity Manager debug pages to set trace options on the following classes:

• com.waveset.adapter.OracleERPResourceAdapter

• com.waveset.adapter.JdbcResourceAdapter

• com.waveset.adapter.JActionUtil (if using before/after actions)