Oracle Business Intelligence Beans Sample

BI User Community Synchronization with LDAP

Overview

The BI User Community Synchronization Sample demonstrates how to bootstrap and synchronize users in a given BI Beans Catalog with users in a given Oracle Internet Directory (OID, Oracle’s implementation of an LDAP Server). This sample is intended for use with an OID. If your user community is hosted in a different repository, then you can choose to implement your own form of synchronization.

Contents

This document explains the sample and also does the following:

Assumptions

A discussion of administrative tasks, such as creating and deleting users and groups and changing attributes, is beyond the scope of this document. This document and sample assume the following:

Related information

For additional information on the topics that are discussed in this document, consult the following resources:

Users and the BI Beans Catalog

Types of users

In this document, the application user is the person who invokes a BI Beans application. The application user is also referred to as the BIUser in this document and in the BI Beans Help topics. The administrator is the person who executes the scripts to add users to the BI Beans Catalog and is the audience for this document and sample. In this document, the term subject generically refers to a user or to a group.

Adding users to the BI Beans Catalog

The BI Beans Catalog is the only BI Beans component that requires user credentials. Although the BI Beans Catalog does not perform any authentication of its own, it does ensure that the user exists in the user tables in the Catalog before allowing access for that user. Therefore, you must add users to the user tables before the users can connect to the Catalog. Applications seeking connections to the Catalog must supply user credentials in the form of BIUser.

You can add users and groups to the Catalog in one of the following two ways. Use either one of these ways, but not both.

Bootstrapping and Synchronizing Users

Bootstrapping users

When the BI Beans Catalog is newly installed, it contains only one user, who is the database user that installed the Catalog. You must add users to the user tables before the users can connect to the Catalog.

Bootstrapping is the process of adding multiple users to the Catalog from an externally managed user community. The scripts that are provided with this sample allow you to bootstrap users from an OID. You should bootstrap before synchronizing users and should add only those users or groups who need to access the Catalog.

Synchronizing users

After most of the users have been bootstrapped, you should periodically ensure that the users in the Catalog match those in the external user repository, which in this sample is an OID. Synchronization is the process of updating the user tables in the BI Beans Catalog with the changes made to the user or group information in the OID. Such changes include the creation of new users and groups and the updating of user and group identities.

For example, if a new user has been added to the OID after the bootstrapping has occurred, then the synchronization script detects that addition and imports the new user to the Catalog. The script can also detect and synchronize changes to the membership of a group. (The scripts do not handle deletions of users or groups from the Catalog when users or groups are deleted in the external community.) You need to synchronize only those users or groups that need to access the Catalog.

Unidirectional synchronization

The scripts that are provided with this sample perform only a unidirectional synchronization. That is, the scripts are intended only to bootstrap and synchronize users from an OID to the BI Beans Catalog. The scripts are not meant to work in the opposite direction. The OID is considered to be the centrally managed source for user and group identity.

Interoperability with OracleAS Single Sign-On

When you execute the sample scripts, you import users and synchronize them against the OID. If a BI Beans application is integrated with the OracleAS Single Sign-On Server, then the user synchronization must be performed against the same OID that is used by the OracleAS Single Sign-On Server. Running the sample scripts allows smooth interoperability with OracleAS Single Sign-On. The user that is authenticated by OracleAS Single Sign-On against the OID has already been imported to the Catalog, and the application can present the authenticated user identity to the Catalog to gain access.

Tracking entries from the OID

The following table is created by the LDAP Sync Schema during the installation of the schema. This table tracks entries that have been imported from the OID to the BI Beans Catalog and so reveals whether a user or group originated in the OID. This table is not installed as part of the BI Beans Catalog schema.

create table LDAP_BICATALOG_ID_STORE ( CATALOG_USER_ID raw(16) references BISM_SUBJECTS(SUBJECT_ID) on delete cascade, LDAP_USER_ID VARCHAR2(2000) constraint LDAP_BICATALOG_ID_STORE primary key (LDAP_USER_ID,CATALOG_USER_ID) );

The Sample Scripts

Using scripts for bootstrapping and synchronizing

The BI User Community Synchronization Sample contains the following four sample scripts that you can use to bootstrap and synchronize the users in the BI Beans Catalog with the users in the OID:

Guidelines for running the scripts

Before setting up to run the scripts, bear the following points in mind:

How the scripts handle entries

Before running the scripts, you should understand how they handle entries from the OID, as described in the following list:

Setting Up to Run the Scripts

Setup process

To set up to run the sample scripts, perform the following steps:

  1. Install the first part of the schema by installing the DBMS_LDAP package.

  2. Install the second part of the schema by installing the LDAP Sync Schema.

  3. Catalog the appropriate attributes in the OID.

  4. Ensure that the subject name of each entry in the OID does not exceed 64 bytes.

  5. If you are using OracleAS Single Sign-On, then ensure that the user synchronization is performed against the same OID that is used by OracleAS Single Sign-On.

  6. Edit the customizable settings in the scripts that you plan to run.

One-time task: Installing the DBMS_LDAP package

The DBMS_LDAP package is provided with the Enterprise Edition of Oracle9i. You can determine if the DBMS_LDAP package has been installed in your database by entering the describe DBMS_LDAP command at the SQL*PLUS command prompt. If this describe command displays a description of the DBMS_LDAP package, then the package has been installed.

If the package is not installed, then complete the following steps:

  1. Log in to the database as the SYS user.

  2. At the SQL*PLUS command prompt, execute the catldap.sql script in the $ORACLE_HOME/rdbms/admin directory.

One-time task: Installing the LDAP Sync Schema

The LDAP Sync schema is provided with this sample and is composed of the following database objects:

A database user who has the required privileges to create procedures and tables can install these objects by executing the install_sync_schema.sql script that is provided with this sample in the downloaded file. You must run this script in the same database and in the same database user schema in which the BI Beans Catalog is installed. These database objects are not installed by the BI Beans Catalog schema.

One-time task: Cataloging attributes

To use the scripts, you must ensure that the createtimestamp and modifytimestamp attributes are cataloged in the OID. The sample scripts can see only those entries that have been added or modified after cataloging the attributes.

Examine the OID to determine if these attributes have been cataloged. If they have not been cataloged, then you can catalog them by executing the catalog.sh script in the $ORACLE_HOME/ldap/bin directory. The catalog.sh script provides help with the arguments to specify.

If the catalog.sh script is not applicable to your target platform (such as Windows NT), then you can perform the following steps to catalog the createtimestamp and modifytimestamp attributes:

  1. Access a command prompt for your platform.

  2. Change to the appropriate directory by entering a command such as the following:

    cd $ORACLE_HOME/ldap/bin

  3. Copy the catalog_attrs.ldif file from the sample download file to the current directory.

  4. Enter the following command to catalog the attributes:

    ldapmodify -h <host-name> -p <port> -D "<bindn>" -w "<bindpassword> " -f catalog_attrs.ldif

    where host-name is the host where the OID runs and port is the OID port number. The -D option must be followed by the bind distinguished name and the bind password.

The following line provides a sample ldapmodify command.

ldapmodify -h manchester.abc.com -p 389 -D "cn=orcladmin" -w "welcome" -f catalog_attrs.ldif

Making custom settings

Before running any of the sample scripts, make the required custom settings, as described in the following table. Locate these parameters in each script file, immediately after the first BEGIN statement. The LDAP administrator can provide the values for many of these parameters.

Parameter Description Applicable Scripts

C_LDAP_HOST

The name of the host where the OID server is running.

All scripts

C_LDAP_PORT

The port number where the OID is running, which is typically 389 for non‑SSL ports.

All scripts

C_LDAP_USER

The bind distinguished name.

All scripts

C_LDAP_PASSWD

The bind password.

All scripts

C_LDAP_BASE

The search root directory.

All scripts

C_SEARCH_SCOPE

The search scope.

All scripts

C_USER_TRACE

Either True or False. A value of True causes trace messages to be sent to the log file. See Consulting the log file for more information.

All scripts

C_BOOTSTRAP_FILTER

The object class that is used when searching for entries. Specify the word objectclass followed by the class name, as described in the following list:

  • For groups, enter the value(s) that is used to represent groups in the OID, such as groupofnames or groupofuniquenames.

  • For users, enter the value that is used to represent users in the OID, such as inetorgperson or person.

An example setting for groups is shown here:

C_BOOTSTRAP_FILTER = '(objectclass=groupofnames)'

Bootstrap scripts

C_GROUPS

The group names. Set this parameter if you do not have groups in the OID but you wish to group the users as they are imported to the Catalog. To add the users to multiple groups, use a setting such as the following one:

C_GROUPS(1) := 'group1';
C_GROUPS(2):= 'group2';

If you do not want to specify any groups for these scripts, then simply comment the C_GROUPS setting as shown here. Two dashes at the start of a variable name represent a comment in PL/SQL.

-- C_GROUPS;

Upon successfully retrieving users and groups from the OID, the script proceeds to add the groups (if relevant), add the users, and associate users with groups. If groups are not relevant, then the script adds each user. Finally, the script assigns any applicable privileges.

BootstrapWithoutGroups.sql and SyncWithoutGroups.sql

C_PERMISSION

The integer value 0, if you want to import users and groups but not grant privileges. One of the following integer values, if you want to import users and groups and also grant privileges on the root folder:

10 = LIST privileges
20 = READ privileges
30 = ADD privileges
40 = WRITE privileges
50 = FULL CONTROL privileges

Refer to the topics on security in the BI Beans Help system for information on privileges.

All scripts

C_USERNAME_ATTRIBUTE

The attribute from the OID to use. Do not set this attribute if you are using OracleAS Single Sign-On integrated with the OID. See User name representation in the BI Beans Catalog for complete information.

All scripts

C_COMMON_CONTAINER_DN

The distinguished name of the Common container node in the OID. Set this attribute if you are using OracleAS Single Sign-On integrated with the OID.

This is not required if you have set the C_USERNAME_ATTRIBUTE parameter.

All scripts

C_SYNC_FILTER

The object class that is used for entries. Specify the word objectclass followed by the class name, as described in the following list:

  • For groups, enter the value that is used to represent groups in the OID, such as groupofnames or groupofuniquenames.

  • For users, enter the value that is used to represent users in the OID, such as inetorgperson or person.

You must also specify values for the createtimestamp and modifytimestamp attributes. If you have already run the bootstrapping or synchronization script once, then you can obtain the values for these attributes from the log files from the previous executions of the scripts. An example specification is shown here, where x is the time stamp from the log files:

C_SYNC_FILTER=‘(&(objectclass=groupofnames)(|
  (createtimestamp=x)(modifytimestamp=x)))’

See Consulting the log file for more information.

Synchronization scripts

Running the Scripts

Before running the scripts

Before running the scripts, ensure that you have completed all the steps that are described in Setting Up to Run the Scripts.

Entering the commands that run the scripts

Complete the following steps to run the scripts:

  1. Log into SQL*PLUS using a command such as the following:

    Command prompt> sqlplus username/password@connect-string

    Complete the username, password, and connect-string variables as appropriate.

  2. Type the name of the script that you want to run. For example if you want to run the BootstrapWithGroups.sql script, then enter the following command at the sqlplus command prompt.

    sqlplus>@BootstrapWithGroups.sql

  3. When you have finished running scripts, log out of SQL*PLUS, using a command such as the following one:

    sqlplus>exit

  4. Examine the contents of the log file that is named similarly to the script that you ran, such as BootstrapWithGroups.log. Resolve any problems that the log file describes. See Consulting the log file.

Consulting the log file

When you run a script, that script creates a log file in the same directory in which the SQL*PLUS command was started. The log file has the same name as the script name, except that it has the .log extension instead of the .sql extension. For example, if you run the BootstrapWithGroups.sql script, then a corresponding BootstrapWithGroups.log file is created.

Each log file contains important error messages, warnings, and trace information, as well as a Script Execution Time field, which is stamped in UTC format to indicate the time when the script was run. The format is YYYYMMDDHH24MISS without any delimeters and is described in the following list:

For example, if you ran the script on January 1st, 2002 at 10:00 am, then the time is stamped as 20020101100000.

You can use this time stamp the next time that you run the synchronization script. Simply copy and paste the time value from a log file to the synchronization script, in the C_SYNC_FILTER parameter. Bear in mind that the log file is overwritten every time a script is run.

Use Case Scenarios

Description of scenarios

The remainder of this document provides scenarios in which you can use the sample scripts. Three scenarios are provided:

Common Directory Information Tree

For the purpose of example in our scenarios, assume that the OID has the following Directory Information Tree (DIT):

The following figure shows the DIT for a default subscriber in a non-hosted environment.  Your DIT could be different from the DIT depicted below.

DIT for a default subscriber in a non-hosted environment

Assigning privileges

You have the option of specifying privileges to the users and groups as they are being imported to the Catalog. Privileges are assigned to the root folder only. If you have groups in the OID, then privileges are assigned to the groups and the individual users. If you have no groups in the OID but you have specified the name of a group in the script to which to import users, then privileges are assigned to the specified groups and the users. If you have no groups in the OID and you have not specified any groups in the script, then privileges are assigned to the user.

Group name representation in the BI Beans Catalog

Groups in the OID that have been created with objectclass types of groupofnames, groupofuniquenames, or a similar example do not have attributes such as SN and UID. Instead, this sample uses the CN attribute for the group name. Suppose there is a group with the distinguished name cn=Group1,ou=Groups,o=abc.com. The group’s CN attribute value, which is Group1, is used to represent this group in the Catalog.

Groups with the CN attribute apply only to Use Case A and C, and not to B. For Use Case B, the sample uses the group name that is specified in the C_GROUPS setting.

User name representation in the BI Beans Catalog

A user in the OID can have many attributes associated with it. For example, you might have a user named Sam Jones with the following attributes:

CN=Sam,OU=users,O=abc.com; UID=sjones;

When you are importing users from the OID to the Catalog, you must understand which of the many attributes will represent that user in the Catalog, as described in the following list. Not all the attributes are imported for every user; only one attribute is imported.

Use Case A : All users belong to groups

Audience

If your organization has groups in the OID and if all users belong to groups, then read this use case scenario.

Overview

A group is an aggregation of users. In the BI Beans Catalog, privileges are granted to a group and to users. Each user acquires the maximum privileges of all groups to which that user belongs.

You generally run the BootstrapWithGroups.sql script only once to import the users from the OID to the BI Beans Catalog for the first time. As this script runs, it searches for groups based on the filter and search scope in a given OID and under a specified LDAP base.

After the initial bootstrapping is done, you can use the SyncWithGroups.sql script to synchronize the users as often as necessary. As this script runs, it searches for any groups that have been added or modified since the time stamps that are specified in the script’s parameters. The script detects any new groups and membership changes to groups, and the script propagates these changes accordingly.

How the scripts work

The following list describes how either the bootstrapping script or the synchronization script works, based on the specified criteria (such as filter, scope, and base).

Deleting groups and users

The synchronization script detects membership changes to a group and propagates those changes accordingly. For example, the script detects changes such as particular users being added to or deleted from a group or a user being moved from one group to another. The script does not handle the actual deletion of users or groups. If a user or a group has been deleted from the OID, then the script does not automatically delete that user or group from the Catalog. The script can only track membership changes to a group. You can decide to remove that user manually from the BI Beans Catalog.

Use Case B : No users belong to groups

Audience

If your organization does not have groups in the OID, then read this use case scenario.

Overview

Recall that the BI Beans Catalog grants privileges based on groups and users. Even if there are no groups in the OID, you can still specify a group that users belong to, so that users from the OID are added to the specified group. If you want to add users in the OID to one or more groups, then use the C_GROUPS parameter in the BootstrapWithoutGroups.sql and SyncWithoutGroups.sql. scripts to specify the groups. All of the users that the script retrieves from the OID become part of all of the groups that you specified.

You generally run the BootstrapWithoutGroups.sql script only once to import the users from the OID to the BI Beans Catalog for the first time. As this script runs, it searches for users based on the filter and search scope in a given OID and under a specified LDAP base.

After the initial bootstrapping is done, you can use the SyncWithoutGroups.sql script to synchronize the users as often as necessary. As this script runs, it searches for any users that have been added or modified since the time stamps that are specified in the script’s parameters. The script detects any new users and changes to user identity, and the script propagates these changes accordingly.

Additional information

Refer to How the scripts work for an explanation of how the bootstrapping script and the synchronization script work.

Refer to Deleting groups and users for an explanation of how to remove groups and users.

Use Case C: Some users belong to groups and some users do not belong to groups

Audience

If your organization has some users that belong to groups and some user that do not belong to any groups, then read this use case scenario. Bear in mind that you need to import only those users or groups that need access to the Catalog. This scenario is a combination of use cases A and B, as described above.

Overview

You must run the BootstrapWithGroups.sql script to import those users that are part of groups. Then run the BootstrapWithoutGroups.sql script to import those users that do not belong to any group. You can periodically run both the SyncWithGroups.sql and SyncWithoutGroups.sql scripts to synchronize the user community.

For more information, refer to the descriptions of Use Case A and Use Case B.


Copyright © 2004 Oracle Corporation.
All Rights Reserved.