This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Mar 8, 2012 7:18 AM by 749925 RSS

Help with Authentication (APEX_LDAP.AUTHENTICATE)

749925 Newbie
Currently Being Moderated
I am having much trouble configuring authentication and authorization schemes with Apex and OID.

Why does this work as desired?:

IF APEX_LDAP.AUTHENTICATE(
p_username =>'testuser',
p_password =>'password',
p_search_base => 'cn=users,dc=jis,dc=org',
p_host => 'jis04951-5.jis.org',
p_port => 389)

But this does not work?:

IF APEX_LDAP.AUTHENTICATE(
p_username =>'anotheruser',
p_password =>'password',
p_search_base => 'ou=AnotherLevel,ou=ActiveDirUsers,cn=users,dc=jis,dc=org',
p_host => 'jis04951-5.jis.org',
p_port => 389
  • 1. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    Questions:

    1. Can APEX_LDAP.AUTHENTICATE only read to this level/location - cn=users,dc=jis,dc=org ?

    2. Does it matter that the users have different dn? cn=testuser and uid=anotheruser ?
  • 2. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    I think I have isolated the issue.

    It appears that Apex authentication and authorization themes have troubling with users who have something other than *"cn="* for the distinguished name.

    Example:

    cn=jdoe,cn=Users,dc=jis,dc=org -> works!

    uid=jdoe,cn=Users,dc=jis,dc=org -> will not work

    Any advice on how to use these schemes with users that are distinguished by "*uid=*"??

    Thanks for any help offered!
  • 3. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Portal Dude,

    I also found this issue very recently. Unfortunately, it was too late to include a fix in 4.1.1. The apex_ldap.authenticate function in 4.1.1 and older versions calls something along the lines of
    dbms_ldap.simple_bind_s (
      g_session,
      'cn='||p_username||case when p_search_base is not null then ','||p_search_base end,
      p_password )
    to verify user/password.

    As a workaround, you could use dbms_ldap and craft your own authenticate function. Or you could use the - unsupported - wwv_flow_custom_auth_ldap.authenticate function, like this:
    begin
      if wwv_flow_custom_auth_ldap.authenticate (
             p_dn            => 'cn=Users,dc=jis,dc=org',
             p_search_filter => 'uid=jdoe',
             p_password      => '...',
             p_ldap_host     => 'jis04951-5.jis.org',
             p_ldap_port     => 389,
             p_use_exact_dn  => 'N' )
      then
        htp.p('auth ok');
      else
        htp.p('auth err');
      end if;
    end;
    /
    Regards,
    Christian

    Edited by: Christian Neumueller on Mar 7, 2012 2:56 AM
  • 4. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    Christian, thanks so much for the info.

    Yes, your code for wwv_flow_custom_auth_ldap.authenticate does work in our environment.

    The problem is that I really need to use the other built in Apex ldap packages, like "APEX_LDAP.MEMBER_OF".

    I really don't want to write these myself, in that this ease of functionality was the driving force for us to choose Apex as a technology.

    Do you happen to know if this is fixed in the 4.1.1 patch?

    Thanks again
  • 5. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Portal Dude,

    sorry if my mail wasn't clear. Although only little code had to be changed, I could not include it in 4.1.1, because of there was no time to test on different LDAP servers. This should be fixed in 4.2.

    For group membership, another unsupported api may help you again:
    declare
        g dbms_ldap_utl.string_collection;
    begin
        g := apex_ldap.get_groups (
                 p_username     => 'uid=jdoe',
                 p_search_base  => 'cn=Users,dc=jis,dc=org',
                 p_ldap_host    => 'jis04951-5.jis.org',
                 p_ldap_port    => 389 );
        htp.p(g.count||' group(s)');
        for i in 1 .. g.count loop
            htp.p('...'||g(i));
        end loop;
    end;
    We can not guarantee that these unsupported APIs will be available or work the same in future versions. If you decide to use them today, please encapsulate them, so it's easier to switch to a supported API in the future.

    Regards,
    Christian
  • 6. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    Great, Christian that really works for us nicely.

    I do not have much plsql experience, can you provide me a snippet that would return true if the APP_USER is in a current group, so that I could use for an Authentication scheme?

    Is there a package similar, something like "apex_ldap.ismember"??

    Thanks so much.
  • 7. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Christian Neumueller Expert
    Currently Being Moderated
    You could use code like this:
    create or replace package portaldude_ldap_pkg as
    
    function is_member (
        p_username in varchar2,
        p_search_base in varchar2,
        p_group in varchar2,
        p_ldap_host in varchar2,
        p_ldap_port in number default 389 )
        return boolean;
    
    end portaldude_ldap_pkg;
    /
    create or replace package body portaldude_ldap_pkg as
    
    function is_member (
        p_username in varchar2,
        p_search_base in varchar2,
        p_group in varchar2,
        p_ldap_host in varchar2,
        p_ldap_port in number default 389 )
        return boolean
    is
        g dbms_ldap_utl.string_collection;
        l_found boolean := false;
    begin
        g := apex_ldap.get_groups (
                 p_username     => p_username,
                 p_search_base  => p_search_base,
                 p_ldap_host    => p_ldap_host,
                 p_ldap_port    => p_ldap_port );
        for i in 1 .. g.count loop
            if upper(p_group) = upper(g(i)) then
                l_found := true;
                exit;
            end if;
        end loop;
        return l_found;
    end is_member;
    
    end portaldude_ldap_pkg;
    /
  • 8. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    Christian, again, thanks for you time and patience.

    I have created the package and package body, but am still unclear as to what code to use in my Authorization Scheme type "PL/SQL Function Returning Boolean" to call them.

    I have tried the following, but it errors out as invalid sql:

    function is_member (
    p_username 'uid=APP_USER',
    p_search_base 'cn=Users,dc=jis,dc=org',
    p_group 'mygroup',
    p_ldap_host 'jis04951-5.jis.org',
    p_ldap_port 389 )
    return boolean
    is
    g dbms_ldap_utl.string_collection;
    l_found boolean := false;
    begin
    g := apex_ldap.get_groups (
    p_username => 'uid=APP_USER',
    p_search_base => 'cn=Groups,dc=jis,dc=org',
    p_ldap_host => 'jis04951-5.jis.org',
    p_ldap_port => 389 );
    for i in 1 .. g.count loop
    if upper(p_group) = upper(g(i)) then
    l_found := true;
    exit;
    end if;
    end loop;
    return l_found;
    end is_member;




    Thanks again.
  • 9. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Christian Neumueller Expert
    Currently Being Moderated
    The authorization itself can be something like
    return portaldude_ldap_pkg.is_member (
        p_username => 'uid='||apex_custom_auth.get_user,
        p_search_base => 'cn=Users,dc=jis,dc=org',
        p_group => 'mygroup',
        p_ldap_host => 'jis04951-5.jis.org' );
    It has to be a PL/SQL block, not a function with parameters, that returns a boolean value.
  • 10. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    Thanks Christian, you are really helping me, I am very close.

    I'm now getting this with the authorization scheme applied to my application:


    Error processing authorization.
    ORA-20000: 0 entries found. The search string must return 1 user.
    Technical Info (only visible for developers)
    is_internal_error: true
    apex_error_code: APEX.AUTHORIZATION.UNHANDLED_ERROR
    ora_sqlcode: -20000
    ora_sqlerrm: ORA-20000: 0 entries found. The search string must return 1 user.
    component.type: APEX_APPLICATION_AUTHORIZATION
    component.id: 25054614460413303
    component.name: QCLEGACY GROUP - TEST
    error_backtrace:
    ORA-06512: at "SYS.WWV_DBMS_SQL", line 904
    ORA-06512: at "APEX_040100.WWV_FLOW_DYNAMIC_EXEC", line 588
    ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 69
    ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 147
    ORA-06512: at "APEX_040100.WWV_FLOW_PLUGIN_ENGINE", line 2089
    ORA-06512: at "APEX_040100.WWV_FLOW_AUTHORIZATION", line 378
  • 11. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Portal Dude,

    you seem to be on 4.1. In 4.1.1, the error backtrace should show a bit more detailed information.
    Please make sure that the function returns the intended results if you call it in SQL Workshop with the same parameters.
    You should probably add debug output before calling the ldap function, to log what parameter values are actually passed to the function. Example:
    apex_application.debug('calling portaldude_ldap_pkg.is_member with p_username=> uid='||apex_custom_auth.get_user);
    return portaldude_ldap_pkg.is_member (
        p_username => 'uid='||apex_custom_auth.get_user,
        p_search_base => 'cn=Users,dc=jis,dc=org',
        p_group => 'mygroup',
        p_ldap_host => 'jis04951-5.jis.org' );
    Regards,
    Christian
  • 12. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Frank van Bortel Newbie
    Currently Being Moderated
    The APEX LDAP packages do not do what you would expect. Whereas the command line tools use a search depth of -s sub, the packages only search base.
    I have rewritten parts of the packages to allow for '-s sub' type searches.
    Soon the source will be on vanbortel.blogspot.com - I'm not quite sure where to end, yet.
  • 13. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    749925 Newbie
    Currently Being Moderated
    Hi Christian, again, excellent information and patience, much appreciated.

    I think this is actually working, but my problem seems to be now that my authorization scheme protected page is being run without calling my authentication scheme first.

    Debug shows:

    *"calling portaldude_ldap_pkg.is_member with p_username=> uid=nobody"*
  • 14. Re: Help with Authentication (APEX_LDAP.AUTHENTICATE)
    Frank van Bortel Newbie
    Currently Being Moderated
    Isn't that a symptom, seen more often in 4.1.1, but not in the previous (4.1.0) version? There are quite a lot of threads here, with similar symptoms.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points