Thursday, October 23, 2008

'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
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)

12 comments:

Anonymous said...

Hi Srinivas,

I have EBS with some subset of enterprise users. I am putting up a OID for Directory Services and OIM for Provisioning users to both OID and EBS. What would be roadmap for this process. Pls let me know

Regards
Devi

DBA University Blog said...

Hello Devi,

I guess this will be the same solution that i have talked about in my Identity Management articles. Check this architecture http://www.dailydba.com/2008/09/oracle-e-business-suite-login.html

You will need a ENTERPRISE DIRECTORY -> OID -> E-Business Suite uni directional way.

Go thru the architecture diagram and you will understand. Otherwise come online on Tuesday 6 to 8 pm EST (I conduct chat session through this website during these 2 hours). The solution is easy and straightforward.

-Srinivas

Anonymous said...

Hi Srinivas,

This is an awesome blog full with information on SSO-OID-AD.
Am currently doing some implementation project and have few query.

R12 - Ebiz, IDM- 10.1.4.3

Currently my provisioning of user is AD -> OID -> EBIZ and it is working fine.

1)SSO Auto link user is still not happening after setting GUID = NULL and using fndssouu.sql ? it is still going for more information page wher i need to manually link the user to existing one with same name.

2)I need to migrate all exisiting users from FND_USER to Active Directory. How i can do this. DIo i need to manually create all the users or is there any script/tool which will do that.

3) My assumption is as we have user provisioning from AD -> OID -> EBiz. If i creat user similar to existing one in Ebiz it wil be directly migrated to OID and linked to Ebiz user.

Let me know if ebiz -> OID bulk migration is required ?? in my approach

Cheers,
Ashok

DBA University Blog said...

Ashok,

Apologies for the late reply. Been busy at work. We should be able to fix your issues.

1) Have you set your "Applications SSO Auto Link User" to ENABLED ?

2) Why do you have to migrated users to Active Directory. The idea is to let users login with their "already existing corporate AD login/pwd" right ? You just have to carefully map the AD user to FND USER and fix mismatched accounts. This user cleanup takes up the most time in this project. For example, your AD user might have "ASHOKDAS" as the username, whereas the one created in FND_USER could have been ASHOKDAS1. The challenge is to figure our that ASHOKDAS1 and ASHOKDAS refer to the same humanbeing in your enterprise and then update ASHOKDAS1 to ASHOKDAS in FND_USER table. Thats it.

3) We have stopped provisioning at AD to OID. We are not doing automatic provisioning from OID to EBIZ. If we want to create a new user, we manually add it in CREATE USER form (we customized it to first lookup in OID whether this user exists in the enterpise or not). You could make the user provisioning automatic (Read the restriction though at http://www.dailydba.com/2008/11/restriction-on-automatic-user.html) , but that can cause a proliferation of users. Read my article about methods to provision at http://www.dailydba.com/2008/10/different-methods-of-provisioning-users.html

Come online on tuesdays. I have a live chat open on every Tuesday.

-Thanks
Srinivas

Anonymous said...

Hi Srinivas,

Currently i am working on single sign on and Active Directory Integration project and i have a question and i thought you can help me resolving the issue.

We have around 4000 users at the enterprise level in AD, and all users have been created in OID using the dipassistant command:

dipassistant bootstrap -port 389 -profile ActiveChgImp -dn cn=orcladmin -passwd welcome99

What i am expecting is loading only the oracle users (350) instead of all the enterprise level users (4000). How do we filter to get only oracle users before loading into OID?
Thanks in advance.

In Active directory,

domain controller is:

cn=people,cn=OA111,dc=gsci,dc=com

DBA University Blog said...

Somu,

Do you have a special attribute that says it is ONLY oracle user ? I doubt you have that.

If you a special attribute in AD that mentions this is a oracle user, then you can use that filter to get the Oracle only users into OID. We faced the same challenge and we were not able to do it because when you create an new account in AD (lets say for an new employee), we dont add any special attribute that he will use Oracle in future.


Lets say your AD has got like 400,000 entries (including employee usernames, generic accounts, computer names, printer names, telephone names etc.)

1) A good to start with is to get all the "meaningful" entries from AD to OID. Eliminate all computers, printers, generic accounts etc by writing custom filter.

http://www.dailydba.com/2008/12/how-to-create-lean-oracle-internet.html

Lets say this retrieves 20,000 entries into OID

2) Then use 'Applications SSO Auto Link User' = Enabled profile option. This will let all "real" oracle users to access Oracle. The rest 20,000-350 (19650 entries) will be dummy in OID.

OID is just a gateway. No harm in having extra entries. So in short, the key is how best you write your filter.


-Thanks
Srinivas Ramineni

Derek Hendy said...

Hi Srinivas, excellent blog. I am currently attempting to use the fnd_user_pkg.createuser procedure to create our FND users. We derive the guid from OID and pass this to the overloaded method. The FND user account gets created but the user_guid field does not get set. We are convinced that this worked.

We can make a subsequent call to the updateuser method, passing the guid, and it updates the user_guid field and sets attributes in the OID.

Could this be related to some profile settings or does the public method in fnd_user_pkg actually not do anything with the user_guid, as I was informed by Oracle support.

Any ideas gratefully received.

Cheers

Derek

Unknown said...

Hi Srinivas,

I'm currently working on E-Business Suite OSSO integration. COuld you please tell me, the attribue orclisenabled in OID is mapped to whihc column in FND_USER table on E-BIZ. If i disable a user in OID ,how can i confirm on E-Business that this user was disabled??

Please do reply me ASAP.

Thanks In Advance.

Regards,

Anu.

DBA University Blog said...

Anu,

Have you tried checking the end_date column in FND_USER table ? It must be end dated in FND_USER table. It wont be deleted. The row will be there, but it will be end dated.

-Srinivas

Unknown said...

Hi Srivivas,

I had to do a rebootstrap on our system. And have had only one real issue since.

user A is seeing user B's responsibilities when they login through SSO

-bash-3.00$ ldapsearch -h server -p 389 -D "cn=orcladmin" -w my_password -b "" -s subtree "uid=userB" orclguid

orclguid: 9F889B804B7F3477E04400144F4AC40B



-bash-3.00$ ldapsearch -h server -p 389 -D "cn=orcladmin" -w my_password -b "" -s subtree "uid=UserA" orclguid

orclguid: 9F889B804B673477E04400144F4AC40B



SQL> Select USER_GUID from FND_USER where USER_NAME='USERA';

USER_GUID
--------------------------------
4D2E283D88C8380AE04400144F4AC40A

SQL> Select USER_GUID from FND_USER where USER_NAME='USERB';

USER_GUID
--------------------------------
9F889B804B673477E04400144F4AC40B

my case here is not to update the FND_USER GUID for userA as it is correct but to update the GHUID for usera at the OID end. HOw do I do that ? I deleted user out of SSO and re-ran sync and it pulled user back in but still with wrong GUID.

Any help appreciated

Dave

Unknown said...

Hi ,

Thanks for publishing this note.
We had setup the SSO , with OAM11gR1 and EBS R12.1.3
As Some users were not mapped corectly in OID while syncing from AD, we had to delete all the users from OID and then again bootstrap the users back to OID. The profile mentioned "Applications SSO Auto Link USer " is set to enabled. As there was mismatch of the GUID in EBS Fnd_user table , i followed the workaround as mentioned here in this post. But, the fnd_user table is not getting updated when i try to login to ebs after following the workaround mentioned here.

Please help us in resolving the issue.

Unknown said...

Hi ,

Thanks for publishing this note.
We had setup the SSO , with OAM11gR1 and EBS R12.1.3
As Some users were not mapped corectly in OID while syncing from AD, we had to delete all the users from OID and then again bootstrap the users back to OID. The profile mentioned "Applications SSO Auto Link USer " is set to enabled. As there was mismatch of the GUID in EBS Fnd_user table , i followed the workaround as mentioned here in this post. But, the fnd_user table is not getting updated when i try to login to ebs after following the workaround mentioned here.

Please help us in resolving the issue.

Related Posts Plugin for WordPress, Blogger...