OraExplorer

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

Related topics:

• The Access Control Lists to Network Services (e.g., UTL_HTTP, UTL_SMTP, UTL_TCP, etc.) in Oracle 11g
• 11gR2 New Feature – Alter Database Link to Change Password

Tags: 11g, ACL, Network Service, Utility, UTL_HTTP, UTL_INADDR, UTL_SMTP, UTL_TCP