Oracle 19：无法通过ACL使用PL/SQL连接到主机

Question

我正在尝试获得一个简单的概念证明，以使用PL/SQL消耗API。 我正在使用Oracle-Base.com上的文章来指导我在这里，但我撞墙了。

技术细节： Oracle 19c EE（版本19.3）安装在Windows 10 Pro笔记本电脑上 设置为多租户使用单个可插入数据库-PDB1

这是我所做的...

预先检查以确保已安装XML DB：

-- 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;

接下来我将SYS登录到PBD1中，并double检查我在右PDB：

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

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

现在我创建一个测试用户test1api，授予我需要的东西，并创建一个ACE（我相信ACL是在12C上隐式创建的）：

-- 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;
/

从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;

然后，我使用 弃用的方法也

-- 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;

似乎很好。用户test1api具有针对Oracle-Base.com的HTTP PRIV，端口80。

然后，我检查（使用不推荐使用的方法）PRIV的状态是什么：

-- 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;

所有为用户授予HTTP的状态。

最后我进行了测试：

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.

我尝试将l_url设置为'oracle-base.com'，'http://www.oracle-base.com'和'www.oracle-base.com'，所有这些都失败了方式。

谁能指出我在这里缺少的东西？

谢谢。

Answer 1

我认为问题是您从 http://oracle-base.com/ href =“ https://oracle-base.com” rel =“ nofollow noreferrer”> https://oracle-base.com （端口443）。将端口范围扩展到包括443。但是，SSL可能会给您另一个问题，因为数据库可能需要钱包中的SOM证书。我还建议准备好卷发，以便您可以这样测试请求：

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

我希望这可以帮助一个做类似事情并避免大量浪费时间和沮丧的人。

我想使用ACL功能测试Oracle DB和API（通过HTTP）之间的连接。看来Oracle-base是HTTPS，因此我要遵循的示例不会像Oisene突出显示那样起作用。我需要设置安全证书等。因此，回到我通过HTTP消费API的最初任务，我写了以下内容。我使用node.js编写了一个非常简单的API，并从PL/SQL中表达并消耗了API。

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.

接下来，我将通过HTTPS努力消费API！

谢谢。