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

        hi kiran,

         

        I tried to do the same, but I face the following problem (the code which raise the problem is blold and RED)

        in PORTALS home page,create a page process pl/sql on -load-before-header
        and put the following code

        declare
                     LDAP_SERVER constant varchar2(200) := 'hct.org';
                     LDAP_PORT constant number := 389;                   
                     LDAP_USER constant varchar2(200) := 'hct\itnew';    
                     LDAP_PASSW constant varchar2(200) := 'itnew';     
                     LDAP_BASE constant varchar2(200) := 'DC=hct,DC=org';
                                                                                
                     rc              integer;                                   
                     ldapSession     DBMS_LDAP.session;                         
                    ntUser          varchar2(30);                              
                    attrName        varchar2(255);                             
                    attrList        DBMS_LDAP.string_collection;               
                    valList         DBMS_LDAP.string_collection;               
                    ldapMessage     DBMS_LDAP.message;                         
                    ldapEntry       DBMS_LDAP.message;                         
                    berElem         DBMS_LDAP.ber_element;                     
                                                                               
                    --// very primitive assertion interface - should be catering
                    --// for unique error code and messages in a prod environment
                    procedure assert( condition boolean ) is                    
                    begin                                                       
                            if not condition then                               
                                    raise_application_error(                    
                                            -20001,                             
                                            'LDAP call unsuccessful.'           
                                    );                                          
                            end if;                                             
                    end;                                                        
                                                                                
                    procedure W( line varchar2 ) is                             
                    begin                                                       
                            DBMS_OUTPUT.put_line( line );                       
                    end;                                                        
            begin                                                               
                    --// logon to the Microsoft Active Directory Server         
                    DBMS_LDAP.USE_EXCEPTION := false;                            
                 --   W( 'Logging on to AD server;' );                            
                    ldapSession := DBMS_LDAP.init( LDAP_SERVER, LDAP_PORT );    
                                                                                
                    rc := DBMS_LDAP.simple_bind_s(                              
                            ld => ldapSession,                                  
                            dn => LDAP_USER,                                    
                            passwd => LDAP_PASSW                                
                    );                                                          
                    assert( rc = DBMS_LDAP_UTL.SUCCESS  );                                                                
                                                                                
                    --// set the NTLM user and attributes that we want                        
                    ntUser := 'et04';                                        
                    attrList(1) := 'title';   
                    --// so a search on the username (NTLM username typically)  
               --     W( 'Doing a basic search on NT username' );                 
                    rc := DBMS_LDAP.search_s(                                   
                            ld => ldapSession,                                  
                            base => LDAP_BASE,                                  
                            scope => DBMS_LDAP.SCOPE_SUBTREE,                   
                            filter => '(&(objectclass=USER)(SAMAccountName='||ntUser||'))',
                            attrs => attrList,                                            
                            attronly => 0,                                                
                            res => ldapMessage                                            
                    );                                                                    
                                                                                          
                    assert( rc = DBMS_LDAP_UTL.SUCCESS  );                                
                                                                                         
                    if DBMS_LDAP.count_entries(ldapSession,ldapMessage) > 0 then          
                           -- W( '1st entry - only 1 expected as we did a unique account lookup' );
                            ldapEntry := DBMS_LDAP.first_entry( ldapSession, ldapMessage );
         
                            while (ldapEntry is not null) loop
                                    --// get the attribute
                                    attrName := DBMS_LDAP.first_attribute(
                                                    ld => ldapSession,
                                                    ldapEntry => ldapEntry,
                                                   ber_elem  => berElem
                                            );
                                    while (attrName is not null) loop
                                            --// get the list of values for the attribute
                                            valList := DBMS_LDAP.get_values(
                                                            ld => ldapSession,
                                                           ldapEntry => ldapEntry,
                                                            attr =>  attrName
                                                   );
                                            --// for simplicity sake, we expect a scalar name-value and
                                           --// thus a single value only
                                         --  W( attrName||'='||valList(0) );
        if valList(0)='Student' then
        apex_util.redirect_url(p_url=>'http://oradev.hct.org:8080/ords/f?p=114:1');
        else
        apex_util.redirect_url(p_url=>'http://oradev.hct.org:8080/ords/f?p=113:1');
        end if;
        -- dbms_output.put_line(valList(0));
                                            --// proceed to process the next attribute
                                            attrName :=  DBMS_LDAP.next_attribute(
                                                            ld => ldapSession,
                                                            ldapEntry => ldapEntry,
                                                            ber_elem  => berElem
                                                    );
                                    end loop;
         
                                  --// not really needed in this case as we're processing a single SAMaccount entry
                                   ldapEntry := DBMS_LDAP.next_entry( ldapSession, ldapEntry );
                           end loop;
                   end if;

                 --  W( 'Disconnecting from AD server' );
                   rc := DBMS_LDAP.unbind_s( ld => ldapSession );
          
           end;

        I tried to do the following but failed

         

        SYSDBASQL>   begin
          DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
            acl          => 'apex_040200.xml',
            principal=>'APEX_040200',
            is_grant =>TRUE,
            privilege=>'connect',
            position=>null);
            commit;
          end;
        SQL> /
        begin
        *
        ERROR at line 1:
        ORA-46114: ACL name /sys/acls/apex_040200.xml not found.
        ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
        ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 243
        ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 550
        ORA-06512: at line 2

        how to fix this?

         

        Thanks

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

          Hi Maahjoor,

          Maahjoor wrote:


          I tried to do the following but failed

          SYSDBASQL>   begin
            DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
              acl          => 'apex_040200.xml',
              principal=>'APEX_040200',
              is_grant =>TRUE,
              privilege=>'connect',
              position=>null);
              commit;
            end;
          SQL> /
          begin
          *
          ERROR at line 1:
          ORA-46114: ACL name /sys/acls/apex_040200.xml not found.
          ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
          ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 243
          ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 550
          ORA-06512: at line 2

          how to fix this?

               The error in the screenshot says that you do not have the correct ACLs in place for APEX_040200 schema.

               Refer how to grant required ACLs for APEX_040200 schema : Enable Network Services in Oracle Database 11g

               Execute both the anonymous blocks given under the section "Granting Connect Privileges" and do not forget to commit after successful execution of the block.

           

          Regards,

          Kiran

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

            I have already run the first block. but the second one is for limited acces, somhow, I run it as will and it was successful and I do commit.

             

            but the same error.

             

            Regards

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

              I have tried the below steps as well from this site How to resolve ORA-24247: network access denied by access control list (ACL) | DB Tricks

               

              ---------------create an acl------------------------

              begin

              dbms_network_acl_admin.create_acl (

              acl => 'apex_util.xml', -- or any other name

              description => 'apex_util Access',

              principal => 'APEX_040200', -- the user name trying to access the network resource

              is_grant => TRUE,

              privilege => 'connect',

              start_date => null,

              end_date => null

              );

              end;

              commit;

              -----------------------check the privileges-------------------

              SELECT * FROM dba_network_acl_privileges where principal='APEX_040200';

              -------------------give priviliges-------------

              begin

              DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apex_util.xml',

              principal => 'APEX_040200',

              is_grant => true,

              privilege => 'connect');

              end;

              /

              commit;

              begin

              DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apex_util.xml',

              principal => 'HCT',

              is_grant => true,

              privilege => 'connect');

              end;

              /

              begin

              DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apex_util.xml',

              principal => 'FLOWS_FILES',

              is_grant => true,

              privilege => 'connect');

              end;

              /

              begin

              DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apex_util.xml',

              principal => 'APEX_LISTENER',

              is_grant => true,

              privilege => 'connect');

              end;

              /

              begin

              DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apex_util.xml',

              principal => 'APEX_REST_PUBLIC_USER',

              is_grant => true,

              privilege => 'connect');

              end;

              /

              commit;

              but the same error.

               

              Regards

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

                Hi Maahjoor,

                 

                    Can you post the output of :

                SELECT * FROM dba_network_acl_privileges where principal='APEX_040200';
                

                 

                    Can you check whether the on page load process works with:

                if valList(0) = '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;
                

                  

                     Also, if still it is giving error, set the debug ON, check the debug log and paste it to forum from the point where the error starts.

                 

                    Check whether the login to the PORTALS application is successful, by setting the on page load process to never.

                 

                Regards,

                Kiran

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

                  Can you check whether the on page load process works with:

                   

                  1. if valList(0) = 'Student' then 
                  2. apex_util.redirect_url(p_url=>'f?p=114:1'); 
                  3. else 
                  4. apex_util.redirect_url(p_url=>'f?p=113:1'); 
                  5. end if; 

                  I tried it and result is the same, I mean the same error.

                  Also, if still it is giving error, set the debug ON, check the debug log and paste it to forum from the point where the error starts.

                  could you guide how could I enable the degub ON please?

                   

                  Check whether the login to the PORTALS application is successful, by setting the on page load process to never.

                  yes, it is successful, I tried to login without the process and I was loged in.

                   

                  Thank you.

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

                    Hi Maahjoor,

                    Maahjoor wrote:

                    Also, if still it is giving error, set the debug ON, check the debug log and paste it to forum from the point where the error starts.

                    could you guide how could I enable the degub ON please?

                         The ACLs look good, so the ACL error should not be raised.

                         Here is how you could enable the debug mode : http://docs.oracle.com/cd/E37097_01/doc.42/e35125/debug_mode.htm#HTMDB10003

                         Check the debug log as to exactly where the issue lies.

                     

                    Regards,

                    Kiran

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

                      one strange thing I noticed

                      I comment these lines in the code, but I still get the error

                       

                      /*if valList(0)='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;

                      */

                      why?

                       

                      Thanks

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

                        this is the debug page

                         

                        37 is the lates number when I try to run the page.

                         

                        Thanks

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

                          Hi Maahjoor,

                           

                               Could you paste the error screen-shot of the page again?

                               The debug log shows first exception as ORA-20987, but the page screen-shot above shows ORA-24247. This is quite confusing.

                           

                          Regards,

                          Kiran

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

                            I Take new snapshots for you. I have commented the following code, but still facing this error

                            /*if valList(0)='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;

                            */

                             

                            and

                             

                            Regards

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

                              Hi Maahjoor,

                              Maahjoor wrote:

                               

                              what do you think about the following blog? should I try it?

                              http://www.pythian.com/blog/setting-up-network-acls-in-oracle-11g-for-dummies/

                                  No, this blog refers for 11g and there are changes in Fine-grained Access to Network Services in Oracle Database 12c.

                                  Refer :

                                  Usually the normal ACL should work for APEX_040200 schema, but with LDAP I think there is different case.

                                  Try this:

                              BEGIN
                              
                                  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
                                      host        => 'hct.org',
                                      lower_port  => 389,
                                      upper_port  => 389,
                                      ace          => xs$ace_type ( privilege_list => xs$name_list('connect'),
                                                                    principal_name => 'APEX_040200',
                                                                    principal_type => xs_acl.ptype_db));
                              
                              END;
                              /
                              COMMIT;
                              

                                

                                   Refer :

                               

                                  Hope this helps!

                               

                              Regards,

                              Kiran

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

                                hi,

                                 

                                I tried your solution but result is the same.

                                I think something is problematic in my below code. could you recheck it please? or guide me an alternative which could read the TITLE for me from LDAP?

                                 

                                declare
                                             LDAP_SERVER constant varchar2(200) := 'hct.org';
                                             LDAP_PORT constant number := 389;                   
                                             LDAP_USER constant varchar2(200) := 'hct\itnew';    
                                             LDAP_PASSW constant varchar2(200) := 'itnew';     
                                             LDAP_BASE constant varchar2(200) := 'DC=hct,DC=org';
                                                                                                        
                                             rc              integer;                                   
                                             ldapSession     DBMS_LDAP.session;                         
                                            ntUser          varchar2(30);                              
                                            attrName        varchar2(255);                             
                                            attrList        DBMS_LDAP.string_collection;               
                                            valList         DBMS_LDAP.string_collection;               
                                            ldapMessage     DBMS_LDAP.message;                         
                                            ldapEntry       DBMS_LDAP.message;                         
                                            berElem         DBMS_LDAP.ber_element;                     
                                                                                                       
                                            --// very primitive assertion interface - should be catering
                                            --// for unique error code and messages in a prod environment
                                            procedure assert( condition boolean ) is                    
                                            begin                                                       
                                                    if not condition then                               
                                                            raise_application_error(                    
                                                                    -20001,                             
                                                                    'LDAP call unsuccessful.'           
                                                            );                                          
                                                    end if;                                             
                                            end;                                                        
                                                                                                        
                                            procedure W( line varchar2 ) is                             
                                            begin                                                       
                                                    DBMS_OUTPUT.put_line( line );                       
                                            end;                                                        
                                    begin                                                               
                                            --// logon to the Microsoft Active Directory Server         
                                            DBMS_LDAP.USE_EXCEPTION := false;                            
                                         --   W( 'Logging on to AD server;' );                            
                                            ldapSession := DBMS_LDAP.init( LDAP_SERVER, LDAP_PORT );    
                                                                                                        
                                            rc := DBMS_LDAP.simple_bind_s(                              
                                                    ld => ldapSession,                                  
                                                    dn => LDAP_USER,                                    
                                                    passwd => LDAP_PASSW                                
                                            );                                                          
                                            assert( rc = DBMS_LDAP_UTL.SUCCESS  );                                                                
                                                                                                        
                                            --// set the NTLM user and attributes that we want                        
                                            ntUser := 'et04';                                        
                                            attrList(1) := 'title';   
                                            --// so a search on the username (NTLM username typically)  
                                       --     W( 'Doing a basic search on NT username' );                 
                                            rc := DBMS_LDAP.search_s(                                   
                                                    ld => ldapSession,                                  
                                                    base => LDAP_BASE,                                  
                                                    scope => DBMS_LDAP.SCOPE_SUBTREE,                   
                                                    filter => '(&(objectclass=USER)(SAMAccountName='||ntUser||'))',
                                                    attrs => attrList,                                            
                                                    attronly => 0,                                                
                                                    res => ldapMessage                                            
                                            );                                                                    
                                                                                                                  
                                            assert( rc = DBMS_LDAP_UTL.SUCCESS  );                                
                                                                                                                 
                                            if DBMS_LDAP.count_entries(ldapSession,ldapMessage) > 0 then          
                                                   -- W( '1st entry - only 1 expected as we did a unique account lookup' );
                                                    ldapEntry := DBMS_LDAP.first_entry( ldapSession, ldapMessage );
                                 
                                                    while (ldapEntry is not null) loop
                                                            --// get the attribute
                                                            attrName := DBMS_LDAP.first_attribute(
                                                                            ld => ldapSession,
                                                                            ldapEntry => ldapEntry,
                                                                           ber_elem  => berElem
                                                                    );
                                                            while (attrName is not null) loop
                                                                    --// get the list of values for the attribute
                                                                    valList := DBMS_LDAP.get_values(
                                                                                    ld => ldapSession,
                                                                                   ldapEntry => ldapEntry,
                                                                                    attr =>  attrName
                                                                           );
                                                                    --// for simplicity sake, we expect a scalar name-value and
                                                                   --// thus a single value only
                                                                 --  W( attrName||'='||valList(0) );
                                /*if valList(0)='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;
                                */
                                -- dbms_output.put_line(valList(0));
                                                                    --// proceed to process the next attribute
                                                                    attrName :=  DBMS_LDAP.next_attribute(
                                                                                    ld => ldapSession,
                                                                                    ldapEntry => ldapEntry,
                                                                                    ber_elem  => berElem
                                                                            );
                                                            end loop;
                                 
                                                          --// not really needed in this case as we're processing a single SAMaccount entry
                                                           ldapEntry := DBMS_LDAP.next_entry( ldapSession, ldapEntry );
                                                   end loop;
                                           end if;

                                         --  W( 'Disconnecting from AD server' );
                                           rc := DBMS_LDAP.unbind_s( ld => ldapSession );
                                  
                                   end;

                                 

                                'itnew' is the testing user for student in active directory.

                                 

                                Thank you.

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

                                  Also , please check the result of the following two queries;

                                   

                                  and

                                  I am leaving my office, I will update the thread tomorrow.

                                  Regards.