How to Allow Network Access with DBMS_NETWORK_ACL_ADMIN

PL/SQL network packages such as UTL_HTTP, UTL_TCP, UTL_SMTP, and UTL_INADDR cannot reach any host until a DBA allows it. Access control entries, added with DBMS_NETWORK_ACL_ADMIN, name the user, the host, the ports, and the kind of access. Without one, calls fail with ORA-24247.

Code for This Guide

The main examples are in the examples/pkg-files-network folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_network_acl_admin.append_host_ace(
  host       => 'host name, IP address, or wildcard',
  lower_port => null, upper_port => null,      -- null: all ports
  ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                            principal_name => 'USER',
                            principal_type => xs_acl.ptype_db));

The privileges are http for UTL_HTTP, connect for UTL_TCP, UTL_SMTP, and UTL_MAIL, and resolve for name lookups with UTL_INADDR.

Grant Access to NIMBUS

Run this as a DBA, such as SYS, in the pluggable database. It allows HTTP and plain connections to a local test server on ports 8099 and 2525, HTTP to example.com, and name lookups for every host.

Example:

begin
  -- HTTP to the test server, and to example.com over HTTPS
  dbms_network_acl_admin.append_host_ace(
    host => 'host.docker.internal', lower_port => 8099, upper_port => 8099,
    ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => 'NIMBUS', principal_type => xs_acl.ptype_db));
  dbms_network_acl_admin.append_host_ace(
    host => 'example.com',
    ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                        principal_name => 'NIMBUS', principal_type => xs_acl.ptype_db));
  -- plain TCP connections (UTL_TCP, UTL_SMTP, UTL_MAIL) to the test server's ports
  dbms_network_acl_admin.append_host_ace(
    host => 'host.docker.internal', lower_port => 2525, upper_port => 2525,
    ace  => xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => 'NIMBUS', principal_type => xs_acl.ptype_db));
  dbms_network_acl_admin.append_host_ace(
    host => 'host.docker.internal', lower_port => 8099, upper_port => 8099,
    ace  => xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => 'NIMBUS', principal_type => xs_acl.ptype_db));
  -- name lookups (UTL_INADDR) for any host
  dbms_network_acl_admin.append_host_ace(
    host => '*',
    ace  => xs$ace_type(privilege_list => xs$name_list('resolve'),
                        principal_name => 'NIMBUS', principal_type => xs_acl.ptype_db));
end;
/
select host, lower_port as lport, upper_port as uport, privilege
from   dba_host_aces
where  principal = 'NIMBUS'
order  by host, privilege;

Output:

PL/SQL procedure successfully completed.

HOST                       LPORT    UPORT PRIVILEGE
_______________________ ________ ________ ____________
*                                         RESOLVE
example.com                               HTTP
host.docker.internal        8099     8099 CONNECT
host.docker.internal        2525     2525 CONNECT
host.docker.internal        8099     8099 HTTP

DBA_HOST_ACES lists the five entries. The test server is the small Node.js program setup/netlab/server.mjs in the repository, which the database container reaches as host.docker.internal.

Check Access as the User

Users see their own entries in USER_HOST_ACES. DBMS_NETWORK_ACL_UTILITY.DOMAINS lists every host pattern that would match a host name, from the most specific to *.

Example:

select host, lower_port, upper_port, privilege, status from user_host_aces order by 1, 4;

select column_value as matching_acl_hosts
from   dbms_network_acl_utility.domains('api.flights.example.com');

Output:

HOST                       LOWER_PORT    UPPER_PORT PRIVILEGE    STATUS
_______________________ _____________ _____________ ____________ __________
*                                                   RESOLVE      GRANTED
example.com                                         HTTP         GRANTED
host.docker.internal             8099          8099 CONNECT      GRANTED
host.docker.internal             2525          2525 CONNECT      GRANTED
host.docker.internal             8099          8099 HTTP         GRANTED

MATCHING_ACL_HOSTS
__________________________
api.flights.example.com
*.flights.example.com
*.example.com
*.com
*

Access Denied

Example:

-- port 8100 has no access control entry for NIMBUS
select utl_http.request('http://host.docker.internal:8100/') from dual;

Output:

Error starting at line : 2
In command -
select utl_http.request('http://host.docker.internal:8100/') from dual
Error at Command Line : 2 Column : 8
Error report -
SQL Error: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1594
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1534
ORA-06512: at line 1

Port 8100 is not in any entry for NIMBUS, so the request fails with ORA-24247 inside ORA-29273.

Things to Know

• Grant the narrowest access that works: one host, the ports needed, and only the privileges used.
• The most specific matching host entry decides, so an entry for api.example.com takes precedence over *.example.com.
• REMOVE_HOST_ACE takes an entry away again.

Related Guides

• How to Read a Text File Line by Line with UTL_FILE.GET_LINE
• How to Use DB_DEVELOPER_ROLE in Oracle

Conclusion

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE opens specific hosts and ports to specific users for HTTP, connections, or name lookups. Grant as little as possible, and check what a user can reach in USER_HOST_ACES.