Oracle 11g Acces Control List and ORA-24247

This manuel describes the solution ORA-24247: network access denied by access control list (ACL).

for the ACL’ss offers oracle more fine-grained access control for users who have access to external resources.
The packages UTL_MAIL , UTL_SMTP , UTL_HTTP , UTL_TCP etc. provide the ability to communicate to the outside world. When there are rights, data can be created from the database connection to all hosts. This can be seen as a security hole because there is no login is required . Since Oracle 11g , the Access Control List is introduced. You can not only control who has access to these packages, but you can also determine which resources they may comminceren .
For example, when a user is allowed to send emails through UTL_MAIL , you can also determine which server it should gebeuren.Ik UTL_MAIL will use the package as an example When a user does not have permission to email appears the error message:

ORA-24247: network access denied by access control list (ACL).

SQL> connect scott/tiger Connected. begin utl_mail.send( sender => 'scott@tiger.com' recipients => 'john@doe.org' message=> ' Hello World' );

1 2 3 4 5 6 7

SQL> connect scott/tiger Connected. begin   utl_mail.send(   sender => 'scott@tiger.com'   recipients => 'john@doe.org'   message=> ' Hello World'   );

ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “SYS.UTL_MAIL”, line 654 ORA-06512: at “SYS.UTL_MAIL”, line 671 ORA-06512: at line 1

This is because the user SCOTT has no rights to connect to the mail or SMTP servers. The user must be added to the ACL

Create ACL and privilege

The ACL is created as a file and manages the process of handing out rights and privileges. First we are going to create an ACL as SYS or another user with the right to execute DBMS_NETWORK_ACL_ADMIN . This file hold the rights to . You can add as many rights to this file.

begin   dbms_network_acl_admin.create_acl (     acl         => 'utl_mail.xml',     description => 'Allow mail to be send',     principal   => 'SCOTT',     is_grant    => TRUE,     privilege   => 'connect'     );     commit; end;

1 2 3 4 5 6 7 8 9 10

begin   dbms_network_acl_admin.create_acl (     acl         => 'utl_mail.xml',     description => 'Allow mail to be send',     principal   => 'SCOTT',     is_grant    => TRUE,     privilege   => 'connect'     );     commit; end;

Add Privilege

Now that the ACL is created you can add more as the privilege to ‘ resolve ‘ privilege .

begin   dbms_network_acl_admin.add_privilege (   acl       => 'utl_mail.xml',   principal => 'SCOTT',   is_grant  => TRUE,   privilege => 'resolve'   );   commit; end;

1 2 3 4 5 6 7 8 9

begin   dbms_network_acl_admin.add_privilege (   acl       => 'utl_mail.xml',   principal => 'SCOTT',   is_grant  => TRUE,   privilege => 'resolve'   );   commit; end;

Grant ACL

The user SCOTT is granted connect and resolve , but there is not yet indicated to what resources he can connect.

begin   dbms_network_acl_admin.assign_acl(   acl  => 'utl_mail.xml',   host => 'smtp server host name or address'   );   commit; end;

1 2 3 4 5 6 7

begin   dbms_network_acl_admin.assign_acl(   acl  => 'utl_mail.xml',   host => 'smtp server host name or address'   );   commit; end;

Test

SQL> connect scott/tiger Connected.

1 2	SQL> connect scott/tiger Connected.

begin   utl_mail.send(   sender     => 'scott@tiger.com',   recipients => 'john@doe.org',   message    => 'Hello World'   );   commit; end; PL/SQL procedure successfully completed.

1 2 3 4 5 6 7 8 9 10

begin   utl_mail.send(   sender     => 'scott@tiger.com',   recipients => 'john@doe.org',   message    => 'Hello World'   );   commit; end; PL/SQL procedure successfully completed.

Connection to websites and TCP ports

With the ACL , you can set up TCP ranges start and end dates.

SQL> select utl_http.request('http://www.tiger.com') from dual; select utl_http.request('http://www.tiger.com') from dual        * ERROR at line 1: 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

1 2 3 4 5 6 7 8

SQL> select utl_http.request('http://www.tiger.com') from dual; select utl_http.request('http://www.tiger.com') from dual        * ERROR at line 1: 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

Create ACL, add privileges and TCP ports

Excecute as SYS

begin   dbms_network_acl_admin.create_acl (     acl         => 'utl_http.xml',     description => 'HTTP Access',     principal   => 'SCOTT',     is_grant    => TRUE,     privilege   => 'connect',     start_date  => null,     end_date    => null   );   dbms_network_acl_admin.add_privilege (     acl        => 'utl_http.xml',     principal  => 'SCOTT',     is_grant   => TRUE,     privilege  => 'resolve',     start_date => null,     end_date   => null   );   dbms_network_acl_admin.assign_acl (     acl        => 'utl_http.xml',     host       => 'www.tiger.com',     lower_port => 80,     upper_port => 80   );   commit; end;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

begin   dbms_network_acl_admin.create_acl (     acl         => 'utl_http.xml',     description => 'HTTP Access',     principal   => 'SCOTT',     is_grant    => TRUE,     privilege   => 'connect',     start_date  => null,     end_date    => null   );   dbms_network_acl_admin.add_privilege (     acl        => 'utl_http.xml',     principal  => 'SCOTT',     is_grant   => TRUE,     privilege  => 'resolve',     start_date => null,     end_date   => null   );   dbms_network_acl_admin.assign_acl (     acl        => 'utl_http.xml',     host       => 'www.tiger.com',     lower_port => 80,     upper_port => 80   );   commit; end;

In the hosts parameter dbms_network_acl_admin.assign_acl, can you can al so add wild cards ‘*.tiger.com’ or even ‘*’.
Execute as SCOTT:

SQL> select utl_http.request('http://www.tiger.com') from dual;

1	SQL> select utl_http.request('http://www.tiger.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.TIGER.COM') ---------------------------------------- [result here]

1 2 3

UTL_HTTP.REQUEST('HTTP://WWW.TIGER.COM') ---------------------------------------- [result here]

Now try to connect to the same URL but with a different port .

SQL> select utl_http.request('http://www.tiger.com:1234') from dual; select utl_http.request('http://www.tiger.com:1234') from dual        * ERROR at line 1: 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

1 2 3 4 5 6 7 8

SQL> select utl_http.request('http://www.tiger.com:1234') from dual; select utl_http.request('http://www.tiger.com:1234') from dual        * ERROR at line 1: 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

dba_network_acls

You can by carrying out the following view the ACL and privileges by reacting a query loose on the dba_network_acls .

select host, lower_port, upper_port, acl   from dba_network_acls   where ACL='/sys/acls/'utl_http.xml';

1 2 3

select host, lower_port, upper_port, acl   from dba_network_acls   where ACL='/sys/acls/'utl_http.xml';

Remove ACL and priviliges

Execute asSYS:

Unassign ACL

begin   dbms_network_acl_admin.unassign_acl(     acl => 'utl_http.xml',     host => 'www.tiger.com',   lower_port => 80,   upper_port => 80 ); end;

1 2 3 4 5 6 7 8

begin   dbms_network_acl_admin.unassign_acl(     acl        => 'utl_http.xml',     host       => 'www.tiger.com',     lower_port => 80,     upper_port => 80   ); end;

Remove Privilege

begin   dbms_network_acl_admin.delete_privilege( 'utl_http.xml', 'SCOTT', NULL, 'connect' ); end;

1 2 3 4 5

begin   dbms_network_acl_admin.delete_privilege(     'utl_http.xml', 'SCOTT', NULL, 'connect'   ); end;

Drop ACL

begin   dbms_network_acl_admin.drop_acl( 'utl_http.xml' ); end;

1 2 3 4 5

begin   dbms_network_acl_admin.drop_acl(     'utl_http.xml'   ); end;

Activate UTL_MAIL

SQL> @?/rdbms/admin/utlmail.sql SQL> @?/rdbms/admin/prvtmail.plb SQL> alter system set smtp_out_server = '<smtp host>' scope=spfile; SQL> shutdown immediate SQL> startup

1 2 3 4 5

SQL> @?/rdbms/admin/utlmail.sql SQL> @?/rdbms/admin/prvtmail.plb SQL> alter system set smtp_out_server = '<smtp host>' scope=spfile; SQL> shutdown immediate SQL> startup

Please let me know if this manual ‘Oracle 11g Acces Control List and ORA-24247’ was usefull to you. If there are  errors or you have suggestions regarding this manual, please let me know. No rights can be derived from this manual