Skip to content

Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Bulk Loading Users for Single Sign-on
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548

Bulk Loading Users for Single Sign-on

by John Garmany


Oracle’s Single Sign-on (SSO) application is tightly integrated into the Oracle Application Server 10g. If you implement Forms or Portals, you will most likely use SSO to validate and authenticate users. But, SSO has powerful integration capabilities allowing it to authenticate users for internally designed and legacy applications. To use the convenience and capabilities of SSO, your users must have SSO accounts. These accounts are stored in the Oracle Internet Directory (OID), an LDAPv3 compliant directory service that is part of the Oracle Application Server. SSO uses OID to validate and authenticate user credentials before allowing access to the applications it protects. In this article, I will introduce a method to load users into OID for use by SSO.

Once you have your portal application developed and deployed onto your application server, it’s time to open it up to the company. You will need to add a couple hundred employees to Single Sign-on, so they can start accessing the application. You could use the Oracle Internet Directory Self Service Console to add the basics of each employee (user name, password, and so on) and then let the employees sign on and update all the other personal information. This is what the Self Service Console was designed for — self-maintenance of account data. But this process entails adding each employee individually, a time consuming task. What you need is a way to add all the basic employee information into OID in bulk, and Oracle provides a utility to do just that. If up-to-date employee information is currently stored in an LDAP directory, you may be able to dump the data in a format that can be directly loaded into OID.

There are actually two utilities that will load data into OID: ldapadd and bulkload. The utility called ldapadd reads a flat file in LDIF format and inserts each entry into OID. The utility bulkload can load the data into OID, but it also has the capability to bypass OID and use SQL*Loader to load the data directly into the metadata repository database tables. If you are loading thousands of records, bulkload is the way to go. For our few hundred employees, we will use the utility ldapadd.


The first step is to get the employee data into LDAP Data Interchange Format (LDIF) so the utility will know where to load it into OID. (The internal workings of an LDAP directory or the Oracle Internet Directory are beyond the scope of this article.) In short, an LDAP directory is organized like a tree, with a root node and a series of descending nodes as shown in Figure 1. You can think of it as a file system with subdirectories leading to a user subdirectory that contains a set of objects and attributes that define that user.

Figure 1: LDAP directory tree structure.

When you installed the Oracle Application Server, you created a context in OID that can be thought of as a new root node. You need to tell ldapadd in what tree to add your data, and to do this, you use an LDIF file. The LDAP Data Interchange Format was designed to move data between LDAP directories; it is also used to load new data into a directory. An LDIF file contains one or more entries, starting with a distinguished name and ending with a blank line. The distinguished name identifies the node where the objects and attributes are stored. In the example in Figure 1, the distinguished name is:

dn: cn=garmanyj, cn=Users, dc=mycomp, dc=com

Note that the LDIF data is case sensitive.

Now all the attributes and object classes in this entry will be placed in the node shown at the bottom of figure 1, labeled garmanyj. If additional information about LDIF is needed, you can Google “LDIF LDAP” for a long list of articles. For our purposes, we want to load our employees into OID, so we are limited to those attributes already defined as an SSO user.

The easiest way to find these attributes is to add a user to OID using the Self Service Console and then use the utility oidadmin to locate the necessary information. Before starting oidadmin, OID must be running and you need to set the environmental variables for the infrastructure instance. On my system, that is:

# export $ORACLE_HOME=/opt/oracle/infra10g
# export $ORACLE_SID=asdb
# export $PATH=$ORACLE_HOME/bin:$PATH

All the utilities in this article are located in the infrastructure’s ORACLE_HOME/bin directory.

Log into oidadmin as the cn=orcladmin user, utilizing the password that was set during installation. Once in the oidadmin program, expand the Oracle Internet Directory Servers, the cn=orcladmin, and the Entry Management tabs. When I installed the application server, I created the context Therefore, in oidadmin, I would expand the dc=com and the dc=mycomp tabs next. Lastly, expand the User tab to get a list of the current users in that OID context. Hereafter, if I log onto OID as cn=orcladmin, I need to identify each user as:

dn: cn=<username>, cn=Users, dc=mycomp dc=com

With this data, OID will know where to place each user within the directory tree.

The next step is to create the user data. You can utilize oidadmin to locate the information that you want to add to each account. If you select the user entry created in the OID Self Service Console, you will get a list of the user’s attributes or properties in the right window of oidadmin. Each property has a name and a value. Some values must be included in order for the user to sign into SSO. Below are the properties I will be adding to my users as I load them using the ldapdd utility.

dn: cn=Sam Someone,cn=Users,dc=mycomp,dc=com
cn: Sam Someone
sn: Someone
objectclass: top
objectclass: person
objectclass: inetorgperson
objectclass: organizationalperson
objectclass: orcluser
objectclass: orcluserv2
givenname: Sam
uid: someones
userpassword: welcome1

The last three properties define the minimum entries required by the Self Service Console. The uid and userpassword define the user name and password requested by SSO to authenticate.

Creating the LDIF File

Now that you have the information you need, the next step is to create the text file with the data for each employee. For this example I will pull the data from an Oracle database table and create the file using a simple PL/SQL anonymous block.

dn: cn=Mark Jones,cn=Users,dc=mycomp,dc=com
cn: Mark Jones
sn: Jones
objectclass: top
objectclass: person
objectclass: inetorgperson
objectclass: organizationalPerson
objectclass: orcluser
objectclass: orcluserv2
givenname: Mark
uid: jones
userpassword: welcome1
dn: cn=Alvis Hester,cn=Users,dc=mycomp,dc=com
cn: Alvis Hester
sn: Hester
objectclass: top
objectclass: person
objectclass: inetorgperson
objectclass: organizationalPerson
objectclass: orcluser
objectclass: orcluserv2
givenname: Alvis
uid: hester
userpassword: welcome1

dn: cn=Erin Weaton,cn=Users,dc=mycomp,dc=com
cn: Erin Weaton
sn: Weaton

As you can see, each entry begins with a distinguished name and ends with a blank line.

Loading Users with ldapadd

Now that we have the LDIF file ready, it is time to load it into OID. First, we need to ensure that we can bind or connect to OID. You will use a utility called ldapbind, which — if the connection is successful — will return the message, “bind successful.” Since I am working on the application server infrastructure, the OID host is localhost. If you are not on the infrastructure server or are connecting to another OID instance on another server, substitute the server name or IP from localhost, as shown in the following examples.

First, check your ability to connect to OID.

[oracle]$ ldapbind –p 389 –h localhost –D “cn=orcladmin” –w app432
bind successful

In the previous example, the parameters passed are:

         -p: OID Port  defaults to 389 non SSL
  -h: server where OID is running
  -D: user name, here we are using the default administrator orcladmin
  -w: user’s password, for orcladmin the password was defined during installation.

If you received an error, ensure that the environment is set for the infrastructure and that the ldapbind program is in your PATH.

Once we have confirmed that we can bind successfully with OID, it is time to load the employees from the LDIF file. For this, we use the utility ldapadd, passing the same parameters plus the file name using the –f parameters.

[oracle]$ ldapbind -p 389 -h localhost -D -cn=orcladmin -w welcome /
           -f emp.ldif
adding new entry cn=Mark Jones,cn=Users,dc=mycomp,dc=com
adding new entry cn=Alvis Hester,cn=Users,dc=mycomp,dc=com
adding new entry cn=Erin Weaton,cn=Users,dc=mycomp dc=com
adding new entry cn=Pierre Jeckle,cn=Users,dc=mycomp,dc=com
adding new entry cn=Lester Withers,cn=Users,dc=mycomp,dc=com
adding new entry cn=Juan Petty,cn=Users,dc=mycomp,dc=com
adding new entry cn=Louis Clark,cn=Users,dc=mycomp,dc=com
adding new entry cn=Minnie Mee,cn=Users,dc=mycomp,dc=com
adding new entry cn=Dirk Shagger,cn=Users,dc=mycomp,dc=com
adding new entry cn=Diego Smith,cn=Users,dc=mycomp,dc=com

If an entry contains errors, the ldapadd utility will stop and show an error message. You can fix the error in the LDIF file, delete the entries already processed, and rerun the utility to load the remaining employees. To verify the entries, you can log onto oidadmin or start the OID Self Service Console and log on as one of the new users. Entries already processed are not rolled back and will remain in the OID if ldapadd encounters an error.

You are now ready for your users to log onto the OID Self Service Console and update any data not already entered. Afterwards, they can go to the Oracle Portal and log in through SSO.

In my small example, I loaded 10 users and it required about 11 seconds for the ldapadd utility to process all 10 entries. Thus, loading up to a few hundred records would not be a problem for the utility ldapadd. But, if you have to load many thousands of entries, you will want to use the bulkload utility, which would use SQL*Loader to load entries directly into the OID database tables.

Special for readers:DBA Scripts to Manage Oracle Application Server,” from the new book, Oracle Application Server 10g Administration Handbook, by John Garmany and Don Burleson.


John Garmany is a senior DBA with Burleson Consulting. John is a graduate of West Point, an Airborne Ranger and a retired Lt. Colonel with 20+ years of IT experience. John is an OCP Certified Oracle DBA with a Master's Degree in Information Systems, a Graduate Certificate in Software Engineering, and a BS degree (Electrical Engineering) from West Point. A respected Oracle expert and author, John serves as a writer for Oracle Internals, DBAzine, and John is the author of Logical Database Design — Principles & Practices, CRC Press; the Oracle Database 10g Application Server Administration Handbook, Oracle Press; and Oracle Replication — Snapshot, Multi-master & Materialized Views Scripts and Oracle SQL*Plus Reports — Fast reporting with SQL and SQL*Plus, Rampant TechPress. John can be reached at

Contributors : John Garmany
Last modified 2005-06-22 12:48 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone