1 2 Previous Next 17 Replies Latest reply: Mar 8, 2012 9:18 AM by 749925 RSS

    Help with Authentication (APEX_LDAP.AUTHENTICATE)

    749925
      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
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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-Oracle
                      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
                        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-Oracle
                          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
                            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-Oracle
                              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
                                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
                                  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
                                    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