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.
Вопрос:
Мы используем 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:\Oracle\product\11.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
Ответ №4
Мы обнаружили, что старые сертификаты в кошельке для сайта https, хотя срок их действия не истек, больше не могут быть использованы. Тест с новыми сертификатами в новом кошельке доказал это. Удаление старого сертификата и добавление новых сертификатов в оригинальный кошелек решило проблему.
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.
I have installed Oracle databese 18c XE, and Oracle APEX 21.2.0,
I was getting the below error when trying to (Create REST Data Source) for APEX Application
“ORA-24247: network access denied by access control list(ACL)”
after doing some research I connected sys as sysdba using SQL Plus, and then executed the below:
ALTER SESSION SET CONTAINER = XEDBA1;
after that I executed the below commands:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
principal_name => 'APEX_210200',
principal_type => xs_acl.ptype_db));
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
principal_name => 'MDS_SCHEMA',
principal_type => xs_acl.ptype_db));
END;
/
ACL Commands
where:
APEX210200 is the INTERNAL workspace schema
MDSSCHEMA is the schema for the workspace which the application was build
Now I’m getting the error “ORA-28860: Fatal SSL error” when trying to create REST Data Source
Create REST Data Source
ORA-28860
And I can’t send emails knowing that SMTP server is set correctly
Please help so I can Create REST Data Source and send Emails through my APEX application
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 