When I presented a case study out at Collaborate 11 in Orlando this week there seemed to be some interest in how we implemented security through LDAP. Below are the details on how we set this up.
Authentication
OBIEE provides out of the box support for active directory authentication. OBIEE passes the username and password entered in the login prompt to Active Directory which verifies the password is valid. This means usernames and passwords will not be maintained in OBIEE, rather Active Directory. The steps to configure this are as follows:
1. Create an LDAP Server using the admin Tool.
2. Create and LDAP Initialization Block and associate it with an LDAP Server.
3. Define a System Variable “USER” and map it to LDAP Attribute “sAMAccountName.”
4. Associate the “USER” system variable with the LDAP initialization block.
The details of these steps are outlined in the Oracle Business Intelligence Server Administration Guide, and the step by step setups will be documented here, when they occur.
Create LDAP Server
1. Login to Administrator.
2. Navigate to Manage -> Security to Open Security Manager.
3. From Security Manager, navigate to Action -> New -> LDAP Server.
4. Provide all of the necessary inputs to create the LDAP Server. Note: a Service Account should be used in place of a users account to bind to LDAP.
Field Name | Value |
Name | Active Directory Server |
Host Name | adhost.yourserver.com |
Port Number | 389 |
LDAP Version | 3 |
Base DN | DC=yourserver,DC=com |
Bind DN | myserviceacct |
Bind Password | ********** |
Connection timeout | 1 minute(s) |
ADSI | Yes |
SSL | No |
User name attribute type | sAMAccountName |
Create LDAP Initialization Block
1. Login to Administrator.
2. Navigate to Manage -> Variables to Open Variable Manager.
3. From Variable Manager, navigate to Action -> New -> Session -> Initialization Block...
a. Browse to select the LDAP data source created in the previous section.
b. Create the “USER” variable and set it as the Variable Target.
c. Check the Required for Authentication box.
The table below documents the values required to create the initialization block.
Field Name | Value |
Name | Authentication |
Disabled | No |
Data Source | Active Directory Server |
Variable Target | |
Name | USER |
LDAP Variable | sAMAccountName |
Name | DISPLAYNAME |
LDAP Variable | Cn |
Execution Precedence | None |
Required for authentication | Yes |
Authorization
Authorization will utilize Active Directory as well, however; there will be some custom development to accomplish this. The high-level steps below are required for the Authorization process:
1. Define Groups within OBIEE to give access to Subject Areas, Reports and Dashboards.
2. Define Groups within Active Directory. It is preferred if the names of the groups match, or that a simple rule can be applied to get from an Active Directory Group name to an OBIEE group name.
3. Create a PL/SQL (for Oracle DBs) function that utilized the dbms_ldap pre-installed package to retrieve and process Group information.
4. Create Repository Variables to support the PL/SQL function.
5. Define an additional connection pool for the OBIA user to be utilized explicitly for authorization and data security.
6. Create an Initialization block to execute the function and populate the “GROUP” and “WEBGROUP” system variables.
GET_GROUPS PL/SQL Function
The text below is the source code for the PL/SQL Function with in-line comments.
FUNCTION GET_GROUPS (p_ldap_host VARCHAR2
,p_ldap_host2 VARCHAR2
, p_ldap_port INTEGER
, p_ldap_search VARCHAR2
, p_dn VARCHAR2
, p_password VARCHAR2
, p_username VARCHAR2
, p_instance VARCHAR2) RETURN VARCHAR2 is
vSession DBMS_LDAP.session;
vResult PLS_INTEGER;
vUserHd DBMS_LDAP_UTL.handle;
vStatus VARCHAR2(1000);
vAttrs DBMS_LDAP.STRING_COLLECTION;
vAttrValues DBMS_LDAP.STRING_COLLECTION;
vMessage DBMS_LDAP.MESSAGE;
vCount INTEGER;
vUserName varchar2(200) := p_username;
vPassword varchar2(200) := p_password;
vInstance varchar2(3) := p_instance;
vDN VARCHAR2(200) := p_dn;
vHostName VARCHAR2(200) := p_ldap_host;
vHostName2 VARCHAR2(200) := p_ldap_host2;
vLDAPPort INTEGER := p_ldap_port;
vLDAPSearch VARCHAR2(1000) := p_ldap_search;
vGroups VARCHAR2(2000);
BEGIN
DBMS_LDAP.use_exception := TRUE; -- this variable allows for the use of LDAP exceptions.
begin
vSession := DBMS_LDAP.init -- initialize an LDAP session
( hostname => vHostName
, portnum => vLDAPPort
);
-- return 'Success';
exception when others then
begin
vSession := DBMS_LDAP.init -- on error connecting to primary LDAP Server attempt to connect to server #2
( hostname => vHostName2
, portnum => vLDAPPort
);
exception when others then
RAISE;
end;
end;
-- return vDn||'~'||vPassword;
vResult := DBMS_LDAP.simple_bind_s -- Login to LDAP with a service account.
( ld =>vSession
, dn => vDn
, passwd =>; vPassword
);
--return 'success';
vAttrs(1) := 'memberOf'; -- Define the LDAP attribute we want to retrieve
vResult := DBMS_LDAP.search_s(vSession, -- Searches the Tree based on Username
vLDAPSearch,
DBMS_LDAP.SCOPE_SUBTREE,
'(sAMAccountName='||vUserName||')',
vAttrs,
0,
vMessage);
vAttrValues := dbms_ldap.get_values(vSession, vMessage, vAttrs(1)); -- return set of memberOf attributes. This
-- attribute is a ";" delimited list of groups
-- a user is a member of
for x in vAttrValues.first..vAttrValues.last loop -- loop through returned values. Expect only one.
vAttrValues(x) := substr(vAttrValues(x),4,instr(vAttrValues(x),',') - 4); -- removes all but the LDAP group name.
if substr(vAttrValues(x),1,3) = 'BI-' -- filter to capture only the BI groups
and substr(vAttrValues(x),1,6) != 'BI-LOC' -- exclude data filter groups
and vInstance = substr(vAttrValues(x),-3) then -- get only instance specific groups
vGroups := vGroups || replace(replace(vAttrValues(x),'-'||vInstance),'BI-')||';'; -- remove prefix and suffix
end if;
end loop;
vGroups := substr(vGroups,1,length(vGroups) -1); -- remove the last ";" BI needs it to be gone
vResult := DBMS_LDAP.unbind_s(vSession); -- disconnect from LDAP server
return vGroups;
exception when others then
vGroups := vUserName||'~'|| vDN||'~'|| vPassword;
return vGroups;
END;
OBIEE Variables
The table below lists the new variables needed to support this security solution.
Variable | Value |
LDAP_HOST | Yourhost.yourdomain.com |
LDAP_PORT | 389 |
LDAP_HOST_2 | Myhost2.yourdomain.com |
SERVICE_DN | serviceaccount |
SERVICE_PWD | ********* |
LDAP_SEARCH_FILTER | DC=yourdomain,DC=com |
INSTANCE | ‘DEV’ |
