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.
CodePudding user response：
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>
CodePudding user response：
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.