Thursday, November 12, 2009

DNS resolve from Oracle Database 11g

Thursday, November 12, 2009 Posted by Andre Broers
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:
1.create or replace function resolve (ip varchar2) return varchar2 as
2.begin
3.return utl_inaddr.get_host_name(ip);
4.exception
5.when others then
6.return '-';
7.end;
8. 
In Oracle 10g this works out of the box. In 11g I get the following error:
SQL> select  utl_inaddr.get_host_name(‘127.0.0.1′) from dual;
select  utl_inaddr.get_host_name(‘127.0.0.1′) 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
SQL>
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(‘127.0.0.1′) from dual;
UTL_INADDR.GET_HOST_NAME(‘127.0.0.1′)
——————————————————————————–
localhost.bekijkhet.com
SQL>