About
A authentication provider called BISQLGroupProvider that provide the groups through database look-up.
This group can then be linked to application role.
The database just stores the groups to be associated with users. There is no password for the users.
Articles Related
Restriction
You can only have a single LDAP Authenticator linked to a single database Group look-up 'authenticator'.
For example, if you need Delivers to deliver content to members of an application role the following restrictions apply:
- You can only pair a single LDAP authenticator with a single BISQLGroupProvider. That is, if you configure multiple LDAP authenticators and you want to retrieve group membership from the BISQLGroupProvider, you will not be able to deliver content to all members of an application role, as the security code invoked by Delivers is unable to resolve Application Role membership based on users and group membership in this configuration.
- You cannot have the same group defined in more than one Identity Store (for example, you cannot have a group called BIAdministrators in both LDAP and the database). If you do, the security code invoked by Delivers is unable to resolve application role membership.
Prerequisites
- For Release before and 11.1.1.5.0, the patch epmos/faces/PatchResultsNDetails must be applied
Steps
Installation
The JAR file BISecurityProviders.jar contains the authenticator. and must be copied from
MW_HOME/ORACLE_HOME/bifoundation/security/providers
to
MW_HOME/wlserver_10.3/server/lib/mbeantypes
Then restart Weblogic (ie the Administration Server)
Schema
The advantage of defining tables (or views) identical to the below figure is that the configuration of the BISQLGroupProvider can use the default SQL
GROUPMEMBERS_VW is a view with an outer join between GROUPMEMBERS and GROUPS tables.
You must map the users in your LDAP store to Groups in your database table by login name. The value of G_MEMBER in the GROUPMEMBERS table must match the value of the LDAP attribute used for login (for example, uid, cn or mail), as specified in the LDAP authenticator. For example, you should not map the database groups by uid if the login attribute is mail.
Database Connection
Data Sources > New > Generic Data Source.
- Name - For example, enter: BIDatabaseGroupDS
- JNDI Name - For example, enter: jdbc/BIDatabaseGroupDS (The JNDI path to which this JDBC data source will be bound).
- Database Type - For example, Oracle …
- and all others parameters: JDBC Url, User and Password
New Authentication Provider
Create a New Authentication Provider with
- as Name: MySQLGroupProvider
- and as type: BISQLGroupProvider
SQL statements
In the Provider Specific tab, you can specify the SQL statements used to query and authenticate against your database tables.
the question mark (?) is a runtime query placeholder and must be always present (rather than hardcode a user or group name).As the data from the database is case sensitive, be sure to include the UPPER function in any BISQLGroupProvider SQL statements:
SELECT G_MEMBER FROM GROUPMEMBERS WHERE UPPER(G_NAME) =UPPER( ? ) AND UPPER(G_MEMBER) =UPPER(?)
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 members 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
Enabling Virtualization
You configure the identity store to enable virtualization so that more than one identity store can be used with the identity store service, and therefore user profile information can be split across different authentication providers (identity stores).
- Go to the identity store configuration page
- Add this two properties (The case of the property name is important.
):
- Property Name=virtualize, Value=true
- Property Name=OPTIMIZE_SEARCH, Value=true
If you set the virtualize custom property value to true, Oracle recommends that the BISystemUser must exist in only one identity store.
Authentication providers Control Flag
When using multiple authentication providers, configure the Weblogic - Login Module (Control Flag and authentication flow) setting for all authentication providers as follows:
- SUFFICIENT: If each user appears in only one authentication provider. (If two Authenticators are configured, they should both be marked as SUFFICIENT 11.1.1.3 will use the first Authenticator. OBIEE 11.1.1.5 and higher supports chaning of authenticators)
- OPTIONAL: If users appear in more than one authentication provider. For example, if a user's group membership is spread across more than one authentication provider
Configure a database adaptor
You configure a database adaptor to make it appear like an LDAP server, which enables the virtualized identity store provider to retrieve group information from a database using the database adapter.
- Create a file named bi_sql_groups_adapter_template.xml. This file describes the mapping of the GROUPMEMBERS table to a virtual LDAP store.
See 3.4.4.4.3 Configuring a Database Adaptor to Retrieve Group Information
You cannot modify an existing database adapter, so if you make an error in either the libovdadapter command, or the templates you use to create the adapters, you must delete then recreate the adapter.
Testing
Add a group to an application role
Configuration
Cache
Critical
Unable to Sign In
An error occurred during authentication.
Try again later or contact your system administrator
See 3.4.6 Configuring Multiple Authentication Providers so that When One Fails, Users from Others can Still Log In to Oracle Business Intelligence Manually edit adapters_os.xml in
<MW_HOME>/user_projects\domains\bifoundation_domain\config\fmwconfig\ovd\default
and change the
trueto
falsefor the BISQLProvider adapter.
Making this adapter non-critical (i.e. if it fails it will carry on) should not matter. After this change, restart the WLS server.