Oracle 19: Failing to connect to host using PL/SQL via ACL

Question

I'm trying to get a simple proof of concept working to use PL/SQL to consume an API. I'm using the articles on oracle-base.com to guide me here, but I'm hitting a wall.

Technical Details: Oracle 19c EE (release 19.3) installed on Windows 10 Pro laptop Setup as multi-tenant with a single pluggable database - PDB1

This is what I have done ...

Pre-checks to ensure XML DB installed:

-- user XDB exists
select * from ALL_USERS order by username desc;

-- resource_view exists
select * from resource_view;

-- shows XML DB is installed
select * from dba_registry;

Next I logged in as sys into PBD1 and double checked I'm in the right PDB:

-- ensure container is PDB1
ALTER SESSION SET CONTAINER = PDB1;

-- double check I'm in correct PDB
show con_name

Now I create a test user test1api, grant what I need to, and creat an ACE (I believe the ACL is implicitly created in 12c onwards):

-- created a user test1api
CREATE USER test1api
IDENTIFIED BY test1api 
CONTAINER=CURRENT 
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE default;

-- granted what I needed to (I hope!)
grant RESOURCE to test1api;
grant CREATE VIEW to test1api;
grant CREATE SYNONYM to test1api;
grant create session to test1api;
grant unlimited tablespace to test1api;
GRANT EXECUTE ON UTL_HTTP TO test1api;

-- create the acl using the new recommended 12c method as Oracle recommends
-- NB: I've avoided the deprecated function create_acl
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'oracle-base.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'test1api',
                              principal_type => xs_acl.ptype_db)); 
END;
/

Then I checked the settings using every method I learnt from oracle-base ...

-- NB: new dba_host_acls view
SELECT HOST,
       LOWER_PORT,
       UPPER_PORT,
       ACL,
       ACLID,
       ACL_OWNER
FROM   dba_host_acls
ORDER BY host;

-- NB: new dba_host_aces view
SELECT host,
       lower_port,
       upper_port,
       ace_order,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date,
       grant_type,
       inverted_principal,
       principal,
       principal_type,
       privilege
FROM   dba_host_aces
ORDER BY host, ace_order;

and I used the deprecated methods as well ...

-- NB: dba_network_acls deprecated view in 12c
SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls
ORDER BY host;

-- NB: dba_network_acl_privileges deprecated view in 12c
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
ORDER BY acl, principal, privilege;

All seems well. User test1api has http priv against oracle-base.com, port 80.

Then I checked (using the deprecated methods) what the status of the priv is:

-- NB: DEPRECATED: DBMS_NETWORK_ACL_ADMIN.check_privilege
-- I get a GRANTED for this
SELECT DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege('NETWORK_ACL_77CB1FEFE33A4107A46B8134B3DA4671', 'TEST1API', 'http'),
         1, 'GRANTED', 0, 'DENIED', 'DUNNO') privilege 
FROM dual;


-- NB: DEPRECATED: DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid
-- I get a GRANTED for this
SELECT acl,
       host,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TEST1API', 'http'),
         1, 'GRANTED', 0, 'DENIED', 'DUNNO') privilege 
FROM   dba_network_acls;

All states GRANTED for http for the user.

And finally I run a test:

DECLARE
  l_url            VARCHAR2(50) := 'http://oracle-base.com';
  
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
BEGIN
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(l_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  UTL_HTTP.end_response(l_http_response);
END;
/

Error report -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1288
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512: at "SYS.UTL_HTTP", line 1278
ORA-06512: at line 9
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.

I have tried setting l_url to 'oracle-base.com', 'http://www.oracle-base.com', and 'www.oracle-base.com' and all fail the same way.

Can anyone point out what I'm missing here?

Thanks.

Answer 1

I think the problem is that you get directed from http://oracle-base.com/ to https://oracle-base.com (port 443). Expand the port range to include 443. But, SSL may give you another issue, because the database probably needs som certificates in the wallet. I also recommend to have curl ready so you can test the request like this:

curl http://www.oracle-base.com

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>301 Moved Permanently</title>
</head><body>
<h1>Moved Permanently</h1>
<p>The document has moved <a href="https://oracle-base.com/">here</a>
</p>
</body></html>

Answer 2

I hope this may help someone who is doing something similar and avoid a lot of wasted time and frustration.

I wanted to test a connection between the Oracle DB and an API (via http) using the ACL functionality. It appears that oracle-base is https, so the example I was trying to follow will not work as highlighted by oisene. I would need to setup a security certificate, etc. So going back to my original task of consuming an API via http, I wrote the following which works. I wrote a very simple API using node.js and express and consumed the API from PL/SQL.

Node.JS Server code (API):

const express = require("express");
const app = express();

// root endpoint
app.get('/', function(req, res) {
    res.write('<h1>The Heroes API</h1>');
    res.write('<p>Available endpoints:</p>');
    res.write('<p>localhost:3000/heroes</p>');
    res.send();
})

//heroes endpoint
app.get('/heroes', function(req, res) {

    let heroesObj = { 
        "heroes": [ 
            {"name": "Batman", "antagonist": "Joker"},
            {"name": "Black Panther", "antagonist": "Erik Killmonger"},
            {"name": "Storm", "antagonist": "Shadow King"},
            {"name": "Wonder Woman", "antagonist": "Ares"}
        ]
    };

    heroesDetail = JSON.stringify(heroesObj);

    res.write(heroesDetail);
    res.send();
});

// listener
app.listen(3000, function() {
    console.log('Server started on port 3000');
});

Navigate to localhost:3000/heroes and it should display the JSON document.

Oracle setup code logged in as sys:

-- create user
CREATE USER test1api
IDENTIFIED BY test1api 
CONTAINER=CURRENT 
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE default;

-- grants
grant RESOURCE to test1api;
grant CREATE VIEW to test1api;
grant CREATE SYNONYM to test1api;
grant create session to test1api;
grant unlimited tablespace to test1api;
GRANT EXECUTE ON UTL_HTTP TO test1api;

-- add the access control entity for test1api against localhost port 3000 with http priv
BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'localhost',
    lower_port => 3000,
    upper_port => 3000,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'test1api',
                              principal_type => xs_acl.ptype_db)); 
END;
/

Now login as user test1api and create the Oracle client code that will consume the API on localhost:3000 ...

set serveroutput on

DECLARE
  url            VARCHAR2(50) := 'http://localhost:3000/heroes';
  http_request   UTL_HTTP.req;
  http_response  UTL_HTTP.resp;
  http_value     varchar2(1024) := NULL; 
BEGIN
  -- Make a HTTP request and get the response.
  http_request  := UTL_HTTP.begin_request(url);
  http_response := UTL_HTTP.get_response(http_request);
  UTL_HTTP.READ_LINE(http_response, http_value, TRUE);
  DBMS_OUTPUT.PUT_LINE(http_value);
  UTL_HTTP.end_response(http_response);
END;
/

This should print out the JSON document sent back from the API.

I'll work on consuming an API via https next!

Thanks.