How to Access Control Lists (ACL) in Oracle

What is Access Control Lists(ACL) in Oracle?

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduced fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants. Oracle provide the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages to allow ACL management from PL/SQL.

Oracle Database 12c has deprecated many of the procedures and functions in the DBMS_NETWORK_ACL_ADMIN package, replacing them with new procedures and functions. We still have the concept of Access Control Lists (ACLs), but these are often created implicitly when adding an Access Control Entry (ACE), which is similar to adding privileges using the previous API. The biggest change is an Access Control Entry can be limited to specific PL/SQL APIs (UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL).

How to see Network Access Level on your database is granted.

set linesize 200
set pagesize 200
col host format a30
col ACL format a50
col acl_owner format a10
select * from dba_network_acls;

set linesize 200
set pagesize 200
col host format a30
col start_date format All
col end_date format All
col principal format a25
col privilege format a10

select host,lower_port,upper_port,ace_order,
to_char(start_date,’DD-MON-YYYY’) AS start_date, to_char(end_date,’DD-MON-YYYY’) AS end_date,
grant_type,inverted_principal,principal,principal_type,
privilege
from dba_host_aces
order by bosts,ace_order;

How to ORA-20000: Failed to send email: Error Code-24247: ORA-24247 : network access denied by access control list(ACL)

The above is failed for Network Access Level for Sending Email. For email is port 25. You can grant access on to the particular host/domain to grant for port 25 as below

$ sqlplus / as sysdba

BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => ‘hostname or domain name’,
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(privilege_list => xs$name_list(‘smtp’),
principal_name => ‘test1’,
principal_type => xs_acl.ptype_db));
END;
/

Note: From a security standpoint, it’s not a good idea to allow complete network access from the database,

privilege_list : The list of privileges available to the ACE.
principal_name : The database user the ACE applies to.
principal_type : You will always use XS_ACL.PTYPE_DB for these network ACEs as they apply to users and roles.

The privilege_list specifies one or more privileges in a comma separated list. The available privileges are shown below.

http : Access restricted to the UTL_HTTP package and the HttpUriType type.
http_proxy : Needed in conjunction with http if HTTP access is via a proxy.
smtp : Access restricted to the UTL_SMTP and UTL_MAIL packages.
resolve : Access restricted to the UTL_INADDR packages.
connect : Opens access to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and DBMS_LDAP packages and the HttpUriType type.
jdwp : Enables Java Debug Wire Protocol debugging operations.