list accounts on the EBS database

Kind of related to the previous post.

I'm an sqlplus beginner. So I think this is really important info. Someday soon I'll be embarrassed I even bothered to write this.

Log onto the EBS database as the APPS user.

sqlplus APPS/{appspw]@[dbname]


Then do this:


SQL> SPOOL /tmp/userlist.txt;
SQL> SELECT USER_NAME FROM FND_USER;
...
SQL> SPOOL OFF;
SQL> quit


You'll get a nice list of all the User ID's on the database. The "SPOOL" command will print the output to the file indicated, so you don't have to do a screen scrape.

If you want everything in the table substitute '*' for 'USER_NAME'.

linking an OID orclguid with a USER_GUID in EBS

Unless you're actually looking for the answer this article provides, you probably won't have any idea what I'm talking about.

Here's the situation. You've put up an Oracle 10g infrastructure, complete with it's own Oracle Internet Directory (OID). By some unknown magic, your 10g infrastructure (including OID and Single Sign-on, SSO) has just been integrated with an existing Oracle Enteprise Business Suite (EBS) Applications instance.

Part of the integration involves setting up automatic provisioning of user accounts in EBS from entries created in OID. You create a new user in OID, and voila! the user gets an account (albeit one with limited access) in EBS.

Your DBAs, developers, consultants, admins and just about everyone who can draw a breath have been haphazardly creating and deleting accounts in both OID and EBS both before and after the integration. Ugh.

Finally, as expected, some of those accounts are now out of sync. You try to create a new account on EBS for an existing OID user and you get the dreaded "account already there" error. Checking the FND_USER table on the EBS database you find that, sure enough, it is there. But no matter how hard you try, you can't seem to get SSO to log you into EBS. The OID entry won't link to the EBS account. Instead of jumping up and down screaming, calm down. Here's what you do.

Log onto the server hosting EBS as the system user (something like "oracle" or "oraebs"). If the .bash_profile for this user doesn't set the environment for you, you'll need to source it -- hopefully from a file you've created like "ebs.env".

First get the orclguid value from the corresponding OID entry. Since this is a system attribute, it won't show up in a normal search. You need to specify it.

ldapsearch -h [oidhost] -D "cn=orcladmin" -w [adminpw] -b "dc=my,dc=corp" \
-s sub "(cn=[userid])" orclguid


This will return a long string of numbers and letters for orclguid. Highlight and copy it.

Now fire up sqlplus and connect to the EBS database as the "APPS" user.

sqlplus APPS/{appspw]@[dbname]


Now follow the following procedure:


SQL> SELECT USER_GUID FROM FND_USER
WHERE USER_NAME='[EBS User ID]';


Where 'USERID' is the ID of the user account you need to fix.

SQL> UPDATE FND_USER
SET USER_GUID = '[orclguid value]'
WHERE USER_NAME = '[EBS User ID]';
...
SQL> COMMIT;
...
SQL>quit


There it is.

ldap browser on linux


Jarek Gawor's LDAP Browser-Editor has been one of the most popular LDAP tools in use by directory admins for at least 7 years. Never mind that the last release of the software was v2.8.2b2 (that's Beta 2) in 2001, it remains top on my list because, warts and all (not the least of which is that it was programmed using Java Swing), it is still the best at what it does.

Installing and configuring it on Linux has never been hard, unless you want to make it play nice in a multi-user environment. If you don't want to force each user to install their own personal copy, you're going to have to make some changes to the shipping shell script used to launch the app and make a dot directory in your user's home.

My standard setup puts the unarchived app code into a directory called /opt/ldapbrowser, which I normally create by simply copying Browser282b2.tar.gz to /opt and doing a tar czf on it right there.

Next, I backup the shipping lbe.sh and replace it with my own:

#!/bin/sh
JAVA_HOME=/usr/java/jdk1.5.0_11
LBE_ROOT=/opt/ldapbrowser

cd ~/.lbe

${JAVA_HOME}/bin/java -jar ${LBE_ROOT}/lbe.jar $1 $2 $3 $4 $5 $6 $7 $8 $9

Then I run a symlink from the new lbe.sh to /usr/bin/lbe:
ln -s /opt/ldapbrowser/lbe.sh /usr/bin/lbe

Finally, I go and create an .lbe directory in my user's home and copy attributes.config from /opt/ldapbrowser into it. I also run symlinks from /opt/ldapbrowser/help and /opt/ldapbrowser/templates into .lbe, so the resources in those directories will display properly.

Changing Active Directory Passwords Using LDAPS

This is an article from my old personal site.

Which is the point of what came before ...

Just a brief script to show how to change an Active Directory user's password using LDAPS (LDAP over SSL). Requires that the target Active Directory domain controller be SSL enabled, as described in this article. In Active Directory password data is not stored in userpassword, but instead the hidden "system" attribute, unicodePwd. The script contains a routine used to convert the ASCII string supplied for the password into it's Unicode equivalent.

The "require" line simply imports config info (bind dn and password, etc.) for the script to use.


#!/usr/bin/perl

use Net::LDAP;
use Net::LDAP::Entry;
use Unicode::Map8;
use Unicode::String qw(utf16);

our($adHost,$adAdmin,$adPass);
require "../etc/ldap.inc";

my $adURI = "ldaps://$adHost";
my $basedn = "DC=test,DC=example,DC=com";
my @attrs = qw(cn sn givenname uid mail unicodePwd);
my $query = "(cn=orson)";
my $newpw = "rosebud";

my $charmap = Unicode::Map8-> new('latin1') or die $!;
$newpw = $charmap-> tou('"'.$newpw.'"')-> byteswap()-> utf16();

my $ldaps = Net::LDAP-> new( $adURI ) or die "$@";

my $mesg = $ldaps-> bind($adAdmin, password =>$adPass);

$mesg = $ldaps-> search (
base => $basedn,
scope => 'sub',
filter => $query,
attrs => \@attrs
);

while (my $entry = $mesg-> shift_entry()) {
my $userdn = $entry-> dn;
print $userdn, "\n";
# $entry-> dump;
$entry-> replace('unicodePwd' => $newpw);
$entry-> update($ldaps);


}

$ldaps-> unbind;

__END__;

LDAPS with Net::LDAP

This is an article from my old personal site.

This script is an example of how to make an LDAP over SSL connection. LDAPS requires a special secure port, usually TCP port 636. The target server in this instance is a Microsoft Active Directory domain controller that has been SSL enabled (a configuration discussed in another article).

#!/usr/bin/perl

use Net::LDAP;

our($adHost,$adAdmin,$adPass);
require "../etc/ldap.inc";

my $basedn = "DC=test,DC=example,DC=com";
my @attrs = qw(cn sn givenname uid mail unicodepwd);
my $query = "(cn=Administrator)";

my $ldaps = Net::LDAP-> new( "ldaps://$adHost" ) or die "$@";

my $mesg = $ldaps-> bind($adAdmin, password => $adPass) or die "$@";

$mesg = $ldaps-> search (
base => $basedn,
scope => 'sub',
filter => $query,
attrs => \@attrs
);

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

$entry-> dump;

}

$ldaps-> unbind;

__END__;

SSL Enabling Active Directory

This is an article from my old personal site.

There are several ways to do this. The easiest is to enable an Enterprise CA and let auto-enrollment propagate SSL certificates to all domain controllers (DC's). This is Microsoft's preference. Most Active Directory (AD) administrators are loathe to go along with this. First, because they believe SSL is an evil virus created by Netscape, and second, because they're afraid to alter the shipping configuration of AD in any way. From their point of view AD is already complicated enough without mucking things up with some Internet standard protocol functionality. As a result of this, most real-world LDAP admins compromise on getting at least one DC enabled for SSL. The most non-intrusive way to do this is to use a 3rd party certificate authority (CA) to sign an appropriately formed certificate request. The resulting certificate is then imported, along with the CA's own certificate, into the target DC's local certificate store.

Official Documentation
How to enable LDAP over SSL with a third-party certification authority

Best Practices for Implementation of a Microsoft Windows Server 2003 Public Key Infrastructure

The first article listed above is the official "how to" from Microsoft on using a 3rd party CA to SSL enable AD. It is written by someone who's obviously done this many times, and because of that is not a very good beginner's guide. Reading through all of the articles linked in the second reference will give you most of what you need. Still, I found that there was no substitute for building my own test AD domain controller and diving right in to the deep end of the pool. Your mileage may vary (please keep in mind that I first "cut my teeth" on Windows NT 3.51 in an enterprise environment over 10 years ago, and had an MCSE number in the low thousands). Even so, on my first go around, I installed Windows 2003 in a VMware Server virtual machine. Happiness is being able to roll back to the last snapshot when bad things happen.

Prerequisites
You'll need a working AD environment, in which all services (especially DNS) are properly configured. You'll also need a 3rd party CA. In my case this was an OpenSSL CA I'd set up on a Linux box.

Generating the Certificate Request
To do this you need to follow the instructions set out in the first article cited above exactly, but for one major bit of misinformation.

In describing the request.inf file used with the certreq utility to generate the request, the article adds a caveat to the effect that some CA's might require additional elements in the "Subject" line -- such as e-mail address, organization name, etc. Adding these elements, however, will guarantee that you won't be able to successfully import the signed certificate. The reason? Because the local computer certificate store (a/k/a the "MY" certificate store) will not have any of these elements and so the distinguished name will not match that of the machine.

Here is the actual request.inf I used to generate my request. Note that the values I used for the different elements should be changed to match your environment:

 ;----------------- request.inf -----------------

[Version]

Signature="$Windows NT$

[NewRequest]

Subject = "CN=dc01.test.mydomain.com"
KeySpec = 1
KeyLength = 1024
Exportable = TRUE
MachineKeySet = TRUE
SMIME = False
PrivateKeyArchive = FALSE
UserProtected = FALSE
UseExistingKeySet = FALSE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
KeyUsage = 0xa0

[EnhancedKeyUsageExtension]

OID=1.3.6.1.5.5.7.3.1 ; this is for Server Authentication


Once you have your request.inf file, you must run the following command to generate the request. Do this while logged in as the machine Administrator (cn=Administrator, cn=Users, dc=test, dc=domain,dc=com or Administrator@test.domain.com):

certreq -new request.inf newreq.pem

The resulting request file, newreq.pem can now be submitted to a 3rd party CA for signing.

Signing the Request
To sign the certificate request using OpenSSL, you need to transfer the file to where your "demoCA" has been set up. In my case this is at /export/CA on a CentOS Linux box. I like the CA script for this kind of work. It expects the request file to be named newreq.pem by default. As root, I use the following command after changing to /export/CA and making sure that newreq.pem is located there:

/usr/share/ssl/misc/CA -sign

After supplying the CA's secret (password), I accept the defaults and the new signed certificate, by default named newcert.pem is created alongside the newreq.pem file. I usually rename the certificate file to something more unique, in this case dc01.cer.

Importing the Certificate(s)
A careful reader will notice I add a "(s)" to the end of this section title. That is because to make this all work you need to import into Active Directory not only your newly signed certificate, but also the certificate for the CA that signed it. The procedure for importing these certificates differs. Make sure to do it exactly as described. You can use the Certificates Snap-in to import the server certificate, but it will not report some kinds of errors. To be safe, use the command line tool. First transfer the certificate to the DC filesystem, and then, while logged in as Administrator, run the following command:

certreq -accept dc01.cer

Now add the Certificates (not the Certification Authority) Snap-in to your Administrative Tools menu (run mmc, add the snap-in and save as a new console object), being careful to specify that it will manage certificates for the "Computer Account". Use the snap-in to verify that the new server certificate exists under Certificates/Personal/Certificates. At this point the new certificate will be considered suspect, since the system will not recognize the signing CA. To cure this, transfer the CA's public certificate (without the key) to the DC filesystem and import it into the "Third Party Root Certificate Authorities" container using the Certificates Snap-in.

Effect and Verify the Change
Once the above steps are completed, reboot the DC system to make the change effective and enable the LDAPS (LDAP over SSL) listener on the DC. To verify that it is now working, use the ldp.exe from the Windows Support Tools to connect to the local AD instance, by checking off "SSL" and changing the port number to 636.
You can also test the SSL connection using the ldapsearch utility. Before doing this, copy the CA's certificate to the local filesystem (I use a system-wide location of /etc/ssl, you could also use the default /etc/openldap/cacerts) and configure ldap.conf to include the full path to this cert in the TLS_CACERT directive (e.g. TLS_CACERT /etc/ssl/myca.pem). Once you've disposed of these preliminaries, try the following command:

ldapsearch -x -H ldaps://dc01.test.mydomain.com \
-D "cn=administrator,cn=users,dc=test,dc=mydomain,dc=com -w mypass \
-b "dc=example,dc=com" -s base "objectclass=*"

LDAP over TLS with Net::LDAP

This is an article from my old personal site.

Following is a simple script using LDAP over TLS (start_tls). Like LDAP over SSL (LDAPS), communications are done over a secure channel. In the case of TLS, however, those communications happen over port 389 instead of a dedicated secure port (LDAPS defaults to port 636). The "require" line is used to import host, userdn and password values from a separate configuration file.
#!/usr/bin/perl

use Net::LDAP;

our($dirHost,$dirUsr,$dirPass);

require "../etc/config.inc";

my $basedn = "dc=example,dc=com";
my @attrs = qw(cn sn givenname uid mail);
my $query = "(cn=*)";

my $ldap = Net::LDAP-> new( $dirHost );
my $mesg = $ldap-> start_tls(verify=> 'require',
cafile => '/etc/ssl/cacert.pem'
);
# die $mesg->error() if $mesg-> code();

$mesg = $ldap-> bind($dirUsr, password => $dirPass);

my $mesg = $ldap-> search (
base => $basedn,
scope =>'sub',
filter => $query,
attrs => \@attrs
);

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

$entry-> dump;

}

$ldap-> unbind;

__END__;

checkfields.pl

This is one of those works-in-progress that may have some use to others, so I'll share it here.

Oracle DBA's called this morning wanting to know if the length of any of the fields in a delimited file I send them changed (got longer). Told them I had no idea. LDAP doesn't care about field length. Makes it easier to use as a white pages platform if you don't have to keep track of how many characters are in the surname in each entry.

Anyway, because I'm such a nice guy I told them I'd go find out what the largest number of characters were in each field of the file.

Here's another of my (infamously) simplistic Perl scripts that I used to get the info:

#!/usr/bin/perl
# checkfields.pl Audit a delimited LDAP feed
# Read in pipe-delimited feed, count number of chars in each field to get highest
# total. For use in reporting number of chars that import needs to
# accomodate for each field.

use Text::ParseWords;

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

my $feedfile = "$HOME/tmp/ldap.dat";
my %wholefeed;

my $totfields;


print "Checking $feedfile\n";

open FH, "<$feedfile" or die $!;

while (<FH>) {

my @line = &parse_line('\|',0,$_);

$totfields = scalar(@line);

my $fieldct =0;

my $index = @line[0];

foreach my $field(@line) {

my $fieldno = $fieldct++;
my $newlen = length($field);

my $oldlen = $wholefeed{$fieldno};

if ($newlen >$oldlen) {
$wholefeed{$fieldno} = $newlen;

}

}

}

print "Total Fields: $totfields\n";

print "Field,Chars\n";

my $count;

for ($count =0; $count <$totfields; $count++) {
print $count, ",", $wholefeed{$count}, "\n";
}

__END__;