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; /
- The second method for resolving the ORA-24247 error is to grant the user requesting network access the XDBADMIN role.
SQL> grant XDBADMIN to Scott;
This will grant an extra privilege to the Oracle user and is not recommended.