Friday, October 17, 2008

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.

26 comments:

Derek Hendy said...

Hi Srinivas, Excellent Blog. We are working with the procedures in the fnd_user_pkg. We call the createuser procedure passing in the guid which we derive from the OID. The FND account is created and no errors are raised but the user_guid field is not populated. We are sure that this procedure use to work.

We can the subsequently call the updateuser procedure, again passing the guid, and the user_guid field is updated and attributes within the OID set.

Could it be that we require a specific profile option set Oor combination of profile options)? Or is it, as Oracle support have informed me, that the createuser method doesn't touch the user_guid even though it has it defined in a public method?

Do you have any experience using the fnd_user_pkg?

Thanks

Derek

DBA University Blog said...

Derek,

I did use FND_USER package a couple of years ago. Now we create users directly via the CREATE USER form that comes in E-Business Suite. We make sure that the user we create exists in OID (we customized the create user form with a database link lookup to OID database). Using this approach we dont worry about USER_GUID.

The USER_GUID is automatically set when the user logs in for the "first time". We have set the 'Applications SSO Auto Link User' = 'Enabled'. So Oracle populates FND_USER's USER_GUID column with the same value as the corresponding record in OID.

So in short, you dont have to worry about USER_GUID even if you use the FND_USER pkg as long as you have the 'Applications SSO Auto Link User' = 'Enabled'. Infact enabling this profile option makes the life easy to end users as they have to type in their OID username/pwd just once to use Oracle Applications.

I am not sure how FND_USER package updates attributes in OID. Usually OID attributes have to be the master. So you dont have to update them at all.

-Srinivas

Derek Hendy said...

Hi Srivinas, I think the 'Auto Link User' is fine, but in our case we do not create users in FND with the same names as those in the OID. We can't therefore rely on this standard process.

Thanks

Derek

Anonymous said...

Great post Srini..

I setup SSO server and OID is running and integrated with apps. I can create user in OID and user successfully moves to FND_USER table. Users can successully login to apps using SSO server.

But the problem I am facing, after deleting the user from OID, user not deleted from FND_USER table. I setup provision profile (ProvOIDToApps.tmp) while registering apps with SSO. Not sure now where to check and how to troubleshoot the user deletion from both OID and apps. I want, users should be sync between OID and apps (User creation, update and delete)

Any help?

Unknown said...

Dear Visitor,

I vaguely remember working on such a test case(checking if delete in OID deletes in Oracle EBS table also). But we did not enable the automatic provisioning from OID to EBS, so we stopped worrying about this topic. We create users in EBS through a manual process (not automatic).

Now coming to your point, Is the record getting deleted in OID or just disabled in OID ? I have two suggestions for you.

1) Can you review this article that i posted .

http://www.dailydba.com/2008/11/restriction-on-automatic-user.html

The DIT structure also plays a role here.

2) There are a couple of manual ways to delete/end-date in E-Business Suite. You may not want to "delete" the row in E-Business Suite. Instead, you can just "enddate" the EBS record if the corresponding one is deleted in OID.

Please read the signature of the functions and procedures available for the DBMS_LDAP utility (You can run this from E-Business Suite database only which makes a connection to the OID server). Particular functions that may be interest to you are search_s, compare_s etc.


Is your OID a gateway to Microsoft Active Directory (AD) ? If so, you can use ldapsearch to search for orclsourceobjectdn globally in OID. If it contains the Disabled string, then find all such entries and take appropriate action in FND_USER table

You can setup periodic cronjobs for the above and this should take care of your problem.

Try to create an SR with Oracle incase you are not satisfied with the above manual workarounds

-Srinivas

Anonymous said...

Thanks Srini,

I appreciate your response. I got my answer.

User accounts deleted from the Oracle Internet Directory are end-dated in Oracle E-Business Suite, in order to maintain an audit trail.

I was expecting that user will be deleted from fnd_user after user removed from OID but actually user is end-dated in fnd_user.

Thanks again.

DBA University Blog said...

Dear Visitor,

Sure. What about username updates. Is the username change in OID getting updated in the FND_USER table ? Just curious.

-Srinivas Ramineni

Charles Bryant said...

Hi Srinivas, great blog!

Here's my issue (aren't all the comments like this?...)

We have 10.2.0.4 integrated with AD and eBS 12.0.6.

When our users login, they can authenticate via SSO, but then they're presented with a blank form.

We can see that the user's account in FND_USER has a USER_GUID value. In order to try re-linking OID and eBS, we have null'ed the USER_GUID value, but to no avail.

Currently, we have the profile value 'Application SSO LDAP Synchronization' because we were getting an error message when trying to create new eBS users that the fnd_ldap_pkg.create_user was failing with an ORA-20001.

DBA University Blog said...

Charles,

1) What is 10.2.0.4 ? Is it the Identity Management product version you are referring to or the database version? There is only 10.1.4.x that i know of in Oracle Identity Management.

2) Now coming to the actual issue, I feel it is not a problem with the SSO itself. If you get a blank page after authentication, then it could be a problem with the R12 side of it. Have you checked the OPMN, OC4J logs ?

$INST_TOP/logs/ora/10.1.3/j2ee/oacore/oacore_default_group_1
$INST_TOP/logs/ora/10.1.3/opmn

One issue that we face in R12 is the JAVA CACHE related issue that is sometimes not starting the services properly, which can result in a blank page.

Try to make it false and bounce the services to see if the issue goes away

$INST_TOP/ora/10.1.3/j2ee/oacore/config/oc4j.properties

LONG_RUNNING_JVM=false

3) Is this is a test instance and the first time you are configuring R12 with SSO ? Is the direct SSO login working fine without the R12 instance (the orcladmin login in the SSO login page)

-Srinivas

Charles Bryant said...

Thanks for the advice, Srinivas, I'll check my DBAs to see if they can verify the cache issues.

Anonymous said...

Hi Srinivas, My customer who wants to setup SSO for their's new R12 instance. The question from my customer are

1) Is it possible to use an existing OID (which is implemented for Oebs 11.5.10.2)?

2) What is advantage/disadvantage of this configuration?

3) Is this supported to use single OID for different version of OeBS?

Thanks,
Brgds/Kumar

DBA University Blog said...

Kumar,

1) Is it possible to use an existing OID (which is implemented for Oebs 11.5.10.2)?

Yes. We have used OID 10.1.4.3 initially for 11.5.10.2 . Our first implementation of OID was for 11.5.10.2 . We then upgraded to R12 (12.1.1) and still use the same OID 10.1.4.3

So the answer to your queston is "Yes. It is possible"

2) What is advantage/disadvantage of this configuration?

No disadvantage. But OID has got a new release 11.1.1.2.0 (This is part of Fusion Middleware 11g). OID 11.1.1.2.0 has got some new features and it is certified with 11.5.10.2, 12.0 and 12.1 also.

3) Is this supported to use single OID for different version of OeBS?

Yes. A single OID is supported for different version of eBS. I am not sure of all the versions, but defenetly YES for 11.5.10.2 and 12.0, 12.1 versions

-Hope that helps
Srinivas Ramineni

Anonymous said...

Thank you for your quick response.

Brgds/Kumar

Anonymous said...

Hi Srivinas,

Can you help me with this issue?

I have a customer who wants to upgrade OAS 10g (10.1.2.x) to 10.1.4.3 and they are asking, do they need to choose new infra ORACLE_HOME? or can use the an exsisting ORACLE_HOME?

Thanks in advance!
rgds/Kumar

DBA University Blog said...

Raj Naidu's question

"Hi Srini,

I'm in the midst of implementing SSO with Ebiz 11.5.10.2 Integrated with AD. All the configurations have been completed and working well.I've a requirement to check if there is any way we can avoid the responsibility(Preferences SSWA) getting assigned in AD - OID - Ebiz direction of user provisioning.

"

Raj - Good to see your post. It has been a long time i worked on SSO with Ebiz. I also noticed this "Preferences SSWA" getting defaulted when you create an user. This has nothing to do with OID provisioning. Oracle creates this default responsibility for every user you define in 11i. I don't exactly recall how you avoid this default initial responsibility. But should be easy to fix.



-Srinivas

Anonymous said...

Hi Srinivas,

We Integared OID with AD.
We dont need all account need to create users from OID to Ebusiness 11i.

Which provisioing method is best solution? Please advise

Anonymous said...

Hi Srinivas,

We Integared OID with AD. We dont need all accounts create from OID to Ebusiness 11i.

As per you,we can use Security => User => Define form in 11i method.

For this solution which provisoining tempalte(ProvBiDirection.tmp) i can register with Ebusiness 11i?

Please advise

Anonymous said...

consider a xxx account created in apps ... the same will be
created in OID ... now if AD has XXX account then what happens

Please advise

Regards
Sudheer

DBA University Blog said...

I have done this in the past. You need to write a custom search filter to remove UNWANTED entries in OID.

Read my article at http://www.dailydba.com/2008/12/how-to-create-lean-oracle-internet.html (How to create a LEAN Internet Directory)

DBA University Blog said...

Sudheer

I did not quite get your question.

What is your user provisioning direction ? Is it AD to OID to E-Business Suite ?

Can you explain your question more elaborately.

Please read the below related article in the meanwhile

http://www.dailydba.com/2009/06/how-to-avoid-conflict-in-oid-during-ad.html

Anonymous said...

Srinivas,

We are using AD->OID->Ebiz integration. when the user created in E business suite it will be provisioning to OID. if the user already exist in AD what is going to happen ?

Regards,
Sudheer

DBA University Blog said...

Sudheer,

You have said AD->OID->Ebiz is your direction. Now when you create in EBiz, how will it create in OID (the opposite).

If what you said is right about direction (AD->OID->Ebiz) , then if you create in EBiz it will NOT be created in OID (as this direction will be the opposite of what you said).

eldin said...

Hi Srinivas,

Do you have article or document about how to create user and responsibility E-Business Suite from OID ?

thanks,
eldin

DBA University Blog said...

Eldin,

In the same article that i wrote in my blog =>
http://www.dailydba.com/2008/10/different-methods-of-provisioning-users.html

Check "On-Demand User Creation" section. You need to set the profile option 'Applications SSO Auto Link User' to 'Create a new user and link to OID user'

eldin said...

Hi Srinivas, I'm still curios about.
Is it possible to assign another responsibility besides Preferences SSWA ?

thanks,
eldin

Anonymous said...

Hi srinavas,

Great Blog. I have some issue with my OAM-EBS integration. Account Auto Linking seems to be throwing an error.

I have got the "On-Demand User Creation" scenario that you mentioned working.

However when i try the "Security => User => Define form in 11i" scenario i am getting an error. I have set the profile parameters accordingly.

Following is the error that i am getting:


FINE: autoLinkEnabled=Y
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.server.FndSsoLoginProcess2 doGet
FINE: FndSsoLoginProcess2--doGet(): currentState-->VALID_SSO_VALID_GUEST
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.server.FndSsoLoginProcess2 doGet
WARNING: for GUID=B9F1F4F7B4E83C8BE040920ACE5B4A0A FND_USER ID =NULL
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.server.FndSsoLoginProcess2 doGet
FINE: AppsUser is not in FND_USER for GUID=B9F1F4F7B4E83C8BE040920ACE5B4A0A
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.server.FndSsoLoginProcess2 doGet
FINE: Case for linking for GUID=B9F1F4F7B4E83C8BE040920ACE5B4A0A
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.server.FndSsoLoginProcess2 doGet
FINE: autoLinkEnabled=Y block --begins
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.ProfileStoreImpl getSpecificProfile
FINE: -name=APPS_SSO_LOCAL_LOGIN&& userId=1007944& applId=null & respId=null
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.ProfileStoreImpl getSpecificProfile
FINE: Option=APPS_SSO_LOCAL_LOGIN~USER~1007944~~
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.ProfileStoreImpl getSpecificProfile
FINE: cached value=null
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.ProfileStoreImpl getSpecificProfileFromDB
FINE: Returns value=BOTH
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.ProfileStoreImpl getSpecificProfile
FINE: DB returns value=BOTH
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.ProfileStoreImpl getSpecificProfile
FINE: Returns value=BOTH
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.common.server.FndSsoLoginProcess2 doGet
FINE: hasLinkableAppsUser & updateUserGuid being called
Mar 4, 2012 5:51:30 AM oracle.apps.fnd.ext.sso.SsoUser updateUserGuid
SEVERE: SEVERE exception while updating user GUID -->
java.sql.SQLException: ORA-06550: line 1, column 310:
PLS-00302: component 'ADD_USER_TO_OID_SUB_LIST' must be declared
ORA-06550: line 1, column 297:
PL/SQL: Statement ignored

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)

Related Posts Plugin for WordPress, Blogger...