5 Replies Latest reply on Jul 10, 2013 9:39 AM by Christian Neumueller-Oracle

    Apex- Ldap user function

    User516662-OC

      Hi,


      I’ve a problem when I run the edit ldap user function: ERR-10412 Unable to run edit ldap user function

      The function is working in DB. When I call the function in Apex, I used:  return ldap_authenticate_fnc;


      How can I call my function?

       

      create or replace function  ldap_authenticate_fnc (

          p_username        in varchar2,

          p_password        in varchar2)

      return boolean

      ..................


      I found the following:

       

      There is the APEX explanation on using home made function for authentication process:

      Enter a function name like this: return fn , where fn is the name of the function, qualified by schema name, package name, if required. The Application Express engine expects this function to have the signature (p_username in varchar2, p_password in varchar2) return boolean. The value of the username and password fields passed to the login API, which is called by the login page, will be passed to your function. In the login API call, which you can find on the login page's after-submit process, you can optionally specify a p_preserve_case boolean argument. Set this to true if you don't want the username converted to upper case during credentials verification and session registration.


      Thanks!

        • 1. Re: Apex- Ldap user function
          Christian Neumueller-Oracle

          Hi 997787,

           

          what does ldap_authenticate_fnc do? Did you implement custom LDAP credentials checking with DBMS_LDAP?

           

          The edit function is a simple conversion function which takes the username as the user entered it and transforms it in a format that is suitable for LDAP. The documentation here

           

            http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/sec_authentication.htm#HTMDB25956

           

          explains how to use the built-in LDAP authentication scheme and also has an example for an edit function.

           

          Regards,
          Christian

          • 2. Re: Apex- Ldap user function
            User516662-OC

            Hi Christian,

             

            There is a bind variable for the PASSWORD?

             

            I developed a function that implements a custom LDAP credentials using the 2 parameters: username and password.

            I check the credentials for my user with DBMS_LDAP because my DN is dynamic and I must transform it for each user.

             

            My function is working in DB but I tried to use it in Apex.

            For that, I call my function in the edit ldap user function

             

            Thank you!

            • 3. Re: Apex- Ldap user function
              Christian Neumueller-Oracle

              Hi,

               

              what you wrote is an authentication function, not a username edit function.. The edit ldap user function is the wrong place if you want to do custom credentials checking. You have to create a custom authentication scheme or an authentication plugin instead.

               

              However, the existing LDAP auth scheme might be sufficient, if you can define a transformation function that does that dynamic DN configuration you mentioned. Consider the example user edit function below. It adds "l=<region id>" (e.g. l=emea) to the given username, based on data in a local user table.

               

                function my_username_edit_fnc (

                    p_username in varchar2 )

                    return varchar2

                is

                    l_region varchar2(30);

                begin

                    begin

                        select region

                          into l_region

                          from user_data

                         where username = p_username;

                    exception when no_data_found then

                        l_region := 'emea';

                    end;

               

                    return apex_escape.ldap_dn(p_username)||

                           ',l='||apex_escape.ldap_dn(l_region);

                end my_username_edit_fnc;

               

              Regards,

              Christian

              • 4. Re: Apex- Ldap user function
                User516662-OC

                Hi Christian,

                 

                I've used the example http://www.oracle.com/technetwork/developer-tools/apex/how-to-ldap-authenticate-099256.html

                and I want to use the LDAP Active Directory from Microsoft but my DM is not fix. For that reason I use my function.

                 

                Did you mean that I must change my model with a new model from scratch (custom authentication scheme) using for my function?

                Can you send me an example or send me a link ? In this case I must use SSL ?


                Thank you one more time !

                 

                PS:

                 

                My function/package is:

                 

                 

                CREATE OR REPLACE package PPTDEV01101.ldap_globals_pkg

                --authid definer

                is

                 

                    g_host constant varchar2(255)   := 'xxx';

                    g_port constant number := 999;

                    g_search_base   constant varchar2(255)   := 'o=xxx;

                 

                 

                    g_search_filter constant varchar2(255) := 'cn=';

                    g_exact_dn      constant boolean         := false;

                    g_use_ssl       constant boolean         := false;

                 

                    -- if g_exact_dn is false, then we will start at the search base and find the user to get their DN.

                    --   if true then we will just bind with the DN and you should pass the fully qualified user DN into p_username

                 

                    -- SSL is hard-coded to use mode 1, as modes 2 and 3 are highly unlikely.  See the DBMS_LDAP doc for more info.

                    -- No wallet is needed for mode 1

                 

                end ldap_globals_pkg;

                /

                 

                 

                CREATE OR REPLACE function PPTDEV01101.ldap_authenticate_fnc (

                    p_username        in varchar2,

                    p_password        in varchar2)

                return boolean

                authid definer

                is

                    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_dn varchar2(1000);

                    l_user_handle dbms_ldap_utl.handle;

                    l_return pls_integer;

                    l_user_type   pls_integer;

                 

                    l_filter varchar2(256);

                 

                    function do_connect

                        return pls_integer

                    as

                        l_ldap_user varchar2(256) := NULL;

                        l_ldap_pwd varchar2(256) := NULL;

                    begin

                        dbms_ldap.use_exception := true;

                        l_session := dbms_ldap.init( ldap_globals_pkg.g_host , ldap_globals_pkg.g_port);

                 

                    if ldap_globals_pkg.g_use_ssl = true then

                      l_retval := DBMS_LDAP.open_ssl(

                ld => l_session,

                sslwrl          => NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle

                sslwalletpasswd => NULL, -- wallet password

                sslauth         => 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3

                    end if;

                 

                        return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );

                    end;

                 

                    procedure do_close

                    as

                    begin

                        l_retval := dbms_ldap.unbind_s( l_session );

                    end do_close;

                 

                    procedure handle_error(p_message in varchar2)

                    as

                    begin

                        do_close;

                 

                        $IF $$logger $THEN

                logger.log_error(p_message);

                        $END

                 

                        RAISE_APPLICATION_ERROR (-20001,p_message);

                    end;

                 

                begin

                    l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;

                 

                    l_retval := do_connect;

                 

                    if ldap_globals_pkg.g_exact_dn = false then

                 

                        l_attrs(1)  := 'dn';

                    l_filter := '('|| ldap_globals_pkg.g_search_filter|| p_username||')';

                        l_retval := dbms_ldap.search_s( l_session, ldap_globals_pkg.g_search_base, dbms_ldap.scope_subtree, l_filter, l_attrs, 0, l_message );

                 

                        l_retval := DBMS_LDAP.count_entries(l_session, l_message);

                 

                        if l_retval > 1 then

                handle_error('More than one entry found when searching for users DN.');

                        end if;

                 

                        if l_retval = 0 then

                handle_error('No entries were found when searching for user DN');

                        end if;

                 

                        l_entry := dbms_ldap.first_entry( l_session, l_message );

                        l_dn := dbms_ldap.get_dn( l_session, l_entry );

                    else

                        l_dn := p_username;

                    end if; -- ldap_globals.g_exact_dn = false

                 

                    l_retval := null;

                    l_retval := DBMS_LDAP_UTL.create_user_handle(

                user_hd     => l_user_handle,

                user_type   => DBMS_LDAP_UTL.TYPE_DN,

                user_id     => l_dn);

                 

                      begin

                      dbms_ldap.use_exception := false;

                      l_return := null;

                     

                      l_return := dbms_ldap.simple_bind_s(

                          ld => l_session,

                          dn => l_dn,

                          passwd  => p_password

                          );

                 

                      if l_return != 0 then

                        do_close;

                        return false;

                      end if;

                 

                        exception when others then

                            do_close;

                            raise;

                        end;

                 

                    dbms_ldap.use_exception := true;

                    do_close;

                    if l_return is null then

                handle_error('simple_bind_s did not return a value.');

                        return false;

                    end if;

                 

                    if l_return = 0 and p_password is not null

                        then

                             return true;

                        else return false;

                    end if;

                 

                    exception when others then

                        do_close;

                        raise;

                        return false;

                end ldap_authenticate_fnc;

                /

                 

                Ce message a été modifié par : 997787

                • 5. Re: Apex- Ldap user function
                  Christian Neumueller-Oracle

                  Hi,

                   

                  in your case, I would try the built-in LDAP authentication scheme first, without any edit function. You can set "Use Exact Distinguished Name (DN)" to "No" and configure a search filter, to find the DN (e.g. "cn=%LDAP_USER%") that is ultimately used to check credentials. Note that this initially requires an anonymous bind to do the search, like in your ldap_authenticate_fnc function. The AD server has to be configured to allow anonymous binds and searches, but since you mentioned that the function is already working in the DB, this should not be a problem.

                   

                  The built-in LDAP authentication scheme does not require SSL. You can configure this with the "Use SSL" parameter.

                   

                  Regards,

                  Christian