PL/SQL SQL

Accessing a website from the database

One of the things people want more and more often is to somehow read from a web-resource using a database. It could be to just get the HTML off of a website or access REST via PL/SQL, or grab a JSON-file residing on a public website. No matter which it is we need to declare that it is a website we want the database user to reach.

To have a simple example I’ll just show how to read the HTML off of a website with SQL. The mechanics for the rest is the same, there is just a bit more work with using those techniques that has nothing to do with the ability to reach the web-resource.

Let us start with the most simple of all things, get html from a website via HTTP. You will have a hard time finding a website willing to talk HTTP today as even web browsers refuses that more and more.

Try Basic Access

The one I found when I tried was example.com which still serves both HTTP and HTTPS. Most other ones redirects HTTP to HTTPS. You may not have noticed, but if you look a bit closer your requested HTTP-page returns a “secure” HTTPS-page.

select utl_http.request('http://example.com') from dual;

That is all it takes to write a SQL that grabs the HTML of of a web-page. However, it returns an ACL-error when we run it.

ORA-24247: nätverksåtkomst nekad av lista för åtkomstkontroll

To the uninitiated that may sound like gobbledygook. It is however a very specific error saying that you have not set up your Access Control List to allow the database to reach that domain. There are plenty of blogs about how to do this in pre 12c and 12c and later. Here I’m just using the command needed to allow the access post 12c.

If we look in the views dba_host_acls and dba_host_aces there are no entries for the host example.com.

Set up ACL for HTTP to example.com

To set up the access, we can run this command as a privileged user (i e SYS).

begin
 dbms_network_acl_admin.append_host_ace
   ( host => 'example.com'
   , lower_port => 80
   , upper_port => 80
   , ace => xs$ace_type( privilege_list => xs$name_list('http')
                       , principal_name => 'TEST_USR'
                       , principal_type => xs_acl.ptype_db));
end;
/

Now if we take a look in dba_host_acls we see that we have an entry for example.com.

select host
     , lower_port lp
     , upper_port up 
  from dba_host_acls 
 where host = 'example.com';

HOST        LP UP
example.com 80 80

Thus we have an ACL now for the host for the ports we expect. 80 is of course the port for standard HTTP-traffic, the port your web browser uses for HTTP unless you explicitly specify a different port.

If we take a look now at the ACEs – Access Control Entries – for the grants given.

select host
     , lower_port lp
     , upper_port up
     , grant_type type
     , principal  usr
     , privilege
  from dba_host_aces 
 where host = 'example.com';

HOST        LP UP TYPE  USR      PRIVILEGE
example.com 80 80 GRANT TEST_USR HTTP

In this case we see the same data as from ACLS, but we also see that we have granted TEST_USR the HTTP privilege.

With that in place we should now be able to get the data from the example.com page.

Accessing example.com with HTTP should now work

As our test user test_usr in this case we run the same SQL again.

select utl_http.request('http://example.com') from dual;
<!doctype html>
<html>
<head>
 <title>Example Domain</title>
...

I cut off the rest of the output. This shows that we can now from pure SQL read the HTML off of the website. We can then process it further if we want to in SQL or in PL/SQL.

How about accessing it with HTTPS

Now that it works with HTTP, let’s try the same with HTTPS.

select utl_http.request('https://example.com') from dual;
ORA-24247: nätverksåtkomst nekad av lista för åtkomstkontroll

Yes, we’re back to not having given the user the right to make the call. HTTP is using port 80 while HTTPS uses port 443, so the ACL we did set up is not enough. Fortunately we already know how to do that though. Again as a privileged user (i e SYS).

begin
 dbms_network_acl_admin.append_host_ace
   ( host => 'example.com'
   , lower_port => 443
   , upper_port => 443
   , ace => xs$ace_type( privilege_list => xs$name_list('http')
                       , principal_name => 'TEST_USR'
                       , principal_type => xs_acl.ptype_db));
end;
/

The only change is to change lower and upper port to 443 from 80. The two views DBA_HOST_ACLS and DBA_HOST_ACES gets one row more that are identical to what we looked at before, except that they show lower_port and upper_port to be 443 instead of 80.

Time to try the SQL again.

select utl_http.request('https://example.com') from dual;
ORA-29024: Fel vid certifikatvalidering

It still does not work, but we got a new error. The database made the access to the example.com server using HTTPS. Now we’re running into HTTPS-related issues. This is what HTTPS does for us, it should require us to have a trust store set up. That shows the certificates we trust to be in the certificate chain on the server we access. The chain includes the site itself and the vendor the site used to get their certificate. The whole chain need to be trusted if we are to trust the site.

Until Next Time

That however will be the subject of the next blog-post. How’s that for a cliff hanger?

This post is focused on ACL for making a call to a web-resource and that part now works, we made the call to example.com via HTTPS. There is just some more work to do to let the database know that it can trust the server we’re accessing

4 Comments

  1. Pingback: Wallet setup for https – Oracle DB Development

  2. Pingback: Scripting the Oracle Wallet truststore – Oracle DB Development

  3. Hello Mathias,
    I am getting this error when trying to access a HTTPS Rest api using UTL_HTTP, and when using APEX_WEB_SERVICE.make_rest_request.
    UTL_HTTP Error :
    /***
    Error report –
    ORA-29106: Cannot import PKCS #12 wallet.
    ORA-06512: at “SYS.UTL_HTTP”, line 380
    ORA-06512: at “SYS.UTL_HTTP”, line 1148
    ORA-06512: at line 12
    29106. 00000 – “Cannot import PKCS #12 wallet.”
    *Cause: A required parameter is NULL or the BER-encoding is malformed.
    *Action: Enable tracing and attempt the connection again. Contact
    Oracle customer support with the trace output.
    ***/

    APEX_WEB_SERVICE.make_rest_request error :
    Error report –
    ORA-29273: HTTP request failed
    ORA-06512: at “APEX_180200.WWV_FLOW_WEB_SERVICES”, line 1017
    ORA-29106: Cannot import PKCS #12 wallet.
    ORA-06512: at “SYS.UTL_HTTP”, line 380
    ORA-06512: at “SYS.UTL_HTTP”, line 1148
    ORA-06512: at “APEX_180200.WWV_FLOW_WEB_SERVICES”, line 917
    ORA-06512: at “APEX_180200.WWV_FLOW_WEB_SERVICES”, line 1523
    ORA-06512: at “APEX_180200.WWV_FLOW_WEBSERVICES_API”, line 369
    ORA-06512: at line 8
    29273. 00000 – “HTTP request failed”
    *Cause: The UTL_HTTP package failed to execute the HTTP request.
    *Action: Use get_detailed_sqlerrm to check the detailed error message.
    Fix the error and retry the HTTP request.

    Would you please help me out ?
    Things I have tried and did not see a positive result:
    1. changing the wallet location from a NFS mounted folder to a local folder
    2. Adding root and intermediate certificates to the wallet
    3. When creating the wallet with orapki , tried creating with auto_login and without auto_login
    4. receiving this error when the wallet is empty (that is code run after wallet creation and before certificate add)

    Database is on 19c (upgraded from 12c), and regular HTTP call works fine.
    Certificate Signature algorithm says sha256RSA and Signature hash algorithm says sha256

    • Hmmm. Are you sure the database is able to read the file using the specified path? Can you test with utl_file or some such technique.

Leave a Reply to Mathias Cancel

Your email address will not be published. Required fields are marked *

*