Oracle 11g Network Access Denied by Access Control List (ACL) when using UTL_INADDR

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:

 

, , , , , , ,

5 Responses to Oracle 11g Network Access Denied by Access Control List (ACL) when using UTL_INADDR

  1. Andrew May 18, 2010 at 1:33 pm #

    Great article! Very concise.

  2. Verla Fecher June 1, 2010 at 7:32 pm #

    Hi,this is Verla Fecher,just discovered your Post on google and i must say this blog is great.may I share some of the article found in your website to my local mates?i’m not sure and what you think?in either case,Thx!

  3. Generic Meds June 11, 2010 at 12:39 pm #

    Thank you! I constantly needed to write on my website something like that. Can I take a fragment of your post to my site?

  4. Francisco Villavicencio March 27, 2014 at 8:12 am #

    Hi, I run the sentence

    SELECT utl_inaddr.get_host_name FROM dual;

    without the previous Step 2. and I had the output

    GET_HOST_NAME
    ———————————————-
    localhost.localdomain

    That it means Oracle 11.2 g has by default build Step 2.?

    • ittichai April 18, 2014 at 10:20 pm #

      Not sure it is granted by default. You can, however, verify the granted acl by querying this dba_network_acl_privileges.

Leave a Reply to Andrew Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress. Designed by WooThemes