Oracle 11g – How to Add Users to the HTTP ACL

indexmonitoringoracleoracle-11g-r2

How do I add a user to the network ACL (access control list)?

When running this code

declare
    req utl_http.req;
begin
    req := utl_http.begin_request('http://example.com');
end;

I receive this error.

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

Best Answer

First, see if you have an ACL for port 80, the HTTP port. If you do, it should look something like this.

select * from dba_network_acls;

HOST     LOWER_PORT UPPER_PORT ACL                     ACLID                                                                                                                                                                                                
---      ---------- ---------- ---                     -----
*        80         80         /sys/acls/www.xml       1FBEC09C32D78F5AE05335D6488A7883

If you need to recreate it, you can drop it with this command:

dbms_network_acl_admin.drop_acl('www.xml');

Create the ACL:

 dbms_network_acl_admin.create_acl(
     acl => 'www.xml',
     description => 'WWW ACL',
     principal => 'SCOTT',
     is_grant => true,
     privilege => 'connect'
 );

Assign hosts and ports to the ACL:

 dbms_network_acl_admin.assign_acl(
     acl => 'www.xml',
     host => '*',
     lower_port => 80
 );

Add users to the ACL:

 dbms_network_acl_admin.add_privilege(
     acl => 'www.xml',
     principal => 'OE',
     is_grant => true,
     privilege => 'connect'
 );

version note: kevinsky points out that this is applicable for Oracle 11 but that it has changed in Oracle 12.

Related Question