Oracle 11g: Access Control List and ORA-24247

From a more DBA point of view, I would like to go in more detail in response to Marcel’s blog about APEX web service references and ACL.

With ACL’s, Oracle offers more fine-grained access control for users to access external network resources.

The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed. This can be interpreted as a security flaw as no login is required when using UTL_TCP for example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.

Since Oracle 11g, the Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.
For instance, when a user is granted to send emails using UTL_MAIL, you can also control that he/she is only able to send through a specified mail server.
At first this looks like a obstacle (ORA-24247), but since the Voyager worm struck Oracle databases a year ago, it is introduced as an extra security measurement.

I will use the UTL_MAIL package as an example, please scroll to the end of this blog to enable UTL_MAIL as it is disabled by default.

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

If a user is not allowed to connect to a specific server due to ACL restrictions, the following message will appear: ORA-24247: network access denied by access control list (ACL).

I will walk through the solution by using UTL_MAIL as an example; try to execute the following as SCOTT:

SQL> connect scott/tiger
Connected.

begin   utl_mail.send(   sender     => 'scott@tiger.com',   recipients => 'john@doe.org',   message    => 'Hello World'   ); end;

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 SCOTT does not have the privilege to access the mail/smtp server. So it must be added to the ACL.

ACL: Access Control List

The ACL is created as a file and it’s file name is used as the key in the process of adding and removing privileges.

Create ACL and privileges

Now first create an ACL as SYS (or any other user with DBMS_NETWORK_ACL_ADMIN execute granted), this will hold the privileges. You can add as many privileges as you like to this file, but I would recommend to split privileges in the ACL to specific tasks or users. You must create an ACL with at least one privilege, so lets start with the ‘connect’ privilege for user SCOTT, (also a role can be added as principal):

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

Great, now that the ACL is created, you can add more privileges like the ‘resolve’ privilege:

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

Assign ACL

Cool, you granted SCOTT to connect and resolve, but you have not defined to which resources he is allowed to connect:

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

Try again

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.

Access to websites and ports

The ACL also allows you to control begin and end ports, begin and end dates.

Run as SCOTT:

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 assign the ACL with ports

Run 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;

The hosts parameter in dbms_network_acl_admin.assign_acl, can also contain wild cards like ‘*.tiger.com’ or even ‘*’.

Try again

Run as SCOTT:

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

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

Now try to access the same URL, but with another port. You will see this fails, because only port 80 is privileged.

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

You can specify lower and upper ports like a range when assigning ACL’s.

dba_network_acls

You can view ACL’s and privileges by querying dba_network_acls.

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

Removing ACL and priviliges

Of course , removing ACL’s and privileges is also possible and  self explainable.

Run the following as SYS:

Unassign ACL

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

Delete Privilege

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;

Enabling UTL_MAIL

In these examples I use UTL_MAIL, this package is disabled by default, run the following statements as SYS to enable it:

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