4 Replies Latest reply: Mar 10, 2013 9:18 AM by rogers42 RSS

    Need help to debug the ACL

    rogers42
      Hi Folks,

      I am trying to implement an Access Control List. While I can successfully access the network service as sys user, but not as the test1 user.

      I was wondering, if somebody might be able to take a look at my code and point out my mistake.

      DB Version: *11.2.0.1.0*
      Platform: Windows 7 (64 bit)

      ACL Creation Code:
      # Create a user
      CREATE USER test1 IDENTIFIED BY xxxxxx;
      GRANT CONNECT TO test1;
      
      
      # Create the ACL
      BEGIN
        dbms_network_acl_admin.create_acl (
          acl                  => 'test_acl_file.xml',
          description          => 'A test of the ACL functionality',
          principal            => 'TEST1',
          is_grant             => TRUE,
          PRIVILEGE      => 'connect',
          start_date           => SYSTIMESTAMP,
          end_date        => NULL);
          
        COMMIT;
      END;
      /
      
      # 
      BEGIN
        dbms_network_acl_admin.assign_acl (
          acl       => 'test_acl_file.xml',
          host      => 'dbaexpert.com',
          lower_port  => NULL,
          upper_port  => NULL);  
          
      END;
      /
      Test Results:
      User: sys
      
      SELECT utl_http.request('http://www.dbaexpert.com')
      from DUAL;
      
      Output:
      
      UTL_HTTP.REQUEST('HTTP://WWW.DBAEXPERT.COM')                                                                                                                                                                                                   --------------------------------------------------------------------------------------
      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
       <html xmlns="http://www.w3.org/1999/xhtml"><head>                                                                                                                                                                                                                                                                       ...... 
      </head>
      User: test1

      Executing the above statement as "test1" user
      Output:
      
      Error starting at line 1 in command:
      SELECT utl_http.request('http://www.dbaexpert.com')
      from DUAL
      Error report:
      SQL Error: ORA-29273: HTTP request failed
      ORA-06512: at "SYS.UTL_HTTP", line 1722
      ORA-24247: network access denied by access control list (ACL)
      ORA-06512: at line 1
      29273. 00000 -  "HTTP request failed"
      *Cause:    The UTL_HTTP package failed to execute the HTTP request.
      *Action:   Use get_detailed_sqlerrm to check the detailed error message.
                 Fix the error and retry the HTTP request.                                                       
      Sanity Check:
      select ACL,
             PRINCIPAL,
             privilege,
             IS_GRANT
      from DBA_NETWORK_ACL_PRIVILEGES;
      
      Output:
      
      ACL||','||PRINCIPAL||','||PRIVILEGE||','||IS_GRANT  
      /sys/acls/test_acl_file.xml,TEST1,connect,true                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      Thanks in advance

      rogers42