title: A Perl Script to help with re-linking EBS users with OID link: https://onemoretech.wordpress.com/2008/04/01/a-perl-script-to-help-with-re-linking-ebs-users-with-oid/ author: lembobro description: post_id: 547 created: 2008/04/01 03:36:52 created_gmt: 2008/04/01 03:36:52 comment_status: open post_name: a-perl-script-to-help-with-re-linking-ebs-users-with-oid status: publish post_type: post

A Perl Script to help with re-linking EBS users with OID

If you’ve been following the last few posts you’ve probably guessed that I’m now in the middle of a pretty intense effort to get control of user management in an Oracle Internet Directory (OID) + Enterprise Business Suite (EBS) environment.

What’s happened is that the consultants, developers and business teams have been out creating accounts on the EBS database ahead of the security team’s effort to create users on OID. Now that we’ve finally begun to integrate EBS with the new Oracle 10g Application Server Single Sign-On Infrastructure, we need to make sure that the user’s OID entries are properly linked with their EBS accounts.

When Auto Linking (the System Profile option “Applications SSO Auto Link Users”) is enabled this happens “automagically”, but in our case, after many hours were put into creating accounts, resetting passwords and verifying that things were linked — the whole EBS app tier was cloned from another, similarly integrated, environment.

In such cases the USER_GUID values in many, if not all, user accounts on EBS are going to have a USER_GUID that does not match the value in the orclguid attribute of the user’s OID entry. To fix this what we needed to null out the USER_GUID values on EBS so Auto Linking could do it’s thing.

We also needed to remove any uniquemember entries for the affected users from the following node in OID:

cn=ACCOUNTS,cn=subscription_data,cn=subscriptions, orclApplicationCommonName=[EBS instance name],cn=EBusiness cn=Products,cn=OracleContext,dc=[realm name],dc=com

… as well as any child entries that correspond to the user’s OID orclguid (you can query the user’s OID entry for this system attribute by explicitly asking for “orclguid” — ldapsearch -L -h [oidhost] -D "cn=orcladmin" -w [password] -b "cn=users,dc=[realm],dc=com" -s sub "(cn=[username])" orclguid). Otherwise the system would not be able to create a new subscription for these users, resulting in a failure of modifies to sync between OID and EBS.

To avoid having to use the sqlplus procedure to null the GUIDs that I outlined in an earlier article, I decided to put together a script to automate the process. Below is the result, which still contains some (commented) debugging code that I think others may find useful. This script’s read_ebsdb subroutine generates a list of user accounts on EBS along with each user’s associated EBS USER_GUID and OID orclguid. I strongly recommend that read_ebsdb be run first, with mod_ebsdb commented out. This way you can eyeball the list before committing any changes to EBS.

`

#!/usr/bin/perl
# ebnullguids.pl Updates EBS FND_USER accounts from list. Makes USER_GUID null
# Used for post cloning, pre OID/SSO integration level set of accounts.
# Would not run the mod portion of this without first eyeballing the list.
# Created by P Lembo.
	
use strict;
use Text::ParseWords;
use DBI;
use Net::LDAP;
use Net::LDAP::Entry;
	
our($oid1Host,$oidUsr,$oid1Pass,$ebs1sid,$appsUsr,$ebs1apppw);
my $HOME = $ENV{'HOME'};
$ENV{'TNS_ADMIN'} = "/etc/oracle";
require "$HOME/etc/app.conf";
my $infile = "$HOME/data/ebsusers.csv";
my $ebssid = $ebs1sid;
my $ebsappusr = $appsUsr;
my $ebsapppw = $ebs1apppw;
my $oidHost = $oid1Host;
my $oidPass = $oid1Pass;
my $userbase = "dc=example,dc=com";
	
my $ldap = Net::LDAP->new($oidHost);
my $mesg = $ldap->bind($oidUsr, password =>$oidPass) or die $!;
	
read_ebsdb();
# mod_ebsdb();
	
$ldap->unbind;
	
sub read_ebsdb {
  open FH, ">$infile" or die $!;
  print FH "USER_NAME,USER_GUID,orclguidn";
	
  my $dbh = DBI->connect(
                        "dbi:Oracle:$ebssid",
			"$ebsappusr",
			"$ebsapppw",
                        ) or die "Database not connected: $DBI::errstr";
	
  my $sql = "SELECT USER_NAME,USER_GUID
	     FROM FND_USER";
  my $sth = $dbh->prepare($sql);
  $sth->execute();
	
  while (my ($user_name,$user_guid)  = $sth->fetchrow()) {
     print $user_name, "n";
     my %info = ldap_lookup($user_name);
	 my $uid = $info{uid};
	 my $orclguid = $info{orclguid};
	  if($uid =~ /./) {
		if ($user_guid !~ /$orclguid/gi) {
			print "$user_name,$user_guid,$orclguidn";
			print FH "$user_name,$user_guid,$orclguidn";
		}
	  }
	  else {
		print "$user_name,$user_guid,NO OIDn";
	    	print FH "$user_name,$user_guid,NO OIDn";
	 }
  }
  close FH;
  $sth->finish;
  $dbh->disconnect;
}
	
sub mod_ebsdb {
  open FH, "<$infile" or die $!;
  while() {
      chomp;
	  my (
                   $user_name,
		   $user_guid,
		   $orclguid
		 ) = ( &parse_line(’,',0,$_) );
	
      if(($user_name eq ‘USER_NAME’)||($orclguid eq ‘NO OID’)) { next; }
      if($orclguid) {
      	my $dbh = DBI->connect(
                                 “dbi:Oracle:$ebssid”,
				 “$ebsappusr”,
				 “$ebsapppw”,
				 {AutoCommit => 0}
	  	   ) or die “Database not connected: $DBI::errstr”;
	
	my $sql = “UPDATE FND_USER
		   SET USER_GUID = ‘’
		   WHERE USER_NAME = ‘$user_name’”;
	
      	my $sth = $dbh->prepare($sql);
      	my $rows_affected = $sth->execute();
	
      	if ($rows_affected > 1) {
          $dbh->rollback();
	      print “There are $rows_affected users with “;
	      print “the user name $user_name. Transaction cancelled!n”;
      	}
      	else {
          $dbh->commit();
	      print “$user_name USER_GUID is now NULLn”;
      	}
      	$sth->finish;
      	$dbh->disconnect;
	}
  }
   close FH;
}
	
sub ldap_lookup {
   my $userid = @_[0];
   my %info;
   my @attrs = qw( cn uid orclguid);
   my $query = “(cn=$userid)”;
	
   $mesg = $ldap->search(
		base => $userbase,
		scope => ’sub’,
		filter => $query,
		attrs =>@attrs
	);
   my $check;
   if ($mesg->count shift_entry()) {
	          my $dn = $entry->dn;
		  my $uid = $entry->get_value(’uid’);
		  my $orclguid = $entry->get_value(’orclguid’);
		  $info{uid} = $uid;
		  $info{orclguid} = $orclguid;
      }
   }
   return %info;
 }
	
__END__;

`

Copyright 2004-2019 Phil Lembo