Resolve hostname from Oracle RDBMS

Env: 12.1.0.2

Quite often i ‘am asked the question on how to resolve hostname from oracle rdbms. Some ask to grant them the necessary privilege so that they could run a job using dbms_scheduler at OS level to resolve hostname. Well, the answer is ou may use UTL_INADDR.get_host_address().

If you are not a super user, you may get ACL error, for which you need to grant ‘resolve’ to the user.

‘ORA-24247: network access denied by access control list (ACL)’

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*',
    ace        => xs$ace_type(privilege_list => xs$name_list('resolve'),
                              principal_name => 'PROD',
                              principal_type => xs_acl.ptype_db)); 
END;
/
set pages 1000 lines 200
col principal format a40
col HOST format a50
col privilege format a40

SYS: > select PRINCIPAL,host,lower_port,upper_port,privilege from dba_host_aces where principal='PROD';

PRINCIPAL          HOST                  LOWER_PORT UPPER_PORT PRIVILEGE
------------------ --------------------- ---------- ---------- ------------------
CMC_PROD           *                                           RESOLVE
select UTL_INADDR.get_host_address ('anything.com') from dual;
UTL_INADDR.GET_HOST_ADDRESS('ANYTHING.COM')
-------------------------------------------------------------------
23.236.62.147

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s