Follow these steps to create a BISQLGroupProvider against the BIDatabaseGroupDS data source using an example table structure.

This task explains how to create a BISQLGroupProvider against the BIDatabaseGroupDS data source using the example table structure outlined in Create a Sample Schema for Groups and Group Members. You may need to modify the SQL statements used (table or column names) if your structure differs from the example.

1. Log in to Oracle WebLogic Server Administration Console as a WebLogic administrator, and click Lock & Edit in the Change Center.

2. Select Security Realms from the left pane and click myrealm.

   The default Security Realm is named myrealm.

3. Display the Providers tab, then display the Authentication sub-tab.

4. Click New to launch the Create a New Authentication Provider page.

5. Enter values in the Create a New Authentication Provider page as follows:

   • Name: Enter a name for the authentication provider. For example, MySQLGroupProvider.

   • From the Type list, select BISQLGroupProvider.

   • Click OK to save the changes and display the authentication providers list updated with the new authentication provider.

6. In the authentication providers table, click MySQLGroupProvider in the Name column to display the Settings page.

7. Display the Provider Specific tab to specify the SQL statements used to query and authenticate against your database tables.

8. Specify the DataSource Name. Don't use the JNDI name. For example: jdbc/BIDatabaseGroupDS.

9. Enter all of the SQL statements appropriate to your authenticator.

   The SQL is case sensitive.

10. Click Save.

11. Perform the following steps to reorder the authentication providers:

    1. Display the Providers tab.

    2. Click Reorder to display the Reorder Authentication Providers page

    3. Select BISQLGroupProvider and use the arrow buttons to move it into the first position in the list.

    4. Click OK to save your changes.

12. Perform the following steps to configure the Control Flag setting of BISQLGroupProvider:

    1. At the main Settings for myrealm page, display the Providers tab, then display the Authentication sub-tab, then select BISQLGroupProvider to display its configuration page.

    2. Display the Configuration\Common tab and select OPTIONAL from the Control Flag list.

    3. Click Save.

13. In the Change Center, click Activate Changes.

14. Restart the Oracle Analytics Server components, use Fusion Middleware Control once the Administration Server has been restarted, Oracle WebLogic Server, and Managed servers.

You configure a database adaptor to make it appear like an LDAP server to enable the virtualized identity store provider to retrieve group information from a database using the database adapter.

In this task you create a file containing the elements for an adapter templates that specifies how to use your database tables as an identity store to map groups. The file describes the mapping of the GROUPMEMBERS_VW view to a virtual LDAP store. The view uses an outer join to ensure that you can reference fields from more than one table by the database adaptor.

1. Create a file named bi_sql_groups_adapter_template.xml.

2. Adapt the following elements to match your table and column attributes against LDAP server attributes.

   Note:

   For the element:

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

   This must match the user attribute and root User DN of the main authenticator. For example, for the default authenticator:

   uid=%uniquemember%,ou=people,ou=myrealm,dc=bifoundation_domain

   <?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=oracle,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_VW" field="G_NAME" type=""/>
               <attribute ldap="groupnameattr" table="GROUPMEMBERS" field="G_NAME" type=""/>
               <attribute ldap="description" table="GROUPMEMBERS_VW" field="G_NAME" type=""/>
               <attribute ldap="uniquemember" table="GROUPMEMBERS_VW" field="G_MEMBER" type=""/>
               <attribute ldap="orclguid" table="GROUPMEMBERS" field="G_NAME" type=""/>
            </objectClass>
         </mapping>
         <useCaseInsensitiveSearch>true</useCaseInsensitiveSearch>
         <connectionWaitTimeout>10</connectionWaitTimeout>
         <oracleNetConnectTimeout>0</oracleNetConnectTimeout>
         <validateConnection>false</validateConnection>
      </dataBase>
   </adapters>
   

3. Customize appropriate sections for the following elements:

   • ReplaceAttribute

     Specifies how to define the unique member for a group. The %uniquemember% is a placeholder for a value that is passed at runtime when looking up whether a user is a member of a group.

     The only aspect of this element you may want to change is the specification of the root for your users. While this is notional, by default it must match whatever you specify as the root of your user population when you run the libovdadapterconfig script in Step 7.

   • groupofuniquenenames

     Specifies how group attributes are mapped to database fields.

     You must map the following attributes:

     • cn maps to a unique name for your group.

     • uniquemember maps to the unique name for your user in the user/group mapping table in your database schema.

     Mapping the following attribute is optional:

     • description is optional.

     No other attributes are configurable.

4. Copy the adapter file into the following folder:

   ORACLE_HOME/oracle_common/modules/oracle.ovd/templates/

5. Open a command prompt/terminal at:

   ORACLE_HOME/oracle_common/bin

6. Ensure the following environment variables are set, for example:

   • ORACLE_HOME=oraclehome

   • WL_HOME=ORACLE_HOME/wlserver/

   • JAVA_HOME=ORACLE_HOME/jdk/jre

7. Run the libovdadapterconfig script to create a database adapter from the template file. The syntax is:

   libovdadapterconfig -adapterName <name of adapter> -adapterTemplate <name (NOT including path) of template file which defines adapater> -host localhost -port <Admin Server port> -userName <user id of account which has administrative privileges in the domain> -domainPath <path to the BI domain> -dataStore DB -root <nominal specification of a pseudo-LDAP query to treat as the "root" of this adapter - must match that specified in template for adapter 2 above> -contextName default -dataSourceJNDIName <JNDI name for DataSource which points at the database being mapped>
   

   For example:

   ./libovdadapterconfig.sh -adapterName biSQLGroupAdapter -adapterTemplate bi_sql_groups_adapter_template.xml -host localhost -port 9500 -userName weblogic -domainPath /opt/oracle_bi/user_projects/domains/bifoundation_domain/ -dataStore DB -root cn=users,dc=oracle,dc=com -contextName default -dataSourceJNDIName jdbc/BIDatabaseGroupDS
   
   

   Note:

   Use the JNDI name and not just the DS name for the dataSourceJNDIName.

   Note:

   The root parameter value should match the root dn specified in the <param name>="replaceattribute" element in the adaptor template. For example, if user is specified in the default authenticator, set the root to ou=people, ou=myrealm, dc=bifoundation_domain.

   The script should exit without error.

8. Restart WebLogic Administration Server and Managed servers.

   Note:

   When you start WebLogic, you can ignore the following Warning: BISQLGroupsProvider: Connection pool not usable .

   Log in to WebLogic and Oracle Analytics Server using credentials stored in the database.

A user with the appropriate privileges can log in to the Oracle WebLogic Server Administration Console using the WebLogic database authenticator.

1. Log in to Oracle WebLogic Server Administration Console.
2. In the Change Center, click Lock & Edit.
3. From Domain Structure, select Security Realms and click myrealm.
4. In Settings for myrealm, click the Providers tab, and then click the Authentication tab.
5. In Authentication Providers, click New.
6. In Create a New Authentication Provider, in Name type a name for the authentication providers such as UserGroupDBAuthenticator.
7. From the Type list, select ReadOnlySQLAuthenticator, and click OK.
8. From the Authentication Providers table, select the provider you just created.
9. In the Settings for <your new authentication provider name>, click the Provider Specific tab.
10. Optional: In the Provider Specific tab, if your password column is in plain text, select Plaintext Password Enabled.
11. In the Data Source Name field, type the name of an existing data source, for example, UserGroupsDS, to use this authentication provider.
    The data source name must match the existing data sources defined in Oracle WebLogic Server Administration Console.
12. In the Provider Specific tab, specify the SQL statements used to authenticate user access and to query your database tables.
13. After entering all of the required SQL statements for your authenticator, click Save.

Learn options available for creating SQL statements when implementing a SQL authentication provider.

Use a JAAS Control Flag for each provider to control how the authentication providers are used in the login sequence.

1. From the myrealm Settings page, click the Providers tab, and then click the Authentication tab.
2. From the Authentication Providers table, select DefaultAuthenticator.
3. In Settings for DefaultAuthenticator on the Configuration page in the Common tab, from the Control Flag list, select SUFFICIENT.
4. Click Save.

After adding a new authenticator, you can reorder the Authentication Providers table.

1. From the myrealm Settings page, click the Providers tab, and then click the Authentication tab.
2. In the Authentication Providers table, click Reorder.
3. In Reorder Authentication Providers, from Available, select the provider to use as the default, click the up arrow, and then click OK.
4. In the Change Center, click Activate Changes.

Follow these steps to configure a database adaptor to make the database appear like an LDAP server. This enables the virtualized identity store provider to retrieve user profile information from a database using the database adapter.

<?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>DBGUID</name>
               <class>oracle.ods.virtualization.engine.chain.plugins.dbguid.DBGuidPlugin</class>
               <initParams>

					                  <param name="guidAtribute" value="orclguid"/>
               </initParams>
            </plugin>
         </plugins>
         <default>
            <plugin name="DBGUID"/>
         </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="person" rdn="cn">
						<attribute ldap="cn" table="USER_VW" field="U_NAME" type=""/>
						<attribute ldap="uid" table="USER_VW" field="U_NAME" type=""/>
						<attribute ldap="usernameattr" table="USER_VW" field="U_NAME" type=""/>						
						<attribute ldap="loginid" table="USER_VW" field="U_NAME" type=""/>
						<attribute ldap="description" table="USER_VW" field="U_NAME" type=""/>
						<attribute ldap="orclguid" table="USER_VW" field="GUID" type=""/>
						</objectClass>
      </mapping>
      <useCaseInsensitiveSearch>true</useCaseInsensitiveSearch>
      <connectionWaitTimeout>10</connectionWaitTimeout>
      <oracleNetConnectTimeout>0</oracleNetConnectTimeout>
      <validateConnection>false</validateConnection>
   </dataBase>
</adapters>

1. Create a file named adapter_template_usergroup1.xml that maps the user table to a virtual LDAP store.
2. In the <mapping> element, add the <objectclass> element with attributes similar to the following example:

   <mapping>
             <joins/>
   	  <objectClass name="person" rdn="cn">
   		<attribute ldap="cn" table="USER_VW" field="U_NAME" type=""/              
                 <attribute ldap="uid" table="USER_VW" field="U_NAME" type=""/>
   		<attribute ldap="usernameattr" table="USER_VW" field="U_NAME" type=""/>
   		<attribute ldap="loginid" table="USER_VW" field="U_NAME" type=""/>
   		<attribute ldap="description" table="USER_VW" field="U_NAME" type=""/>
   		<attribute ldap="orclguid" table="USER_VW" field="GUID" type=""/>
   	  </objectClass>
         </mapping>

3. Create a file, named adapter_template_usergroup2.xml, to map the group table to a virtual LDAP store.
4. In the <objectClass name="groupofuniquenames"> element map the group table to the virtual LDAP store, as shown in the example:

     <mapping>
            <joins/>
   						<objectClass name="groupofuniquenames" rdn="cn">
   						<attribute ldap="cn" table="GROUPMEMBERS_VW" field="G_NAME" type=""/>
                                             <attribute ldap="groupnameattr" table="GROUPMEMBERS"  field="G_NAME" type=""/>
   						<attribute ldap="description" table="GROUPMEMBERS_VW" field="G_NAME" type=""/>
   						<attribute ldap="uniquemember" table="GROUPMEMBERS_VW" field="G_MEMBER" type=""/>
   						       <attribute ldap="orclguid" table="GROUPMEMBERS_VW" field="G_MEMBER" type=""/>
   						</objectClass>
         </mapping>

5. Copy the two adapter files into the following folder:

   ORACLE_HOME/oracle_common/modules/oracle.ovd/templates/

6. Open a command prompt/terminal from within:

   ORACLE_HOME/oracle_common/bin

7. Verify that the environment variables are set:

   • ORACLE_HOME=ORACLE_HOME/oraclehome

   • WL_HOME=ORACLE_HOME/wlserver

   • JAVA_HOME=ORACLE_HOME/jdk/jre

8. Run the libovdadapterconfig script to create each of the two adapters from the template files using the syntax as follows:

   libovdadapterconfig -adapterName <name of adapter> -adapterTemplate <name (NOT including path) of template file which defines adapter> -host localhost -port <Admin Server port> -userName <user id of account which has administrative privileges in the domain> -domainPath <path to the BI domain> -dataStore DB -root <nominal specification of a pseudo-LDAP query to treat as the "root" of this adapter - must match that specified in template for adapter 2 above> -contextName default -dataSourceJNDIName <JNDI name for DataSource which points at the database being mapped>		

   For example:

   ./libovdadapterconfig.sh -adapterName userGroupAdapter1 -adapterTemplate adapter_template_usergroup1.xml -host localhost -port 9500 -userName weblogic -domainPath /opt/oracle_bi/user_projects/domains/bifoundation_domain/ -dataStore DB -root cn=users,dc=oracle,dc=com -contextName default -dataSourceJNDIName jdbc/UserGroupDS
   
   ./libovdadapterconfig.sh -adapterName userGroupAdapter2 -adapterTemplate adapter_template_usergroup2.xml -host localhost -port 9500 -userName weblogic -domainPath /opt/oracle_bi/user_projects/domains/bifoundation_domain/ -dataStore DB -root cn=users,dc=oracle,dc=com -contextName default -dataSourceJNDIName jdbc/UserGroupDS

9. Restart WebLogic Administration Server and Managed servers.
10. Sign in to WebLogic and Oracle WebLogic Server using credentials stored in the database.