OBIEE Security – Active Directory Solution to assign Users to Groups

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...

4.       Provide all of the necessary inputs to create the 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’