Resolving the ORA-24247: network access denied by ACL error

Before Oracle 11g access to network services was controlled by granting privileges on packages such as UTL_HTTP, UTL_TCP, UTL_SMTP, and UTL_MAIL. After 11.1 Oracle introduced Application Control Lists (ACL) as part of their Application Security and has now added Application Control Entry (ACE).

If you run into the ORA-24247: network access denied by access control list (ACL) error you can use one of the following methods to resolve the error.

The best solution to the ORA-24247 error is to create an ACE using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to grant access control privileges to a user. According to Oracle documentation, the procedure will append an access control entry with specified privilege to the ACL for the given host. If the ACL does not exist it will create it for you. The syntax for the procedure is listed below.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
  host         => 'host_name', 
  lower_port   => null|port_number,
  upper_port   => null|port_number,
  ace          => ace_definition); 
END;

host: can be either the ip address or the hostname. You can also use a wildcard for a domain or an IP subnet.
host => ‘mailhost.com’
host => ‘*.domain.com’

lower_port and upper_port: these values are used to set the lower and upper port range. This is only used for the connect privilege and can be omitted for the resolve privilege. If set to null then there are no port restrictions.
lower_port => 80
upper_port => 3999

ace: You define the ACE by using the XS$ACE_TYPE constant with the following specifications.
- privilege_list: this can be one or more of the following, http, http_proxy, smtp, resolve, connect, jdwp. Enclose each privilege with single quotes and separate each with a comma.
- principal_name: enter either a database user or role.
- principal_type: enter xs_acl_ptype_db for a database user or role.

In this example, the user Scott is being granted network access to send SMTP to a host, mailhost.com, through the UTL_SMTP and UTL_MAIL packages.

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
HOST => 'mailhost.com',
LOWER_PORT => NULL,
UPPER_PORT => NULL,
ACE => XS$ACE_TYPE(PRIVILEGE_LIST => xs$name_list('smtp'),
    			     PRINCIPAL_NAME => 'Scott',
    		     PRINCIPAL_TYPE => xs_acl.ptype_db
   		   )
);
END;
/