I recently upgraded Oracle XE from 10G to 11G, and found that none of the PL/SQL code using UTL_HTTP was working after upgrade.
The code failed with the error:
declare * ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 47
Oracle 10G used to be happy as long as the user running network packages like UTL_HTTP had execute permission on the package. Oracle 11G and above are not so easy to please (and rightly so!) — they enforce extra security, which means you need more access control configuration to get this working.
Hello ACL.
What is ACL?
Access Control List (ACL) is a fine-grained security mechanism introduced in Oracle 11G. It is used to define which users or roles can perform which operations, on which data.
In ACL terminology,
users or roles are called principals
operations are called privileges
An ACL consists of a list of ACEs i.e. Access Conrol Entries. An ACE grants or denies a privilege to a principal. The ACE does not, itself, specify the data (object/resource) on which the privilege works; the association of ACL with target data is done separately.
An ACL is in XML file format; each ACE is an XML element. The file is created in the /sys/acls directory by default.
For more on ACL and related access control concepts, see the Oracle documentation: Access Control Lists and Security Classes.
How to set up ACL for UTL_HTTP to work
Here’s what you need to do to configure ACL for UTL_HTTP:
Step 1. Create a new ACL (if required) or use an existing ACL to grant "connect" privilege to the user that needs to run UTL_HTTP.
Step 2. Assign the ACL of step 1 to the network host to be accessed using UTL_HTTP.
ACL for UTL_HTTP: Script Creation and Execution
Use Case:
In a PL/SQL script, UTL_HTTP is used to call a web service GetCitiesForCountry, which takes as input a country name and returns important cities of the county in the response.
The web service to be called is hosted at webservicex.com.
The script is run as database user HR.
ACL Script Approach:
The approach is to create a new ACL ‘utl_http.xml’ for principal user HR, and grant privilege ‘connect’ to it.
Then assign ACL ‘utl_http.xml’ to web service host ‘*.webservicex.com’.
Note: Using wildcard ‘*’ in the host name gives access to any sub-domain on the host. If you want to restrict to specific sub-domains only, put the exact name in the host instead of ‘*’.
Multiple hosts can be assigned to one ACL. e.g. Two separate assignments can be made to ‘dom1.webservicex.com’, ‘dom2.webservicex.com’.
ACL Script:
begin
-- Step 1. Create a new ACL utl_http.xml.
-- Grant privilege 'connect' to user 'HR'
-- using the ACL
dbms_network_acl_admin.create_acl (
acl => 'utl_http.xml',
description => 'HTTP Access',
principal => 'HR',
is_grant => TRUE,
privilege => 'connect'
);
-- Step 2. Assign the ACL created in Step 1
-- to the network host for the web service
dbms_network_acl_admin.assign_acl (
acl => 'utl_http.xml',
host => '*.webservicex.com'
);
commit;
end;
/
When run:
SQL> begin 2 -- Step 1. Create a new ACL utl_http.xml. 3 -- Grant privilege 'connect' to user 'HR' 4 -- using the ACL 5 6 dbms_network_acl_admin.create_acl ( 7 acl => 'utl_http.xml', 8 description => 'HTTP Access', 9 principal => 'HR', 10 is_grant => TRUE, 11 privilege => 'connect' 12 ); 13 14 -- Step 2. Assign the ACL created in Step 1 15 -- to the network host for the web service 16 17 dbms_network_acl_admin.assign_acl ( 18 acl => 'utl_http.xml', 19 host => '*.webservicex.com' 20 ); 21 commit; 22 end; 23 / PL/SQL procedure successfully completed.
ACL Done. Let’s try UTL_HTTP now…
After setting up ACL as sys, connect back as user HR and run the PL/SQL script which calls the web service.
UTL_HTTP Script:
declare
-- String that holds the HTTP request string
soap_request varchar2(32767);
-- PL/SQL record to represent an HTTP request,
-- as returned from the call to begin_request
http_req utl_http.req;
-- PL/SQL record to represent the output of
-- get_response
http_resp utl_http.resp;
-- HTTP version that can be used in begin_request: 1.0 or 1.1
t_http_version varchar2(10) := 'HTTP/1.1';
t_content_type varchar2(50) := 'text/xml; charset=utf-8';
-- URL of the HTTP request, set after begin_request.
t_url varchar2(100) := 'www.webservicex.com/globalweather.asmx';
-- Variables to handle the web service response
x_clob CLOB;
l_buffer VARCHAR2(32767);
-- US city ZIP for which the weather data has
-- to be fetched via web service
l_country_name varchar2(10) := 'Singapore';
begin
dbms_output.put_line('Country: ' || l_country_name );
-- Build the HTTP soap request
soap_request := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetCitiesByCountry xmlns="http://www.webserviceX.NET">
<CountryName>' || l_country_name || '</CountryName>
</GetCitiesByCountry>
</soap:Body>
</soap:Envelope>';
-- Begin a new HTTP request: establish the network
-- connection to the target web server or proxy
-- server and send the HTTP request line.
http_req := utl_http.begin_request(t_url
, 'POST' -- method
, t_http_version);
-- Set HTTP request header attributes. The
-- request header is sent to the web server
-- as soon as it is set.
utl_http.set_header(http_req
, 'Content-Type', t_content_type);
utl_http.set_header(http_req
, 'Content-Length'
, length(soap_request));
utl_http.set_header(http_req
, 'SOAPAction'
, '"http://www.webserviceX.NET/GetCitiesByCountry"');
-- Write soap request text data in the HTTP request
utl_http.write_text(http_req, soap_request);
-- get_response output of record type utl_http.resp.
-- http_resp contains a 3-digit status_code, a
-- reason_phrase and HTTP version.
http_resp := utl_http.get_response(http_req);
-- Build a CLOB variable to hold web service response
dbms_lob.createtemporary(x_clob, FALSE );
dbms_lob.open( x_clob, dbms_lob.lob_readwrite );
begin
loop
-- Copy the web service response body
-- in a buffer string variable l_buffer
utl_http.read_text(http_resp, l_buffer);
-- Append data from l_buffer to CLOB variable
dbms_lob.writeappend(x_clob
, length(l_buffer)
, l_buffer);
end loop;
exception
when others then
-- Exit loop without exception
-- when end-of-body is reached
if sqlcode <> -29266 then
raise;
end if;
end;
-- Verify the response status and text
dbms_output.put_line('Response Status: '
||http_resp.status_code
||' ' || http_resp.reason_phrase);
dbms_output.put_line('Response XML:'
|| replace(replace(cast(x_clob as varchar2), '>', '>'), '<', '<'));
utl_http.end_response(http_resp) ;
-- x_clob response can now be used for extracting text
-- values from specific XML nodes, using XMLExtract
end;
/
When run:
SQL> declare
2 -- String that holds the HTTP request string
3 soap_request varchar2(32767);
4
5 -- PL/SQL record to represent an HTTP request,
6 -- as returned from the call to begin_request
7 http_req utl_http.req;
8
9 -- PL/SQL record to represent the output of
10 -- get_response
11 http_resp utl_http.resp;
12
13 -- HTTP version that can be used in begin_request: 1.0 or 1.1
14 t_http_version varchar2(10) := 'HTTP/1.1';
15
16 t_content_type varchar2(50) := 'text/xml; charset=utf-8';
17
18 -- URL of the HTTP request, set after begin_request.
19 t_url varchar2(100) := 'www.webservicex.com/globalweather.asmx';
20
21 -- Variables to handle the web service response
22 x_clob CLOB;
23 l_buffer VARCHAR2(32767);
24
25 -- US city ZIP for which the weather data has
26 -- to be fetched via web service
27 l_country_name varchar2(10) := 'Singapore';
28
29 begin
30
31 dbms_output.put_line('Country: ' || l_country_name );
32
33 -- Build the HTTP soap request
34 soap_request := '<?xml version="1.0" encoding="utf-8"?>
35 <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
36 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
37 <soap:Body>
38 <GetCitiesByCountry xmlns="http://www.webserviceX.NET">
39 <CountryName>' || l_country_name || '</CountryName>
40 </GetCitiesByCountry>
41 </soap:Body>
42 </soap:Envelope>';
43
44 -- Begin a new HTTP request: establish the network
45 -- connection to the target web server or proxy
46 -- server and send the HTTP request line.
47 http_req := utl_http.begin_request(t_url
48 , 'POST' -- method
49 , t_http_version);
50
51 -- Set HTTP request header attributes. The
52 -- request header is sent to the web server
53 -- as soon as it is set.
54 utl_http.set_header(http_req
55 , 'Content-Type', t_content_type);
56 utl_http.set_header(http_req
57 , 'Content-Length'
58 , length(soap_request));
59 utl_http.set_header(http_req
60 , 'SOAPAction'
61 , '"http://www.webserviceX.NET/GetCitiesByCountry"');
62
63 -- Write soap request text data in the HTTP request
64 utl_http.write_text(http_req, soap_request);
65
66 -- get_response output of record type utl_http.resp.
67 -- http_resp contains a 3-digit status_code, a
68 -- reason_phrase and HTTP version.
69 http_resp := utl_http.get_response(http_req);
70
71 -- Build a CLOB variable to hold web service response
72 dbms_lob.createtemporary(x_clob, FALSE );
73 dbms_lob.open( x_clob, dbms_lob.lob_readwrite );
74
75 begin
76
77 loop
78 -- Copy the web service response body
79 -- in a buffer string variable l_buffer
80 utl_http.read_text(http_resp, l_buffer);
81
82 -- Append data from l_buffer to CLOB variable
83 dbms_lob.writeappend(x_clob
84 , length(l_buffer)
85 , l_buffer);
86 end loop;
87
88 exception
89 when others then
90 -- Exit loop without exception
91 -- when end-of-body is reached
92 if sqlcode <> -29266 then
93 raise;
94 end if;
95 end;
96
97 -- Verify the response status and text
98 dbms_output.put_line('Response Status: '
99 ||http_resp.status_code
100 ||' ' || http_resp.reason_phrase);
101 dbms_output.put_line('Response XML:'
102 || replace(replace(cast(x_clob as varchar2), '>', '>'
), '<', '<'));
103
104 utl_http.end_response(http_resp) ;
105
106 -- x_clob response can now be used for extracting text
107 -- values from specific XML nodes, using XMLExtract
108 end;
109 /
Country: Singapore
Response Status: 200 OK
Response XML:<?xml version="1.0" encoding="utf-8"?><soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetCitiesByCountryRespo
nse xmlns="http://www.webserviceX.NET"><GetCitiesByCountryResult><NewDataSet>
<Table>
<Country>Singapore</Country>
<City>Singapore / Paya
Lebar</City>
</Table>
<Table>
<Country>Singapore</Country>
<City>Singapore / Changi Airport</City>
</Table>
</NewDataSet></GetCitiesByCountryResult></GetCitiesByCountryResponse><
/soap:Body></soap:Envelope>
PL/SQL procedure successfully completed.
No more of "ORA-24247: network access denied by access control list (ACL)". You get back a neat web service response.
Note that this PL/SQL code runs for host ‘*.webservicex.com’ because it is assigned to the Access Control List (ACL). To access any other websites, you will need to assign the network hosts to the ACL too, or the same ORA-24247 error will block your way.
…and that’s how you work with ACL in Oracle 11G.
References
- DBMS_NETWORK_ACL_ADMIN
- Managing Fine-Grained Access to External Network Services
- Security Features in Oracle 11G
