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
{ 1 comment… read it below or add one }
Thank you for great explanation!