OBIEE Security – Active Directory Solution to assign Users to Groups

noreply@blogger.com (Nick Roppolo) — Fri, 15 Apr 2011 13:10:00 +0000

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;

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

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’

What is Business Intelligence?

noreply@blogger.com (Nick Roppolo) — Fri, 04 Feb 2011 23:00:00 +0000

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

My First Blog Entry

noreply@blogger.com (Nick Roppolo) — Fri, 28 Jan 2011 23:42:00 +0000

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…

Nick's BI Blog

OBIEE Security – Active Directory Solution to assign Users to Groups

Authentication

Create LDAP Server

Create LDAP Initialization Block

Authorization

GET_GROUPS PL/SQL Function

OBIEE Variables

What is Business Intelligence?

My First Blog Entry