Access Active Directory data with a view

From: willy <w.tadema_at_planet.nl>
Date: 14 Aug 2006 00:17:39 -0700
Message-ID: <1155539859.127302.315750@i42g2000cwa.googlegroups.com>

I'm an Oracle newbie and I've got a question concerning Active Directory. By modifying the Oracle example script slightly I've managed

to make a PL/SQL script to retrieve the sAMAccountName and employeenumber for users in our Active Directory.

I'd like to make these data accessible with a single view. I now have a PL/SQL procedure which fills the table ActiveDirectory and

on this table I've build a view.
Can this be done in fewer steps? Or with only a view? Any help would be

appreciated.

The code of the procedure I've build is listed below.

Willy Tadema

SET SERVEROUTPUT ON
DECLARE

  l_ldap_host    VARCHAR2(256) := 'xxx';
  l_ldap_port    VARCHAR2(256) := 'xxx';
  l_ldap_user    VARCHAR2(256) := 'xxx';
  l_ldap_passwd  VARCHAR2(256) := 'xxx';
  l_ldap_base    VARCHAR2(256) := 'xxx';
  l_filter       VARCHAR2(256) := 'sAMAccountName=T*'; -- Alleen

userid's die beginnen met de letter T

  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;

  TYPE t_gebruikersnaam IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
  TYPE t_registratienr IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;

  l_gebruikersnaam t_gebruikersnaam;
  l_registratienr t_registratienr;
  entry_index PLS_INTEGER;

BEGIN

• Foutafhandeling activeren DBMS_LDAP.USE_EXCEPTION := TRUE;
• Verbinding maken met de 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);

• Haal attributen op l_attrs(1) := 'sAMAccountName'; -- Haal USERID op l_attrs(2) := 'employeeNumber'; -- Haal employeeNumber op l_retval := DBMS_LDAP.search_s(ld => l_session, base => l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, filter => l_filter, attrs => l_attrs, attronly => 0, res => l_message);

  IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN

• Ga naar de eerste entry van de geretourneerde resultaatset. l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);

    entry_index := 1;

    << entry_loop >>
    WHILE l_entry IS NOT NULL LOOP

• Haal alle attributen op voor deze entry l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,

                                                ldapentry => l_entry,
                                                ber_elem  =>
l_ber_element);
      << attributes_loop >>
      WHILE l_attr_name IS NOT NULL AND l_attr_name
IN('sAMAccountName','employeeNumber') LOOP
        -- Haal alle waarden op van dit attribuut
        l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                        ldapentry => l_entry,
                                        attr      => l_attr_name);
        << values_loop >>
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
          DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' =

' || SUBSTR(l_vals(i),1,200));

          IF l_attr_name='sAMAccountName' THEN
            l_gebruikersnaam(entry_index) := substr(l_vals(i),1,15);
          ELSIF l_attr_name='employeeNumber' THEN
            l_registratienr(entry_index) := substr(l_vals(i),1,9);
          END IF;


        END LOOP values_loop;
        l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,

                                                ldapentry => l_entry,
                                                ber_elem  =>
l_ber_element);
      END LOOP attibutes_loop;


      l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                      msg => l_entry);


          IF (l_gebruikersnaam.count < entry_index OR

l_registratienr.count <
entry_index) THEN BEGIN

        IF l_gebruikersnaam.count < entry_index THEN
                  l_gebruikersnaam(entry_index) := null;
        END IF;
        IF l_registratienr.count < entry_index THEN
          l_registratienr(entry_index) := null;
        END IF;
            END;
      END IF;
      entry_index := entry_index + 1;

    END LOOP entry_loop;
  END IF;

• Verbreek de verbinding met de LDAP server. l_retval := DBMS_LDAP.unbind_s(ld => l_session); DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
• Maak de tabel ActiveDirectory eerst leeg en voeg dan de nieuwe, actuele records toe --TRUNCATE TABLE ActiveDirectory; EXECUTE IMMEDIATE 'TRUNCATE TABLE ActiveDirectory'; FORALL i IN l_gebruikersnaam.FIRST .. l_gebruikersnaam.LAST INSERT INTO ActiveDirectory VALUES (l_gebruikersnaam(i), l_registratienr(i)); COMMIT;
• Verwijder de PL/SQL tabellen l_gebruikersnaam.DELETE; l_registratienr.DELETE;

END;
/ Received on Mon Aug 14 2006 - 02:17:39 CDT