part of Oracle Security Implementation is that you can’t only grant an oracle user direct execution privilege on packages such as (UTL_TCP , UTL_SMTP, UTL_MAIL , UTL_HTTP). An access control mechanism is implemented as a second tier security.

 

if you receive the following error, then you need to configure the ACL:

ERROR MESSAGE: ORA-24247: network access denied by access control list (ACL)

*** SOLUTION:

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

    acl => ‘TEST_ACL.xml’,

    description => ‘This Access is used for TEST Application user external mailing services’,

    principal => ‘TEST’,

    is_grant => true,

    privilege => ‘connect’);

COMMIT;

END;

/

**// where TEST_ACL: is the name of the ACL XML file

TEST: is the oracle user //**

 

 

BEGIN

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

    acl => ‘TEST_ACL.xml’,

    principal => ‘TEST’,

    is_grant => true,

    privilege => ‘resolve’);

COMMIT;

END;

/

 

 

BEGIN

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

    acl => ‘TEST_ACL.xml’,

    host => ‘*’);

 

   COMMIT;

END;

/

 SQL statement for verification:

select host, lower_port, upper_port, acl from dba_network_acls;

 if you want to drop the ACL:

begin

  dbms_network_acl_admin.drop_acl(

    ‘TEST_ACL.xml’

  );

end;