Ora 28860 неустранимая ошибка ssl

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.

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:

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.

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.

Источник

Last week I had another error while using UTL_HTTP from an Oracle database to fetch a web page: ORA-28860 Fatal SSL error. The server was previously using http, and then began forwarding to https.

After updating the URL to use https (in case the forwarding was the problem), my first thought was that this is another symptom of a missing trusted root certificate. So I added the certificate to the Oracle Wallet, but that had no effect:

SELECT utl_http.request('https://test.com/page', NULL, 'file:/path/to/wallet', 'WalletPassword')
FROM   dual
       *
Error at line 1
ORA-29273: HTTP request failed
ORA-28860: Fatal SSL error
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1

So my working hypothesis was that the fatal error occurs during SSL negotiation prior to validation of the server’s certificate, possibly to do with supported SSL version or cipher suites. One of the few references to this problem supported this theory, although the two specific issues mentioned at the end (to do with SSL version) did not apply in my case.

With that in mind, I used a standard tool to see what the server supports:

wget https://testssl.sh/testssl.sh
chmod a+x testssl.sh
./testssl.sh https://test.com/

The output is comprehensive; this is the pertinent section:

--> Testing protocols (via sockets except TLS 1.2 and SPDY/NPN)

 SSLv2      not offered (OK)
 SSLv3      not offered (OK)
 TLS 1      offered
 TLS 1.1    offered
 TLS 1.2    offered (OK)
 SPDY/NPN   not offered

--> Testing ~standard cipher lists

 Null Ciphers                 not offered (OK)
 Anonymous NULL Ciphers       not offered (OK)
 Anonymous DH Ciphers         not offered (OK)
 40 Bit encryption            not offered (OK)
 56 Bit encryption            Local problem: No 56 Bit encryption configured in /usr/bin/openssl
 Export Ciphers (general)     not offered (OK)
 Low (<=64 Bit)               not offered (OK)
 DES Ciphers                  not offered (OK)
 Medium grade encryption      not offered (OK)
 Triple DES Ciphers           offered (NOT ok)
 High grade encryption        offered (OK)

--> Testing (perfect) forward secrecy, (P)FS -- omitting 3DES, RC4 and Null Encryption here

 PFS is offered (OK)  ECDHE-RSA-AES256-GCM-SHA384 ECDHE-RSA-AES256-SHA384 ECDHE-RSA-AES256-SHA ECDHE-RSA-AES128-GCM-SHA256 ECDHE-RSA-AES128-SHA256 ECDHE-RSA-AES128-SHA

--> Testing server preferences

 Has server cipher order?     yes (OK)
 Negotiated protocol          TLSv1.2
 Negotiated cipher            ECDHE-RSA-AES128-GCM-SHA256,
 Cipher order
     TLSv1:     ECDHE-RSA-AES128-SHA ECDHE-RSA-AES256-SHA AES256-SHA AES128-SHA DES-CBC3-SHA
     TLSv1.1:   ECDHE-RSA-AES128-SHA ECDHE-RSA-AES256-SHA AES256-SHA AES128-SHA DES-CBC3-SHA
     TLSv1.2:   ECDHE-RSA-AES128-GCM-SHA256 ECDHE-RSA-AES128-SHA256 ECDHE-RSA-AES128-SHA ECDHE-RSA-AES256-GCM-SHA384 ECDHE-RSA-AES256-SHA384 ECDHE-RSA-AES256-SHA AES128-GCM-SHA256 AES256-GCM-SHA384 AES128-SHA256 AES256-SHA AES128-SHA DES-CBC3-SHA

With TLSv1.2 supported, this should be fine with Oracle 12c. The next step, is to see what UTL_HTTP offers when it makes the connection to the server. I couldn’t find any documentation on this, so I thought it would be fun (I don’t get out much) to look at the packets on the network to see what’s going on.

$ sudo tcpdump -i eth0 -w ssl_dump.out
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
^C158 packets captured
158 packets received by filter
0 packets dropped by kernel

After starting this, and before interrupting it with ^C, I used another session to call UTL_HTTP as above. Then I opened the output file in Wireshark, and found the SSL ClientHello message:

Secure Sockets Layer
    TLSv1.2 Record Layer: Handshake Protocol: Client Hello
        Content Type: Handshake (22)
        Version: TLS 1.2 (0x0303)
        Length: 107
        Handshake Protocol: Client Hello
            Handshake Type: Client Hello (1)
            Length: 103
            Version: TLS 1.2 (0x0303)
            Random
                GMT Unix Time: Sep  9, 2016 21:01:14.000000000 GMT Daylight Time
                Random Bytes: cabc488d10a2b6be6aa7ef0777628f62c9a100dd8d878998...
            Session ID Length: 0
            Cipher Suites Length: 32
            Cipher Suites (16 suites)
                Cipher Suite: TLS_RSA_WITH_AES_256_GCM_SHA384 (0x009d)
                Cipher Suite: TLS_RSA_WITH_AES_128_GCM_SHA256 (0x009c)
                Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA256 (0x003d)
                Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA256 (0x003c)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 (0xc02c)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 (0xc02b)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 (0xc024)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 (0xc023)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA (0xc00a)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA (0xc009)
                Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA (0x0035)
                Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA (0x002f)
                Cipher Suite: TLS_RSA_WITH_3DES_EDE_CBC_SHA (0x000a)
                Cipher Suite: TLS_RSA_WITH_RC4_128_SHA (0x0005)
                Cipher Suite: TLS_RSA_WITH_RC4_128_MD5 (0x0004)
                Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV (0x00ff)
            Compression Methods Length: 1
            Compression Methods (1 method)
                Compression Method: null (0)
            Extensions Length: 30
            Extension: signature_algorithms
                Type: signature_algorithms (0x000d)
                Length: 26
                Signature Hash Algorithms Length: 24
                Signature Hash Algorithms (12 algorithms)
                    Signature Hash Algorithm: 0x0201
                        Signature Hash Algorithm Hash: SHA1 (2)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0301
                        Signature Hash Algorithm Hash: SHA224 (3)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0401
                        Signature Hash Algorithm Hash: SHA256 (4)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0501
                        Signature Hash Algorithm Hash: SHA384 (5)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0601
                        Signature Hash Algorithm Hash: SHA512 (6)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0202
                        Signature Hash Algorithm Hash: SHA1 (2)
                        Signature Hash Algorithm Signature: DSA (2)
                    Signature Hash Algorithm: 0x0403
                        Signature Hash Algorithm Hash: SHA256 (4)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0503
                        Signature Hash Algorithm Hash: SHA384 (5)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0203
                        Signature Hash Algorithm Hash: SHA1 (2)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0303
                        Signature Hash Algorithm Hash: SHA224 (3)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0603
                        Signature Hash Algorithm Hash: SHA512 (6)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0101
                        Signature Hash Algorithm Hash: MD5 (1)
                        Signature Hash Algorithm Signature: RSA (1)

and the server’s response:

Secure Sockets Layer
    TLSv1.2 Record Layer: Alert (Level: Fatal, Description: Handshake Failure)
        Content Type: Alert (21)
        Version: TLS 1.2 (0x0303)
        Length: 2
        Alert Message
            Level: Fatal (2)
            Description: Handshake Failure (40)

My original conclusion was a mismatch between the ciphers, but thanks to the comment below, and more careful checking using the hex codes, that was proved incorrect and it appears to be an issue with SNI support in UTL_HTTP.

Platform Information

The material in this article was most recently tested against Oracle 12.1.0.2.160719 on 64-bit Linux.

This entry was posted in Network. Bookmark the permalink.

Источник

Вопрос:

Мы используем 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

Это код, который мы используем:

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;

Мы установили поставляемые сертификаты в Oracle Wallet, и мы используем тот же код для разных клиентов без проблем.

Любые идеи?

Ответ №1

Ответ №2

Вы не указываете свои сетевые списки управления доступом к сети (ACL), но в Oracle 11g вы должны настроить ACL для хоста, к которому хотите подключиться, и для кошелька, который вы хотите использовать. Поскольку вы не упоминаете об ошибке “ORA-24247: отказ в доступе к сети через контроль доступа (ACL)”, я предположим, что эта часть настроена правильно.

ACL кошелька определяет его местоположение и предоставляет привилегии против кошелька пользователям. Без этих привилегий Oracle не откроет кошелек и не предоставит сертификат веб-серверу, даже если у вас есть правильный пароль. ACL кошелька создается со следующим запуском PL/SQL как SYS:

BEGIN
UTL_HTTP.ASSIGN_WALLET_ACL (
acl          => 'your_acl_name.xdb',
wallet_path  => '/path/to/my/wallet/');
END;
/

После создания ACL кошелька пользователь должен иметь предоставленные ему привилегии.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl         => 'your_acl_name.xml',
principal   => 'MY_USER',
is_grant    =>  TRUE,
privilege   => 'use-client-certificates');
END;
/

Это позволит Oracle открыть кошелек от имени вашего пользователя и представить сертификат на веб-сервер.

Ответ №3

Я хотел бы предложить следующее:

Создайте 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:Oracleproduct11.2.0dbhome_1javavmlibsecuritycacerts"
-file example.com.cer -storepass changeit
```
Используйте функцию в запросе или PL/SQL, например,
```
SELECT PCK_HTTP.GetSSL('https://www.example.com') FROM DUAL
```

Ответ №4

Мы обнаружили, что старые сертификаты в кошельке для сайта https, хотя срок их действия не истек, больше не могут быть использованы. Тест с новыми сертификатами в новом кошельке доказал это. Удаление старого сертификата и добавление новых сертификатов в оригинальный кошелек решило проблему.

Источник

Solution 1

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.

There is this known bug, but it affects versions up to 11.1 apparently:

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

There is also a bug 20323753 registered for 11.2.0.4 recently, still not fixed. Possibly could be the same case as yours.

Solution 2

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.

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.

Comments

We are using Oracle 11g (11.2.0.3.0) and we are receiving the following error when executing a UTL_HTTP call:

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.

Any ideas?

Line 23 is your authentication: UTL_HTTP.SET_AUTHENTICATION(r => l_http_request, username => user_id_chr, PASSWORD => password_chr); Could it be wrong user/password?

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post — you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
Perhaps it could have been worded more directly, but I do provide an answer. First, I explain that the «Fatal SSL error» is likely Oracle not presenting a client certificate. Secondly, I explain that creating a wallet ACL and granting the user a specific privilege will allow Oracle to present the wallet for that user and eliminate that error. Finally, I offer a shortcut for testing connections between Oracle and a web server. Should I rewrite in a more active manner?
As it is, it indeed looks like a combination of questions, suggestions and comments, more than like an answer. It would probably be best to rewrite it in a more answer-like style, if only to prevent it being flagged and deleted as «not an answer».

Recents

Источник

Recently dealed with a SSL issue in an Oracle Database 11.2.0.4 Enterprise Edition environment.
Issue was appearing when testing a SSL web service.
This SSL web serice was called using UTL_HTTP through an Oracle Wallet.

Example of test command :
select UTL_HTTP.request(‘https://<url>’,null,'<wallet_path>’,’wallet_password’) from dual;

The call was ending with «ORA-29273 HTTP Request failed» and «ORA-28860: Fatal SSL error» errors.

I checked the wallet and it was okay..
Certificates were correct and wallet was accessible..(it could be opened and queried)
The database version was 11.2.0.4 enterprise edition.. It was running on an Exadata Cloud at Customer machine.
The database was created using Cloud GUI of the Exadata Cloud at Customer environment.

Anyways; the «Fatal SSL error» seemed so weird to me, so I decided to analyze it further.

I first checked the IP address of the server that was hosting this web service.
Then I checked the route on OS to find the interface that was used when we called this web service.
After finding the interface, I started a tcpdump on it and reproduced the error. (using sqlplus / as sysdba on the database server)

My tcpdump command was as follows;

tcpdump -s 0 -i bondeth0 -w erman.tcpdump

Note that:
I used the option s because -s 0 will set the capture byte to its maximum.
I used the option -w to create an output file for analyzing with Wireshark.
I used -i to specify the Ethernet interface to capture.

After gathering the tcpdump, I opened the file named erman.tcpdump with Wireshark.

I reordered the contents of the file by the destination ip address and directly saw the SSL connection related traffic & packets..

The issue was there..

The server was trying to speak TLS V1.2, but the client (Oracle Database) was not able to handle it.

The real error was «Protocol Version» error.. This means, the Oracle Database which was trying to call the webservice could not handle the TLS 1.2 traffic.

Actually, I had a blog post about another SSL case and in that blog post, I was already mentioning this TLS 1.2 Support of Oracle Database 11.2.0.4 thing.

Here -> https://ermanarslan.blogspot.com/2018/12/rdbms-tls-12-support-and-issues-ora.html

Basically;

Oracle Database needs MESv415 for supporting TLS 1.2 and this MESv415 comes with OCT 2018 DB PSU.. (or Exadata Bundle Patch OCT 2018)

Note that -> as this was an ECC environment, we applied Exadata Bundle OCT 2018.. (DB PSU 2018 had lots of conflicts and incompatabilities with the patches that were applied in Oracle Home which was created by Cloud GUI)

The patch that I applied was Oracle Database
Patch For EXADATA(OCT2018- 11.2.0.4.181016) for Bug 28462975.

Note that: MES is short for RSA BSAFE Micro Edition Suite which is a software development toolkit for building cryptographic, certificate, and Transport Layer Security (TLS) security technologies into C and C++ applications, devices and systems. With release of Oct 2018 PSU, all supported DB versions use RSA BSAFE toolkit MESv415 or greater.

Well.. After patching the database with Exadata Bundle Patch OCT 2018, the issue was fixed.

But actually, I wrote this blog post to show you the analysis part..

As you may already recognize, it is important to do the analysis correctly…

At the end of the day, we analyze the network packets using wireshark.. We also used route command, tcpdump command etc..

Another point that you may derive from this post is that being a good DBA requires more that just the database knowledge