Translate

Wednesday, 3 November 2010

Shahed

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;

Blog Archive

About Authors

Shahed Munir

Krishna Udathu

Shahed and Krishna are Oracle / Big Data Experts