oracle ORA-28860：使用 UTL_HTTP 时出现致命的 SSL 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27932660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-28860: Fatal SSL error when using UTL_HTTP?
提问by Steve
We are using Oracle 11g (11.2.0.3.0) and we are receiving the following error when executing a UTL_HTTP call:
我们正在使用 Oracle 11g (11.2.0.3.0)，并且在执行 UTL_HTTP 调用时收到以下错误：
EXCEPTION: ORA-28860: Fatal SSL error
EXCEPTION: ORA-06512: at "SYS.UTL_HTTP", line 1128
ORA-06512: at line 23
EXCEPTION: ORA-28860: Fatal SSL error
This is the code we are using:
这是我们正在使用的代码：
DECLARE
url_chr VARCHAR2(500);
user_id_chr VARCHAR2(100);
password_chr VARCHAR2(20);
wallet_path_chr VARCHAR2(500);
wallet_pass_chr VARCHAR2(20);
l_http_request UTL_HTTP.REQ;
l_http_response UTL_HTTP.RESP;
l_text VARCHAR2(32767);
BEGIN
url_chr := '*****';
user_id_chr := '*****';
password_chr := '*****';
wallet_pass_chr := '*****';
wallet_path_chr := 'file:/etc/ORACLE/WALLETS/astens/rtca/cer/';
UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(TRUE);
UTL_HTTP.SET_WALLET(wallet_path_chr, wallet_pass_chr);
l_http_request := UTL_HTTP.BEGIN_REQUEST(url_chr);
UTL_HTTP.SET_AUTHENTICATION(r => l_http_request, username => user_id_chr, PASSWORD => password_chr);
l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request);
DBMS_OUTPUT.PUT_LINE ('STATUS_CODE : ' || l_http_response.STATUS_CODE);
BEGIN
LOOP
UTL_HTTP.READ_TEXT(l_http_response, l_text, 32766);
DBMS_OUTPUT.PUT_LINE (l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(l_http_response);
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||UTL_HTTP.GET_DETAILED_SQLERRM);
UTL_HTTP.END_RESPONSE(l_http_response);
END;
We have installed the supplied certificates into the Oracle Wallet, and we use the same code for different clients without issues.
我们已将提供的证书安装到 Oracle Wallet 中，我们对不同的客户端使用相同的代码没有问题。
Any ideas?
有任何想法吗？
回答by Wanted
The site you're calling could be preventing connections via outdated SSLv3 protocol and at the same time, a newer algorithm might not be supported by Oracle DB 11.2.0.3.
您正在调用的站点可能会阻止通过过时的 SSLv3 协议进行连接，同时，Oracle DB 11.2.0.3 可能不支持较新的算法。
There is this known bug, but it affects versions up to 11.1 apparently:
有这个已知的错误，但它显然影响到 11.1 的版本：
UTL_HTTP Package Fails With ORA-29273 ORA-28860 When Using TLSv1 (Doc ID 727118.1) https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=842518171804826&id=727118.1&_afrWindowMode=0&_adf.ctrl-state=142oqbz21t_4
使用 TLSv1 时，UTL_HTTP 包因 ORA-29273 ORA-28860 失败（文档 ID 727118.1） https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=842518171804826&id=727118.1&_1fr-WindowMode.1&_12afl
There is also a bug 20323753 registered for 11.2.0.4 recently, still not fixed. Possibly could be the same case as yours.
最近11.2.0.4也注册了一个bug 20323753，还没修复。可能和你的情况一样。
回答by James Schrumpf
You don't mention your network Access Control List (ACL) grants, but in Oracle 11g you must set up an ACL for both the host you want to connect to and for the wallet you want to use. Since you don't mention getting the "ORA-24247: network access denied by access control list (ACL)" error, I'll assume that part is set up properly.
您没有提到您的网络访问控制列表 (ACL) 授权，但在 Oracle 11g 中，您必须为要连接的主机和要使用的钱包设置一个 ACL。由于您没有提到获得“ORA-24247：访问控制列表（ACL）拒绝网络访问”错误，我假设该部分设置正确。
The wallet ACL defines its location and grants privileges against the wallet to users. Without these privileges, Oracle will not open the wallet and present the certificate to the web server, even if you have the correct password. The wallet ACL is created with the following PL/SQL run as SYS:
钱包 ACL 定义其位置并向用户授予针对钱包的权限。如果没有这些权限，即使您拥有正确的密码，Oracle 也不会打开钱包并向 Web 服务器提供证书。钱包 ACL 是使用以下以 SYS 身份运行的 PL/SQL 创建的：
BEGIN
UTL_HTTP.ASSIGN_WALLET_ACL (
acl => 'your_acl_name.xdb',
wallet_path => '/path/to/my/wallet/');
END;
/
After the wallet ACL is created, the user must have privileges granted to it.
创建钱包 ACL 后，用户必须具有授予它的权限。
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'your_acl_name.xml',
principal => 'MY_USER',
is_grant => TRUE,
privilege => 'use-client-certificates');
END;
/
That will allow Oracle to open the wallet on your user's behalf and present the certificate to the web server.
这将允许 Oracle 代表您的用户打开钱包并将证书提交给 Web 服务器。
回答by Sergiy Balter
I'd like to offer the following:
我想提供以下内容：
Create a JAVA-function
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HttpSSLGet" AS import java.net.URL; import java.io.*; import javax.net.ssl.HttpsURLConnection; public class HttpSSLGet { public static String GetSSL(final String url) { StringBuffer buffer = new StringBuffer(); try { URL myUrl = new URL(url); HttpsURLConnection con = (HttpsURLConnection)myUrl.openConnection(); InputStream ins = con.getInputStream(); InputStreamReader isr = new InputStreamReader(ins); BufferedReader in = new BufferedReader(isr); String inputLine; while ((inputLine = in.readLine()) != null) { buffer.append(inputLine); } in.close(); } catch (Exception e) { return buffer.toString() + "\n" + e.toString(); } return buffer.toString(); } }Create a PL/SQL Package (of standalone function)
CREATE OR REPLACE PACKAGE PCK_HTTP AUTHID DEFINER AS function GetSSL(aUrl Varchar2) return Varchar2; END; / CREATE OR REPLACE PACKAGE BODY PCK_HTTP AS function GetSSL(aUrl Varchar2) return Varchar2 AS LANGUAGE JAVA NAME 'HttpSSLGet.GetSSL(java.lang.String) return java.lang.String'; END; /There is a problem with the built-in JAVA-machine in Oracle. It contains less certificates as standarte "satandalone" JAVA. Probably, you should add a downloaded certificate to built-in java machine(not standalone java), for ex. in command line (Windows):
keytool -import -alias geos -keystore "d:\Oracle\product.2.0\dbhome_1\javavm\lib\security\cacerts" -file example.com.cer -storepass changeitUse function in query or PL/SQL, for eg.
SELECT PCK_HTTP.GetSSL('https://www.example.com') FROM DUAL
创建一个 JAVA 函数
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HttpSSLGet" AS import java.net.URL; import java.io.*; import javax.net.ssl.HttpsURLConnection; public class HttpSSLGet { public static String GetSSL(final String url) { StringBuffer buffer = new StringBuffer(); try { URL myUrl = new URL(url); HttpsURLConnection con = (HttpsURLConnection)myUrl.openConnection(); InputStream ins = con.getInputStream(); InputStreamReader isr = new InputStreamReader(ins); BufferedReader in = new BufferedReader(isr); String inputLine; while ((inputLine = in.readLine()) != null) { buffer.append(inputLine); } in.close(); } catch (Exception e) { return buffer.toString() + "\n" + e.toString(); } return buffer.toString(); } }创建一个 PL/SQL 包（独立函数）
CREATE OR REPLACE PACKAGE PCK_HTTP AUTHID DEFINER AS function GetSSL(aUrl Varchar2) return Varchar2; END; / CREATE OR REPLACE PACKAGE BODY PCK_HTTP AS function GetSSL(aUrl Varchar2) return Varchar2 AS LANGUAGE JAVA NAME 'HttpSSLGet.GetSSL(java.lang.String) return java.lang.String'; END; /Oracle 内置的 JAVA 机器有问题。它包含较少的证书作为标准的“独立”JAVA。可能，您应该将下载的证书添加到内置 java 机器（不是独立 java），例如。在命令行（Windows）中：
keytool -import -alias geos -keystore "d:\Oracle\product.2.0\dbhome_1\javavm\lib\security\cacerts" -file example.com.cer -storepass changeit在查询或 PL/SQL 中使用函数，例如。
SELECT PCK_HTTP.GetSSL('https://www.example.com') FROM DUAL
回答by Saroj Bhumbla
We found that old certificates in wallet for https site even though not expired was no longer usable. Test with new certs in new wallet proved that. Removing old cert and adding new certs to the original wallet fixed the issue.
我们发现 https 站点钱包中的旧证书即使未过期也不再可用。在新钱包中使用新证书进行测试证明了这一点。删除旧证书并将新证书添加到原始钱包解决了该问题。
