Using Perl to Re-Link EBS GUIDS

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. This happens "automagically" in most cases, but in our situation there was a last-minute decision to change our DIT (Directory Information Tree) structure that required the deletion and re-creation of all our new OID entries AFTER they'd been linked to their corresponding EBS account.

To avoid having to use the tedious procedure Oracle recommends to fix this that I outlined earlier, 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.

#!/usr/bin/perl
# updateguid.pl Updates USER_GUID on EBS FND_USER from orclguid value 
# in corresponding OID user entry.

use strict;
use Net::LDAP;
use Net::LDAP::Entry;
use Net::LDAP::LDIF;
use DBI;


our($OIDhost,$OIDusr,$OIDpw,$ebssid,$ebsappusr,$ebsapppw);

my $HOME = $ENV{'HOME'};

$ENV{'TNS_ADMIN'} = "/etc/oracle";

require "$HOME/etc/orclapp.conf";

my $usrbase = "cn=users,dc=mycorp,dc=com";
my $query = "(givenname=*)"; # Just hit the "real" people
my @attrs = qw(uid orclguid);


fix_guids();

sub fix_guids {

  my $ldap = Net::LDAP->new($OIDhost);
  my $mesg = $ldap->bind($OIDusr, password =>$OIDpw);
  die ("failed to bind with ",$mesg->code(),"\n") if $mesg->code();

  $mesg = $ldap->search( base =>$usrbase,
           filter =>$query,
    scope =>'sub',
    attrs =>\@attrs

 );

  die "Failed to search with ",$mesg->error(),"\n" if $mesg->code();

  while (my $entry = $mesg->shift_entry()) {

      my $dn = $entry->dn();
      my $uid = $entry->get_value('uid');
      my $orclguid = $entry->get_value('orclguid');

      ## Use this code block for testing guid retrieval
      # my $user_name = read_ebsdb($uid);
      #print $user_name, "\n";
      # print $orclguid, " \n";
      # my $user_guid = read_ebsdb($uid);
      # print $user_guid, "\n";

      mod_ebsdb($uid,$orclguid);
    

  }

}



# This subroutine is for testing guid retrieval
sub read_ebsdb {

  my $user_name = @_[0];

  my $dbh = DBI->connect("dbi:Oracle:$ebssid",
   "$ebsappusr",
   "$ebsapppw",

 ) or die "Database not connected: $DBI::errstr";

  my $sql = "SELECT USER_GUID
      FROM FND_USER
      WHERE USER_NAME = '$user_name'";

  my $sth = $dbh->prepare($sql);

  $sth->execute();

  while (my ($user_guid)  = $sth->fetchrow()) {

      return $user_guid;

  }

  $sth->finish;
  $dbh->disconnect;
  
}

# This is the subroutine that commits changes to EBS FND_USER
sub mod_ebsdb {

  my $user_name = @_[0];
  my $orclguid = @_[1];

  # Use AutoCommit =>0 to allow rollback in case of error  
  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 = '$orclguid'
             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 $orclguid\n";

  }

}

__END__;