Querying Active Directory Through Oracle

It’s usually easy to tell when a muskie fisherman in another boat sees a good sized fish.  He’ll usually use the trolling motor to slowly move around a small area, picking it apart from different angles with his casts.  He may stay in an area of 100 square yards for a couple hours, trying to find the right presentation and angle to get the follower to bite.  Seeing a big fish follow your bait to the boat is exciting, but if you want to catch more of these followers, you need to be ready to change go at them with something different.  If bucktail gets them to follow lazily, try a jerkbait.  If casting from shallow water brings them in too late, try casting from the deep.

In my previous post, I showed how to query Active Directory from SQL Server.  This was nice and easy to do; since they’re both Microsoft products, they integrate really easily.  However, what if you need to do it in Oracle?  Fortunately, Oracle realized this would be a need and included a package called DBMS_LDAP to help us get at this information.  Here’s how to use this package to retrieve data from Active Directory into Oracle:

The first step is to add a table to hold the data.  I created a table called ACTIVEDIRECTORY:

CREATE TABLE "ACTIVEDIRECTORY"
  (
    "DISPLAYNAME"VARCHAR2(255 BYTE),
    "EMAIL"      VARCHAR2(255 BYTE),
    "EXTENSION"  VARCHAR2(10 BYTE),
    "DIRECTDIAL" VARCHAR2(25 BYTE),
    "MOBILE"     VARCHAR2(25 BYTE),
    "RTYPE"      CHAR(1 BYTE)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "TS_TABLESPACE_PD";
COMMENT ON COLUMN "ACTIVEDIRECTORY""RTYPE"
IS
  'E=Employee, R=Room, G=Group, V=Vendor';
  ALTER TABLE "ACTIVEDIRECTORY"MODIFY ("RTYPE"NOT NULL ENABLE);
  ALTER TABLE "ACTIVEDIRECTORY"MODIFY ("DISPLAYNAME"NOT NULL ENABLE);

Nothing too special about this table, I am going to be capturing the Displayname, Email, Extension, DirectDial, and Mobile phone numbers from Active Directory. I create a Type field that I am going to be manually adding to differentiate the type of records there. All rows should have a Displayname and a Type.

Next I need to create a stored procedure which will capture the data and insert it into the table.  I had to Google some examples to get the code working.  The most helpful example I found was at Oracle-Base.

CREATE OR REPLACE PROCEDURE Refresh_ActiveDirectory
IS
  l_ldap_host   VARCHAR2(256) := 'domaincontroller.domain.com';
  l_ldap_port   VARCHAR2(256) := '389'; --Default
  l_ldap_user   VARCHAR2(256) := domain\username;
  l_ldap_passwd VARCHAR2(256) := ********;
  l_ldap_base   VARCHAR2(256) := 'OU=Users,OU=ougroup,dc=dcgroup,dc=com';
  dispname      VARCHAR2(255);
  email         VARCHAR2(255);
  Extension     VARCHAR2(10);
  DirectDial    VARCHAR2(25);
  Mobile        VARCHAR2(25);
  RType         CHAR(1) := 'E';
  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;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ACTIVEDIRECTORY';
  -- 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)                                                   := 'displayname';
  l_attrs(2)                                                   := 'mail';
  l_attrs(3)                                                   := 'mobile';
  l_attrs(4)                                                   := 'homephone';
  l_attrs(5)                                                   := 'ipphone';
  l_retval                                                     := DBMS_LDAP.search_s(ld => l_session, base => l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, filter => '(&(&(objectclass=user)(!(physicaldeliveryofficename=no phone)))(!(useraccountcontrol=514)))', attrs => l_attrs, attronly => 0, res => l_message);
  IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
    -- Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);
    << entry_loop >>
    WHILE l_entry IS NOT NULL
    LOOP
      -- Get all the attributes for this entry.
      l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
      --Clear variables
      dispname   := NULL;
      email      := NULL;
      Extension  := NULL;
      DirectDial := NULL;
      Mobile     := NULL;
      << attributes_loop >>
      WHILE l_attr_name IS NOT NULL
      LOOP
        -- Get all the values for this attribute.
        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
          IF l_attr_name    = 'displayName' THEN
            dispname       := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'ipPhone' THEN
            DirectDial     := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'mail' THEN
            email          := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'homePhone' THEN
            Extension      := SUBSTR(l_vals(i),1,200);
          ELSIF l_attr_name = 'mobile' THEN
            Mobile         := SUBSTR(l_vals(i),1,200);
          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;
      INSERT
      INTO ACTIVEDIRECTORY
        (
          DISPLAYNAME,
          EMAIL,
          EXTENSION,
          DIRECTDIAL,
          MOBILE,
          RTYPE
        )
        VALUES
        (
          dispname,
          email,
          Extension,
          DirectDial,
          Mobile,
          RType
        );
      l_entry := DBMS_LDAP.next_entry(ld => l_session, msg => l_entry);
    END LOOP entry_loop;
  END IF;
  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  --Delete non-person users
  DELETE
  FROM ACTIVEDIRECTORY
  WHERE DisplayName IN('Laptop','Report User','huffman_01','time test','Time Clock','manager','VPN Test','Tanya VPN');
  
END;

An important thing to note about this stored procedure is the letter case. We keep our SQL Server collation such that case sensitivity doesn’t matter, but in Oracle it does. Pay attention to your collation settings to determine whether or not you need to worry about capitalization.
At this point I had the code working properly in the SQL Developer window, but when I called the stored procedure I got an error:

ORA-24247: network access denied by access control list (ACL).

Oracle uses access control lists to enforce security on network locations. To get around this, I created an access control list that allowed access to my domain controller:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'ldap_access.xml',
    description  => 'Permissions to access LDAP servers.',
    principal    => 'dbname',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'ldap_access.xml',
    host         => 'domaincontroller.domain.com',
    lower_port   => 389,
    upper_port   => 389
    );
   COMMIT;
END;

You can see access control lists in your database with these queries:

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

20150115 Oracle LDAP
20150115 Oracle LDAP 2

The last step is to set up a job to run the stored procedure to refresh the table. I’ve chosen a job that runs daily.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB ( job_name => 'Refresh_ActiveDirectory', 
                              job_type => 'STORED_PROCEDURE', 
                              job_action => 'REFRESH_ACTIVEDIRECTORY', 
                              start_date => '13-JAN-15 11:00:00 PM', 
                              repeat_interval => 'FREQ=DAILY');
END;

Now I have data from Active Directory flowing into my database daily.

Advertisements

8 thoughts on “Querying Active Directory Through Oracle

    • Unfortunately, LDAP has a query size limit of 1,000, which means any query with more than 1,000 results will error out. This is intended functionality and something that you will need to work around. I would suggest finding a way to filter your results and then looping through the different slices. For example, if you want to filter on name, run the query once for each person’s name that starts with each letter of the alphabet. The query will return up to 26 results, then you can bring them all together.

      Like

    • You can do a Google search that will explain these in better detail, but at a rough, high level:
      At the right you have the domain. If my domain is davegugg.com, I would have dc=davegugg, dc=com. Next you have to find the container that holds the user (or whatever else) you are searching for. So if my user is in a folder called DBAs inside another folder called IT, I would have OU=DBAs, OU=IT. So my entire l_ldap_base would be OU=DBAs, OU=IT, dc=davegugg, dc=com.
      To find your directory structure you can log into a windows server and bring up the Active Directory Users and Computers console. If it isn’t on there yet, open the Microsoft Management Console (search for mmc at the start menu) and click File -> Add/Remove Snap-in.
      I hope that helps MDev!

      Like

  1. Thanks for your great reply. Definitely you help me but also I need your help again where I applied your example with some edits to get the employee id attribute which by default defined on active directory but till now I can’t get it. I did every thing but really I don’t know where is the problem. You can find my code below. I’m waiting your response 🙂

    CREATE OR REPLACE PROCEDURE ActiveDirectoryAuth (
    p_user IN VARCHAR2,
    p_password IN VARCHAR2,
    emp_id OUT VARCHAR2)
    AS
    l_ldap_host VARCHAR2(256) := ‘10.10.73.12’;
    l_ldap_port VARCHAR2(256) := ‘389’; –Default
    l_ldap_base VARCHAR2(256) := ‘CN=Users,DC=company,DC=com’;
    l_ldap_user VARCHAR2(256);
    l_ldap_passwd VARCHAR2(256) ;
    l_ldap_dc varchar2(250) :=’@company.com’;
    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;

    BEGIN
    — Choose to raise exceptions.
    DBMS_LDAP.USE_EXCEPTION := TRUE;

    — Connect to the LDAP server.
    l_ldap_user := p_user||l_ldap_dc;
    l_ldap_passwd :=nvl(p_password,0);
    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 Employee Id attribute
    l_attrs(1) := ’employeeID’;

    l_retval := DBMS_LDAP.search_s(ld => l_session,
    base => l_ldap_base,
    scope => DBMS_LDAP.SCOPE_SUBTREE,
    filter => ‘objectclass=*’,
    attrs => l_attrs,
    attronly => 0,
    res => l_message);
    IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
    — Get all the entries returned by our search.
    l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);
    <>
    WHILE l_entry IS NOT NULL
    LOOP
    — Get all the attributes for this entry.
    l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
    –Clear variables
    emp_id := NULL;

    <>
    WHILE l_attr_name IS NOT NULL
    LOOP
    — Get all the values for this attribute.
    <>
    FOR i IN l_vals.FIRST .. l_vals.LAST
    LOOP
    IF l_attr_name = ’employeeID’ THEN
    emp_id := SUBSTR(l_vals(i),1,200);
    ELSE
    emp_id :=’error’;
    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);
    END LOOP entry_loop;
    END IF;
    — Disconnect from the LDAP server.
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    END;

    When I test I don’t get results !!!
    DECLARE
    P_USER VARCHAR2(32767);
    P_PASSWORD VARCHAR2(32767);
    EMP_ID NUMBER;

    BEGIN
    P_USER := ‘username’;
    P_PASSWORD := ‘password’;
    EMP_ID := NULL;

    XXKOTC_MOBILE.ACTIVEDIRECTORYAUTH ( P_USER, P_PASSWORD, EMP_ID );
    COMMIT;
    END;

    Thank you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s