# 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:

- Provides information about users and the BI Beans Catalog.

- Explains the processes of bootstrapping and synchronizing users.

- Provides information and use case scenarios for running the scripts.

- Describes the requirements for setting up your system to run the scripts and manage users.

- Explains how to run the scripts.

### 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:

- That an administrator has integrated Oracle Application Server Single Sign-On with an OID to function as the user repository.

- That the user of this document and sample understands the OID, LDAP, and OracleAS Single Sign-On.

### Related information

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

- The BI Beans Help system, especially the topics on managing security

- Oracle Internet Directory documentation

- OracleAS Single Sign-On documentation

- PL/SQL User’s Guide

## 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.

- By automatically importing users from an external user community store such as an OID, using scripts that ship with this sample.

- By manually adding users who are not part of an external community, using scripts that ship with BI Beans.

## 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:

- `BootstrapWithGroups.sql` and `SyncWithGroups.sql`: Use these scripts when the OID contains user groups.

- `BootstrapWithoutGroups.sql` and `SyncWithoutGroups.sql`: Use these scripts when the OID does not contain user groups.

### Guidelines for running the scripts

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

- The sample scripts are implemented in PL/SQL and must be executed using the SQL*PLUS command prompt.

- The sample scripts can synchronize only with the OID.

- The sample scripts do not work with nested groups. Groups cannot belong to groups.

- While the sample scripts are running, BI Beans Catalog users can experience downtime because of updates to the Catalog user tables. Run these scripts when no users are connected to the Catalog.

- You generally run the bootstrapping scripts only once. You can run the synchronization scripts as often as necessary. Consult the use case scenarios later in this document for information on when to use the various sample scripts.

### 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:

- When a bootstrapping or synchronization script runs and encounters a subject that already exists through manual entry in the BI Beans Catalog, then the script skips over that subject. However, if the script created the subject in a previous run, then the script recognizes and updates the subject appropriately.

- If the OID has two subjects with the same identity, then the script moves only the first subject's entry to the BI Beans Catalog.

- If a group contains invalid members, then the script skips the invalid entries.

- If the OID contains thousands of entries, then the scripts can take several minutes to run.

## 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:

- LDAP_BICATALOG_SYNC_UTIL package

- LDAP_BICATALOG_ID_STORE table

- ListObject object

- List collection

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:

- YYYY is the four digit year.

- MM is the two digit month.

- DD is the two digit day.

- HH24 is the hours in 24 hour format, while MI is minutes and SS is seconds.

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:

- Use Case A: Users belong to groups.

- Use Case B: Users do not belong to groups.

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

### Common Directory Information Tree

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

- A corporation called ABC, Incorporated has abc.com as its root entry in the OID.

- The root entry has two child entries called "users"and "groups."

- The "groups" entry is the node in which groups are stored. In this example, there are 2 groups called Group1 and Group2. Among other attributes, each group entry has a multivalued attribute called "member," which holds the distinguished names of the users who comprise that group. Group entries holds references to the user entries that are stored elsewhere in the OID. Some organizations may not have groups in the OID.

- The "users" entry is the node in which users are stored. A sample user is `cn=Sam Carter,ou=users,o=abc.com`. In this example, there are 3 users called Cindy, Bob, and Sam.

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.

### 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.

- OracleAS Single Sign-On uses a setting called orclcommonnicknameattribute in the OID. The purpose of this setting is to store the attribute (such as CN or UID) that identifies a user who is logging in. The setting is made on the Common container object that resides in your DIT. This object has the following DN:

  `cn=Common,cn=Products,cn=OracleContext,<your site information or subscriber dn>`

  The DN for the Common container must be set in the scripts. The bootstrapping and sychronization scripts in this sample by default query the OID to determine the value of this setting. The scripts use this value for representing user names in the Catalog, so that the user names that are retrieved by OracleAS Single Sign-On match the users that were imported into the Catalog. If the OID is integrated with OracleAS Single Sign-On, then you must allow the scripts to query the orclcommonnicknameattribute setting and use its value every time that the scripts run. In this case, set the C_USERNAME_ATTRIBUTE to null and specify a value for C_COMMON_CONTAINER_DN which is the distinguished name of the Common container object.

- If you are not using OracleAS Single Sign-On, then you can instruct the scripts to use a different attribute. Specify the attribute that you want to use by setting the C_USERNAME_ATTRIBUTE parameter. In this case, you do not need to set the C_COMMON_CONTAINER_DN parameter.

## 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).

- The script gathers all the groups that are likely to be added or updated in the Catalog and traverses each group, listing its members. The script queries the value of orclcommonnicknameattribute or another specified attribute and retrieves the user name for each of the members. The script makes a list of all the user names that belong to each group and adds the names to the Catalog.

- The script handles the absence or existence of users and groups in the BI Beans Catalog in the following ways:

- If the user and the group do not exist, then the script creates the user and the group in the Catalog and forms an association between the user and the group.

- If the user already exists and if the user was not originally imported from the OID, then the script logs a message and does not add that user to a group.

- If the group already exists and if the group was not originally imported from the OID, then the script logs a message and does not import that group and its members.

### 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.