1 2 3 Previous Next 36 Replies Latest reply on Jul 5, 2015 4:39 AM by Maahjoor Go to original post
      • 30. Re: separate authentication and authorization for Active directory groups
        Maahjoor

        Hi Kiran,

         

        I will consider this thread as answered since the original purpose of the thread is fixed.

        I will open a new thread for ACL issue.

         

        Thank you so much for your kind help and guidance.

         

        Regards.

        • 31. Re: separate authentication and authorization for Active directory groups
          Kiran Pawar

          Hi Maahjoor,

          Maahjoor wrote:


          I will consider this thread as answered since the original purpose of the thread is fixed.

          I will open a new thread for ACL issue.

               Okay with opening of new thread. I am trying out your code on my Oracle Database 12c instance as well as trying out the first demo script mentioned here:

               Oracle 12c DBMS_LDAP Demos

               Will get back to you if got any breakthrough.

           

          Regards,

          Kiran

          • 32. Re: separate authentication and authorization for Active directory groups
            Kiran Pawar

            Hi Maahjoor,

             

                 Try this (this will enlist all the attributes for given user) by connecting to your schema from SQL Developer:

            DECLARE
            
              -- Adjust as necessary.
              l_ldap_host    VARCHAR2(256) := 'hct.org';
              l_ldap_port    VARCHAR2(256) := '389';
              l_ldap_user    VARCHAR2(256) := 'cn=hct\itnew';
              l_ldap_passwd  VARCHAR2(256) := 'itnew';
              l_ldap_base    VARCHAR2(256) := 'DC=hct,DC=org';
            
              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;
            
            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_ldap_user||','||l_ldap_base,
                                                  passwd => l_ldap_passwd);
            
              -- Get all attributes
              l_attrs(1) := '*'; -- retrieve all attributes
              l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                             base     => l_ldap_base,
                                             scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                             filter   => l_ldap_user,
                                             attrs    => l_attrs,
                                             attronly => 0,
                                             res      => l_message);
            
              IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
                -- Get all the entries returned by our search.
                l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                                 msg => l_message);
            
                << entry_loop >>
                WHILE l_entry IS NOT NULL LOOP
                  -- Get all the attributes for this entry.
                  DBMS_OUTPUT.PUT_LINE('---------------------------------------');
                  l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                                           ldapentry => l_entry,
                                                           ber_elem  => l_ber_element);
                  << attributes_loop >>
                  WHILE l_attr_name IS NOT NULL LOOP
                    -- Get all the values for this attribute.
                    l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                                    ldapentry => l_entry,
                                                    attr      => l_attr_name);
                    << values_loop >>
                    FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
                      DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
                    END LOOP values_loop;
                    l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                            ldapentry => l_entry,
                                                            ber_elem  => l_ber_element);
                  END LOOP attibutes_loop;
                  l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                                  msg => l_entry);
                END LOOP entry_loop;
              END IF;
            
              -- Disconnect from the LDAP server.
              l_retval := DBMS_LDAP.unbind_s(ld => l_session);
              DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
            
            END;
            /
            

                 NOTE : The DN parameter on line 29 requires exact distinguished name for the user. Moreover, on line 37 for filter you can use username viz. 'cn=firstname.lastname'.

             

                 You can specify specific attribute to be fetched for the user in concern by changing line 33 from:

            l_attrs(1) := '*';
            

                 to

            l_attrs(1) := 'title';
            

             

                 Then you can write a function based on above code to fetch the LDAP user attribute as follows:

            create or replace function fnc_get_ldap_user_attr_val ( p_username in varchar2
                                                                  , p_password in varchar2
                                                                  , p_attrname in varchar2 )
            return varchar2
            as
            
              -- Adjust as necessary.
              l_ldap_host    VARCHAR2(256) := 'hct.org';
              l_ldap_port    VARCHAR2(256) := '389';
              l_ldap_user    VARCHAR2(256) := 'cn='||p_username;
              l_ldap_passwd  VARCHAR2(256) := p_password;
              l_ldap_base    VARCHAR2(256) := 'DC=hct,DC=org';
            
              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_attr_value   VARCHAR2(256);
              l_ber_element  DBMS_LDAP.ber_element;
              l_vals         DBMS_LDAP.string_collection;
            
            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_ldap_user||','||l_ldap_base,
                                                  passwd => l_ldap_passwd);
            
              -- Get specific attributes
              l_attrs(1) := p_attrname;
              l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                             base     => l_ldap_base,
                                             scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                             filter   => l_ldap_user,
                                             attrs    => l_attrs,
                                             attronly => 0,
                                             res      => l_message);
            
              IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
                -- Get all the entries returned by our search.
                l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                                 msg => l_message);
            
                << entry_loop >>
                WHILE l_entry IS NOT NULL LOOP
                  -- Get all the attributes for this entry.
                  DBMS_OUTPUT.PUT_LINE('---------------------------------------');
                  l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                                           ldapentry => l_entry,
                                                           ber_elem  => l_ber_element);
                  << attributes_loop >>
                  WHILE l_attr_name IS NOT NULL LOOP
                    -- Get all the values for this attribute.
                    l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                                    ldapentry => l_entry,
                                                    attr      => l_attr_name);
                    << values_loop >>
                    FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
                      DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
                      l_attr_value := l_vals(i);
                    END LOOP values_loop;
                    l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                            ldapentry => l_entry,
                                                            ber_elem  => l_ber_element);
                  END LOOP attibutes_loop;
                  l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                                  msg => l_entry);
                END LOOP entry_loop;
              END IF;
            
              -- Disconnect from the LDAP server.
              l_retval := DBMS_LDAP.unbind_s(ld => l_session);
              DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
              DBMS_OUTPUT.PUT_LINE('Attribute value: ' || l_attr_value);
            
              return l_attr_value;
            
            END fnc_get_ldap_user_attr_val;
            /
            

               

                 Then create an Application Item say AI_USER_AD_TITLE in you Application -> Shared Components.

                 Create following procedure to set the application item on the login of the user in your APEX application:

            create or replace procedure ldap_post_auth
            as
            
              l_attr_value varchar2(512):
            
            begin
            
              l_attr_value := fnc_get_ldap_user_attr_val ( p_username => apex_util.get_session_state('P101_USERNAME')
                                                         , p_password => apex_util.get_session_state('P101_PASSWORD')
                                                         , p_attrname => 'title' );
            
              apex_util.set_session_state('AI_USER_AD_TITLE', l_attr_value);
            
            end ldap_post_auth;
            

               

                 Change the "Post-Authentication Procedure Name" in your Authentication Scheme to "ldap_post_auth".

                 Then change the on-load process on your home page of your PORTALS application to:

            begin
            
                if :AI_USER_AD_TITLE = 'Student' then
                    apex_util.redirect_url(p_url=>'f?p=114:1');
                else
                    apex_util.redirect_url(p_url=>'f?p=113:1');
                end if;
            
            end;
            

               

            Hope this helps!

             

            Regards,

            Kiran

            1 person found this helpful
            • 33. Re: Re: separate authentication and authorization for Active directory groups
              Maahjoor

              Hi kiran,

               

              so much thorough and helpful, and I am thinking that this is the solution of my problem, but unfortunately I am facing a credential problem in your first code.

              when I run the first anonymous block you provide from sql developer while I am connected to sys user, It throw the following error

              -- Disconnect from the LDAP server. 

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

                DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval); 

               

              END; 

              Error report -

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

              ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

              ORA-06512: at "SYS.DBMS_LDAP", line 1487

              ORA-06512: at "SYS.DBMS_LDAP", line 79

              ORA-06512: at line 27

              31202. 00000 -  "DBMS_LDAP: LDAP client/server error: %s"

              *Cause:    There is a problem either on the LDAP server or on the client.

              *Action:   Please report this error to the LDAP server administrator or

                         your Database administrator.

               

              I think the problem is here

              l_ldap_user    VARCHAR2(256) := 'cn=hct\itnew';

              I tried many possibilities like 'hct\itnew'  and 'cn=itnew' and 'itnew' and giving the firstname.lastname like 'cn=hct\itnew.itnew'

              but none of the above worked. I have called our ldap administrator but he cannot help.

              I search internet and am still searching for the reason. I think the problem is in ACL.

              could you help in this regard please?

               

              thank you.

              • 34. Re: Re: separate authentication and authorization for Active directory groups
                Maahjoor

                regarding ACL, I have the followings;

                select * from DBA_NETWORK_ACL_PRIVILEGES ;

                select * from DBA_NETWORK_ACLS ;

                 

                the queries I adopted from APEX and ORA-24247: network access denied by access control list (ACL)

                regards

                • 35. Re: Re: Re: separate authentication and authorization for Active directory groups
                  Kiran Pawar

                  Hi Maahjoor,

                  Maahjoor wrote:

                   

                  when I run the first anonymous block you provide from sql developer while I am connected to sys user, It throw the following error

                  Error report -

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

                  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

                  ORA-06512: at "SYS.DBMS_LDAP", line 1487

                  ORA-06512: at "SYS.DBMS_LDAP", line 79

                  ORA-06512: at line 27

                  31202. 00000 -  "DBMS_LDAP: LDAP client/server error: %s"

                  *Cause:    There is a problem either on the LDAP server or on the client.

                  *Action:  Please report this error to the LDAP server administrator or

                            your Database administrator.

                  I think the problem is here

                  l_ldap_user    VARCHAR2(256) := 'cn=hct\itnew';

                  I tried many possibilities like 'hct\itnew'  and 'cn=itnew' and 'itnew' and giving the firstname.lastname like 'cn=hct\itnew.itnew'

                  but none of the above worked. I have called our ldap administrator but he cannot help.

                      The line 29 from the first anonymous block requires the DN to be a correct LDAP Distinguished Name (DN) string. And in this case only your LDAP Administrator can help you. He can tell you what should be the correct DN string for your LDAP server users.

                      The line 29 concatenates the l_ldap_user and l_ldap_base viz. it concatenates CN with LDAP base string to get a DN. In your case, the variables are:

                  l_ldap_user  VARCHAR2(256) := 'cn=hct\itnew';
                  l_ldap_base  VARCHAR2(256) := 'DC=hct,DC=org';
                  

                      so your concatenated DN string on line 29 will be:

                  'cn=hct\itnew,DC=hct,DC=org'
                  

                      Check with your LDAP administrator what should be the CN and what should be DN for a particular LDAP user. LDAP is an hierarchical tree and a full DN string identifies a leaf node in the tree.

                  I search internet and am still searching for the reason. I think the problem is in ACL.

                      I don't think the problem is ACL(after seeing the query results for DBA_NETWORK_ACL_PRIVILEGES), otherwise on executing the first anonymous block itself it would have raised the error : "ORA-24247: network access denied by access control list (ACL)"

                   

                  Regards,

                  Kiran

                  1 person found this helpful
                  • 36. Re: separate authentication and authorization for Active directory groups
                    Maahjoor

                    yes, true,

                     

                    i have currently deployed the application on oracle 10g, but our organization is moving to 12c soon.

                    once we moved to 12c, i will dig this issue again, first with our ldap administrators, and then this forum.

                    i really appriciate your kind help and assistance. specially the reply i mark as correct is very very thorough and it was what i want for this specific problem since i was really feeling myself in a close street.

                     

                    Thank you dear.

                    Regards.

                    1 2 3 Previous Next