OBIEE 11g Security Week : Connecting to Active Directory, and Obtaining Group Membership from Database Tables

In this final posting in the OBIEE 11g Security Week, we're going to look at two common tasks that an OBIEE 11g administrator might have to perform:

Connecting the system to Microsoft Active Directory, so users can log-into the dashboard using their Windows Active Directory username and password, and retrieve group membership, and
Connecting the system to an external set of database tables that contain the group membership for users authenticated through Active Directory

Whilst OBIEE 11g comes with the embedded WebLogic LDAP server to hold users and groups, the license for this is restricted such that you can't just move all your other user details from other applications into the LDAP server. Realistically, you wouldn't want to do that anyway as it's likely you've got a corporate directory somewhere that you want to leave user and group details in, with OBIEE instead just connecting to it as an authentication and authorisation source. Luckily, now that OBIEE 11g uses WebLogic and Fusion Middleware's Oracle Platform Security Services framework, connecting to external directories such as Active Directory is pretty straightforward, especially with recent versions of OBIEE such as 11.1.1.6 that do this all a lot smoother now.

So in this example, we've got an Active Directory server running on the host pdc.gcbc.com, that contains three users:

ADBISystemUser, which will be used as the principal that OBIEE uses to connect to the Active Directory server
Anne Administrator, a user on Active Directory who wants to have administration rights in the Presentation Server and BI Server
AD User, another user that just wants to be able to create analyses and dashboards

These users are organised into three groups in the AD server:

ADBIAdministrators, analogous to the BIAdministrators group in the WLS LDAP server
ADBIAuthors, ditto
ADBIConsumers, ditto again

Now if you search the internet and Oracle docs for instructions on how to integrate OBIEE 11g with Active Directory, there seems to be about as many different ways to do it as there are sets of instructions. A lot of this is because Active Directory is highly-configurable, and a lot depends on how much you want to replace, or just work alongside, the existing WLS LDAP server. In this example, our objective is to keep the WLS LDAP server and the user accounts within it (including the biadmin administrator account), but then make it possible for Active Directory users to also log in, and be assigned to the standard application roles that the WLS LDAP users have. Keeping the WLS LDAP users and administration account considerably simplifies the configuration process, though you might still want to go the full way if you intend to completely replace WLS LDAP with Active Directory. For now though, we'll have the two running alongside each other.

Looking at the Active Directory Users and Configuration utility, we can see the three users we're interested in:

And the three groups:

The groups have just got those users as members, and the users are just regular AD users, including the ADBISystemUser account. Internally, the domain is called gcbc.com, with the users held in the Users directory and groups in the Builtin directory - fairly standard stuff.

So let's go into the WebLogic Server Administration Console (http://[machine_name]:7001/console) and start configuring the system for Active Directory integration.

Log into the WebLogic Server Administration Console as an administration user, for example biadmin/welcome1
When the Admin Console homepage is displayed, click on the Security Realms menu item on the left-hand side, and then then on myrealm when the link is shown.
You are now going to alter the domain configuration, so press the Lock and Edit button. Then, click on the Providers tab in the Settings for my realm page.
Active Directory integration is achieved through registering a new authentication provider, using the Active Directory provider type. To register this, press the New button just under the Authentication Providers label.
The Create a New Authentication Provider page will be displayed. Give the provider a name (for example, ADProvider) and select ActiveDirectoryAuthenticator as the Type.
Now click on this new authentication provider in the list, and then when the Settings for ADProvider page is shown, set the Control Flag to SUFFICIENT, and press Save.
Then, click on the Provider Specific tab, and enter the following details for your Active Directory installation, amending the settings as appropriate for your AD server.

Host : pdc.gcbc.com
Port : 389
Principal : CN=ADBISystemUser, CN=Users, DC=gcbc, DC=com
Credential : Welcome1
Confirm Credential : Welcome1
User Base DN : CN=Users,DC=gcbc, DC=com
User Name Attribute : cn
User Object Class : user
Group Base DN : CN=Builtin, DC=gcbc, DC=com
GUID Attribute : objectguid

Then, press Save to save and close the page.
Now go back to the list of providers, and click on the DefaultAuthenticator one. With the Configuration > Common sub-tab selected, set the Control Flag to OPTIONAL, and press Save.
Then, again with the list of authentication providers displayed, press the Reorder button and then change the order of the providers so that ADProvider is first, followed by DefaultAuthenticator and DefaultIdentityAsserter.
You're now at the point where you can restart your BI domain and see the new users and groups within the WebLogic Admin Console. To do this, restart the BI Domain (the Admin and Managed Servers), and once complete, log in again into the WebLogic Admin Console and select Security Realms > myrealm > Users and Groups > Groups. You should then see the Active Directory users listed alongside the WLS LDAP ones.

Similarly, you should see your AD groups under the Groups tab. Note that you can't edit these AD users and groups from within the WebLogic Admin Console, nor can you create new AD users here - to do that, you'd need to use Active Directory's own console and tools.
Next we will switch over to Enterprise Manager, first to configure Fusion Middleware's Oracle Platform Security Services to accept users and groups from both WLS LDAP and Active Directory when logging into the dashboard, and then we'll map the Active Directory groups to their equivalent application roles.

Log into Enterprise Manager, and select the WebLogic Domain > bifoundation_domain menu item on the left. Right-click on it and select Security > Security Provider Configuration. When the Security Provider Configuration page is displayed, expand the Identity Store Provider area and press the Configure… button.

The Identity Store Configuration page will then be displayed. Press the Add button next to the Custom Properties area, and add a new custom property with these settings :

Property Name : virtualize
Value : true

Press OK to close the page.
Now right-click on the Business Intelligence > coreapplication entry in the left-hand side menu, and select Security > Application Roles. As you may have done with the application role settings in yesterday's postings, edit the BIAdministrator, BIAuthor and BIConsumer application roles so that the new Active Directory groups are listed as members.

Doing this ensures that the Active Directory users get the same type of Presentation Server and repository privileges as WLS LDAP users, but they won't have administration access to WebLogic or Enterprise Manager.

You can, if you want, grant these users the same sorts of domain administrator rights as the WLS LDAP users, and you can indeed remove all of the WLS LDAP users and groups and move over to Active Directory entirely. But in most cases I see, this level of integration is sufficient, as it still allows the OBIEE administrators to control their own user accounts and privileges.
You should now be able to log in as one of the Active Directory users. In the screenshot below, the AD User user has logged in, and has been granted the BIAuthor role through their membership of the ADBIAuthors Active Directory group. If Anne Administrator, an Active Directory user assigned to the ADBIAdministrator group, logs in she will be able to administer the Presentation Server permissions and privileges, but she won't be able to log into Enterprise Manager to change the repository, for example.

So what we've seen here so far is OBIEE 11g connecting to Active Directory, to retrieve in addition to the existing WLS LDAP users and groups, users and groups from this directory. But what if the groups in Active Directory bear no resemblance to the groups and application roles that you'd like to organise users into? Because you can map LDAP groups to roles in Enterprise Manager, it's possible to "reshape" group membership to fit your BI requirements, but often organisations will solve this problem by creating a couple of database tables on a spare database, and use those to define which users belong to which group.

Now this is something that was done a lot in OBIEE 10g - using Active Directory to authenticate someone, then retrieve their group membership through a separate database table lookup - but you're not supposed to mix WLS provider-based authentication with old-style init block authorisation, so how will this work, if, for example we've got a couple of tables called GROUPS and GROUPMEMBERS that detail which user belongs to which group:

To handle this type of situation, OBIEE 11.1.1.5 (through the patch associated with Bug 11667221 / ARU 14523400) and OBIEE 11.1.1.6 (by default, though you need to copy the BISecurityProviders.jar file from [middleware_home]/Oracle_BI1/bifoundation/security/providers to [middleware_home]/wlserver_10.3/server/lib/mbeantypes, and then restart the Admin Server before it's available), has a new authenticator called BISQLGroupProvider that can do this for you.

To use this new authenticator with either OBIEE 11.1.1.5 or 11.1.1.6, you'll need to perform the following tasks:

Configure a data source within WebLogic that the provider will use to connect to the schema and tables described above
Configure a BISQLGroupProvider with the SQL SELECT statements required to access these tables
Re-order your authentication providers, and if you've not done so already, enable the virtualised identity store adapter (we did this infact in the previous example)
Configure a database adapter so that the Identity Store APIs can map your groups into application roles.

Full details of this new authenticator are in a document on My Oracle Support, Doc. ID. 1428008.1. So, with some new users added to my Active Directory server and corresponding entries in the two database tables, so that these users are assigned to groups such as QA Managers, HR Managers and SF Managers, let's get this set up.

If you've not done so already, apply the above patch to OBIEE 11.1.1.5 if that's the version you're running, and then copy the BISecurityProviders.jar file as directed above (this applies to 11.1.1.6 as well, which already has the file without needing the patch applied). Once done, restart the WebLogic Admin Server.
Now you will configure the data source and BISQLGroupProvider. To do so, use your Web browser to navigate to the WebLogic admin console (http://[machine_name]:7001/console), and then press the Lock and Edit button.

From the left-hand menu select Services > Data Sources. Then, from the Data Sources list, press New > Generic Data Source.

Then, on the Create a New JDBC Data Source page, enter or select the following details:

Name : BIDatabaseGroupsDS
JNDI Name : jdbc/BIDatabaseGroupsDS
Database Type : Oracle (for example)

On the following page, select the Database Driver, and then at the Connection Properties page, enter the connection details to your schema and database, for example:

Database Name : orcl
Host Name : obisrv1c
Port : 1521
Database User Name : gcbc_bi_groups
Password : password
Confirm Password : password

Once entered, test the connection on the next page, on the next page deploy the datasource to all of your WebLogic servers, then press Finish, and then press the Activate Changes button.
Next you will create a BISQLGroupProvider against this JDBC data source. The SQL that's in the SELECT statements below is particular to the tables that I diagrammed earlier, and you'd need to change it if your table structure was different.

Start by pressing the Lock & Edit button, to start editing the domain configuration. Then, select Security Realm > myrealm > Providers from the menus and tabs.

With the Providers tab selected, press the New button to create a new authentication provider. When prompted, enter MySQLGroupProvider as the Name, and select BISQLGroupProvider as the Type.

Then, press OK to close the page, and then click on the new MySQLGroupProvider authentication provide to display its settings page. Select the Provider Specific tab, and then type in the name of the JDBC datasource that you created earlier, i.e. jdbc/BIDatabaseGroupDS.

If you used the same table and column names as in the diagram before, the SQL settings for this provider will not need to be changed. If you did alter the table or column names though, update the SQL commands to reflect your actual database structure.

Once complete, press Save.
Now go back to the list of providers, and Reorder them so that the new MySQLGroupProvider is at the top of the list.
If you have not done so already, set the virtualized=true flag in the Identity Store Provider settings in Enterprise Manager - see the steps earlier in this posting for details on how to do this.

Once you've done this, press the Activate Changes button and then stop, and then start your entire BI system, so that all WebLogic and OBIEE components restart.

Next, you are going to create an XML file that will be an adapter template for the database adapter, and will be used by the Identity Store APIs to map groups to application roles. Use a text editor and call the file bi_sql_groups_adapter_template.xml, and substitute your own LDAP details into the

<param name="ReplaceAttribute"value="uniquemember={cn=%uniquemember%,cn=Users,dc=gcbc,dc=com}"/>

section, and also the:

<objectClass name="groupofuniquenames" rdn="cn">

section. In addition, if you have used different database table names and columns, you'll need to adjust the SQL statements in the XML file accordingly.


<?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=gcbc,dc=com}"/>
               </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>

Now, open a command-prompt session in the server running Oracle Business Intelligence, and enter the following commands, adjusting for your particular environment and LDAP settings:

cd c:\Middleware\oracle_common\bin
set ORACLE_HOME=c:\Middleware\Oracle_BI1
set WL_HOME=c:\Middleware\wlserver_10.3
set JAVA_HOME=c:\Middleware\jdk160_24

libovdadapterconfig -adapterName biSQLGroupAdapter -adapterTemplate bi_sql_groups_adapter_template.xml -host localhost -port 7001 -userName biadmin -domainPath c:\Middleware\user_projects\domains\bifoundation_domain -dataStore DB -root cn=Users,DC=gcbc,DC=com -contextName default -dataSourceJNDIName jdbc/BIDatabaseGroupDS

When prompted, enter the password for the Administration Server. Once complete, you should see the message:

Adapter created successfully: biSQLGroupAdapter

Now stop and restart the entire BI system. During the restart, you will see an error message saying that the connection pool you just created is unusable - this is expected and will not cause a problem.

Now, go into Enterprise Manager and create a matching role for one of your new database-defined groups. You should see the new groups appearing when you go to add a group to the application role - if not, check the console output for the WebLogic Server for any diagnostic messages.
Finally, you're now ready to test out the new roles and groups. Restart your entire BI system, then log in as one of the users with groups in the database tables, and then view the list of roles assigned to the user. You should see your new roles, corresponding to the group settings in the database tables, assigned to the user - in this case, the HR Manager role.

So that concludes my look this week at OBIEE 11g security. There's a lot more you could cover - EBS integration, setting up of SSO and SSL, etc, but I think this gives you a flavour of what's involved. On now to write the actual book chapter, so no blogging for me for a couple of weeks.