Access Control List (ACL) in Oracle 11G

January 24, 2017

in DBA, Exceptions, ORA Errors, PL/SQL, Scripts, UTL packages

Access Control List (ACL) in Oracle 11G

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), '&gt;', '>'), '&lt;', '<'));

  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), '&gt;', '>'
), '&lt;', '<'));
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

{ 1 comment… read it below or add one }

1 Alex December 20, 2018 at 3:31 pm

Thank you for great explanation!

Leave a Comment

Previous post:

Next post: