Error
likes "*Cause: The UTL_HTTP package failed to execute the HTTP
request" are very common with Oracle 11g and older versions of Oracle
database. The problem is that PL/SQL packages: UTL_TCP, UTL_HTTP,
UTL_SMTP, UTL_MAIL, and UTL_INADDR cannot be executed by the current
user.
Applications rely on database schemas to
execute functions. For example, using internet maps to determine location in an
application requires an external connection to another webpage. This is why the
invoker of those packages needs additional privileges to connect to an external
host or to resolve the name or the IP address of a host.
This is how the process works: The
packages check the invoker for the necessary privileges only when the calls are
made at runtime and raises an exception if the invoker lacks the
privileges.
In Oracle 11.2.0.3.6, this problem was
solved. Prior releases of Oracle requires XML DB to be installed and Configured
a network Access Control Lists (ACLs) in the database before these packages can
work.
What is an ACL? An ACL is a network
access control list that enables a DBA control access to external connection
through the host. Using an ACL is common in production databases when issues
with Network Access like ORA-24247 arises.
Linux 5.4
Oracle 11.2.0.3.0 (Production)
Cause
Privilege to UTL_http should have been granted to specific user or schema that will be using the external connection. Though this can be given through patches/updates, it is highly recommended to reduce host vulnerability to manually assign privilege through ACL.
If schema has access to objects after Oracle patch was installed, this is/could be a problem when installed on the production server because accounts that should not have access to this and other objects will have access to things they are not supposed to.
Solution
Verify that the Port is open
You need to verify that your port is open and can connect externally and also download webpages. Use these commands:
login as user "Oracle" (OS environment)
wget http://maps.google.com/
or
wget -r -l 0 http://maps.google.com/
If you get a response, then your port is open and ready for connection.
Create the user requiring access
Run these commands to create the users needed:
create tablespace patients
datafile '/home/oracle/app/oracle/oradata/patients/datafile/patients01.dbf'
size 50m reuse autoextend on maxsize unlimited;
create smallfile temporary tablespace patientstemp
tempfile '/home/oracle/app/oracle/oradata/patients/datafile/patientstemp01.dbf'
size 5m autoextend on next 2048k maxsize 1024m extent management local uniform
size 1m;
create user patients profile "default"
identified by "patients"
default tablespace patients
temporary tablespace patientstemp
quota unlimited on patients account unlock;
default tablespace patients
temporary tablespace patientstemp
quota unlimited on patients account unlock;
grant create session to patients;
grant connect to patients;
grant create trigger to patients;
grant create view to patients;
grant create procedure to patients;
grant create sequence to patients;
grant create table to patients;
grant create synonym to patients;
grant connect to patients;
grant create trigger to patients;
grant create view to patients;
grant create procedure to patients;
grant create sequence to patients;
grant create table to patients;
grant create synonym to patients;
Run script to create ACL
The script will first drop any existing ACL before creating a new one. This is necessary to reduce or eliminate conflicts.
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'utl_http.xml');
end;
/
commit;
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'utl_http.xml',
description => 'geo code ACL',
principal => 'PATIENTS',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'utl_http.xml',
principal => 'PATIENTS',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'utl_http.xml',
host => 'your_ip_address');
END;
/
COMMIT;
Grant access to user
as user "SYS" run this command
grant execute on utl_http to PATIENTS;
grant execute on DBMS_NETWORK_ACL_ADMIN to PATIENTS;
Assign ACL to networks (in this order)
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'utl_http.xml', host => 'maps.google.com');
END;
/
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'utl_http.xml', host => 'local.yahooapis.com');
END;
/
Verify the ACL
SELECT acl from dba_network_acl_privileges;
/sys/acls/utl_http.xml
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
Desc dba_network_acls;
Name Null? Type
----------------------------------------- -------- ----------------------------
HOST NOT NULL VARCHAR2(1000)
LOWER_PORT NUMBER(5)
UPPER_PORT NUMBER(5)
ACL VARCHAR2(4000)
ACLID NOT NULL RAW(16)
Verify permission
Run this script as user "SYS" to verify if the permission to connect externally has been granted to the user - patients:
SELECT acl, principal, privilege, is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date FROM dba_network_acl_privileges;
SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('/sys/acls/utl_http.xml', 'PATIENTS', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) privilege
4 from dual;
PRIVILE
-------
GRANTED
Check that it can connect
Run this script as user "SYS" to verify if the permission to connect externally has been granted to the user - patients:
SQL> DECLARE
2 l_url VARCHAR2(50) := 'http://maps.google.com';
3 l_http_request UTL_HTTP.req;
4 l_http_response UTL_HTTP.resp;
5 BEGIN
6 l_http_request := UTL_HTTP.begin_request(l_url);
7 l_http_response := UTL_HTTP.get_response(l_http_request);
8 UTL_HTTP.end_response(l_http_response);
9 END;
10 /
I hope this helps. Please drop your comments or/and questions.
Pix credit: Oracle, NetworkDigest
