CODEWITHSUNDEEP
oracle-databaseplsqlactive-directoryldaporacle-apex
ThomasT
ThomasT

Reputation: 390

How to use the MEMBER_OF2 function in Oracle Apex using the APEX_LDAP package

Since I linked our Microsoft Active Directory with my Apex application using LDAP, I am trying to retrieve the groups for the user currently logged in from the Active Directory.

Here's the documentation: https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_ldap.htm#AEAPI242

Here's my code of my dynamic action on page load, I am trying to retrieve a VARCHAR2 of all the groups the current user is part of, and to put it in a display-only field :

BEGIN
    :P1_NEW := APEX_LDAP.MEMBER_OF2(
        p_username => v('APP_USER'),
        p_pass => 'mypassword',
        p_auth_base => 'DOMAIN\',
        p_host => 'XX.X.XXX.XX',
        p_port => 389);
END;

But when I load my page, this error occurs

Ajax call returned server error ORA-31202: DBMS_LDAP : Erreur client/serveur LDAP : Invalid credentials. 80090308: LdapErr: DSID-0C090439, comment: AcceptSecurityContext error, data 52e, v4563 for Execute PL/SQL Code.

What's wrong in my code ? Thank you in advance for your help.

Thomas

Upvotes: 0

Views: 1050

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

I think you are using wrong the API Package APEX_LDAP. I am doing this on my own enviornment in a different way, but I am using Enterprise Edition:

  1. Oracle Database version 19c
  2. Oracle Apex version 20.1
  3. Oracle Oracle Rest Data Services version 20.4

I have an authentication schema to make the login against the LDAP server of my own company. That authentication schema replaces the default one of Apex Locally managed users.

Application --> Shared Components --> Authentication Schemas --> Custom

function fn_val_user_pwd_ldap (
    p_username in varchar2,
    p_password in varchar2
    )
return boolean
is
l_ldap_host  varchar2(100) := 'myldaphost.com';

l_ldap_port  number        := 389 ;
begin 
    if APEX_LDAP.AUTHENTICATE(
      p_username =>p_username,
      p_password =>p_password,
      p_search_base => 'OU=Users,OU=Mycompany,DC=de,DC=com,DC=corp',
      p_host => l_ldap_host,
      p_port => l_ldap_port) 
      then
            dbms_application_info.set_action(null);
            return true;
       else
            apex_util.set_authentication_result(p_code => 110);
            apex_util.set_custom_auth_status(p_status => 'Username or password incorrect.');
            dbms_application_info.set_action(null);
            return false; 
       end if;
end;

Then, I have a post auth procedure in the same authentication method to retrieve the groups in LDAP. I prefer this way because it executes after the authentication, but you can do it also with a dynamic action.

declare
    l_groups varchar2(4000);
BEGIN
    l_groups := APEX_LDAP.MEMBER_OF2(
        p_username => ':APP_USER',
        p_pass => 'mypassword',
        p_auth_base => 'OU=Users,OU=Mycompany,DC=de,DC=com,DC=corp',
        p_host => 'myldaphost.com',
        p_port => 389);
    htp.p('Is Member of:'||l_groups);
END;

We don't use SSL to communicate internally with the LDAP Server, as this application is intranet. So the parameter p_use_ssl is by default to N.

As I was telling you in the comment section of your own question, I think the parameter p_auth_base refers to the LDIF format of your own LDAP server, not the domain name that refers to the AD.

UPDATE

Let me show you how it works. ( Of course, I hide sensitive information of my own company ). Sometimes it might happen that you can't get the group information as null. I am not sure here whether is a LDAP authorization issue rather than a problem with the APEX package itself

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED ECHO ON 
DECLARE
    is_ok        boolean;
    l_mes        varchar2(2000);
    l_val        varchar2(4000);
    l_ldap_host  varchar2(100) := 'myldapserver.com';
    l_ldap_port  number        := 389 ;
BEGIN
    if APEX_LDAP.AUTHENTICATE(
    p_username =>'X329097',
    p_password =>'********',
    p_search_base => 'OU=Users,OU=Mycompany,DC=****,DC=*****,DC=****,DC=corp',
    p_host => l_ldap_host,
    p_port => l_ldap_port ) 
    then
        is_ok := true;
        l_mes := 'Username and Password Correct' ;
        dbms_output.put_line(l_mes);
    else 
        l_mes := 'Username and Password Invalid' ;
        dbms_output.put_line(l_mes);
    end if;
    if is_ok 
    then 
        l_val := APEX_LDAP.MEMBER_OF2(
            p_username => 'X329097',
            p_pass => '*********',
            p_auth_base => 'OU=Users,OU=Mycompany,DC=****,DC=*****,DC=****,DC=corp',
            p_host => l_ldap_host,
            p_port => l_ldap_port);
        dbms_output.put_line(l_val);    
    end if;
END;
/

Username and Password Correct
SC_APEX_ADMIN:SC_ORACLE_ADMIN

PL/SQL procedure successfully completed.

Upvotes: 1

Related Questions