Wednesday, 13 October 2010

Access Control List (ACL) Problems when using the UTL_TCP Package for FTP in Oracle 11g

I am sure you have read this. But pay attention that it is address to:
- particular IP address or host name
- particular Oracle user

First, create an ACL:

        dbms_network_acl_admin.create_acl (
                acl             => 'utlpkg.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null

Here the parameter principal => 'CONNECT' indicates that the ACL applies to the CONNECT role. You can define a user or a role here. The ACL is created as a file called utlpkg.xml. 
After the creation, you can check to make sure the ACL was added:

SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';

The output is: 

Note the last line in the output, which shows the ACL you just created. Next, add a privilege to this ACL. In this example, you are trying to limit this ACL to the user SCOTT. You can also define start and end dates. 
  dbms_network_acl_admin.add_privilege ( 
  acl             => 'utlpkg.xml',
  principal       => 'SCOTT',
  is_grant       => TRUE, 
  privilege       => 'connect', 
  start_date       => null, 
  end_date       => null); 

Assign hosts and other details that will be subject to this ACL: 
  dbms_network_acl_admin.assign_acl (
  acl => 'utlpkg.xml',
  host => '',
  lower_port => 22,
  upper_port => 55);

In this example, you are specifying that "the user SCOTT can call only the host and only for the ports 22 through 55, and not outside it." Now, let's try it: 
SQL> grant execute on utl_http to scott
  2  /
Grant succeeded.
SQL> conn scott/tiger
SQL> select utl_http.request('') from dual;
select utl_http.request('') from dual
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Note the error "ORA-24247: network access denied by access control list (ACL)." The user called the http server on port 80, which is outside the allowed range 22-55. Therefore the action was prevented. 
Now, add another rule to allow the communication:

  1  begin
  2    dbms_network_acl_admin.assign_acl (
  3    acl => 'utlpkg.xml',
  4    host => '',
  5    lower_port => 1,
  6    upper_port => 10000);
  7* end;
  8  /
PL/SQL procedure successfully completed.
SQL> conn scott/tiger
SQL> select utl_http.request('') from dual;
</iframe><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>Proligence Home</TITLE>
<META http-equiv=Content-Language content=en-us>

No comments: