SQL & PL/SQL

Version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Hello,

I'm hoping to get help with extracting 3 pieces of user data from Active Directory.

I have an APEX application that I need to get the Active Directory GUID, First Name, and Last Name.

I've found some posts, specifically https://community.oracle.com/message/13055424, where Kiran creates a function. I've modified this to be a page process instead.

I'm not seeing any errors when I put the page (page 101) into Debug mode and I have a table that I use to insert values into for debugging it that I've put in multiple spots but I'm not even getting any entries in my debugging table (I've taken out those inserts for this post to clean it up). I do see the page process getting called in the Debug window with all the code.

I would like the values of these 3 attributes to go into 3 Application Items to be used for pulling Roles from a SQL Server table (that I have to control over) for Authorization.

This is what I have:

DECLARE
    l_ldap_host     VARCHAR2 (256) := 'xx.xx.x.xx';
    l_ldap_port     VARCHAR2 (256) := '389';
    l_ldap_base     VARCHAR2 (256) := 'cn=user,dc=xxxxx,dc=org';
    l_dn_prefix     VARCHAR2 (100) := 'xxxxx\';
    l_ldap_user    VARCHAR2(256) := 'cn='||:P101_USERNAME;
    l_ldap_passwd  VARCHAR2(256) := :P101_PASSWORD;
    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_attr_value   VARCHAR2(256);
    l_ber_element  DBMS_LDAP.ber_element;
    l_vals         DBMS_LDAP.string_collection;
v_cnt   NUMBER;
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||','||l_ldap_base,
                                        passwd => l_ldap_passwd);
    -- Get specific attributes
    l_attrs (1) := 'givenName';  --First Name
    l_attrs (2) := 'sn';         --Last Name
    l_attrs (3) := 'objectGUID'; --ADGuid
    l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                   base     => l_ldap_base,
                                   scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                   filter   => l_ldap_user,
                                   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);
        << 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
            l_attr_value := l_vals(i);
            IF (UPPER (l_attr_name) = 'GIVENNAME') THEN
                :G222_CDM_APEX_FIRST_NAME := l_attr_value;
            ELSIF (UPPER (l_attr_name) = 'SN') THEN
                :G222_CDM_APEX_LAST_NAME := l_attr_value;
            ELSIF (UPPER (l_attr_name) = 'OBJECTGUID') THEN
                :G222_CDM_APEX_AD_GUID := l_attr_value;
            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);
EXCEPTION
    WHEN OTHERS THEN
        -- Exception means authentication failed.
        l_retval := DBMS_LDAP.unbind_s (ld => l_session);
END;

So what I'm looking for is how to get the Active Directory GUID, First Name, and Last Name from the Active Directory record?

The actual Authentication is being performed from a Function in a package, which is written similar to this code. This code will run after the Authentication process.

What additional information can I provide?

Thanks,

Joe