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.
Thank you for the solution
good article. It help me to resolve the error. thanks
Hi I am getting below error while calling https URL
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1530
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 1470
ORA-06512: at line 1
29273. 00000 – “HTTP request failed”
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1530
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 1470
ORA-06512: at line 1
29273. 00000 – “HTTP request failed”
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
ORA-20001: The following error has occured: ORA-24247: network access denied by access control list (ACL)
ORA-00001: unique constraint (RECEDE.PK_TB_SUJETO) violated
ORA-02063: preceding line from DBL_YAXHA
ORA-06512: at “RECEDE.PKG_MIGRACION_CONSULTA”, line 32
ORA-06512: at “RECEDE.PKG_MIGRACION_CONSULTA”, line 341
ORA-06512: at “RECEDE.PKG_MIGRACION_CONSULTA”, line 947
ORA-06512: at line 7
Hola, paso a responder, que encontré otra manera de resolver este error:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => ‘http_access.xml’,
description => ‘HTTP Access’,
principal => ‘SCHEMA_NAME’, — Reemplaza ‘SCHEMA_NAME’ con el esquema del usuario que ejecuta el procedimiento
is_grant => TRUE,
privilege => ‘connect’
);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => ‘http_access.xml’,
principal => ‘SCHEMA_NAME’, — Reemplaza ‘SCHEMA_NAME’ con el esquema del usuario que ejecuta el procedimiento
is_grant => TRUE,
privilege => ‘resolve’
);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => ‘http_access.xml’,
host => ‘tu_url’, — El host de la URL
lower_port => 8100, — El puerto de la URL
upper_port => 8100 — El puerto de la URL
);
END;
COMMIT;
SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;