LDAP Authentication Via Groups

procedure ad_post_auth is
begin
DECLARE
   v_username     varchar2(250);
   v_password     varchar2(250);
   v_groups       varchar2(4000);

   v_retval       BINARY_INTEGER;
   ldap_session   DBMS_LDAP.session;
   l_attributes   DBMS_LDAP.string_collection;
   return_message DBMS_LDAP.message;
   my_entry       DBMS_LDAP.message;
   entry_index    BINARY_INTEGER;
   attr_index     BINARY_INTEGER;
   my_vals        DBMS_LDAP.STRING_COLLECTION;
   my_ber_elmt    DBMS_LDAP.ber_element;
   my_attr_name   VARCHAR2(256);   
BEGIN
   v_username := apex_util.get_session_state('P101_USERNAME');
   v_password := apex_util.get_session_state('P101_PASSWORD');
   --apex_debug_message.log_message('begin post auth');
   --user or password may not be null
   IF v_username IS NULL OR v_password IS NULL THEN
      RETURN;
   END IF;
   --apex_debug_message.log_message('user and pw not empty');

   -- Choosing exceptions to be raised by DBMS_LDAP library.
   DBMS_LDAP.USE_EXCEPTION := TRUE;
   
   --create a session
   ldap_session := DBMS_LDAP.init(<AD SERVER>,'389');
   --apex_debug_message.log_message('session created');
   
   --authenticate user through simple bind
   v_retval := DBMS_LDAP.simple_bind_s(ldap_session,
                                       v_username||'@<DOMAIN>', 
                                       v_password);
   --apex_debug_message.log_message('bind return: '||v_retval);
   IF v_retval &lt;&gt; DBMS_LDAP.SUCCESS THEN
      RETURN; --authentication failed
   END IF;
   --apex_debug_message.log_message('bind ok');
   --get the groups the user is a member of and store them
   --this must be done in this authentication process because we need
   --a valid session. The AD server does not allow anonymous binds.
   l_attributes(1) := 'memberOf';
   v_retval := DBMS_LDAP.search_s(ldap_session, 
                                  'dc=<******>,dc=<*****>,dc=com', 
                                  DBMS_LDAP.SCOPE_SUBTREE,
                                  'userPrincipalName='||v_username||'@<DOMAIN>',
                                  l_attributes,
                                  0,
                                  return_message);
                                  
   IF v_retval &lt;&gt; DBMS_LDAP.SUCCESS THEN
      NULL; 
      --user is authenticated, some error popped up during the search
      -- you'll have to manually debug this situation!
      -- This error doesn't stop the user from authenticating. He simply
      -- won't have any groups assigned.
   END IF;
   
   -- count the number of entries returned
   v_retval := DBMS_LDAP.count_entries(ldap_session, return_message);
   
   -- If ever multiple attributes are being queried, the following code can handle them
   -- Just provide a handling block for your attribute.
   
   -- get the first entry
   my_entry := DBMS_LDAP.first_entry(ldap_session, return_message);
   entry_index := 1;

   -- Loop through each of the entries one by one
   while my_entry IS NOT NULL loop  -- print the current entry      
      my_attr_name := DBMS_LDAP.first_attribute(ldap_session,my_entry, my_ber_elmt);
      attr_index := 1;
      while my_attr_name IS NOT NULL loop
         my_vals := DBMS_LDAP.get_values (ldap_session, my_entry, my_attr_name);
         if my_vals.COUNT > 0 then
            FOR i in my_vals.FIRST..my_vals.LAST loop            
               v_groups := v_groups ||lower(substr(my_vals(i), 4, instr(my_vals(i), ',') - 4))||';';
            end loop;
         end if;
         my_attr_name := DBMS_LDAP.next_attribute(ldap_session,my_entry, my_ber_elmt);
         attr_index := attr_index+1;
      end loop;
      
      my_entry := DBMS_LDAP.next_entry(ldap_session, my_entry);
      entry_index := entry_index+1;
   end loop; 
      
   APEX_UTIL.set_session_state('AI_USER_AD_GROUPS', v_groups);
   -- unbind from the directory 
   v_retval := DBMS_LDAP.unbind_s(ldap_session); 
END;
end;

SELECT 1
  FROM (
   SELECT substr(split_string, position_from + 1, position_to - position_from - 1) groep
     FROM (SELECT split_string,
                  decode(rownum - 1, 0, 0, instr(split_string, ';', 1, rownum - 1)) position_from,
                  instr(split_string, ';', 1, rownum) position_to
             FROM (SELECT :ai_user_ad_groups split_string
                     FROM dual)
          CONNECT BY LEVEL <= length(split_string)
          )
   WHERE position_to > 0
)
WHERE groep = lower('<GROUPNAME>');

function check_ad_group_membership (
   p_authorization in apex_plugin.t_authorization,
   p_plugin        in apex_plugin.t_plugin )
   return apex_plugin.t_authorization_exec_result
is
   l_test_group  varchar2(4000) := lower(p_authorization.attribute_01);
   l_user_groups varchar2(4000) := lower(:ai_user_ad_groups);
   l_count  number;
   l_result apex_plugin.t_authorization_exec_result;
begin
  WITH user_groups AS
    (SELECT val1
    FROM 
      xmltable('/root/e/text()' passing xmltype('<root><e>'
      || REPLACE(l_user_groups,';','</e><e>')
      || '</e></root>') columns val1 VARCHAR2(50) path '/' )
    ),
    test_groups AS
    (SELECT val2
    FROM 
      xmltable('/root/e/text()' passing xmltype('<root><e>'
      || REPLACE(l_test_group,',','</e><e>')
      || '</e></root>') columns val2 VARCHAR2(50) path '/' )
    )
  SELECT count(*)
  INTO l_count
  FROM user_groups  
  WHERE EXISTS
    (SELECT 1 FROM test_groups WHERE val2 = val1);

   l_result.is_authorized := l_count > 0;
   return l_result;
end check_ad_group_membership;