Error executing UTL_HTTP UTL_INADDR . Configuring Network ACL's
Oracle Database 11g Release 2 (11.2) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XMLDB. If you have applications that use one of these packages, you must install OracleXML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases. Actions are discussed in Post Upgrade tasks (Step 36), as the DBMS_NETWORK_ACL_ADMIN package is introduced after upgrading the database and not available in prior releases.
Configure Fine-Grained Access to External Network Services.
To avoid "ORA-24247: network access denied by access control list (ACL)" when executing UTL packages (Network related packages), access has to be granted to user using these packages.
The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.
DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'principal','privilege') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'principal', is_grant, 'privilege');
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml','ACL description', 'principal', is_grant, 'privilege');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');
END;
COMMIT;
acl_name.xml => Enter a name for the access control list XML file.
ACL description => 'file description',
principal => 'user_or_role',
is_grant => TRUE|FALSE,
privilege => 'connect|resolve',
host_name => host name
Refer to the below note on how to use DBMS_NETWORK_ACL_ADMIN package and also to avoid ORA-24247 : network access denied by access control list (ACL).
Note 453786.1 ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages
|