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
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
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
——————————————————————————–
localhost.bekijkhet.com
SQL>