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

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) := ' 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', ' and ' and all fail the same way.

Can anyone point out what I'm missing here?

Thanks.

2 Answers

I think the problem is that you get directed from to (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
<!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="">here</a>
</p>
</body></html>

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_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.