Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Bulk Loading Users for Single Sign-on

Expert Oracle Tips by Burleson Consulting

Bulk Loading Users for Single Sign-on

Introduction

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.

OID, LDAP, and LDIF

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 mycomp.com. 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
mail: someones@mycomp.co
m

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.

set serveroutput on

begin
  dbms_output.enable(100000); -- big buffer
  for r_c1 in (select * from employee) loop
    dbms_output.put_line(
      'dn: cn='||initcap(r_c1. employee _first_name)||
      ' '||initcap(r_c1. employee_last_name)||
      ',cn=Users,dc=mycomp,dc=com');
    dbms_output.put_line('cn: '||initcap(r_c1. employee _first_name)||
      ' '||initcap(r_c1. employee _last_name));
    dbms_output.put_line('sn: '||initcap(r_c1. employee _last_name));
    dbms_output.put_line('objectclass: top');
    dbms_output.put_line('objectclass: person');
    dbms_output.put_line('objectclass: inetorgperson');
    dbms_output.put_line('objectclass: organizationalPerson');
    dbms_output.put_line('objectclass: orcluser');
    dbms_output.put_line('objectclass: orcluserv2');
    dbms_output.put_line('givenname: '||
      initcap(r_c1. employee _first_name));
    dbms_output.put_line('uid: '||r_c1. employee _last_name);
    dbms_output.put_line('userpassword: welcome1');
    dbms_output.put_line(
      'mail: '||r_c1. employee _last_name||
      substr(r_c1. employee _first_name,1,1)||'@mycomp.com');
  end loop;
end
;

The previous block of code will produce the LDIF text file that the utility ldapadd uses to load each user into OID. If you have additional information about your employees, you may include it. In my example, I only use the employees' first and last names to generate the email, login (uid), and so on. After adding a blank line after each entry, my LDIF file looks like the following partial listing:

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
mail: jonesm@mycomp.com

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
mail: hestera@mycomp.com

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.

 

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster