Access Network Services (UTL_INADDR,UTL_TCP,UTL_HTTP, UTL_SMTP, UTL_MAIL) in Oracle 11g ( ora 24247 network access denied)

From 11g the built-in packages which access the network resources e.g. UTL_HTTP, UTL_SMTP, UTL_MAIL etc. now requires an access control list to be used. If you see this warning that means there are some objects in your database which are using one of these packages. Once the upgrade is complete you need to configure an Access Control List for the users who are using the packages otherwise your applications will fail.
/*
  To see if there are any objects depending upon network packages like UTL_TCP ,
  UTL_SMTP etc.
*/
SELECT owner , name , type , referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
  AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');



Kindly run the below sqls in SYS. It is only for 11g.


Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','DPCDSL',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal =>  'DPCDSL',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_http_access.xml', '*',NULL,NULL);
Commit ;
               
Exec dbms_network_acl_admin.create_acl ('utl_inaddr_access.xml','Normal Access','DPCDSL',TRUE,'resolve',NULL, NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_inaddr_access.xml', principal =>  'DPCDSL',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_inaddr_access.xml', '*',NULL,NULL);
commit;

Exec dbms_network_acl_admin.create_acl ('utl_mail.xml','Allow mail to be send','DPCDSL',TRUE,'connect' );
Exec dbms_network_acl_admin.add_privilege ('utl_mail.xml','DPCDSL',TRUE,'resolve');
Exec dbms_network_acl_admin.assign_acl('utl_mail.xml','*',NULL,NULL);
commit ;


Exec dbms_network_acl_admin.create_acl ('utl_http.xml','HTTP Access','DPCDSL',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','DPCDSL',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_http.xml','*',NULL,NULL);
commit;

---------------------------
Exec dbms_network_acl_admin.create_acl ('utl_smtp.xml','SMTP Access','DPCDSL',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_smtp.xml','DPCDSL',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_smtp.xml','*',NULL,NULL);
commit;

In addition to the above, install the oracle mail scripts to instal UTL_MAIL packages to enable sending emails form inside of database

SQL>@$ORACLE_HOME/rdbms/admin/utlmail.sql

SQL>@$ORACLE_HOME/rdbms/admin/prvtmail.plb

SQL>ALTER SYSTEM SET smtp_out_server='smtp.oracle.com' scope=BOTH;



Grant Execute on utl_inaddr to DPCDSL ;
Grant Execute on utl_http to DPCDSL ;


SELECT global_name,utl_inaddr.get_host_address FROM global_name;

SELECT UTL_INADDR.get_host_address ('www.oracle.com') FROM DUAL;

SELECT UTL_HTTP.request ('http://www.oracle.com') FROM DUAL;


---------------------------------Removing ACL and priviliges
Unassign ACL
begin
  dbms_network_acl_admin.unassign_acl(
    acl        => 'utl_http.xml',
    host       => '*',
    lower_port => 80,
    upper_port => 80
  );
end;

Delete Privilege

begin
  dbms_network_acl_admin.delete_privilege(
    'utl_http.xml', 'DPCDSL', NULL, 'connect'
  );
end;


Drop ACL

begin
  dbms_network_acl_admin.drop_acl(
    'utl_http.xml'
  );
end;


------------------------------testing----------------------
create or replace procedure getTitle(pUrl VARCHAR2)
is
  vResult CLOB;
begin
  vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
  vResult := regexp_replace(vResult,'.*<title> ?(.+) ?</title>.*','\1',1,1,'i');
  dbms_output.put_line(vResult);
end;
/

/*
  This is just a dummy procedure and will only display
  the title if the title tag is defined in the first 2000
  characters in web page.
*/

set serveroutput on
execute getTitle('http://www.oracle.com');




create or replace procedure getTitle(pUrl VARCHAR2)
is
  vResult CLOB;
begin
  vResult := replace(UTL_HTTP.REQUEST(pUrl),chr(10),' ');
  vResult := regexp_replace(vResult,'.*.*','\1',1,1,'i');
  dbms_output.put_line(vResult);
end;
/

set serveroutput on
execute getTitle('http://www.oracle.com');