Its often useful to have the ability when customizing an OBIEE environment, to have access to the corporate LDAP/Active Directory server.
A good example is a client may wish to drive a prompt / default selection on a dashboard page, based on a users office location. Often an employees location is recorded and kept up to date in the corporate LDAP Server that controls a users account, in many cases Microsoft Active Directory, but could be any LDAP Server.
Below is an example of some code you could use to deploy a function into an Oracle database, that will allow you to pass in the username and LDAP attribute you want to query, and return the users information from the LDAP Server.
You might find it useful to first have an understanding of the LDAP Servers hierarchy, in order to retrieve the information. A useful free LDAP client could be used, visit http://www.ldapadmin.org/
See screenshots below, showing a sample LDAP server, for which we could return data.
Root level
Detail view of a user
So some step by step instructions on how this could work, creating a function within an Oracle database, that you could then call within OBIEE / Javascript etc, in order to return LDAP information, that could be used within say an INIT Block during login
First thing, is ensure there are no ACL ( access control list issues ). You often need to explicitly allow Oracle RDBMS permission to contact external services. The SQL scripts below when run as SYSDBA/SYS allow creation of an ACL that will allow the RDBMS to contact the LDAP Server
Check ACLS that might already exist
select * from dba_network_acls
AS SYS SYSDBA execute the following
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => ‘mirw_hlc_ad_acl.xml’,
description => ‘NETWORK ACL’,
principal => ‘MIRW’,
is_grant => true,
privilege => ‘connect’);
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => ‘mirw_hlc_ad_acl.xml’,
principal => ‘MIRW’,
is_grant => true,
privilege => ‘resolve’);
end;
/
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => ‘mirw_hlc_ad_acl.xml’,
host => ‘hlc.ad.local’,
lower_port => 389,
upper_port => 389);
end;
/
Once the ACL is setup, you can create the function. Example function below. Lets assume my username and DN within my HLC Ad is as follows :-
CN=Passmore\, Nick,OU=Standard,OU=Users,OU=HLC,DC=HLC,DC=ad,DC=local
So my username is actually nick.passmore, but within AD is stored as passmore, nick.
Usage : pass in username and AD/LDAP Attribute your want to return the value for
Issue SQL :-
select get_hlc_ad_info(‘Passmore\, Nick’,’physicalDeliveryOfficeName’) as defaultlocation from dual;
Output :-
SQL> select get_hlc_ad_info(‘Passmore\, Nick’,’physicalDeliveryOfficeName’) as defaultlocation from dual;
DEFAULTLOCATION
——————————
288 Hill Lane
SQL>
Call from OBIEE as using the EVALUATE function, you could use this as an expression within an OBIEE Analysis Column
EVALUATE(‘get_hlc_ad_info(%1,%2)’,’Passmore\, Nick’,’physicalDeliveryOfficeName’)
Note the use of the escape sequence in order to pass “Passmore, Nick” to the function
Full Code for Function :-
CREATE OR REPLACE FUNCTION get_hlc_ad_info (username in VARCHAR2, v_attribute varchar2) RETURN varchar2
AS
ldap_host VARCHAR2(256);
ldap_port PLS_INTEGER;
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
retval PLS_INTEGER;
my_session DBMS_LDAP.session;
subscriber_handle DBMS_LDAP_UTL.HANDLE;
sub_type PLS_INTEGER;
subscriber_id VARCHAR2(2000);
my_pset_coll DBMS_LDAP_UTL.PROPERTY_SET_COLLECTION;
my_property_names DBMS_LDAP.STRING_COLLECTION;
my_property_values DBMS_LDAP.STRING_COLLECTION;
group_handle DBMS_LDAP_UTL.HANDLE;
group_id VARCHAR2(2000);
group_type PLS_INTEGER;
user_handle DBMS_LDAP_UTL.HANDLE;
user_id VARCHAR2(2000);
user_type PLS_INTEGER;
my_mod_pset DBMS_LDAP_UTL.MOD_PROPERTY_SET;
my_attrs DBMS_LDAP.STRING_COLLECTION;
group_string VARCHAR2(32767);
full_name VARCHAR2(32767);
BEGIN
— Please customize the following variables as needed
ldap_host := ‘DAS-ldap.nt.ad.local’;
ldap_port := ‘389’;
ldap_user := ‘HLC\SVC_OBIEE’;
ldap_passwd := ‘passwprd’;
sub_type := DBMS_LDAP_UTL.TYPE_DN;
user_type := DBMS_LDAP_UTL.TYPE_DN;
user_id := ‘cn=’ || username || ‘,OU=Standard,OU=Users,OU=HLC,DC=nt,DC=ad,DC=local’;
group_type := DBMS_LDAP_UTL.TYPE_DN;
group_id := ‘OU=MIGroups,OU=SecurityGroups,OU=Groups,DC=hlc,DC=ad,DC=local’;
— Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;
—————————–
— Connect to the LDAP server
— and obtain and ld session.
—————————–
my_session := DBMS_LDAP.init(ldap_host,ldap_port);
—————————–
— Bind to the directory
—————————–
retval := DBMS_LDAP.simple_bind_s(my_session,ldap_user,ldap_passwd);
—————————–
— Create User Handle
—————————–
retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘create_user_handle returns : ‘ || to_char(retval));
END IF;
full_name := ”;
———————————————————————
— Retrieve User Properties
—
———————————————————————
— like .. get firstname
my_attrs.delete();
–my_attrs(1) := ‘givenname’;
my_attrs(1) := v_attribute;
group_string := NULL;
retval := DBMS_LDAP_UTL.get_user_properties(my_session,
user_handle,
my_attrs,
DBMS_LDAP_UTL.ENTRY_PROPERTIES,
my_pset_coll);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘get_user_properties returns : ‘ || to_char(retval));
END IF;
———————————————————————
— Print properties obtained for the user.
—
———————————————————————
IF my_pset_coll.count > 0 THEN
FOR i in my_pset_coll.first .. my_pset_coll.last LOOP
retval := DBMS_LDAP_UTL.get_property_names(my_pset_coll(i),
my_property_names);
IF my_property_names.count > 0 THEN
FOR j in my_property_names.first .. my_property_names.last LOOP
retval := DBMS_LDAP_UTL.get_property_values(my_pset_coll(i),
my_property_names(j),
my_property_values);
IF my_property_values.COUNT > 0 THEN
FOR k in my_property_values.FIRST..my_property_values.LAST LOOP
DBMS_OUTPUT.PUT_LINE( my_property_names(j) || ‘ : ‘ ||
my_property_values(k));
full_name := initcap(my_property_values(k));
DBMS_OUTPUT.PUT_LINE( ‘value is : ‘ || my_property_values(k));
END LOOP;
END IF;
END LOOP;
END IF; — IF my_property_names.count > 0
END LOOP;
END IF; — If my_pset_coll.count > 0
return full_name;
—————————–
— Free handle
—————————–
DBMS_LDAP_UTL.free_handle(user_handle);
— Unbind from the directory
retval := DBMS_LDAP.unbind_s(my_session);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
–Handle Errors
DBMS_OUTPUT.PUT_LINE(‘unbind_s returns : ‘ || to_char(retval));
END IF;
— Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘ Error code : ‘ || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(‘ Error Message : ‘ || SQLERRM);
DBMS_OUTPUT.PUT_LINE(‘ Exception encountered .. exiting’);
END get_hlc_ad_info;
/