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
Great article! Very concise.
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!
Thank you! I constantly needed to write on my website something like that. Can I take a fragment of your post to my site?
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.?
Not sure it is granted by default. You can, however, verify the granted acl by querying this dba_network_acl_privileges.