This discussion is archived
2 Replies Latest reply: Feb 19, 2013 7:04 AM by MatthiasHoys RSS

ORA-24247 UTL_INADDR.get_host_address

MatthiasHoys Explorer
Currently Being Moderated
Hello,

I'm using Oracle XE 11g.

I'm trying to execute UTL_INADDR.get_host_address with another user than SYS, but this always returns the following error:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

I tried granting network access to the user with the following script, but it doesn't work... Any idea???
DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'MATTHIASH',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'MATTHIASH', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'MATTHIASH', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
Thanks,
Matthias
  • 1. Re: ORA-24247 UTL_INADDR.get_host_address
    Udo Guru
    Currently Being Moderated
    Hi Matthias,

    the block you've copied and adapted only grants the connect privilege to your user. For what you are up to, you'll also need resolve to make it work. Run something like
    BEGIN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl => 'power_users.xml',
        principal => 'MATTHIASH',
        is_grant => TRUE,
        privilege => 'resolve');
    END;
    /
    COMMIT;
    assuming your previous script created the "power_users.xml". change the ACL name accordingly.
    For more details, see the [url http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_networkacl_adm.htm]API Doc for DBMS_NETWORK_ACL_ADMIN.

    -Udo
  • 2. Re: ORA-24247 UTL_INADDR.get_host_address
    MatthiasHoys Explorer
    Currently Being Moderated
    Ok, this worked fine!

    Thanks!
    Matthias

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points