Forum Stats

  • 3,770,692 Users
  • 2,253,154 Discussions
  • 7,875,545 Comments

Discussions

A procedure is working fine when called directly but getting error when called from function

raghunadhan
raghunadhan Member Posts: 146 Red Ribbon

We are migrating AIMS to Active Directory as LDAP .we are integrating Apex with AD. We had packages to authenticate to AD that we use .one of the procedures gets users attribute from AD. when it called separately(that is directly) it is working fine. This procedures also called from an authentication function of package but it is throwing invalid ldap session.


That is -- when this procedures is commented in that autentication function this function is working fine when not commented it is throwing invalid ldap session..

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,603 Red Diamond

    The error is accurate - the LDAP session is invalid.

    Why? We do not have your code and environment, and nor have you provided any technical details that can be used to diagnose the problem.

    DBMS_LDAP works just fine when used correctly.

  • raghunadhan
    raghunadhan Member Posts: 146 Red Ribbon

    create or replace PACKAGE BODY "C2C2_LDAP" AS

      g_session   DBMS_LDAP.session;

      g_search_base VARCHAR2(4000);

      g_host    VARCHAR2(255);

      g_port    VARCHAR(10);


      ---------------------------------------------------------------------------------------

      -- Change Seq  Change By     Change Date  Project ID  Description

      -- ----------  ----------------  -----------  ----------  ---------------------- --

      --  001    Oslin Romero    01/09/2011  2011-62   Validate when a    --

      --                        Exp Pass   password is expired  --

      ---------------------------------------------------------------------------------------



      FUNCTION decrypt_pwd(p_pwd IN VARCHAR2)

       RETURN VARCHAR2 IS

      BEGIN

       RETURN p_pwd;

      END decrypt_pwd;



      PROCEDURE do_init(p_ldap_host IN VARCHAR2, p_ldap_port IN NUMBER, p_use_ssl IN VARCHAR2 DEFAULT 'N') AS

       l_wallet_path VARCHAR2(4000);

       l_wallet_pwd VARCHAR2(4000);

       l_retval   PLS_INTEGER;

      BEGIN

       g_session := DBMS_LDAP.init(p_ldap_host, p_ldap_port);


       IF p_use_ssl IN ('Y', 'A') THEN

         IF p_use_ssl = 'A' THEN

          --        l_wallet_path := wwv_flow_platform.get_preference( p_preference_name => 'WALLET_PATH' );

          --        l_wallet_pwd := wwv_flow_platform.get_preference( p_preference_name => 'WALLET_PWD' );

          SELECT wallet_path,                                       -- 001 Begin

                    pkg_security.decrypt_string(wallet_pwd)

           -- 001 End

           INTO l_wallet_path, l_wallet_pwd

           FROM c2c2_ldap_setting

           WHERE is_current = 'Y';


          IF l_wallet_pwd IS NOT NULL THEN

            l_wallet_pwd := decrypt_pwd(l_wallet_pwd);

          END IF;


          l_retval   := DBMS_LDAP.open_ssl(ld       => g_session,

                            sslwrl     => l_wallet_path,

                            sslwalletpasswd => l_wallet_pwd,

                            sslauth     => 2);

         ELSE

          l_retval := DBMS_LDAP.open_ssl(ld => g_session, sslwrl => NULL, sslwalletpasswd => NULL, sslauth => 1);

         END IF;

       END IF;

      EXCEPTION

       WHEN OTHERS THEN

         l_retval := DBMS_LDAP.unbind_s(g_session);

         RAISE;

      END do_init;


      FUNCTION do_connect(p_ldap_host      IN VARCHAR2,

                p_ldap_port      IN NUMBER,

                p_use_ssl       IN VARCHAR2 DEFAULT 'N',

                p_service_account   IN VARCHAR2 DEFAULT NULL,

                p_service_account_pwd IN VARCHAR2 DEFAULT NULL)

       RETURN PLS_INTEGER AS

       l_retval PLS_INTEGER;

      BEGIN

       do_init(p_ldap_host, p_ldap_port, p_use_ssl);

       l_retval := DBMS_LDAP.simple_bind_s(g_session, p_service_account, p_service_account_pwd);

       RETURN l_retval;

      END do_connect;


      PROCEDURE disconnect_session IS

       l_return PLS_INTEGER;

      BEGIN

       l_return := DBMS_LDAP.unbind_s(g_session);

      END disconnect_session;




      PROCEDURE unbind IS

       l_dummy PLS_INTEGER;

      BEGIN

       l_dummy := DBMS_LDAP.unbind_s(g_session);

      END unbind;



      PROCEDURE bind(p_username  IN VARCHAR2 DEFAULT NULL,

             p_password  IN VARCHAR2 DEFAULT NULL,

             p_search_base IN VARCHAR2,

             p_host    IN VARCHAR2,

             p_port    IN VARCHAR2 DEFAULT 389,

             p_use_ssl   IN VARCHAR2 DEFAULT 'N') IS

       l_dummy   PLS_INTEGER;

       l_ldap_user VARCHAR2(255);

      BEGIN

       g_search_base  := p_search_base;

       g_host      := p_host;

       g_port      := p_port;

       l_ldap_user   := 'cn='

                 || p_username;


       IF p_search_base IS NOT NULL THEN

         l_ldap_user   :=  l_ldap_user

                   || ','

                   || p_search_base;

       END IF;


       BEGIN

         do_init(p_host, p_port, p_use_ssl);

         l_dummy := DBMS_LDAP.simple_bind_s(g_session, l_ldap_user, p_password);

       EXCEPTION

         WHEN OTHERS THEN

          l_dummy := DBMS_LDAP.unbind_s(g_session);

          RAISE;

       END;

      END bind;




      FUNCTION authenticate(p_username  IN VARCHAR2 DEFAULT NULL,

                 p_password  IN VARCHAR2 DEFAULT NULL,

                 p_search_base IN VARCHAR2,

                 p_host    IN VARCHAR2,

                 p_port    IN VARCHAR2 DEFAULT 389,

                 p_use_ssl   IN VARCHAR2 DEFAULT 'N')

       RETURN BOOLEAN AS

       l_dummy PLS_INTEGER;

      BEGIN

       --

       -- dbms_ldap.simple_bind_s will accept a null password and allow the bind. Since the purpose of this

       -- function is to authenticate, we expect that developers using it would *not* expect a null password

       -- to result in successful authentication. In the interest of better security out-of-the-box, this

       -- function returns false if the provided password is null.

       --

       --

       IF p_password IS NULL THEN

         RETURN FALSE;

       END IF;


       g_search_base      := p_search_base;

       g_host         := p_host;

       g_port         := p_port;

       DBMS_LDAP.use_exception := TRUE;


       BEGIN

         bind(p_username  => p_username,

           p_password  => p_password,

           p_search_base => g_search_base,

           p_host    => g_host,

           p_port    => g_port,

           p_use_ssl   => p_use_ssl);


         unbind;


         RETURN TRUE;

       EXCEPTION

         WHEN OTHERS THEN

          l_dummy := DBMS_LDAP.unbind_s(g_session);

          RETURN FALSE;

       END;

      EXCEPTION

       WHEN OTHERS THEN

         RETURN FALSE;

      END authenticate;



      FUNCTION is_member(p_username  IN VARCHAR2,

               p_pass    IN VARCHAR2 DEFAULT NULL,

               p_auth_base IN VARCHAR2,

               p_host    IN VARCHAR2,

               p_port    IN VARCHAR2 DEFAULT 389,

               p_use_ssl  IN VARCHAR2 DEFAULT 'N',

               p_group   IN VARCHAR2,

               p_group_base IN VARCHAR2)

       RETURN BOOLEAN IS

       l_retval    PLS_INTEGER;

       l_session   DBMS_LDAP.session;

       l_user_handle dbms_ldap_utl.handle;

       l_user_type  PLS_INTEGER;

       l_user_id   VARCHAR2(2000);

       l_user_id2   VARCHAR2(2000);

       l_group_handle dbms_ldap_utl.handle;

       l_group_id   VARCHAR2(2000);

       l_group_type  PLS_INTEGER;

       l_result    BOOLEAN := FALSE;

      BEGIN

       l_user_type       := DBMS_LDAP_UTL.TYPE_DN;

       l_user_id        := 'cn='

                     || p_username;


       IF p_auth_base IS NOT NULL THEN

         l_user_id   :=  l_user_id

                  || ','

                  || p_auth_base;

       END IF;



       l_group_type      := dbms_ldap_utl.type_dn;

       l_group_id       := 'cn='

                     || p_group;


       IF p_group_base IS NOT NULL THEN

         l_group_id   :=  l_group_id

                  || ','

                  || p_group_base;

       END IF;


       -- Choosing exceptions to be raised by DBMS_LDAP library.

       DBMS_LDAP.use_exception := TRUE;


       -- bind

       do_init(p_host, p_port, p_use_ssl);


       IF p_pass IS NULL THEN

         l_user_id2 := NULL;

       ELSE

         l_user_id2 := l_user_id;

       END IF;


       l_retval        := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id2, passwd => p_pass);

       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;



       -- get user handle

       l_retval        := dbms_ldap_utl.create_user_handle(user_hd  => l_user_handle,

                                     user_type => l_user_type,

                                     user_id  => l_user_id);


       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;


       --get group handle

       l_retval        := dbms_ldap_utl.create_group_handle(group_hd  => l_group_handle,

                                      group_type => l_group_type,

                                      group_id  => l_group_id);


       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;


       -- Check Membership

       l_retval        := dbms_ldap_utl.check_group_membership(ld      => g_session,

                                       user_handle => l_user_handle,

                                       group_handle => l_group_handle,

                                       NESTED    => dbms_ldap_utl.direct_membership);


       IF l_retval != dbms_ldap_utl.success THEN

         l_result := FALSE;

       ELSE

         l_result := TRUE;

       END IF;


       -- unbind

       dbms_ldap_utl.free_handle(l_user_handle);

       dbms_ldap_utl.free_handle(l_group_handle);

       unbind;


       RETURN l_result;

      EXCEPTION

       WHEN OTHERS THEN

         dbms_ldap_utl.free_handle(l_user_handle);

         dbms_ldap_utl.free_handle(l_group_handle);

         unbind;

         RAISE;

      END is_member;




      FUNCTION member_of(p_username IN VARCHAR2 DEFAULT NULL,

               p_pass   IN VARCHAR2 DEFAULT NULL,

               p_auth_base IN VARCHAR2,

               p_host   IN VARCHAR2,

               p_port   IN VARCHAR2 DEFAULT 389,

               p_use_ssl  IN VARCHAR2 DEFAULT 'N')

       RETURN wwv_flow_global.vc_arr2 IS

       l_retval    PLS_INTEGER;

       l_session   DBMS_LDAP.session;

       l_result    DBMS_LDAP.message;

       l_user_handle dbms_ldap_utl.handle;

       l_user_type  PLS_INTEGER;

       l_user_id   VARCHAR2(2000);

       l_groups    dbms_ldap_utl.property_set_collection;

       l_attrs    DBMS_LDAP.string_collection;

       l_group_names DBMS_LDAP.string_collection;

       l_group_values DBMS_LDAP.string_collection;

       l_groups_out  wwv_flow_global.vc_arr2;

       l_count    NUMBER;

      BEGIN

       l_user_type       := dbms_ldap_utl.type_dn;


       IF p_username IS NOT NULL THEN

         l_user_id := 'cn=';

       END IF;


       l_user_id        := l_user_id

                     || p_username;


       IF p_auth_base IS NOT NULL THEN

         l_user_id   :=  l_user_id

                  || ','

                  || p_auth_base;

       END IF;


       -- Choosing exceptions to be raised by DBMS_LDAP library.

       DBMS_LDAP.use_exception := TRUE;


       -- bind

       do_init(p_host, p_port, p_use_ssl);


       --need to store publicUser and passwd as prefs not accessible to the public

       --


       l_retval        := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id, passwd => p_pass);



       -- get user handle

       l_retval        := dbms_ldap_utl.create_user_handle(user_hd  => l_user_handle,

                                     user_type => l_user_type,

                                     user_id  => l_user_id);




       l_attrs.DELETE();

       l_attrs(1)       := 'cn';


       l_retval        := dbms_ldap_utl.get_group_membership(ld     => g_session,

                                      user_handle => l_user_handle,

                                      NESTED   => dbms_ldap_utl.direct_membership,

                                      attr_list  => l_attrs,

                                      ret_groups => l_groups);



       l_count         := 1;


       IF l_groups.count > 0 THEN

         FOR i IN l_groups.first .. l_groups.last LOOP

          l_retval := dbms_ldap_utl.get_property_names(l_groups(i), l_group_names);


          IF l_group_names.count > 0 THEN

            FOR j IN l_group_names.first .. l_group_names.last LOOP

             l_retval := dbms_ldap_utl.get_property_values(l_groups(i), l_group_names(j), l_group_values);


             IF l_group_values.count > 0 THEN

               FOR K IN l_group_values.first .. l_group_values.last LOOP

                l_groups_out(l_count) := l_group_values(K);

                l_count        := l_count

                             + 1;

               END LOOP;                                            --k

             END IF;

            END LOOP;                                               --j

          END IF;

         END LOOP;                                                  --i

       END IF;


       -- unbind

       dbms_ldap_utl.free_handle(l_user_handle);

       unbind;


       RETURN l_groups_out;

      EXCEPTION

       WHEN OTHERS THEN

         dbms_ldap_utl.free_handle(l_user_handle);

         unbind;

         RAISE;

      END member_of;


      FUNCTION member_of2(p_username IN VARCHAR2 DEFAULT NULL,

                p_pass   IN VARCHAR2 DEFAULT NULL,

                p_auth_base IN VARCHAR2,

                p_host   IN VARCHAR2,

                p_port   IN VARCHAR2 DEFAULT 389,

                p_use_ssl  IN VARCHAR2 DEFAULT 'N')

       RETURN VARCHAR2 IS

       l_return VARCHAR2(32767);

       l_array wwv_flow_global.vc_arr2;

      BEGIN

       l_array   := member_of(p_username => p_username,

                    p_pass   => p_pass,

                    p_auth_base => p_auth_base,

                    p_host   => p_host,

                    p_port   => p_port,

                    p_use_ssl  => p_use_ssl);


       l_return   := wwv_flow_utilities.table_to_string(l_array);


       RETURN l_return;

      END member_of2;


      PROCEDURE get_user_attributes(p_use_current_settings IN   VARCHAR2 DEFAULT 'N', -- if Y then get then use bind using the data in c2c2_ldap_setting

                     p_full_dn       IN   VARCHAR2 DEFAULT NULL,

                     p_username       IN   VARCHAR2 DEFAULT NULL,

                     p_pass         IN   VARCHAR2 DEFAULT NULL,

                     p_auth_base      IN   VARCHAR2 DEFAULT NULL,

                     p_host         IN   VARCHAR2 DEFAULT NULL,

                     p_port         IN   VARCHAR2 DEFAULT 389,

                     p_use_ssl       IN   VARCHAR2 DEFAULT 'N',

                     p_attributes      IN   wwv_flow_global.vc_arr2,

                     p_attribute_values    OUT wwv_flow_global.vc_arr2) IS

       l_retval     PLS_INTEGER;

       l_session     DBMS_LDAP.session;

       l_result     DBMS_LDAP.message;

       l_user_handle   dbms_ldap_utl.handle;

       l_user_type    PLS_INTEGER;

       l_c2_ldap_rec   c2c2_ldap_setting%ROWTYPE;

       l_user_id     VARCHAR2(2000);

       l_user_id2    VARCHAR2(2000);

       l_pass      VARCHAR2(2000);

       l_host      VARCHAR2(2000);

       l_port      VARCHAR2(2000);

       l_use_ssl     VARCHAR2(200);

       l_auth_base    VARCHAR2(2000);

       l_count      NUMBER;

       l_attrs      DBMS_LDAP.string_collection;

       l_attr_names   DBMS_LDAP.string_collection;

       l_attr_vals    DBMS_LDAP.string_collection;

       l_pset_collection dbms_ldap_utl.property_set_collection;

       l_position    NUMBER;


       --** ORC 23/08/2011 Obtain Operational Attributes -Ini-

       res_attrs     DBMS_LDAP.string_collection;

       res_message    DBMS_LDAP.message;

       temp_entry    DBMS_LDAP.message;

       temp_ber_elmt   DBMS_LDAP.BER_ELEMENT;

       temp_vals     DBMS_LDAP.STRING_COLLECTION;

       l_vUserNameAtt  c2c2_ldap_setting.username_attribute%TYPE;

       attr_index    PLS_INTEGER;

       entry_index    PLS_INTEGER;

       l_vFilter     VARCHAR2(100);

       temp_dn      VARCHAR2(512);

       temp_attr_name  VARCHAR2(512);

       l_nAux      NUMBER(4) := 0;

       l_vAux      VARCHAR2(10000);

      --** ORC 23/08/2011 Obtain Operational Attributes -Fin-


      BEGIN

       l_user_type       := dbms_ldap_utl.type_dn;


       l_pass         := p_pass;

       l_host         := p_host;

       l_port         := p_port;

       l_use_ssl        := p_use_ssl;

       l_auth_base       := p_auth_base;



       IF p_full_dn IS NOT NULL THEN

         l_user_id := p_full_dn;

       ELSE

         l_user_id   := 'cn='

                  || p_username;


         IF p_auth_base IS NOT NULL THEN

          l_user_id   :=  l_user_id

                   || ','

                   || p_auth_base;

         END IF;

       END IF;




       IF p_pass IS NULL THEN

         l_user_id2 := NULL;

       ELSE

         l_user_id2 := l_user_id;

       END IF;


       IF p_use_current_settings = 'Y' THEN

         SELECT *

          INTO l_c2_ldap_rec

          FROM c2c2_ldap_setting

         WHERE is_current = 'Y';


         IF l_c2_ldap_rec.ldap_search_pwd IS NOT NULL THEN

          l_user_id2 := l_c2_ldap_rec.ldap_search_user;

          -- 001 Begin

          l_pass   := pkg_security.decrypt_string(l_c2_ldap_rec.ldap_search_pwd);

         -- 001 End

         END IF;


         l_host  := l_c2_ldap_rec.ldap_host;

         l_port  := l_c2_ldap_rec.ldap_port;

         l_use_ssl := l_c2_ldap_rec.ldap_use_ssl;

       END IF;


       -- Choosing exceptions to be raised by DBMS_LDAP library.

       DBMS_LDAP.use_exception := TRUE;


       -- bind

       do_init(l_host, l_port, l_use_ssl);


       --need to store publicUser and passwd as prefs not accessible to the public

       --



       l_retval        := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id2, passwd => l_pass);

       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;



       -- get user handle

       l_retval        := dbms_ldap_utl.create_user_handle(user_hd  => l_user_handle,

                                     user_type => l_user_type,

                                     user_id  => l_user_id);


       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;




       FOR i IN p_attributes.first .. p_attributes.last LOOP

         l_attrs(i)      := p_attributes(i);

         p_attribute_values(i) := NULL;                          -- initialize results to null

         DBMS_OUTPUT.put_line(  i

                   || '-'

                   || l_attrs(i));

       END LOOP;



       l_retval        := dbms_ldap_utl.get_user_properties(ld      => g_session,

                                      user_handle  => l_user_handle,

                                      attrs     => l_attrs,

                                      ptype     => dbms_ldap_utl.entry_properties,

                                      ret_pset_coll => l_pset_collection);



       --    if l_retval != dbms_ldap_utl.success then

       --      return false;

       --    end if;

       l_count         := 1;


       IF l_pset_collection.count > 0 THEN

         FOR i IN l_pset_collection.first .. l_pset_collection.last LOOP

          l_retval := dbms_ldap_utl.get_property_names(l_pset_collection(i), l_attr_names);


          --dbms_output.put_line(i || '-' || l_pset_collection(i));

          IF l_attr_names.count > 0 THEN

            FOR j IN l_attr_names.first .. l_attr_names.last LOOP

             l_retval  := dbms_ldap_utl.get_property_values(l_pset_collection(i), l_attr_names(j), l_attr_vals);

             --dbms_output.put_line(j || '-' || l_attr_names(j));

             -- determine original position

             l_position := -1;


             FOR P IN p_attributes.first .. p_attributes.last LOOP

               IF p_attributes(P) = l_attr_names(j) THEN

                l_position := P;

                EXIT;                                      -- exit the loop

               END IF;

             END LOOP;


             IF l_attr_vals.count > 0 THEN

               FOR K IN l_attr_vals.first .. l_attr_vals.last LOOP

                --p_attribute_values(l_count) := l_attr_vals(k);

                p_attribute_values(l_position) := l_attr_vals(K);

                --dbms_output.put_line(l_position || '-' || p_attribute_values(l_position));

                l_count            := l_count

                                 + 1;

               END LOOP;                                            --k

             END IF;

            END LOOP;                                               --j

          END IF;

         END LOOP;                                                  --i

       END IF;


       --** ORC 23/08/2011 Obtain Operational Attributes -Ini-

       IF 1 = 2 THEN                    --p_full_dn = 'gmpersonid=WO22952,ou=People,dc=gm,dc=com' THEN

         SELECT username_attribute, expiry_date_attribute, first_name_attribute

          INTO l_vUserNameAtt, res_attrs(1), res_attrs(2)

          FROM c2c2_ldap_setting

         WHERE is_current = 'Y';


         FOR l_iCount IN p_attributes.first .. p_attributes.last LOOP

          IF p_attributes(l_iCount) = res_attrs(1) THEN

            l_nAux := l_iCount;

          END IF;

         END LOOP;


         res_attrs(1) := 'nsaccountlock';


         l_vfilter   := p_full_dn;

         --res_attrs(1)      := '';

         l_retval   := DBMS_LDAP.SEARCH_S(ld    => g_session,

                           base   => g_search_base,

                           SCOPE  => DBMS_LDAP.SCOPE_SUBTREE,

                           FILTER  => 'gmPersonId=AD0462',                --l_vfilter,

                           attrs  => res_attrs,

                           attronly => 0,

                           res   => res_message);


         l_vAux    :=  'retval: '

                 || to_char(l_retval)

                 || ', filter: '

                 || to_char(l_vfilter);

         l_vAux    :=  l_vAux

                 || ', full_dn: '

                 || p_full_dn;

         l_vAux    :=  l_vAux

                 || ', success: '

                 || DBMS_LDAP.success;

         l_vAux    := l_vAux

                 || '--END--';



         -- -------------------------------------------------------------------------

         -- After the search is performed, the API stores the count of the number of

         -- entries returned.

         -- -------------------------------------------------------------------------

         l_retval   := DBMS_LDAP.COUNT_ENTRIES(g_session, res_message);

         -- -------------------------------------------------------------------------

         -- Retrieve the first entry.

         -- -------------------------------------------------------------------------

         temp_entry  := DBMS_LDAP.FIRST_ENTRY(g_session, res_message);

         entry_index  := 1;


         -- -------------------------------------------------------------------------

         -- Loop through each of the entries one by one.

         -- -------------------------------------------------------------------------


         WHILE temp_entry IS NOT NULL LOOP

          -- ---------------------------------------------------------------------

          -- Print out the current entry.

          -- ---------------------------------------------------------------------

          temp_dn     := DBMS_LDAP.GET_DN(g_session, temp_entry);

          --DBMS_OUTPUT.PUT_LINE(' dn: '

          --           || temp_dn);


          temp_attr_name  := DBMS_LDAP.FIRST_ATTRIBUTE(g_session, temp_entry, temp_ber_elmt);

          attr_index    := 1;


          WHILE temp_attr_name IS NOT NULL LOOP

            temp_vals    := DBMS_LDAP.GET_VALUES(g_session, temp_entry, temp_attr_name);


            IF temp_vals.count > 0 THEN

             FOR i IN temp_vals.first .. temp_vals.last LOOP

               IF l_nAux > 0 THEN

                p_attribute_values(l_nAux) := temp_vals(i);

                l_vAux           :=  l_vAux

                               || ':'

                               || temp_vals(i);

               END IF;

             --DBMS_OUTPUT.PUT_LINE(  rpad('  '

             --               || temp_attr_name,

             --               19,

             --               ' ')

             --           || ': '

             --           || substr(temp_vals(i), 1, 200));

             END LOOP;


             l_nAux := 100000000000000;

            END IF;


            temp_attr_name := DBMS_LDAP.NEXT_ATTRIBUTE(g_session, temp_entry, temp_ber_elmt);

            attr_index   := attr_index

                     + 1;

          END LOOP;


          temp_entry    := DBMS_LDAP.NEXT_ENTRY(g_session, temp_entry);

          --DBMS_OUTPUT.PUT_LINE('===================================================');

          entry_index   := entry_index

                    + 1;

         END LOOP;

       END IF;


       --** ORC 23/08/2011 Obtain Operational Attributes -Fin-


       -- unbind

       dbms_ldap_utl.free_handle(l_user_handle);

       unbind;

      EXCEPTION

       WHEN OTHERS THEN

         dbms_ldap_utl.free_handle(l_user_handle);

         unbind;

         RAISE_application_error(-20000,

                      l_vAux

                     || ', ERR:'

                     || SQLERRM);

      END get_user_attributes;




      PROCEDURE get_all_user_attributes(p_full_dn     IN   VARCHAR2 DEFAULT NULL,

                       p_username     IN   VARCHAR2 DEFAULT NULL,

                       p_pass       IN   VARCHAR2 DEFAULT NULL,

                       p_auth_base    IN   VARCHAR2 DEFAULT NULL,

                       p_host       IN   VARCHAR2,

                       p_port       IN   VARCHAR2 DEFAULT 389,

                       p_use_ssl     IN   VARCHAR2 DEFAULT 'N',

                       p_attributes     OUT wwv_flow_global.vc_arr2,

                       p_attribute_values  OUT wwv_flow_global.vc_arr2) IS

       l_retval     PLS_INTEGER;

       l_session     DBMS_LDAP.session;

       l_result     DBMS_LDAP.message;

       l_user_handle   dbms_ldap_utl.handle;

       l_user_type    PLS_INTEGER;

       l_user_id     VARCHAR2(2000);

       l_user_id2    VARCHAR2(2000);

       l_count      PLS_INTEGER;

       l_attrs      DBMS_LDAP.string_collection;

       l_attr_names   DBMS_LDAP.string_collection;

       l_attr_vals    DBMS_LDAP.string_collection;

       l_pset_collection dbms_ldap_utl.property_set_collection;

      --

      BEGIN

       l_user_type       := dbms_ldap_utl.type_dn;


       IF p_full_dn IS NOT NULL THEN

         l_user_id := p_full_dn;

       ELSE

         l_user_id   := 'cn='

                  || p_username;


         IF p_auth_base IS NOT NULL THEN

          l_user_id   :=  l_user_id

                   || ','

                   || p_auth_base;

         END IF;

       END IF;


       -- Choosing exceptions to be raised by DBMS_LDAP library.

       DBMS_LDAP.use_exception := TRUE;


       -- bind

       do_init(p_host, p_port, p_use_ssl);


       --need to store publicUser and passwd as prefs not accessible to the public

       --

       IF p_pass IS NULL THEN

         l_user_id2 := NULL;

       ELSE

         l_user_id2 := l_user_id;

       END IF;


       l_retval        := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_user_id2, passwd => p_pass);

       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;



       -- get user handle

       l_retval        := dbms_ldap_utl.create_user_handle(user_hd  => l_user_handle,

                                     user_type => l_user_type,

                                     user_id  => l_user_id);


       --    if l_retval != dbms_ldap_utl.success then

       --      -- handle errors

       --    end if;



       l_attrs(1)       := '*';


       l_retval        := dbms_ldap_utl.get_user_properties(ld      => g_session,

                                      user_handle  => l_user_handle,

                                      attrs     => l_attrs,

                                      ptype     => dbms_ldap_utl.entry_properties,

                                      ret_pset_coll => l_pset_collection);



       --    if l_retval != dbms_ldap_utl.success then

       --      return false;

       --    end if;

       l_count         := 1;


       IF l_pset_collection.count > 0 THEN

         FOR i IN l_pset_collection.first .. l_pset_collection.last LOOP

          l_retval := dbms_ldap_utl.get_property_names(l_pset_collection(i), l_attr_names);


          IF l_attr_names.count > 0 THEN

            FOR j IN l_attr_names.first .. l_attr_names.last LOOP

             l_retval    := dbms_ldap_utl.get_property_values(l_pset_collection(i), l_attr_names(j), l_attr_vals);

             p_attributes(j) := l_attr_names(j);


             IF l_attr_vals.count > 0 THEN

               --

               -- For now, only get back the first value

               --

               p_attribute_values(j) := l_attr_vals(l_attr_vals.first);

             ELSE

               p_attribute_values(j) := NULL;

             END IF;

            END LOOP;                                               --j

          END IF;

         END LOOP;                                                  --i

       END IF;



       -- unbind

       dbms_ldap_utl.free_handle(l_user_handle);

       unbind;

      EXCEPTION

       WHEN OTHERS THEN

         dbms_ldap_utl.free_handle(l_user_handle);

         unbind;

         RAISE;

      END get_all_user_attributes;



      -------------------------------------------------------------------------------

      FUNCTION get_groups(p_ldap_host      IN VARCHAR2,                    -- ldap.somecompany.com

                p_ldap_port      IN NUMBER,                              -- 389

                p_use_ssl       IN VARCHAR2 DEFAULT 'N',  -- N= no SSL, Y = SSL no auth, A = SSL with auth

                p_username      IN VARCHAR2,                          -- cn=TMUTH

                p_service_account   IN VARCHAR2 DEFAULT NULL,                    -- cn=Admin

                p_service_account_pwd IN VARCHAR2 DEFAULT NULL,                   -- welcome123

                p_search_base     IN VARCHAR2 DEFAULT NULL,       -- ou=Employees,dc=SomeCompany,dc=com

                p_scope        IN BINARY_INTEGER DEFAULT DBMS_LDAP.scope_subtree, -- scope_subtree | scope_base | scope_onelevel

                p_group_attribute   IN VARCHAR2 DEFAULT 'cn'                       -- dn

                                       )

       RETURN dbms_ldap_utl.string_collection IS

       l_retval    PLS_INTEGER;

       l_attrs    DBMS_LDAP.string_collection;

       l_scope    BINARY_INTEGER;

       l_message   DBMS_LDAP.message;

       l_entry    DBMS_LDAP.message;

       l_dn      VARCHAR2(1000);

       l_dn2     VARCHAR2(1000);

       l_groups    dbms_ldap_utl.property_set_collection;

       l_group_names DBMS_LDAP.string_collection;

       l_group_attrs DBMS_LDAP.string_collection;

       l_group_values DBMS_LDAP.string_collection;

       l_groups_out  DBMS_LDAP.string_collection;

       l_count    NUMBER;

       l_user_handle dbms_ldap_utl.handle;

       l_user_type  PLS_INTEGER := dbms_ldap_utl.type_dn;

       l_timeout   DBMS_LDAP.timeval;

      BEGIN

       DBMS_LDAP.use_exception := TRUE;

       l_user_type       := dbms_ldap_utl.type_dn;


       l_timeout.seconds    := 10;

       l_timeout.useconds   := 0;


       l_retval        := do_connect(p_ldap_host      => p_ldap_host,

                          p_ldap_port      => p_ldap_port,

                          p_service_account   => p_service_account,

                          p_service_account_pwd => p_service_account_pwd);


       l_attrs(1)       := 'NO_ATTRS';

       l_retval        := DBMS_LDAP.search_st(ld    => g_session,

                               base   => p_search_base,

                               SCOPE  => p_scope,

                               FILTER  =>  '('

                                    || p_username

                                    || ')',

                               attrs  => l_attrs,

                               attronly => 0,

                               tv    => l_timeout,

                               res   => l_message);

       l_entry         := DBMS_LDAP.first_entry(g_session, l_message);


       IF DBMS_LDAP.count_entries(g_session, l_message) > 1 THEN

         raise_application_error(-20000,

                     DBMS_LDAP.count_entries(g_session, l_message)

                     || ' entries found. The search string must only return 1 distict user.');

       END IF;


       IF DBMS_LDAP.count_entries(g_session, l_message) = 0 THEN

         raise_application_error(-20000, '0 entries found. The search string must return 1 user.');

       END IF;


       l_dn          := DBMS_LDAP.get_dn(g_session, l_entry);


       -- Get User Handle

       l_retval        := dbms_ldap_utl.create_user_handle(user_hd  => l_user_handle,

                                     user_type => l_user_type,

                                     user_id  => l_dn);



       -- Get Groups

       l_group_attrs.DELETE();

       l_group_attrs(1)    := 'cn';


       l_retval        := DBMS_LDAP_UTL.get_group_membership(ld     => g_session,

                                      user_handle => l_user_handle,

                                      NESTED   => dbms_ldap_utl.nested_membership,

                                      attr_list  => l_group_attrs,

                                      ret_groups => l_groups);




       --

       l_count         := 1;


       IF l_groups.count > 0 THEN

         FOR i IN l_groups.first .. l_groups.last LOOP

          l_group_names(1) := p_group_attribute;


          l_retval     := dbms_ldap_utl.get_property_values(l_groups(i), l_group_names(1), l_group_values);


          IF l_group_values.count > 0 THEN

            FOR K IN l_group_values.first .. l_group_values.last LOOP

             l_groups_out(l_count) := l_group_values(K);

             l_count        := l_count

                          + 1;

            END LOOP;                                               --k

          END IF;

         END LOOP;                                                  --i

       END IF;


       dbms_ldap_utl.free_handle(l_user_handle);

       disconnect_session;

       RETURN l_groups_out;

      EXCEPTION

       WHEN OTHERS THEN

         dbms_ldap_utl.free_handle(l_user_handle);

         disconnect_session;

         RAISE;

      END get_groups;



      FUNCTION get_groups_string(p_ldap_host      IN VARCHAR2,                 -- ldap.somecompany.com

                   p_ldap_port      IN NUMBER,                          -- 389

                   p_use_ssl       IN VARCHAR2 DEFAULT 'N', -- N= no SSL, Y = SSL no auth, A = SSL with auth

                   p_username      IN VARCHAR2,                       -- cn=TMUTH

                   p_service_account   IN VARCHAR2 DEFAULT NULL,                -- cn=Admin

                   p_service_account_pwd IN VARCHAR2 DEFAULT NULL,               -- welcome123

                   p_search_base     IN VARCHAR2 DEFAULT NULL,   -- ou=Employees,dc=SomeCompany,dc=com

                   p_scope        IN BINARY_INTEGER DEFAULT DBMS_LDAP.scope_subtree, -- scope_subtree | scope_base | scope_onelevel

                   p_group_attribute   IN VARCHAR2 DEFAULT 'cn',                   -- dn

                   p_delimiter      IN VARCHAR2 DEFAULT ':'                     -- ~

                                          )

       RETURN VARCHAR2 IS

       l_groups_string VARCHAR2(32767);

       l_groups    DBMS_LDAP.string_collection;

      BEGIN

       l_groups   := c2c2_ldap.get_groups(p_ldap_host      => p_ldap_host,

                          p_ldap_port      => p_ldap_port,

                          p_username      => p_username,

                          p_service_account   => p_service_account,

                          p_service_account_pwd => p_service_account_pwd,

                          p_search_base     => p_search_base,

                          p_scope        => p_scope,

                          p_group_attribute   => p_group_attribute);



       FOR i IN l_groups.first .. l_groups.last LOOP

         IF i > 1 THEN

          l_groups_string   := l_groups_string

                      || p_delimiter;

         END IF;


         l_groups_string   := l_groups_string

                     || l_groups(i);

       END LOOP;                                                   --i


       RETURN l_groups_string;

      END get_groups_string;



      -------------------------------------------------------------------------------



      FUNCTION authenticate_ally(p_username IN VARCHAR2, p_password IN VARCHAR2)

       RETURN BOOLEAN IS

       l_c2_ldap_rec    c2c2_ldap_setting%ROWTYPE;

       l_use_ssl      VARCHAR2(200);

       l_search_user    VARCHAR2(256);

       l_search_pass    VARCHAR2(256);

       l_uid_attribute   VARCHAR2(256);

       l_username_attribute VARCHAR2(256);

       l_attrs       DBMS_LDAP.string_collection;

       l_filter       VARCHAR2(256);

       l_message      DBMS_LDAP.message;

       l_entry       DBMS_LDAP.message;

       l_full_user_dn    VARCHAR2(256);

       l_retval       PLS_INTEGER;


       --001

       client_server_error EXCEPTION;

       PRAGMA EXCEPTION_INIT(client_server_error, -31202);

      --001


      BEGIN

       SELECT *

        INTO l_c2_ldap_rec

        FROM c2c2_ldap_setting

        WHERE is_current = 'Y';


       g_host        := l_c2_ldap_rec.ldap_host;

       g_port        := l_c2_ldap_rec.ldap_port;

       l_use_ssl      := l_c2_ldap_rec.ldap_use_ssl;

       l_search_user    := l_c2_ldap_rec.ldap_search_user;

       -- 001 Begin

       l_search_pass    := pkg_security.decrypt_string(l_c2_ldap_rec.ldap_search_pwd);

       -- 001 End

       g_search_base    := l_c2_ldap_rec.ldap_search_base;

       l_uid_attribute   := l_c2_ldap_rec.uid_attribute;

       l_username_attribute := l_c2_ldap_rec.username_attribute;


       l_filter       :=  '('

                   || l_username_attribute

                   || '='

                   || lower(p_username)

                   || ')';


       -- connect to ldap server

       do_init(g_host, g_port, l_use_ssl);


       -- bind as application user

       l_retval       := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_search_user, passwd => l_search_pass);


       IF l_retval != dbms_ldap_utl.success THEN

         -- unable to authenticate search user

         APEX_APPLICATION.g_print_success_message := 'Unable to Authenticate Search User.';

         l_retval                 := DBMS_LDAP.unbind_s(g_session);

         RETURN FALSE;

       END IF;


       -- search for user in ldap

       l_attrs(1)      := 'dn';

       l_retval       := DBMS_LDAP.search_s(g_session,

                             g_search_base,

                             DBMS_LDAP.scope_subtree,

                             l_filter,

                             l_attrs,

                             0,

                             l_message);


       -- return NULL if 0 or multiple entries are returned for a user

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


       IF l_retval != 1 THEN

         APEX_APPLICATION.g_print_success_message := 'Invalid Login Credentials.<br>Invalid User.';

         l_retval                 := DBMS_LDAP.unbind_s(g_session);

         RETURN FALSE;

       END IF;


       l_entry       := DBMS_LDAP.first_entry(g_session, l_message);

       l_full_user_dn    := DBMS_LDAP.get_dn(g_session, l_entry);


       -- bind to authenticate user

       l_retval       := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_full_user_dn, passwd => p_password);


       IF l_retval != dbms_ldap_utl.success THEN

         -- unable to authenticate user

         --c2c2_security_pub.increment_login_failures(p_username);

         APEX_APPLICATION.g_print_success_message := 'Invalid Login Credentials.<br>Invalid Password';

         l_retval                 := DBMS_LDAP.unbind_s(g_session);

         RETURN FALSE;                                               --**

       END IF;


       l_retval       := DBMS_LDAP.unbind_s(g_session);


       c2c2_security_pub.reset_login_failures(p_username);


       -- retrieve user's ldap attributes

      -- c2c2_security_pub.set_user_ldap_attributes(p_full_dn => l_full_user_dn, p_user_pwd => p_password);

      --  c2c2_security_pub.sync_ldap_user(p_full_dn => l_full_user_dn);


       RETURN TRUE;                                               --success

      EXCEPTION

       --001

       WHEN client_server_error THEN

         BEGIN

          --RAISE;

          APEX_APPLICATION.g_print_success_message := 'Client/Server ERROR';

          IF instr(SQLERRM, 'password expired!') > 0 THEN

            APEX_APPLICATION.g_print_success_message   :=  'Your password has expired.<br/>Please visit '

                                                                    || '<a href="https://exchange.gmacfs.com/web/portal'

                                    || '/home"><FONT SIZE="14">Ally Pulse</font></a></f'

                                    || 'ont> for account maintenance.';

          ELSIF instr(SQLERRM, 'Invalid credentials') > 0 THEN

            APEX_APPLICATION.g_print_success_message   :=  'Invalid Login Credentials.';

          END IF;

          l_retval                 := DBMS_LDAP.unbind_s(g_session);

          RETURN FALSE;

         END;

       --001

       WHEN OTHERS THEN

         BEGIN

          --001

          APEX_APPLICATION.g_print_success_message := 'There was a ERROR in the authentication function.';

          --001

          l_retval                 := DBMS_LDAP.unbind_s(g_session);

          RETURN FALSE;

         END;

      END authenticate_ally;


      FUNCTION get_full_DN(p_username IN VARCHAR2)

       RETURN VARCHAR2 IS

       l_c2_ldap_rec    c2c2_ldap_setting%ROWTYPE;

       l_use_ssl      VARCHAR2(200);

       l_search_user    VARCHAR2(256);

       l_search_pass    VARCHAR2(256);

       l_uid_attribute   VARCHAR2(256);

       l_username_attribute VARCHAR2(256);

       l_attrs       DBMS_LDAP.string_collection;

       l_filter       VARCHAR2(256);

       l_message      DBMS_LDAP.message;

       l_entry       DBMS_LDAP.message;

       l_full_user_dn    VARCHAR2(256);

       l_retval       PLS_INTEGER;

      BEGIN

       SELECT *

        INTO l_c2_ldap_rec

        FROM c2c2_ldap_setting

        WHERE is_current = 'Y';


       g_host        := l_c2_ldap_rec.ldap_host;

       g_port        := l_c2_ldap_rec.ldap_port;

       l_use_ssl      := l_c2_ldap_rec.ldap_use_ssl;

       l_search_user    := l_c2_ldap_rec.ldap_search_user;

       -- 001 Begin

       l_search_pass    := pkg_security.decrypt_string(l_c2_ldap_rec.ldap_search_pwd);

       -- 001 End

       g_search_base    := l_c2_ldap_rec.ldap_search_base;

       l_uid_attribute   := l_c2_ldap_rec.uid_attribute;

       l_username_attribute := l_c2_ldap_rec.username_attribute;


       l_filter       :=  '('

                   || l_username_attribute

                   || '='

                   || lower(p_username)

                   || ')';


       -- connect to ldap server

       do_init(g_host, g_port, l_use_ssl);


       -- bind as application user

       l_retval       := DBMS_LDAP.simple_bind_s(ld => g_session, dn => l_search_user, passwd => l_search_pass);


       IF l_retval != dbms_ldap_utl.success THEN

         -- unable to authenticate search user

         l_retval := DBMS_LDAP.unbind_s(g_session);

         RETURN '';

       END IF;


       -- search for user in ldap

       l_attrs(1)      := 'dn';

       l_retval       := DBMS_LDAP.search_s(g_session,

                             g_search_base,

                             DBMS_LDAP.scope_subtree,

                             l_filter,

                             l_attrs,

                             0,

                             l_message);


       -- return NULL if 0 or multiple entries are returned for a user

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


       IF l_retval != 1 THEN

         l_retval := DBMS_LDAP.unbind_s(g_session);

         RETURN '';

       END IF;


       l_entry       := DBMS_LDAP.first_entry(g_session, l_message);

       l_full_user_dn    := DBMS_LDAP.get_dn(g_session, l_entry);

       RETURN l_full_user_dn;

      END;


    FUNCTION get_user_attribute(i_vUID IN VARCHAR2, i_vAttribute IN VARCHAR2) RETURN VARCHAR2 AS


      l_attributes    wwv_flow_global.vc_arr2;

      l_attribute_values wwv_flow_global.vc_arr2;



      BEGIN


       l_attributes(1) := i_vAttribute;


       c2c2_ldap.get_user_attributes(p_use_current_settings => 'Y',

                      p_full_dn       => C2C2_LDAP.get_full_dn(i_vUID),

                      p_attributes      => l_attributes,

                      p_attribute_values   => l_attribute_values);


       RETURN l_attribute_values(1);


      END get_user_attribute;

      

      FUNCTION INACTIVE_USER_CHK RETURN VARCHAR2 AS

      A VARCHAR2(100);

      BEGIN

      A := 'INACTIVE USER';

         

        return A;

        APEX_APPLICATION.g_print_success_message := A;

        END INACTIVE_USER_CHK;

      

    END c2c2_ldap;


    In above package i commented two procedures .while iam calling that two procedures directly it is not throwing any error but when i call that procedures from another package it is thrwoing invalid ldap session.

  • raghunadhan
    raghunadhan Member Posts: 146 Red Ribbon

    Can anyone help on this.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,603 Red Diamond

    Very difficult to read the code in the above format, and follow its design and process flow.

    There are issues I've spotted. LDAP session created and not closed in some cases. Functions with unreachable code (e.g. return statement terminates function and statements following will never be executed). Etc.

    My suggestion is to make sure the LDAP session is created once only, and properly closed afterwards. Do not leak session handles.

    Add proper instrumentation calls - write trace messages to DBMS_OUTPUT for normal client sessions, or APEX_DEBUG for APEX web sessions.

    Do not repeat code blocks - modularise.

    And realise that PL/SQL is an implementation of the Ada programming language. Thus use style and standards as per the Ada Style Guide. The code posted is not exactly pleasant to look at.