How-to: Enable Case Insensitive User Name Log-in with OBIEE 11g

In my previous article 'OID Authentication with Groups Stored in a Database Table' we discussed how to configure OBIEE 11g to authenticate against an LDAP directory (OID, MSAD) when group credentials are stored in a database table.

In summary, OBIEE 11g authenticates using an OID authenticator via OPSS (Oracle Platform Security Services), followed by a BISQLGroupAuthenticator - both configured in Weblogic Admin Console:

After configuring these authenticators (outlined here and here), you run a test by logging in a user who exists in your LDAP authenticator:

Example 1: Logging in with a valid username, valid password and correct username case sensitivity

• username: cookjr (username is stored as 'cookjr' in external database table)
• member of the following application roles: Authenticated User, BIConsumer, BIAuthor, BIAdministrator

From the above result, you can see the user cookjr has logged in and their application roles are correctly assigned.

Example 2: Logging in with a valid username, valid password and incorrect username case sensitivity

• username: cOoKjR (username is stored as 'cookjr' in external database table)
• member of the following application roles: Authenticated User, BIConsumer, BIAuthor, BIAdministrator
  

 Application roles after login attempt:

 Note how none of the custom application roles were applied to this login attempt even though it's the same user logging in.

WHY?
It is important to remember that the BISQLGroupAuthenticator used for group authentication is a driver that is used to generate Oracle SQL statements that you configure when setting up the authenticator in Weblogic Admin Console:

Keeping this in mind, also remember that Oracle SQL is case sensitive, meaning that:

select * from table where name = 'john' does not equal select * from table where name = 'jOhn'.

To configure the BISQLGroupAuthenticator to be case insensitive for username logins, you'll need to modify your SQL statements in the BISQLGroupAuthenticator 'Provider Specific' tab to use the 'UPPER' function, which takes the username passed in from the Answers login screen, converts it to all caps, and compares it against the UPPER(G_MEMBER) field.

Example:

• SQL List Groups: SELECT G_NAME FROM GROUPS WHERE UPPER(G_NAME) LIKE UPPER(?)
• SQL List Groups: SELECT G_NAME FROM GROUPS WHERE UPPER(G_NAME) LIKE UPPER(?)
• SQL Is Member: SELECT G_MEMBER FROM GROUPMEMBERS WHERE UPPER(G_NAME) = UPPER(?) AND UPPER(G_MEMBER) = UPPER(?)
• SQL List Member Groups SELECT G_NAME FROM GROUPMEMBERS WHERE UPPER(G_MEMBER) = UPPER(?)
• SQL Get Group Description (if description supported enabled) SELECT G_DESCRIPTION FROM GROUPS WHERE UPPER(G_NAME) = UPPER(?)

Using this logic, the user will now be able to log in using any username case sensitivity. Note that you may still have an issue where the user is able to login with an invalid (or no) password. This is most likely due to a legacy LDAP authenticator in your repository. I've outlined a solution for that in my article 'User able to log-in with invalid (or no) password'

 

keywords: OBIEE 11g, OBIEE Security, Weblogic, OPSS, OID, External Table Authentication,  Authentication, data level security, object level security

How-to: Automatic Repository Deployment and Promotion Process OBIEE 11g

A typical deployment process an OBIEE 11g repository will follow in most production environments resembles the following:

The development zone represents a series of developer machines modifying a repository either by:

• Multi User Directory Environment Configuration (MUDE)
• Local development machines where each developer migrates their changes to a centralized OBIEE 11g dev/unit test box via a patch-merge process

We're going to focus on the 'Production Deployment Path' that takes the repository from the Dev/Unit test machine and migrates it through the deployment path from Assembly Test through Production.

This production path is critical because it's at this point where the repository leaves the 'safe haven' of the developer environment and goes through various stages of testing, usually performed by another team. Each testing team will have their own BI Server and database that the repository must connect to for testing.

Usually, the repository remains the same through all environments except for:

• Connection Pools
• Environment specific server variables

We're going to perform the assembly test to production deployment process in a completely automated fashion by:

• Generating an XUDML file that connections connection pool information
• Generating a new system test repository by applying the System test XUDML to the assembly test repository
• Using WLST to upload the RPD to the specif iced environment

 

 Step 1: Generate the XUDML file for the assembly, system, staging and production environments

We're going to create an eXtensible Universal Database Markup Language (XUDML for short) that contains connection pools specific for each environment. This file is generated by biserverxmlgen and is basically the repository exported to XML. The way to accomplish this in OBIEE 10g was using UDML which has seen been deprecated and is not supported by Oracle - see Oracle Note 1068266.1.

Step 1.1 - Set Variables via bi-init.sh

. /export/obiee/11g/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh

Note the space between the '.' and the '/' . This is required for the i-init.sh script to propagate through all folders

Step 1.2 - Generate XUDML file

Navigate to export/obiee/11g/Oracle_BI1/bifoundation/server/bin/ and run:

biserverxmlgen -R C:\testconnpool\base.rpd -P Admin123 -O c:\testconnpool\test.xml -8

• Replace base.rpd with your source RPD - i.e. if you want to generate connection pool information for assembly test, base.rpd should represent your assembly test repository.
• -O generates the output XML file
• -8 represents the UTF-8 formatting for the XML file
• -P represents the password of the base repository

If fail to set your session variables will you encounter the following error:

"libnqsclusterapi64.so: open failed: No Such file or directory"

If you are successful, your output should be as follows:

Step 1.3 Remove inapplicable entries

For connection pool migrations, your script should only include:

<?xml version="1.0" encoding="UTF-8" ?>

<Repository xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DECLARE>
<Connection Pool ......>
</ConnectionPool>
</DECLARE>
</Repository>

You will only need to re-generate this file if you change your connection pool information. This XUDML file will be used to update connection pools of your target environment.

Step 2: Apply XUDML file to base repository 

Let's say you have an assembly test repository and a system test XUDML file. The biserverxmlexec.sh script will take your assembly test repository, system test XUDML file and generate a 'system test repository' using the following command located in export/obiee/11g/Oracle_BI1/bifoundation/server/bin/

biserverxmlexec -I input_file_pathname [-B base_repository_pathname] [-P password]
-O output_repository_pathname

Where:

• input_file_pathname is the name and location of the XML input file you want to execute base_repository_pathname is the existing repository file you want to modify using the XML input file (optional). Do not specify this argument if you want to generate a new repository file from the XML input file.password is the repository password.
• If you specified a base repository, enter the repository password for the base repository. If you did not specify a base repository, enter the password you want to use for the new repository.
• The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release.
• output_repository_pathname is the name and location of the RPD output file you want to generate

Example:

biserverxmlexec -I testxudml.txt -B rp1.rpd -O rp2.rpd
Give password: my_rpd_password

You now have a system test repository that you can upload to your applicable environment.

Step 3: Upload Repository to BI Server via WLST

Many web sites show how to upload the repository via the FMW Enterprise Manager, but that is generally alot slower and not as efficient as scripting it.

The uploadRPD.py script below performs five tasks:

• Connects to WLST
• Locks the System
• Uploads the RPD
• Commits Changes
• Restarts BI Services

Copy the code below and save it as a python script (.py)

connect('user','pass','server')


user = ''
password = ''
host = ''
port = ''
rpdpath = '/path/path2/repository.rpd'
rpdPassword = ''

# Be sure we are in the root
cd("..\..")

print(host + ": Connecting to Domain ...")
try:
domainCustom()
except:
print(host + ": Already in domainCustom")

print(host + ": Go to biee admin domain")
cd("oracle.biee.admin")



# go to the server configuration
print(host + ": Go to BIDomain.BIInstance.ServerConfiguration MBean")

cd ('oracle.biee.admin:type=BIDomain,group=Service')
biinstances = get('BIInstances')
biinstance = biinstances[0]


# Lock the System
print(host + ": Calling lock ...")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("lock", objs, strs)
except:
print(host + ": System already locked")

cd("..")

# Upload the RPD
cd (biinstance.toString())
print(host + ": Uploading RPD")
biserver = get('ServerConfiguration')
cd('..')
cd(biserver.toString())
ls()
argtypes = jarray.array(['java.lang.String','java.lang.String'],java.lang.String)
argvalues = jarray.array([rpdpath,rpdPassword],java.lang.Object)

invoke('uploadRepository',argvalues,argtypes)

# Commit the system
print(host + ": Commiting Changes")


cd('..')
cd('oracle.biee.admin:type=BIDomain,group=Service')
objs = jarray.array([],java.lang.Object)
strs = jarray.array([],java.lang.String)
invoke('commit',objs,strs)


# Restart the system
print(host + ": Restarting OBIEE processes")

cd("..\..")
cd("oracle.biee.admin")
cd("oracle.biee.admin:type=BIDomain.BIInstance,biInstance=coreapplication,group=Service")

print(host + ": Stopping the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("stop", params, signs)

BIServiceStatus = get("ServiceStatus")
print(host + ": BI ServiceStatus " + BIServiceStatus)

print(host + ": Starting the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("start", params, signs)

BIServerStatus = get("ServiceStatus")
print(host + ": BI ServerStatus " + BIServerStatus)

The aforementioned code works on scaled out (clustered) environments since there is only one active admin server. The code will connect to the active admin server located in your first node, and WLST will propagate changes to each node. You can validate this by navigating to the local repository folder of each node.


To run the script, load wlst located at :

 /export/obiee/11g/oracle_common/common/bin/wlst.sh

and perform the execfile command as follows:

execfile(‘/path/path1/path2/uploadRPD.py’)

In conclusion, the entire repository deployment process can be executed by the following two scripts:

• biserverxmlexec (provided by Oracle)
• uploadRpd.py (see above)

Reference: Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition

keywords: Repository, RPD deployment, repository deployment automation, obiee 11g, scripted rpd deployment, wlst

FYI: Overview on Object Level Security, Application Roles, and Inheritance in OBIEE 11g

In Oracle Business Intelligence (OBIEE) 11g, Oracle has fundamentally changed how we map users to various security privileges.In OBIEE 10g, Object Level Security was enforced using the USER session variable, which mapped to a GROUP session variable. This created a list of possible 'groups', which a developer would then apply security restrictions to, in either Answers (Managed Privileges) and/or Security Managed in the repository.

A high level flow is outlined below:

In OBIEE 11g, security authentication is enforced in the Weblogic Admin Server, and a user's security privileges are tied to their corresponding Application Roles in Fusion Middleware as shown in the diagram below:

The key take away is that object level security is applied to application roles and not groups.  Why application roles? In Weblogic and Fusion Middleware, we can actually assign certain privileges to an application roles - we call these 'Application Policies'. For example,  we can grant a certain application role the ability to 'edit the repository', or 'act as another user'. This feature, not possible in OBIEE 10g, now allows us to not only control what objects are being viewed, but also gives us the capability to control who can execute certain actions within the BI environment. This topic will be discussed in much greater detail in another guide.

Now let's go over the basic rules of Object Level Security for Application Roles in OBIEE 11g:

• If a user is a direct member of an application role, they will have access to the reports allowed by that application role.
• If a user is not a member of an application role, they will not have access to the reports allowed by that application role.
• If a user is a direct member of two or more application roles with different security privileges for the same reports, the less restrictive security privilege is applied.
• unless the user is explicitly denied. Explicit denial supersedes all security privileges.
• If a user is a member of Application Role X, and Application Role X is a member of Application Role Y, the privileges in Application Role X supersede the privileges of Application Role Y

Let's cover each scenario in detail:

• If a user is a direct member of an application role, they will have access to the reports allowed by that application role.

 

In this example, I granted Application Role 'Test Role 1' full control to folder 'Folder 1'. I then logged in as 'testuser1' who is a member of Application Role 'Test Role 1'. And as expected, testuser1 can read/write/edit/delete the folder.

• If a user is not a member of an application role, they will not have access to the reports allowed by that application role.

In this example, I created 'Folder 2', only accessible by members of the 'BIAdministrator Application Role'. I then log in as a 'testuser1', which is not a member of the 'BIAdministrator Application Role'

As BIAdministrator:

As testuser1:

Note that in the above scenario, 'denying' the application role access accomplishes the same thing as taking no action onto the application role role (i.e. ignoring it completely)

• If a user is a direct member of two or more application roles with different security privileges for the same reports, the less restrictive security privilege is applied.

In this example, I created Folder 3, which grants 'read' access to Test Role 1 and 'modify' access to 'Test Role 2'. 'Testuser1' is a member of both 'Test Role 1' and 'Test Role 2'.

 

 

As expected, Testuser1 has modify rights to Folder 3 (noted by 'X', ability to delete), despite being a member of Test Role 1 which only grants the user read access

• If a user is a direct member of two or more application roles with different security privileges for the same reports, the less restrictive security privilege is applied.
• unless the role is explicitly denied

In this example, TestUser1 is a member of Test Role 1 and Test Role 2 and Test Role 3. Test Role 1 grants testuser1 open rights, Test Role 2 grants testuser1 modify rights and Test Role 3 is explicitly denied.

As expected, testuser1 does not have access to Folder 4 because of Test Role 3

• If a user is a member of Application Role X, and Application Role X is a member of Application Role Y, the privileges in Application Role X supersede the privileges of Application Role Y

 

In this example, testuser1 is a member of application role 'Test Role 4'. Application role 'Test Role 5' is a member of application Role 'Test Role 4'. Test Role 4 grants 'open' privileges to Folder 5 and Test Role 5 grants 'full control' to Folder 5.

 

As expected, testuser1 only has read/open access to Folder 5 even though Application Role 'Test Role 5' grants full control. This is because direct inheritance overrides indirect inheritance

Even if the inherited role explicitly denies access to folder 5, the user will still be able to access folder 5 because the direct role grants read/open access:

Note how testuser1 has modify access to Folder 5 (noted by the 'X') , despite inheriting a role that is denied access to the same folder.

These basic rules can be applied to any hierarchy, no matter how complex. Think you've mastered these 4 basic rules? Identify the final privileges for User 1 in the scenario below:

* example courtesy of Oracle - found in Oracle Business Intelligence Fusion Middleware Security Guide

Result:

• User is a direct member of Role 1 and 2 and indirect member of Role 3, Role 4 and Role 5
• User has no access to Dashboard A
• User has open access to Dashboard B
• User has full control of Dashboard C
• User has no access to Dashboard E
• User has open access to Dashboard D

keywords : object level security, obiee security, obiee application roles, obiee 11g security, weblogic application roles, obiee inheritance

How-to: OBIEE 11g Start up/Shut Down & Script in Solaris/Linux

'Start BI Services'

This simple, easy to use process for starting & stopping Weblogic & OBIEE 11g in Windows is something many developers take for granted. In personal development and proof of concept environments a Windows environment will suffice, but once we start talking about enterprise testing & production environments we quickly realize that a windows based Weblogic and OBIEE 11g environment just won't work.

We then start down the path of deployment on a Redhat or Solaris environment. After reading Oracle's official guide on starting and stopping OBIEE 11g, we quickly realize just how lucky we were. No longer do we have the option of executing a single file to start and shut down Weblogic and OBIEE, at least not out of the box.

This request is commonly echoed on Oracle Technical Network so today we'll outline how to create a single file that acts as both a start up and shutdown script.

First, let's go over the manual start up process for Weblogic & OBIEE 11g in Solaris & Linux:

• Start Administration Server
• located in your Weblogic Server Domain user_projects folder
• /user_projects/domains/bifoundation/bin/startWebLogic.sh
• example: ./startWebLogic.sh
• Start Node Manager
• located in your Weblogic Server Home Directory
• /wlserver_10.3/server/bin/startNodeManager.sh
• example: ./startNodeManager.sh
• Start Managed Server
• located in your Weblogic Server Domain user_projects folder
• /user_projects/domains/bifoundation/bin/startManagedWebLogic.sh
• example: ./startManagedWebLogic.sh bi_server1 t3://HOSTNAME:7001
• Start BI Services using Oracle Process Manager and Notification Server (opmnctl)
• located in your Orcle Instance folder
• /instances/instance1/bin/opmnctl
• example: opmnctl startall

Second, let's go over the manual shut down process of Weblogic & OBIEE 11g in Solaris & Linux:

• Stop BI Services using Oracle Process Manager and Notification Server (opmnctl)
• located in your Orcle Instance folder
• /instances/instance1/bin/opmnctl
• example: opmnctl stopall
• Stop Managed Server
• located in your Weblogic Server Domain user_projects folder
• /user_projects/domains/bifoundation/bin/stopManagedWebLogic.sh
• example: ./stopManagedWebLogic.sh bi_server1 t3://HOSTNAME:7001
• Stop Node Manager
• There is no 'stopNodeManager', must kill associated proccesses
• example: pkill -TERM -u USERNAME -f "/startNodeManager.sh"
• example: pkill -TERM -u USERNAME -f "/java"
• Stop Admin Server
• located in your Weblogic Server Domain user_projects folder
• /user_projects/domains/bifoundation/bin/stopWebLogic.sh
• example: ./stopWebLogic.sh

So how do we automate this process?

By customizing the shell script below, you'll be able to automate the start up and shutdown sequence in OBIEE 11g.

The lines highlighted in red require customization.

#!/bin/bash
#
# File:    obiee.sh
# Purpose: Start and stop Oracle Business Intelligence 11g components.
#
# description: Start up and shutdown sequence for OBIEE 11g and Weblogic
#

# These values must be adapted to your environment.

ORACLE_OWNR=username                  # Local Unix user running OBIEE
ORACLE_FMW=/export/obiee/11g      # Deployment root directory
                                  
BIEE_USER=weblogic                # BIEE administrator name
BIEE_PASSWD=weblogic              # BIEE administrator password              
BIEE_DOMAIN=bifoundation_domain         # Domain name
BIEE_INSTANCE=instance1             # Instance name
BIEE_SERVER=bi_server1              # Server name
BIEE_MANAGER_URL=hostname:7001    # Admin server URL (hostname:port)
 
WL_PATH=/export/obiee/11g/wlserver_10.3/server/bin
BIEE_PATH=/export/obiee/11g/user_projects/domains/bifoundation_domain/bin
ORACLE_INSTANCE=/export/obiee/11g/instances/instance1
export ORACLE_INSTANCE

START_LOG=/export/obiee/11g/obiee_startup_log/obiee-start.log
STOP_LOG=/export/obiee/11g/obiee_startup_log/obiee-stop.log

# SUBSYS=obiee

start() {
    echo "********************************************************************************"
    echo "Starting Admin Server on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/startWebLogic.sh" &
    wait_for "Server started in RUNNING mode"
   
    echo "********************************************************************************"
    echo "Starting Node Manager on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$WL_PATH/startNodeManager.sh" &
    wait_for "socket listener started on port"

    echo "********************************************************************************"
    echo "Starting Managed Server $BIEE_SERVER on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/startManagedWebLogic.sh $BIEE_SERVER t3://$BIEE_MANAGER_URL" &
    wait_for "Server started in RUNNING mode"

    echo "********************************************************************************"
    echo "Starting BI components on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$ORACLE_INSTANCE/bin/opmnctl startall"

    echo "********************************************************************************"
    echo "OBIEE start sequence completed on $(date)"
    echo "********************************************************************************"
}

stop() {
    echo "********************************************************************************"
    echo "Stopping BI components on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$ORACLE_INSTANCE/bin/opmnctl stopall"

    echo "********************************************************************************"
    echo "Stopping Managed Server $BIEE_SERVER on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/stopManagedWebLogic.sh $BIEE_SERVER t3://$BIEE_MANAGER_URL $BIEE_USER $BIEE_PASSWD"

    echo "********************************************************************************"
    echo "Stopping Node Manager on $(date)"
    echo "********************************************************************************"
   # pkill -TERM -u $ORACLE_OWNR -f "weblogic\\.NodeManager"
    pkill -TERM -u $ORACLE_OWNR -f "/bin/sh/export/obiee/11g/wlserver_10.3/server/bin/startNodeManager.sh"
 pkill -TERM -u $ORACLE_OWNR -f "/export/obiee/11g/Oracle_BI1/jdk/bin/sparcv9/java"    echo "********************************************************************************"
    echo "Stopping Admin Server on $(date)"
    echo "********************************************************************************"
    su $ORACLE_OWNR -c "$BIEE_PATH/stopWebLogic.sh"
   
    echo "********************************************************************************"
    echo "OBIEE stop sequence completed on $(date)"
    echo "********************************************************************************"
}

wait_for() {
    res=0
    while [[ ! $res -gt 0 ]]
    do
        res=$(tail -5 "$START_LOG" | fgrep -c "$1")
        sleep 5
    done
}

case "$1" in
    start)
        echo "********************************************************************************"
        echo "Starting Oracle Business Intelligence on $(date)"
        echo "Logs are sent to $START_LOG"
        echo "********************************************************************************"
        start &> $START_LOG
       # touch /var/lock/subsys/$SUBSYS
    ;;
    stop)
        echo
"********************************************************************************"
        echo "Stopping Oracle Business Intelligence on $(date)"
        echo "Logs are sent to $STOP_LOG"
        echo "********************************************************************************"
        stop &> $STOP_LOG
       # rm -f /var/lock/subsys/$SUBSYS
    ;;
    status)
        echo "********************************************************************************"
        echo "Oracle BIEE components status...."
        echo "********************************************************************************"
        su $ORACLE_OWNR -c "$ORACLE_INSTANCE/bin/opmnctl status"
    ;;
    restart)
        $0 stop
        $0 start
    ;;
    *)
        echo "Usage: $(basename $0) start|stop|restart|status"
        exit 1
esac

exit 0


You must add your admin credentials to the following files:

• /user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.sh

• /user_projects/domains/bifoundation_domain/servers/AdminServer/security/boot.properties

Start the script as follows:



The script will generate the following message after completion:

You can validate all services are running by logging into Fusion Middleware and Weblogic to check AdminServer, ManagedServer, and BI Services:

AdminServer & ManagedServer

BI Services

Stop OBIEE 11g and Weblogic by running the following command:

The script will generate the following message after completion:

You can validate that AdminServer, ManagedServer and BI Services are all shutdown by running ps -ef | grep obiee in your terminal. No weblogic & OBIEE processes should be active:

Note: if you are editing your shell script in a windows environment then uploading it to a Solaris/Linux environment via FTP, you will encounter whitespace issues when attempting to execute. This can be fixed by running the following command on the file:

perl -i -pe's/\r$//;' <file name here>

keywords: obiee start up script, obiee shut down script, obiee 11g installation, obiee admin

How-to: OID Authentication with Groups Stored in an External Database Table - OBIEE 11g

As more firms seek to consolidate their technology stack while expanding their analytics capabilities, it makes more and more sense to transition your OBIEE 10g analytics solution to Oracle's new 11g framework. Oracle's 11g framework provides, among other things:

• An integrated solution for managing & deploying 11g applications from a single environment (Weblogic and Fusion Middleware)
• A centralized system for configuring security across all your environments
• Integration with Oracle's 11g exadata & exalytics solutions
• A framework for utilizing external web services within your reporting solution
• e.g. Your company seeks to expand its business and enter a new market so you create a report using 11g's mapviewer utilizing Yelp.com's API to aggregate user reviews to determine what services consumers are the unsatisfied with, thus allowing you to quantify new business opportunities and entry points. You definitely cannot do that in 10g :)

But before you can save the world and generate new business ideas for your company - step 1 is to deploy the OBIEE 11g application within your company. You've done an analysis of your company's security policy and determined that user authentication is stored in Oracle Internet Directory but corresponding reporting groups are stored in an external database table.

In 11g you can accommodate this security model by deploying two authentication providers: one for OID, and another for the external database table. I've outline the steps below needed to accomplish this task:


Step 1: Configure OID Authentication

To successfully implement an 'OID authentication w/ groups in an external database' security model, you are really completing two separate tasks:

1) Configure OID Authentication
2) Configure external groups authentication

I've posted a complete series on how to configure your 11g environment to utilize OID as an authentication source . The OID authentication configuration process is approximately 15 steps, so take your time, document your process and before proceeding to step 2  -  make sure you can log into your 11g Answers environment with users in your OID.

Do not proceed to step 2 until your OID users can successfully log into the 11g Answers environment.

Step 2:  Deploy your Sample Schema for Groups & Group Members

In your 10g deployment, you probably created an init block that stored a user's groups to the GROUPS session variable. 11g handles user group authentication via Weblogic & Fusion Middleware using an authentication provider similar to the one you created for your OID authentication. The only difference between the OID authentication & the group authentication is instead of hitting OID as the authenticator, we're going to create a BI SQL Group authentication provider that will hit an external database.

Your groups database schema needs to resemble the following data model*:

* Data model taken from Oracle Fusion Middleware Security Guide

Groups table: represents all of the possible groups in your system.

Groupmembers table: stores all of the users and their corresponding group.

Below are 2 rudimentary queries you can use to generate the tables but note they don't utilize any type of indexes, PK/FK relationships, or best practices. I'd use these for a POC (proof of concept) and once the system is ready to scale, create model using best practices:

GROUPS create statement:

CREATE

TABLE USER.GROUPS(

GROUPMEMBERS

VARCHAR2(100 BYTE),

G_NAME

VARCHAR2(100 BYTE),

G_MEMBER

VARCHAR2(100 BYTE)

)

Groupmembers create statement:

CREATE

TABLE USER.GROUPMEMBERS(

GROUPS VARCHAR2(100 BYTE),

G_NAME

VARCHAR2(100 BYTE),

G_DESCRIPTION

VARCHAR2(100 BYTE)

)

Remember that the BISystemUsers, BIAdministrators, BIConsumers and BIAuthors group must appear in your external database table!

Step 3:  Install the BISQLGroupProvider authenticator

Using an external data source for groups within 11g is a new feature that was not initially available in 11.1.1. Oracle later implemented this feature as an add-on but since it wasn't part of the core release (11.1.1.4 and earlier), you're going to have to install the BISQLGroupProvider authenticator before it will appear as an available provider within your provider tab.

Step 3.1)

Copy the BISecurityProviders.jar file located in MW_HOME/ORACLE_HOME/bifoundation/security/providers
to the following folder path:  MW_HOME/wlserver_10.3/server/lib/mbeantypes

Step 3.2)

After copying the file into the specified location you must restart the Administration Server to enable the new provider to appear in the list of available authenticators.

Step 4:  Create the Groups data source in Weblogic

In weblogic (:7001/console/) navigate to : bifoundation_domain -> Services -> Data sources -> Configuration -> New -> Generic Data source

Step 4.1) Create a new JDBC source name

Name:  The value of the 'name' field wwill be used in the config.xml file and in weblogic whenever referring to this data source. For this example, let's use the name : BIDatabaseGroupDS

JDNI Name: This value will be used when creating the database adapter for the virtualized identity store. For this example, let's use: jdbc/BIDatabaseGroupDS

Step 4.2) Specify the database driver

You will need to identify your database driver before proceeding. If you're using an Oracle stack w/ an 11g database, then the default specification will suffice.

Step 4.3) Specify Connection Information

Database Name: For example, enter: ora11. The name of the database that you want to connect to.

Host Name: For example, enter: mymachine.mycompany.com The DNS name or IP address of the server that hosts the database.

Port: For example, enter: 1521. The port on which the database server listens for connections requests.

Database User Name: Typically the schema owner of the tables defined in  Step 2.

Step 4.4) Test database connection

At this point you'll be transferred to a screen that ask you to test the connection using a

SQL SELECT 1 FROM DUAL

You need to pass this step before moving on, so an error like:

is an indication that you've incorrectly configured your data source.

Hopefully, will you see a 'Connection test succeeded' message like below.

Step 4.5) Deploy the JDBC Data Source to the Admin and Managed Server

After clicking 'Finish' you will need to navigate to : bifoundation_domain - > Services -> Data Sources -> BIDatabaseGroupsDS -> Targets. Check the 'AdminServer' and 'bi_cluster' checkbox to deploy the JDBC Data Source.

Step 5: Create the BISQLGroupProvider Authentication Provider

5.1) Navigate to Security Realms -> myrealm -> Providers -> Authentication (as seen below)

5.2) Create a New Authentication Provider called 'MySQLGroupProvider' using type 'BISQLGroupProvider'

5.3) Re-order the Authentication Provider list so that MySQLGroupProvider is the first authentication provider on the list

5.4) Create the custom SQL statements needed to generate the user & corresponding group memberships

Navigate to the 'Provider Specific' tab within your MySQLGroupProvider and populate the SQL Statements as follows (note that you will have to modify these statements if you did not follow the data model in Step 2. Do not remove the '?' from the SQL statement as it is a wild card indicator weblogic populates with a specific value at runtime.

Query	SQL	Notes
SQL List Groups	SELECT G_NAME FROM GROUPS WHERE G_NAME LIKE ?	The SQL statement used to retrieve group names that match a wildcard. The SQL statement requires a single parameter for the group name and must return a resultSet containing matching groups.
SQL Group Exists	SELECT G_NAME FROM GROUPS WHERE G_NAME = ?	The SQL statement used to look up a group. The SQL statement requires a single parameter for the group name and must return a resultSet containing at most a single record containing the group.
SQL Is Member	SELECT G_MEMBER FROM GROUPMEMBERS WHERE G_NAME = ? AND G_MEMBER = ?	The SQL statement used to look up members of a group. The SQL statement requires two parameters: a group name and a member or group name. It must return a resultSet containing the group names that matched.
SQL List Member Groups	SELECT G_NAME FROM GROUPMEMBERS WHERE G_MEMBER = ?	The SQL statement used to look up the groups a user or group is a member of. The SQL statement requires a single parameter for the username or group name and returns a resultSet containing the names of the groups that matched.
SQL Get Group Description (if description supported enabled)	SELECT G_DESCRIPTION FROM GROUPS WHERE G_NAME = ?	The SQL statement used to retrieve the description of a group. Only valid if Descriptions Supported is enabled. The SQL statement requires a single parameter for the group name and must return a resultSet containing at most a single record containing the group description.

Make the Data Source Name: jdbc/BIDatabaseGroupDS

5.4) Navigate to the 'Common' tab and set the Control Flag to 'Optional'

The JAAS Control flag needs to be set to optional to let weblogic know that even if authentication fails (a user isn't found in the group/groupmembers data model) to continue down the authentication provider list.

Step 6) Create a database adapter for the Virtualized Identity Store

Now we're going to create an XML file which will act as a database adapter to facilitate access to the group/groupmembers data model.

Create an XML file called 'bi_sql_groups_adapter_template.xml' and populate it with the following content:


<?xml version = '1.0' encoding = 'UTF-8'?>
<adapters schvers="303" version="1" xmlns="http://www.octetstring.com/schemas/Adapters" xmlns:adapters="http://www.w3.org/2001/XMLSchema-instance">
   <dataBase id="directoryType" version="0">
      <root>%ROOT%</root>
      <active>true</active>
      <serverType>directoryType</serverType>
      <routing>
         <critical>true</critical>
         <priority>50</priority>
         <inclusionFilter/>
         <exclusionFilter/>
         <plugin/>
         <retrieve/>
         <store/>
         <visible>Yes</visible>
         <levels>-1</levels>
         <bind>true</bind>
         <bind-adapters/>
         <views/>
         <dnpattern/>
      </routing>
      <pluginChains xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins">
         <plugins>
            <plugin>
               <name>VirtualAttribute</name>
               <class>oracle.ods.virtualization.engine.chain.plugins.virtualattr.VirtualAttributePlugin</class>
               <initParams>
                  <param name="ReplaceAttribute" value="uniquemember={cn=%uniquemember%,cn=Users,dc=trusted,dc=oracle,dc=dev}"/>
               </initParams>
            </plugin>
         </plugins>
         <default>
            <plugin name="VirtualAttribute"/>
         </default>
         <add/>
         <bind/>
         <delete/>
         <get/>
         <modify/>
         <rename/>
      </pluginChains>
      <driver>oracle.jdbc.driver.OracleDriver</driver>
      <url>%URL%</url>
      <user>%USER%</user>
      <password>%PASSWORD%</password>
      <ignoreObjectClassOnModify>false</ignoreObjectClassOnModify>
      <includeInheritedObjectClasses>true</includeInheritedObjectClasses>
      <maxConnections>10</maxConnections>
      <mapping>
         <joins/>
         <objectClass name="groupofuniquenames" rdn="cn">
            <attribute ldap="cn" table="GROUPMEMBERS" field="G_NAME" type=""/>
            <attribute ldap="description" table="GROUPMEMBERS" field="G_NAME" type=""/>
            <attribute ldap="uniquemember" table="GROUPMEMBERS" field="G_MEMBER" type=""/>
         </objectClass>
      </mapping>
      <useCaseInsensitiveSearch>true</useCaseInsensitiveSearch>
      <connectionWaitTimeout>10</connectionWaitTimeout>
      <oracleNetConnectTimeout>0</oracleNetConnectTimeout>
      <validateConnection>false</validateConnection>
   </dataBase>
</adapters>

The bold text indicates fields that you will need to customize based on your requirements. Let's take this 1 step at a time.

First)   <param name="ReplaceAttribute" value="uniquemember={cn=%uniquemember%,cn=Users,dc=trusted,dc=oracle,dc=dev}"/>  needs to be the User Base DN you specified in Step 2 of Part 1 in my OBIEE 11g OID installation guide 

If, for example, your User Base DN is dc=trusted,dc=oracle,dc=com , then you would need to modify the XML above to be:
 <param name="ReplaceAttribute" value="uniquemember={cn=%uniquemember%,dc=trusted,dc=oracle,dc=com}"/>

The  %uniquemember% field is a placeholder which gets populated via the SQL statements in your Group Authentication provider.


Second)
    <attribute ldap="cn" table="GROUPMEMBERS" field="G_NAME" type=""/>
            <attribute ldap="description" table="GROUPMEMBERS" field="G_NAME" type=""/>
            <attribute ldap="uniquemember" table="GROUPMEMBERS" field="G_MEMBER" type=""/>

GROUPMEMBERS needs to be replaced with the table you created which stores your group members via the group/groupmembers data model in Step 2.



Step 7) Bind the adapter to Weblogic using the Weblogic Scripting Tool (WLST)

7.1) Copy the bi_sql_groups_adapter_template.xml to: ../../oracle_common/modules/oracle.ovd_11.1.1/templates/

7.2) Confirm key environmental variables are set

• ORACLE_HOME=<MW_HOME>/Oracle_BI1
• WL_HOME=<MW_HOME>/wlserver_10.3/
• JAVA_HOME=<MW_HOME>/jdk160_24/
   

7.3) Bind the adapter:

Navigate to /oracle_common/bin and run the following command:

libovdadapterconfig -adapterName MySQLGroupProvider -adapterTemplate bi_sql_groups_adapter_template.xml -host hostname -port 7001 -userName weblogic -domainPath C:\app\11g\mw_home\user_projects\domains\bifoundation_domain\ -dataStore DB -root cn=Staff,cn=Users,dc=trusted,dc=oracle,dc=dev -contextName default -dataSourceJNDIName jdbc/BIDatabaseGroupDS

Parameter	Value
host	Represents the hostname (ip address) of your weblogic server
port	Represents the port of your weblogic server , usually 7001
username	Represents your weblogic administrator account
adapterName	Represnets the name of the group authentication provider
domainPath	Represents the path to your bifoundation_domain folder
root	Represents the User Base DN you specified in your in your bi_sql_groups_adapter_template.xml , excluding the %uniquemember% component
dataSourceJNDIName	represents the JDNI name of your Groups Datasource

The command should execute without any error.

7.4) Restart admin server & managed services (bi_server) 

Step 8) Validate Changes by Creating a Custom Application Role

We're going to create a custom application role based on one of our custom groups to confirm that the Group Authenticator works.

8.1) Create an Application Role
From FMW Enterprise Manager (:7001/em/) -> farm_bifoundation_domain -> Business Intelligence -> coreapplication -> Right Click -> Security -> Application Roles -> Create

Click the Add button and select a Group from your Group Authenticator. In this example, I will add a group called 'ES Worker':

8.2) Login to Answers as a user of the group application role you just created

Navigate to My Account -> Roles and Catalog Groups

That concludes the tutorial on how to integrate weblogic 11g using OID as the user authenticator and storing groups in an external table. Next we will focus on SSO.

keywords: obiee ldap authentication, obiee 11g oid, obiee authentication, weblogic authentication provider, authentication with ldap, external groups authentication, wc_groups_d, wc_groupmembers_d