Friday, April 15, 2011

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.


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
Active Directory Server
Host Name
Port Number
LDAP Version
Base DN
Bind DN
Bind Password
Connection timeout
1 minute(s)
User name attribute type

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
Data Source
Active Directory Server
Variable Target

     LDAP Variable
     LDAP Variable
Execution Precedence
Required for authentication


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.


The text below is the source code for the PL/SQL Function with in-line comments. 
                    ,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);
    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); 

    DBMS_LDAP.use_exception := TRUE;  -- this variable allows for the use of LDAP exceptions.

    vSession := DBMS_LDAP.init                     -- initialize an LDAP session
                  ( hostname => vHostName
                  , portnum  => vLDAPPort
   -- return 'Success';
  exception when others then
      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
 -- 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 
     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;

OBIEE Variables

The table below lists the new variables needed to support this security solution.

Friday, February 4, 2011

What is Business Intelligence?

In the simplest terms Business Intelligence is reporting.  Within any business people rely on information systems to record the workings of the business.  Information about our customers, our vendors, and our interactions with them, or transactions is all captured with these wonderful machines we call computers.  

So we are pouring all of this information into these machines, capturing every move we make, and now it is time to get the information back out.  The problem is we don’t want the information to come out the way we put it in.  What we want is a chart or a graph, or a simple table that tells us what’s going on, where our successes are and where things need to improve.   This is business intelligence.  Extracting from the mountains of information we have captured, the essence of what’s occurring to report status and allow us to make decisions about the future of our business.

If that were the whole picture then there would not be much to talk about.  As it turns out, we want access to this information differently depending on the situation.  Some situations require a printed report, others call for a highly summarized view that guides us to more detail to isolate issues.  Others still require an extremely flexible interaction that facilitates what-if analysis and allows us to manipulate information in any manner we see fit. 

In summary, Business Intelligence is the methodologies and technologies of providing businesses information from all of the data they have collected.  In the next, entry I will cover a number of typical business problems and discuss how they are approached with Business Intelligence technologies.

What’s coming Up:
  •              What is the problem we are trying to solve
  •               Why  you can’t have that report right now
  •                Introducing the Oracle BI Enterprise Edition Platform

Friday, January 28, 2011

My First Blog Entry

I have spent the last year trying to figure out to start this blog.  Yesterday a friend gave me some direction: Just Start, make it short and sweet, and update it often.

What I hope to accomplish:   

My primary goal is to start a dialogue about Business Intelligence, specifically Oracle Business Intelligence, where I chronicle my experiences and musings. 
In addition, I want to use this blog to “de-geek” a little.  I spend a good portion of my time in the bits and bytes and I would like to expand my horizons by doing the least geeky thing possible, ahem...write a blog. Not a blog about poetry but BI no less.  I can already see this might be a fruitless effort, but who knows, maybe I will start blogging about BI in iambic pentameter.   
Finally, I hope to learn from others through the comments of my sole remaining reader.

What type of blog is this?  

Well it will be about Oracle BI basically.  In my adventures with my various customers I have come to the conclusion that many do not really understand the big picture.  This is obviously the fault of consultants like me, software vendors and some IT Professionals.  We just simply do not speak the same language as the business community we serve. 

The first series of entries will address; What is BI, Why is BI important and Why should business users trust us.

What’s coming Up:

  •     What is Business Intelligence
  •     What is the problem we are trying to solve
  •          Why you can’t have that report right now

I invite any comments from anyone on the direction of this series of posts…