How to create a lean Oracle Internet Directory

Introduction

A Directory Service such as Microsoft Active Directory (AD) stores a variety of information about an enterprise viz. User Accounts, Organization Units, Printer names, Computer names, Fax Machine names and other network resources. If AD is the master source of truth for Oracle Internet Directory (OID), the Oracle Directory Integration Assistant provides a default filter to retrieve only the needed resourcers i.e user accounts (objectclass=user), groups and Organization Units (objectclass=organizationalunit). Below is the default filter to retrieve information from a Microsoft Active Directory. This can be viewed by launching dipassistant -gui tool on the Oracle Internet Directory (OID) host (dipassistant -gui => ActiveChgImp => Filtering)

"searchfilter=((objectclass=group)(objectclass=organizationalunit)(&(objectclass=user)(!(objectclass=computer))))"

Custom Filter

One can add additional conditions in the searchfilter to block unwanted resources such as disabled user accounts, special user accounts, generic user accounts or even organization units if a FLAT DIT is implemented. In a huge enterprise, Active Directory can contain thousands of unwanted user account names and if a custom filter is not used, all these unwanted entries will be loaded into FND_USER table on the E-Business Suite database if "Unconditional Provisioning" is enabled.

A few advantages of a custom filter are the following

1. A lean OID (less number of rows in the ODS schema and lesser number of entries processed during the initial bootstrap operation to get data from AD to OID).
2. Less work performed by the AD to OID syncrhonziation jobs during every scheduled interval.
3. A lean FND_USER table if Unconditional Provisioning is turned on.

How to write a custom filter

Conditions written in boolean operators are usually in the form of (condition_1 and condition_2), (condition_1 and (condition_2 or condition_3)) etc.

But an LDAP searchfilter is written differently. Note the position where the boolean operators are placed in the below examples.

Example 1 : (condition_1 and condition_2) is written as and(condition_1)(condition_2)

Example 2 : (condition_1 and (condition_2 or condition_3)) is written as and(condition_1)or((condition_2)(condition_3))

In our enterprise's Active Directory, special account names have a . (DOT) in them such as ADMIN.PAYROLL, Inactive users are stored in the attribute Org-UserStatus with an "I". The custom search filter for our case is

searchfilter=(&(objectclass=user)(!((cn=*.*)(sAMAccountName=*.*)(Org-UserStatus=I)(objectclass=computer))))

If one wants to write a custom filter, one should work with the AD administrator to obtain the conditions and attribute names that can be used in the filtering process. Now follow the rules mentioned above to place the boolean operators in the right positions to get the filter working.

FLATTENED Directory Information Tree

Introduction

By default, Oracle creates the Directory Information Tree (DIT) in an identical structure during the initial data load (bootstrap) and subsequent data syncrhonization from an external LDAP directory (such as MS Active Directory) to the Oracle LDAP Directory (Oracle Internet Directory). However, several entries failed to get loaded into OID when we tried with this default method. Our bootstrap.log file contained the below errors for the entries that failed to get processed in OID.

LDAP: error code 32 - Parent entry not found in the directory
Error occurred while loading - cn=emp_2,ou=aurora,ou=IL,dc=oid_srv,dc=mycompany,dc=com

DN in Active Directory (AD)

cn=emp_2,ou=aurora,ou=IL,dc=ad_srv,dc=mycompany,dc=com

DN in Oracle Internet Directory (Identical to the DN in AD)

cn=emp_2,ou=aurora,ou=IL,dc=oid_srv,dc=mycompany,dc=com

Solution

This error is at best misleading because the parent entry (ou=aurora) did get created in OID. We could not get a solution for this error and hence altered the design of our DIT on the target OID. We have FLATTENED the DIT. A FLAT DIT does not have any sub tree such as OUs etc. and all the entries are loaded under a single sub tree (FLATTENed). To create a FLAT DIT, the Domain Mapping Rule has to be configured in the AD->OID syncrhonization profile.

DN in Active Directory (AD)

cn=emp_2,ou=aurora,ou=IL,dc=ad_srv,dc=mycompany,dc=com

DN in Oracle Internet Directory (FLATTENED without any OUs)

cn=emp_2,dc=oid_srv,dc=mycompany,dc=com




Conclusion

Oracle supports both regular DITs and flat DITs. If one wants to check the source directory's DN value, one can use use the orclsourceobjectdn attribute returned by the ldapsearch command in OID.Furthermore, a flat DIT is simple in design and easy to search for entries in OID using the oidadmin utility. All the LDAP syncrhonization functions such as ADD, MODIFY and DELETE are supported in a FLAT DIT. We have designed a flat DIT and we are happy with it.

OCA CA Root certificate warning in the browser

Introduction

We went LIVE with Single Sign-On integration for Oracle Applications during the Thanks Giving weekend. For the real time system, we configured SSL for network encryption. For the E-Business Suite SSL configuration, we purchased the security certificate from Verisign (A third-party certificate authority). But this time we wanted to try Oracle Certificate Authority (OCA) for the Single Sign-On web server.

Some of the advantages of OCA are

1. Stronger encryption algorithms.
2. Cost effective. Since OCA is Oracle's own certificate issuing system, it eliminates the need for IT administrators to purchase certificates from third-party authorities such as Verisign etc.
3. Automatic storage of the certificate in OID because OCA is tightly integrated with the latter. Policies can be configured in OID for renewal alerts and expiration dates of the security certificates.

However, popular browsers such as Internet Explorer, Mozilla FireFox etc. trust certificates only from a few well established authorities and Oracle is not one of them yet. Because of this limitation, websites configured with SSL certificates issued by OCA show a warning.

Solution

One procedure to avoid such a warning is to request the system administrators to push the CA Root Certificate to all browsers in the enterprise.

Conclusion

To save a few $$, Oracle Certificate Authority is a good alternative to Verisign for securing Oracle resources provided your system administrator agrees to push the CA Root Certificate to the trusted list of certificates of the standard browsers. We saved this hassle for our administrator and purchased a certificate from Verisign (its already trusted by Internet Explorer). However OCA is a good bet for test and UAT systems. By the way, OCA is part of the Identity Management Software. Following is the warning message shown by Internet Explorer when the certificate issued by OCA is not yet trusted by the browser.

How to fix BULKDELETE performance problem

Due to several reasons, one may have to delete all the bootstrapped 3rd party user data from Oracle Internet Directory (OID). For example, thousands of entries may have got loaded from MS Active Directory into OID during the initial bootstrap process. If one wants to bulkdelete all these entries from OID, one can notice a performance problem during the delete operation. This problem currently exists in 10.1.4.2 Oracle Identity Management software. When i tried the bulkdelete operation with 60,000 entries, it took almost 20 hours to finish the execution.

The fix for this performance problem is to create a custom index on one of the OID tables.

1. Login as ods/***@ssodb
2. CREATE INDEX TEMP_UBS_CT_HRCH_QUERY_IDX on CT_HRCH_QUERY(ENTRYID);
3. exit
4. $ORACLE_HOME/ldap/bin/bulkdelete connect="ssodb" basedn="cn=Users,dc=oid_srv,dc=mycompany,dc=com"
5. Login as ods/***@ssodb
6. Drop index TEMP_UBS_CT_HRCH_QUERY_IDX

This index is only for fixing the performance problem and can be dropped after the bulkdelete operation completes successfully. I could complete the bulkdelete operation in less than 20 minutes with the custom index in place to delete the 60,000 entries from OID. Compare that with the 20 hours that i got without the index !

Restriction on Automatic User Provisioning

Introduction

I have explained about the different methods of user provisioning in the previous article here. If one were to go with 'Unconditional Provisioning' (automatic provisioning) of new users from OID to FND_USER table, then one has to consider an important restriction on how such users be placed in the Directory Information Tree in OID.

New User entries that are created in OID and have to be automatically provisioned (created) in FND_USER table must be either placed in the default USERS container or any container that is a sub-tree of the USERS container. If the user entries are in a sub-tree that is at the same level as the default USERS container, then such entries will not be automatically created in FND_USER table even if the 'Applications SSO Enable OID Identity Add Event' system profile option is ENABLED.

Conclusion

This restriction should go away in future releases of Oracle Identity Management, but as of now, the latest 10.1.4.2 does have it. One can load all users in OID in the default USERs container, however for better management and administration purposes, it is always recommended to create normal users in a sub-tree container.

'Applications SSO Auto Link User' profile and bootstrapping again

Introduction 

After integrating Oracle E-Business Suite with SSO, setting the system profile 'Applications SSO Auto Link User' = 'Enabled' lets users login to Oracle using only their SSO username/pwd (Windows NT username/pwd) if the 3rd party LDAP directory is MS Active Directory) WITHOUT the need to enter their Oracle E-Business local username/pwd again in the next page. This profile 'automatically links' the Windows NT username and the existing Oracle username internally (provided there is a user_name in FND_USER that EXACTLY matches the Windows NT username) and directly shows the Oracle E-Business responsibilities navigation pane. It does it by updating FND_USER.USER_GUID to a value that matches with the corresponding OID's ORCLGUID value of the logged in user.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Problem

Due to some circumstances, one may have to bulkdelete all the OID users and reload them from AD to OID using the bootstrap process. Because it is a new bootstrap process, a new ORCLGUID may get generated in the OID that will make it out of sync with the target FND_USER.USER_GUID value. With such a scenario, Oracle will prompt again for the E-Business local username/pwd despite the system profile 'Applications SSO Auto Link User' having an 'Enabled' value. One can verify the mismatch after the reload by running the following command.

ldapsearch -v -h oid_host_name -p 389 -D "cn=orcladmin" -w ***** -b "" -s sub "sn=RAMINENI*" uid orclguid orclactivestartdate orclactiveenddate orclisenabled

 

ldap_open( oid_host_name, 389 )

filter pattern: sn=RAMINENI*

returning: uid orclguid orclactivestartdate orclactiveenddate orclisenabled filter is: (sn=RAMINENI*)

cn=c123456,ou=consultants,ou=users,IdentityRealm

uid=ramineni@mycompany.com

orclguid=844AB8C88EE56B74E040810A0CBC6249

1 matches

This orclguid shown above may NOT match the FND_USER.USER_GUID for the same user because the former is a newly generated one because of the reload process. In this case, Oracle prompts for the Oracle E-Business username/pwd after entering the Windows NT username/pwd in the login page even though the system profile 'Applications SSO Auto Link User' is set to 'Enabled', often confusing the  user.

 

   

 

 

 

 

Solution

The process to 'auto link' SSO username and the FND_USER user_name after a reload of data from the 3rd party LDAP directory to OID is to update FND_USER.USER_GUID value to NULL.

update fnd_user set user_guid = NULL where user_guid is NOT NULL. 

 

A better alternative is to run the $FND_TOP/patch/115/sql/fndssouu.sql USER_NAME

script (Apart from setting the FND_USER.USER_GUID value to NULL, this script also flushes the workflow entity cache for the user)

Different methods of provisioning users from Oracle LDAP to E-Business Suite

Introduction

While working on the Oracle E-Business Suite login integration project with MS Active Directory through Oracle Internet Directory (OID), I have come across several ways to provision (add) new users in the E-Business Suite. Without Single Sign On, the only way to add new users is through the FND_USER_PKG or by using the Security => User => Define of Oracle E-Business 11i. Depending on the setup in OID, I have identified five different ways to add new users in 11i after integration with MS Active Directory

Assumptions

1. Oracle Identity Management 10.1.4.2 and Oracle Applications E-Business Suite 11.5.10.2
2. Uni-directional synchronization from Active Directory to Oracle Internet Directory
3. Uni-directional provisioning from Oracle Internet Directory to the E-Business Suite

Options

On-Demand User Creation

On-Demand User Creation allows any enterprise user who has access to the SSO login page of the E-Business Suite to get an account created automatically. The user has to enter his/her Windows username/pwd on the SSO login page. A click on the 'OK' button will create a user in the FND_USER table of the E-Business Suite with a default 'Preferences SSWA' responsibility and will let the user access Oracle immediately.

Procedure

Set the profile 'Applications SSO Auto Link User' to 'Create a new user and link to OID user'

Advantages

• Account Creation in E-Business Suite is self-service and automatic.

Disadvantages

• Lets any user in the enterprise to have an Oracle account. Anyone who has the login webpage address can get an account created in 11i.

ldifwrite on OID and LDAPUserImport on 11i

Procedure

1. Use the ldifwrite command in the OID server to create a dump file containing the user's LDAP attributes and other information.
2. Copy the ldif file to one of the 11i middle-tiers
3. Run the LDAPUserImport java command to import the user into FND_USER table.

Advantages

• Control on user creation. Only genuine and approved users will be allowed to have an account in Oracle.

Diadvantages

• Manual process. However, it can be automated as well.

provsubtool on OID and Workflow Subscription Event on 11i

Procedure

1. Run the provsubtool command in OID to add new users to an account subscription list
2. On the 11i side, a workflow subscription event oracle.apps.fnd.subscription.add will be triggered at an appropriate time to add these users in the list to the FND_USER table in 11i

Security => User => Define form in 11i

The good old way of using Security => User => Define can still be continued for provisioning users in 11i after an integration with an LDAP directory.

Procedure

1. Verify the user exists in OID using an ldapsearch command or by using the oidadmin tool.
2. Disable Applications 'SSO LDAP Synchronization profile' option
3. Create the user using the Security => User => Define form

Unconditional Provisioning

By enabling the 'Applications SSO Enable OID Identity Add Event' system profile, the provisioning profile will add every user account to E-Business Suite that is synchronized from MS Active Directory to OID. Exercise caution before enabling this system profile because not every employee or consultant in an organization will need an Oracle account. This can lead to a proliferation of users in the FND_USER table.

Conclusion

Sufficient thought has to be exercised before enabling the Unconditional Provisioning or the On-Demand user creation as described above. Use one of the other three options for maximum control on user provisioning.