Friday, December 7, 2007

DNS resolve from Oracle Database 11g

Friday, December 07, 2007 Posted by Unknown , ,
For a a small home test datawarehouse I import all access.log data in a oracle database star shema. In 10g I used the following function to resolve the ip address to a hostname:

[sourcecode language="sql"]

create or replace function resolve (ip varchar2) return varchar2 as
  return utl_inaddr.get_host_name(ip);
when others then
  return '-';

In Oracle 10g this works out of the box. In 11g I get the following error:

SQL> 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


The solution is to create an acl for the user broersa with the privilege resolve granted. After this we have to assign the acl to all hosts.

connect / as sysdba

exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal => 'BROERSA', is_grant => true, privilege => 'resolve')
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host => '*');
connect broersa/xxxxxx

SQL>  select  utl_inaddr.get_host_name('') from dual;