# Oracle Business Intelligence Beans Sample

## BI User Community Synchronization with RDBMS

### Overview

The BI User Community Synchronization with RDMBS Sample demonstrates how to bootstrap and synchronize users and groups in a given BI Beans Catalog with users and roles in a given Oracle RDBMS.

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

- Explains how to run the script.

- Describes the process flow of the script.

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

- The user has DBA access to the RDBMS to get information about users and roles

- The user has access to SQL*Plus.

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

- PL/SQL User’s Guide

- Oracle DBA Handbook

## 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 RDBMS, 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, namely the database user that installed the Catalog. You must add users to the user tables before any 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 external RDBMS. You should bootstrap before synchronizing users.

### 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 RDBMS. 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 RDBMS. 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 RDBMS 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 user. The scripts also handle deletions of users or groups from the Catalog when users or groups are deleted in the external community.

### 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 RDBMS to the BI Beans Catalog. The scripts are not meant to work in the opposite direction. The RDBMS is considered to be the centrally managed source for user and group identity.

## The Sample Scripts

### Using scripts for bootstrapping and synchronization

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

- `rdbms_sync.bat and rdbms_sync.csh`: Use these scripts to run the synchronization sql script below.

- `rdbms_sync.sql`: This script will do both the bootstrapping and the synchronization of users.

### 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, so you must have SQL*PLUS installed.

- The sample scripts can synchronize only one RDMBS at a time.

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

### How the scripts handle entries

Before running the scripts, you should understand how they handle entries from the RDBMS, 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.

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

## Running the Scripts

### Before running the scripts

Update the scripts with user information that pertains to your Catalog and RDBMS as follows:

1. Modify the `rdbms_sync.bat` and `rdbms_sync.csh` files.

   For the command: sqlplus bibcat/bibcat@ashtmp @rdbms_sync.sql

   Change bibcat to the username of the Catalog owner, bibcat to the password for the Catalog owner, and ashtmp to the connect string to the database that contains the Catalog.

2. Modify the `rdbms_sync.sql` SQL script.
   For the line: create database link rdbms_link connect to system identified by oracle using 'ashtmp';
   Change system to the username of the RDBMS which has DBA priviledges, oracle to the password of that user, and ashtmp to the connect string to the RDBMS.

### Entering the commands that run the scripts

Complete the following steps to run the scripts:

1. From a UNIX command prompt, use the following command:

   `rdbms_sync.csh`

   from a Windows/DOS command prompt, use the following command:

   `rdbms_sync.bat`

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

   `sqlplus>exit`

## Process Flow

### Description

- The script identifies users and groups in the RDBMS from the dba_users and dba_roles tables, and adds them to the Catalog (i.e. to the bism_subjects table) if they do not already exist. For each user, an entry is added to the bism_groups table for the user to belong to itself, and a default permission is added to the bism_permissions table.
- The script identifies relationships between users and groups from the dba_role_privs table and add them to the Catalog (i.e. to the bism_groups table) if they do not already exist.
- The script identifies users, groups and relationships that are no longer in the RDBMS, and deletes them from the Catalog. When a user or group is deleted, any relationship that references the user or group is automatically deleted.

Copyright © 2004 Oracle Corporation. All Rights Reserved.