2 Replies Latest reply: Feb 19, 2013 9:04 AM by MatthiasHoys RSS

    ORA-24247 UTL_INADDR.get_host_address

    MatthiasHoys
      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
          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
            Ok, this worked fine!

            Thanks!
            Matthias