ORACLE、UTL_HTTP 和 SSL

ORACLE, UTL_HTTP and SSL

提问by BONNA

I try to reach a WebService provide by a secured site with a TLS 1.2 certificate encrypted that i exported and add in a wallet.

我尝试使用加密的 TLS 1.2 证书访问由安全站点提供的 WebService，我将其导出并添加到钱包中。

First i try to reach the site with the package UTL_HTTP.request on a 11.2.0.1.0 ORACLE Database but i have the ORA-28857 SSL error unknow message.

首先，我尝试使用 11.2.0.1.0 ORACLE 数据库上的包 UTL_HTTP.request 访问该站点，但我收到 ORA-28857 SSL 错误未知消息。

I try the same on a 12.1.0.1.0 ORACLE Database but i have the ORA-29024 message.

我在 12.1.0.1.0 ORACLE 数据库上尝试了相同的操作，但我收到了 ORA-29024 消息。

So, i searched on the web and find everything and nothing about the subject.....

所以，我在网上搜索并找到了关于这个主题的所有内容......

Here is what i did:

这是我所做的：

First: I exported the certificate from Internet Explorer with the PKCS #7 (.p7b) format (Chains included)

首先：我使用 PKCS #7 (.p7b) 格式（包括链）从 Internet Explorer 导出证书

then, i create a wallet with the orapki utility

然后，我使用 orapki 实用程序创建了一个钱包

 orapki wallet create -wallet e:\wallet -pwd <pwd>

then i add my certificat

然后我添加我的证书

 orapki wallet add -wallet e:\wallet -trusted_cert -cert e:\certificats\<cert file> -pwd <pwd>

and i try to reach the secured site

我尝试到达安全站点

 select UTL_HTTP.REQUEST('https://<secured site>.com',null,'file:E:\wallet','<pwd>') from dual;

and i have the message:

我有消息：

ORA-29273: échec de demande HTTP
ORA-06512: à "SYS.UTL_HTTP", ligne 1722
ORA-28857: Erreur SSL inconnue
ORA-06512: à ligne 1
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 tried to create ACLs: BEGIN dbms_network_acl_admin.create_acl( acl => 'utl_http.xml', description => 'Test ACL', principal => '', is_grant => TRUE, privilege => 'connect', start_date => null, end_date => null ); END; /

我尝试创建 ACL： BEGIN dbms_network_acl_admin.create_acl( acl => 'utl_http.xml', description => 'Test ACL', principal => '', is_grant => TRUE, privilege => 'connect', start_date => null , end_date => null ); 结尾; /

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'utl_http.xml',
        principal   => '<user>',
        is_grant    =>  TRUE,
        privilege   => 'use-client-certificates',
                start_date => null,
                end_date => null);
END;
/

BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
       acl => 'utl_http.xml',
       host => '<secured site>',
       lower_port => 1,
       upper_port => 9999);
END;
/

BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
       acl => 'utl_http.xml',
       wallet_path => 'file:E:\wallet');
END;
/

(I m not sur about usefull of all but i m ready to do everything to make that work ^^)

（我不知道有用，但我准备尽一切努力使这项工作^^）

and i try to reach the secured site

我尝试到达安全站点

 select UTL_HTTP.REQUEST('https://<secured site>.com',null,'file:E:\wallet','<pwd>') from dual;

and i have the message:

我有消息：

Rapport d'erreur :
ORA-29273: échec de demande HTTP
ORA-06512: à "SYS.UTL_HTTP", ligne 1130
ORA-29024: Echec de validation de certificat
ORA-06512: à ligne 10
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 read that Oracle 11 have problems withe TLS 1.2 encrypted certificate so i tried with an Oracle 12 (Same ways to create Wallet and ACL)

我读到 Oracle 11 的 TLS 1.2 加密证书有问题，所以我尝试使用 Oracle 12（创建钱包和 ACL 的方法相同）

I have the message:

我有消息：

Rapport d'erreur :
ORA-29273: échec de demande HTTP
ORA-06512: à "SYS.UTL_HTTP", ligne 1130
ORA-29024: Echec de validation de certificat
ORA-06512: à ligne 10
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.

Hope I was clear in my explanations

希望我的解释很清楚

I try to know what to do to reach a secure site by a certificate based on the certificate

我试图通过基于证书的证书了解如何访问安全站点

Thank you for your much needed support ^^

感谢您的大力支持^^

best regards

此致

回答by Kotodid

May be I am too late, but I caught same issues and found some answers.

可能是我太晚了，但我发现了同样的问题并找到了一些答案。

Oracle Database earlier than 11.2.0.3 does not support SHA-2 SSL-standard, for example we cannot connect google from 11.2.0.1.

早于 11.2.0.3 的 Oracle 数据库不支持 SHA-2 SSL 标准，例如我们无法从 11.2.0.1 连接 google。

When use 12c - try to remove end certificate of chain from wallet. (I found this answer here: Using utl_http & wallets on 12c: certificate validation failure)

使用 12c 时 - 尝试从钱包中删除链的结束证书。（我在这里找到了这个答案：在 12c 上使用 utl_http 和钱包：证书验证失败）

回答by James Schrumpf

An Oracle wallet is in PKCS12 format. You can't use a PKCS7 formatted certificate inside an Oracle wallet. You want to use the "Base-64 encoded X.509 (.CER)" option instead. You must also get each certificate in the chain for the certificate of the site to which you want to connect. Those will be loaded into the Trusted Certificates section of the wallet.

Oracle 钱包采用 PKCS12 格式。您不能在 Oracle 钱夹中使用 PKCS7 格式的证书。您想改用“Base-64 编码的 X.509 (.CER)”选项。您还必须获取要连接到的站点的证书链中的每个证书。这些将被加载到钱包的可信证书部分。

There are good detailed instructions at this page:

此页面有很好的详细说明：

UTL_HTTP and SSL(HTTPS) Using Oracle Wallets

使用 Oracle 钱包的 UTL_HTTP 和 SSL(HTTPS)