3 Replies Latest reply on Sep 10, 2015 10:25 AM by Gayan

    DBMS_LDAP: PL/SQL - Invalid LDAP Session. error in login page.

    Gayan

      i'm using apex 5.

      i created custom authentication scheme for my application.i want to authenticate it via LDAP server.i have OracleXE112 as database server.first i created ACL using following code.

      BEGIN

        DBMS_NETWORK_ACL_ADMIN.create_acl (

          acl          => 'ldap_acl_file.xml',

          description  => 'ACL to grant access to LDAP server',

          principal    => 'APEX_050000',

          is_grant     => TRUE,

          privilege    => 'connect',

          start_date   => SYSTIMESTAMP,

          end_date     => NULL);

       

       

        DBMS_NETWORK_ACL_ADMIN.assign_acl (

          acl         => 'ldap_acl_file.xml',

          host        => '<MY_HOST>',

          lower_port  => 389,

          upper_port  => NULL);

       

       

        COMMIT;

      END;

      /

      Then i used following function as authentication function name.

      FUNCTION             ldap_auth(

          p_username IN VARCHAR2,

          p_password IN VARCHAR2 )

        RETURN BOOLEAN

      IS

        l_ldap_host  VARCHAR2(256) := '<MY_HOST>';

        l_ldap_port  VARCHAR2(256) := '389';

        l_ldap_base  VARCHAR2(256) := '<MY_DN>';

        l_dn_prefix  VARCHAR2(100) := '<MY_PREFIX>';         -- Amend as desired'.

        l_auth_group VARCHAR2(100) := 'MY_APP_LDAP_GROUP'; -- Amend as desired'.

        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_attr_name VARCHAR2(256);

        l_ber_element DBMS_LDAP.ber_element;

        l_vals DBMS_LDAP.string_collection;

        l_ok BOOLEAN;

      BEGIN

        -- Choose to raise exceptions.

        DBMS_LDAP.use_exception := TRUE;

        -- Connect to the LDAP server.

        l_session := DBMS_LDAP.init(hostname => l_ldap_host, portnum => l_ldap_port);

        l_retval  := DBMS_LDAP.simple_bind_s(ld => l_session, dn => l_dn_prefix || p_username, passwd => p_password);

        -- No exceptions mean you are authenticated. Now check if authorized.

        -- Get all "memberOf" attributes

        l_attrs(1) := 'memberOf';

        -- Searching for the user info using his samaccount (windows login)

        l_retval := DBMS_LDAP.search_s(ld => l_session, base => l_ldap_base, scope => DBMS_LDAP.scope_subtree, filter => '(&(objectClass=*)(sAMAccountName=' || p_username || '))', attrs => l_attrs, attronly => 0, res => l_message);

        -- Get the first and only entry.

        l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);

        -- Get the first Attribute for the entry.

        l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);

        -- Loop through all "memberOf" attributes

        WHILE l_attr_name IS NOT NULL

        LOOP

          -- Get the values of the attribute

          l_vals := DBMS_LDAP.get_values(ld => l_session, ldapentry => l_entry, attr => l_attr_name);

          -- Check the contents of the value

          FOR i IN l_vals.FIRST .. l_vals.LAST

          LOOP

            -- Check the user is a member of the required group.

            l_ok := INSTR(UPPER(l_vals(i)), l_auth_group) > 0 ;

            EXIT

          WHEN l_ok;

          END LOOP;

          EXIT

        WHEN l_ok;

          l_attr_name := DBMS_LDAP.next_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element );

        END LOOP;

      l_retval := dbms_ldap.unbind_s(ld => l_session);

      IF NOT l_ok THEN

        APEX_UTIL.set_custom_auth_status (p_status => 'You are not in the correct LDAP group to use this application.');

      END IF;

      -- Return authentication + authorization result.

      RETURN l_ok;

      EXCEPTION

      WHEN OTHERS THEN

        -- Exception means authentication failed.

        l_retval := DBMS_LDAP.unbind_s(ld => l_session);

        APEX_UTIL.set_custom_auth_status (p_status => 'Incorrect username and/or password');

        RETURN FALSE;

      END;

       

      In the Sentry Function Name in settings has no value.why i get this error and how can i fix that?

      P.S i read some guides but i couldn't get and idea.they discussed about VPD security attribute.but have no idea.

        • 1. Re: DBMS_LDAP: PL/SQL - Invalid LDAP Session. error in login page.
          Kiran Pawar

          Hi Gayan,

          Gayan wrote:

           

          i'm using apex 5.

          i created custom authentication scheme for my application.i want to authenticate it via LDAP server.i have OracleXE112 as database server.first i created ACL using following code.

          BEGIN

            DBMS_NETWORK_ACL_ADMIN.create_acl (

              acl          => 'ldap_acl_file.xml',

              description  => 'ACL to grant access to LDAP server',

              principal    => 'APEX_050000',

              is_grant     => TRUE,

              privilege    => 'connect',

              start_date   => SYSTIMESTAMP,

              end_date     => NULL);

           

            DBMS_NETWORK_ACL_ADMIN.assign_acl (

              acl         => 'ldap_acl_file.xml',

              host        => '<MY_HOST>',

              lower_port  => 389,

              upper_port  => NULL);

           

            COMMIT;

          END;

          /

               Instead create and use the "power_users.xml" ACL mentioned here.

               Refer : https://docs.oracle.com/cd/E59726_01/install.50/e39144/listener.htm#HTMIG29162

          Then i used following function as authentication function name.

          FUNCTION             ldap_auth(

              p_username IN VARCHAR2,

              p_password IN VARCHAR2 )

            RETURN BOOLEAN

          IS

            l_ldap_host  VARCHAR2(256) := '<MY_HOST>';

            l_ldap_port  VARCHAR2(256) := '389';

            l_ldap_base  VARCHAR2(256) := '<MY_DN>';

            l_dn_prefix  VARCHAR2(100) := '<MY_PREFIX>';         -- Amend as desired'.

            l_auth_group VARCHAR2(100) := 'MY_APP_LDAP_GROUP'; -- Amend as desired'.

            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_attr_name VARCHAR2(256);

            l_ber_element DBMS_LDAP.ber_element;

            l_vals DBMS_LDAP.string_collection;

            l_ok BOOLEAN;

          BEGIN

            -- Choose to raise exceptions.

            DBMS_LDAP.use_exception := TRUE;

            -- Connect to the LDAP server.

            l_session := DBMS_LDAP.init(hostname => l_ldap_host, portnum => l_ldap_port);

            l_retval  := DBMS_LDAP.simple_bind_s(ld => l_session, dn => l_dn_prefix || p_username, passwd => p_password);

            -- No exceptions mean you are authenticated. Now check if authorized.

            -- Get all "memberOf" attributes

            l_attrs(1) := 'memberOf';

            -- Searching for the user info using his samaccount (windows login)

            l_retval := DBMS_LDAP.search_s(ld => l_session, base => l_ldap_base, scope => DBMS_LDAP.scope_subtree, filter => '(&(objectClass=*)(sAMAccountName=' || p_username || '))', attrs => l_attrs, attronly => 0, res => l_message);

            -- Get the first and only entry.

            l_entry := DBMS_LDAP.first_entry(ld => l_session, msg => l_message);

            -- Get the first Attribute for the entry.

            l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);

            -- Loop through all "memberOf" attributes

            WHILE l_attr_name IS NOT NULL

            LOOP

              -- Get the values of the attribute

              l_vals := DBMS_LDAP.get_values(ld => l_session, ldapentry => l_entry, attr => l_attr_name);

              -- Check the contents of the value

              FOR i IN l_vals.FIRST .. l_vals.LAST

              LOOP

                -- Check the user is a member of the required group.

                l_ok := INSTR(UPPER(l_vals(i)), l_auth_group) > 0 ;

                EXIT

              WHEN l_ok;

              END LOOP;

              EXIT

            WHEN l_ok;

              l_attr_name := DBMS_LDAP.next_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element );

            END LOOP;

          l_retval := dbms_ldap.unbind_s(ld => l_session);

          IF NOT l_ok THEN

            APEX_UTIL.set_custom_auth_status (p_status => 'You are not in the correct LDAP group to use this application.');

          END IF;

          -- Return authentication + authorization result.

          RETURN l_ok;

          EXCEPTION

          WHEN OTHERS THEN

            -- Exception means authentication failed.

            l_retval := DBMS_LDAP.unbind_s(ld => l_session);

            APEX_UTIL.set_custom_auth_status (p_status => 'Incorrect username and/or password');

            RETURN FALSE;

          END;

               Why are you using DBMS_LDAP? Why don't you use either:

          • Pre-configured LDAP Authentication Scheme OR

               Refer : https://docs.oracle.com/cd/E59726_01/doc.50/e39147/sec_authentication.htm#CHDHFDFA

          • APEX_LDAP based PL/SQL Authentication Function and "Custom" Authentication Scheme

               Refer : Re: separate authentication and authorization for Active directory groups

               NOTE : Also it is not a good practice to write the whole code above in "PL/SQL Source" for the Authentication Function. You should create stored/packaged function and include only the function name in the "Authentication Function Name".

           

          Regards,

          Kiran

          • 2. Re: DBMS_LDAP: PL/SQL - Invalid LDAP Session. error in login page.
            Gayan

            hi

            thanks for your reply.

            when i use pre configured LDAP server,i face with following error.

            ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db1

            when i workaround this i understood pre-configured LDAP as client stored users in groups.i followed this.Roels Blog: Using LDAP for Authentication and Authorization within APEX.So i chose 'Custom' authentication scheme based on  PL/SQL function where this issue occurs.Consider i am new to LDAP configuration.So how can i fix this.thanks