I wrote in my previous post about the Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g. However, it did not cover another PL/SQL network utility package named UTL_INADDR which retrieves host names and IP addresses of local and remote hosts.
You can read some usage samples of the UTL_INADDR from Eddie Awad’s blog.
Similar to those UTL_ packages, in 11g, you will be required to configure the access control list in order to use the UTL_INADDR. Otherwise, by default, you will receive errors as follows:
TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual;
SELECT utl_inaddr.get_host_name FROM dual
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1
Two simple steps to configure are:
1. Create an access control list and its privilege definition.
SQL> connect / as sysdba
begin
dbms_network_acl_admin.create_acl (
acl => 'Resolve_Access.xml', -- Name of the access control list XML file
description => 'Resolve Network Access using UTL_INADDR', -- Brief description
principal => 'TEST_USER', -- First user account or role being granted or denied permission
-- this is case sensitive,
-- but typically user names and roles are stored in upper-case letters
is_grant => TRUE, -- TRUE = granted, FALSE = denied
privilege => 'resolve', -- connect or resolve, this setting is case sensitive,
-- so always enter it in lowercase
-- connect if user uses the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL
-- resolve if user uses the UTL_INADDR
start_date => null, -- optional, null is the default
-- in format of timestamp_with_time_zone (YYYY-MM-DD HH:MI:SS.FF TZR)
-- for example, '2008-02-28 06:30:00.00 US/Pacific'
end_date => null -- optional, null is the default
);
commit;
end;
/
Note that the privilege used for UTL_INADDR is resolve in lowecase.
You can add more users or roles using DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.
To verify a newly-created ACL.
SQL> SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/Resolve%.xml';
ANY_PATH
--------------------------------------------------------------------------------
/sys/acls/Resolve_Access.xml
2. Assign the the access control list to one or more network hosts.
begin
dbms_network_acl_admin.assign_acl (
acl => 'Resolve_Access.xml', -- Name of the access control list XML file to be modified
host => '*', -- Network host to which this access control list will be assigned
-- This a host name or IP address or wild card name
lower_port => null, -- (optional)
upper_port => null); -- (optional)
commit;
end;
/
TEST_USER @DB11> SELECT utl_inaddr.get_host_name FROM dual;
GET_HOST_NAME
--------------------------------------------------------------------------------
hostname1
Reference: Oracle document on Managing Fine-Grained Access to External Network Services
Tags: 11g, ACL, Network Service, Utility, UTL_HTTP, UTL_INADDR, UTL_SMTP, UTL_TCP
