OBIEE 11g Get MS Active Directory Groups Function GETLDAPGROUPS

Function to Get LDAP MS AD Groups for use within OBIEE 10g and 11g

Please ammend server settings within function below.


  l_ldap_host VARCHAR2(256) := 'l_ldap_host';
  l_ldap_port VARCHAR2(256) := '111';
  l_ldap_user VARCHAR2(256) := 'cn=obieeservice,ou=whateva,dc=shahed-,dc=co,dc=uk';
  l_ldap_passwd VARCHAR2(256) := 'password';
  l_base_dn VARCHAR2(256) := 'dc=shahed,dc=co,dc=uk';

These settings can be given to you by your MS AD Administrator




CREATE OR REPLACE
FUNCTION GETLDAPGROUPS(Username in Varchar2) RETURN VARCHAR2 AS
--------------------------------------------------------------
-- OBIEE Group Membership Extraction
-- Function to Extract ADSI Member groups for a User
-- Developed by Shahed Munir
-- Client . Version 1.0
-- 27 September 2010
--------------------------------------------------------------
-- Adjust as necessary.
  l_ldap_host VARCHAR2(256) := 'l_ldap_host';
  l_ldap_port VARCHAR2(256) := '111';
  l_ldap_user VARCHAR2(256) := 'cn=obieeservice,ou=whateva,dc=shahed-,dc=co,dc=uk';
  l_ldap_passwd VARCHAR2(256) := 'password';
  l_base_dn VARCHAR2(256) := 'dc=shahed,dc=co,dc=uk';
  l_ldap_base VARCHAR2(256) := username;
  l_retval PLS_INTEGER;
  l_session DBMS_LDAP.session;
  l_attrs DBMS_LDAP.string_collection;
  l_message DBMS_LDAP.message;
  l_entry DBMS_LDAP.message;
  l_attr_name VARCHAR2(256);
  l_ber_element DBMS_LDAP.ber_element;
  l_vals DBMS_LDAP.string_collection;
  l_dn VARCHAR2(2000);
  l_count NUMBER;
  l_result VARCHAR2(20000) := '';
BEGIN
  -- Choose to raise exceptions.
  DBMS_LDAP.USE_EXCEPTION := TRUE;
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host, portnum => l_ldap_port);
  l_retval := DBMS_LDAP.simple_bind_s(ld => l_session, dn => l_ldap_user, passwd => l_ldap_passwd);
  -- Get all attributes
  l_attrs(1) := 'memberOf'; -- retrieve only role name
l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                 base     => l_base_dn,
                                 scope    => DBMS_LDAP.SCOPE_SUBTREE, --TODO
                                 filter   => '(samaccountname=*'|| l_ldap_base ||')',
                                 attrs    => l_attrs,
                                 attronly => 0,
                                 res      => l_message);
 
  l_count := DBMS_LDAP.count_entries(ld => l_session, msg => l_message);
 
  IF l_count > 0 THEN
    -- Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);
    l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);


    l_vals := DBMS_LDAP.get_values(ld => l_session, ldapentry => l_entry, attr => l_attr_name);
    FOR valIndx IN l_vals.first .. l_vals.last LOOP
      l_result := l_result || SUBSTR(l_vals(valIndx),4,INSTR(l_vals(valIndx),',')-4) || ';';
    END LOOP;
  END IF;
 
  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
 return l_result;
END GETLDAPGROUPS;