使用 utl_http &12c 上的钱包:证书验证失败

2021-12-24 00:00:00 ssl oracle12c oracle plsql

希望有人能发现我做错了什么，因为我要秃顶了.

Hope someone can spot what I'm doing wrong as I'm going bald from this.

我用过 utl_http &钱包在 11gR1 上调用 https 没有太多麻烦，但我们新的 12c 安装让我很伤心.

I have used utl_http & wallets to call https on 11gR1 without much trouble, but our new 12c installation is causing me a lot of grief.

我尝试使用 oracle 钱包管理器和命令行导入受信任的证书，但没有成功.我知道 oracle 在缓存钱包方面可能很挑剔，所以我尝试了多个新会话，但都没有成功.

I have tried importing the trusted certificate using both oracle wallet manager, and command line, without any success. I know that oracle can be picky as to caching the wallet, so I have tried multiple new sessions without any luck.

我已经为 *.presstogo.com、Geotrust SSL CA & 下载了三个必要的证书.Geotrust 全球 CA.

I have downloaded the three neccessary certificates for *.presstogo.com, Geotrust SSL CA & Geotrust Global CA.

我构建钱包的命令行版本如下:

The command-line version of my building the wallet is as follows:

orapki wallet create -wallet /oracle/product/12.0.1/owm/wallets/test1237 -pwd test=1237 -auto_login  
orapki wallet add -wallet /oracle/product/12.0.1/owm/wallets/test1237 -trusted_cert -cert "*.presstogo.com" -pwd test=1237  
orapki wallet add -wallet /oracle/product/12.0.1/owm/wallets/test1237 -trusted_cert -cert "GeoTrust SSL CA" -pwd test=1237  
orapki wallet add -wallet /oracle/product/12.0.1/owm/wallets/test1237 -trusted_cert -cert "Geotrust Global CA" -pwd test=1237  
orapki wallet display -wallet /oracle/product/12.0.1/owm/wallets/test1237   
Oracle PKI Tool : Version 12.1.0.1  
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.  
Requested Certificates:   
User Certificates:  
Trusted Certificates:   
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign, Inc.,C=US  
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions, Inc.,O=GTE Corporation,C=US  
Subject:        CN=GeoTrust SSL CA,O=GeoTrust, Inc.,C=US  
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign, Inc.,C=US  
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign, Inc.,C=US  
Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US  
Subject:        CN=*.presstogo.com,OU=IT,O=Press to go AS,L=Oslo,ST=Norway,C=NO,SERIAL_NUM=SJYpOHrRdCDHE8KZ6dRFGMJthOjs7-v3

好的，让我们测试一下.登录 sqlplus 并运行以下命令:

Ok, lets test this. Login to sqlplus and run the following:

declare  
    lo_req    utl_http.req;  
    lo_resp   utl_http.resp;  
begin  
    utl_http.set_detailed_excp_support ( true );  
    utl_http.set_wallet ( 'file:/oracle/product/12.0.1/owm/wallets/test1237', 'test=1237');  
    lo_req := utl_http.begin_request ( 'https://production.presstogo.com/mars/hello' );  
    lo_resp := utl_http.get_response ( lo_req );  
    -- A successfull request would have the status code "200".   
    dbms_output.put_line ( lo_resp.status_code );  
    utl_http.end_response ( lo_resp );  
exception  
  when others then    
    utl_http.end_response ( lo_resp );  
    raise;  
end;

声明

*

第 1 行错误:

ORA-29273:HTTP 请求失败

ORA-29273: HTTP request failed

ORA-06512:在SYS.UTL_HTTP"，第 1130 行

ORA-06512: at "SYS.UTL_HTTP", line 1130

ORA-29024:证书验证失败

ORA-29024: Certificate validation failure

ORA-06512:在第 6 行

ORA-06512: at line 6

为了记录，值得注意的是以下确实有效:

For the record, It is worth noting that the following does work:

declare  
    lo_req    utl_http.req;  
    lo_resp   utl_http.resp;  
begin  
    utl_http.set_wallet ( 'file:/oracle/product/12.0.1/owm/wallets/test1237', 'test=1237');  
    lo_req := utl_http.begin_request ( 'https://www.google.be' );  
    lo_resp := utl_http.get_response ( lo_req );  
    dbms_output.put_line ( lo_resp.status_code );  
    utl_http.end_response ( lo_resp );  
end;  
/

帮助我欧比旺，你是我唯一的希望.

推荐答案

为了他人的利益回答我自己的问题.

Answering my own question for the benefit of others.

根据 Oracle Support 的说法，只应导入证书链，而不是终端站点证书.在我上面使用的例子中，只将以下证书导入钱包:

According to Oracle Support only the certificate chain should be imported, not the end site certificate. In the example I used above, only import the following certificates into the wallet:

Geotrust SSL CA &Geotrust Global CA

请勿导入 *.presstogo.com 证书

引用 Oracle 支持:

To quote Oracle support:

select 在 12c 中失败的原因是 12c 不想要将钱包中的用户证书视为可信证书.

The reason that the select is failing in 12c is that 12c does not want to see the user cert in the wallet as a trusted cert.

这在以前的版本中显然不是问题，而是删除了钱包中的证书在这里解决了这个问题.

This was apparently not an issue in previous versions but removing that cert from the wallet fixed the issue here.

这与我在网上找到的有关使用 utl_http 连接到 Https 站点的所有信息相矛盾，并且把我搞糊涂了.

This contradicts all information I have found online regarding the use of utl_http to connect to Https sites, and confused the hell out of me.

希望这能帮助我的情况下的其他人.

Hopefully this will help others in my situation.

相关文章