Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====PL/SQL  12c - Eine Mail mit DBMS_SMTP verschlüsselt versenden=====

Aufgabe: Mit Hilfe von einem PL/SQL Package sollen E-Mails aus der Datenbank versandt werden.
Dabei soll der Versandt verschlüsselt erfolgen und der Mailserver verlangt eine Anmeldung mit einem User Account.

Ablauf:
  * Rechte für den User auf das Netzwerk der Datenbank erlauben (12c Syntax!) - ACL hinterlegen
  * Wallet für SSL mit dem Stammzertifikat des Mailservers ausstellen (nur das Stammzertifikat, nicht das Zertifikat des Mail Servers verwenden!
  * Test Mail mit UTL_SMTP versenden

Um das unter Apex zu verwenden siehe auch hier [[prog:apex_mail_acl|Oracle Apex 5.0 Mail Versandt mit SSL -Hinterlegen einer ACL's in 11g und 12c und Hinterlegen von SSL Zertifikaten]]

==== 12c ACL für den Mailversandt definieren ====

Folgender Fehler wird geworfen,  falls die ACL nicht gesetzt ist: <fc #800000>ORA-24247: network access denied by access control list (ACL)</fc>

Auf den richtigen Port achten, bin mir ist SSL/TLS unter dem Port 465 zu erreichen.

12c Syntax um die ACL anzulegen:
<code plsql>
    DECLARE
      v_mail_server varchar2(256):= '<my_mailserver_ip>';
    BEGIN
        DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
               host       => v_mail_server
            ,  lower_port => 465
            ,  upper_port => 465
            ,  ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                                         principal_name => 'apex_050000',
                                         principal_type => xs_acl.ptype_db)
        );
    END;
    /
</code>

siehe auch https://docs.oracle.com/database/121/DBSEG/fine_grained_access.htm#DBSEG40012


----
==== Wallet mit dem richtigen Zertifikat anlegen====

Tritt dieser Fehler auf, war die Wallet Konfiguration nicht erfolgreich:
<code>
ORA-29024: Certificate validation failure
</code>

<note tip>Hier darauf achten, das NUR und NUR die "Root Certifikat Chain" in der Wallet sein darf! 
NICHT das eigentliche Zertifikat der Gegenstelle! </note>

=== Oracle Wallet anlegen===


Eine Oracle Wallet anlegen, zum Beispiel in "D:\oracle\wallet\  mit dem Oracle Wallet Programm aus dem Datenbank Home eine Wallet mit einem Password erzeugen.

Das Zertifikat des Mail Servers darf <fc #ff0000>**NICHT**</fc> n der Wallet hinterlegt werden, wir brauchen aber die "Root Chain" hinter diesem Zertifikat!

Wie kommen wir aber jetzt an das Zertifikat heran?

== Zertifikat auslesen mit Python ==

Siehe dazu https://support.google.com/a/answer/6180220

Die beschriebene Python Lösung funktioniert allerdings mit Python 3.4 nicht, daher so abgefragt:

<code python>
#see https://support.google.com/a/answer/6180220

import smtplib
import ssl

connection = smtplib.SMTP() 
connection.connect('smtp.gmail.com')
connection._host = 'smtp.gmail.com'
connection.ehlo()
connection.starttls()

print( ssl.DER_cert_to_PEM_cert(connection.sock.getpeercert(binary_form=True)))

</code>

Nach einigem Ausprobieren mit den Zertifikaten habe ich dann erkennen müssen, dass wir das aber nur brauchen, um die Zertifikat Kette rückwärts zu analysieren! D.h. wir erkennen nur mit wer das Zertifikat signiert hat.

== Auslesen mit openssl ==

Für Windows => https://sourceforge.net/projects/openssl/ , herunterladen und z.B. nach c:\tools auspacken.

<code bash>
set-item -path ENV:RANDFILE     -value C:\tools\openssl-1.0.2d-fips-2.0.10\bin\.rnd
set-item -path ENV:OPENSSL_CONF -value C:\tools\openssl-1.0.2d-fips-2.0.10\bin\openssl.cnf

#Verbindung zum Server aufbauen
# mit quit beenden!

.\openssl.exe s_client -showcerts -connect mail.your-server.de:465

---
Certificate chain

   i:/C=US/O=GeoTrust Inc./CN=RapidSSL SHA256 CA - G3
-----BEGIN CERTIFICATE-----

..

-----END CERTIFICATE-----
 1 s:/C=US/O=GeoTrust Inc./CN=RapidSSL SHA256 CA - G3
   i:/C=US/O=GeoTrust Inc./CN=GeoTrust Global CA
-----BEGIN CERTIFICATE-----
MIIEJTCCAw2gAwIBAgIDAjp3MA0GCSqGSIb3DQEBCwUAMEIxCzAJBgNVBAYTAlVT
....



quit

</code>

Die Zertifikate werden angezeigt, auf die Letzen beiden kommt es uns an! 

Diese über die Zwischenablage aus dem Spool kopieren (Bereich ------BEGIN CERTIFICATE  ...  bis END CERTIFICATE-----)

Diese beiden Blöcke müssen dann in der Wallet als Text eingefügt werden!



== Auslesen mit Google Chrome ==

Am einfachsten geht es, wenn wir Glück haben und der Web Sever die gleichen Root Zertifikate verwendet, in Google Chrome lassen sich die Zertifikate recht bequem extrahieren.

== Zertifikat hinterlegen ==

Jetzt das eigentliche Zertifikat NICHT im Wallet hinterlegen, 

**Nur die Root Chain Zertifikate** entweder als Text in der Wallet als "Geschütztes Zertifikat" hinterlegt:

{{ :prog:apex:apex_wallet_mail_zert.png?direct&300 | Oracle Wallet Zertifikat hinterlegen }}


== Rechte auf den Oracle Datenbank User vergeben==

Die DB läuft ja unter Windows mit einem eigenen Anwender, diesen nun Leserechte auf die Wallet erteilen!



=== Fehlersuche===

Klappt aber nicht so wie gewünscht .... immer noch der gleiche Fehler.

Wie kann nun der Fehler analysiert werden????


**Der erste Fehler ist nun ein "ORA-28759: failure to open file"**


Wallet aus der DB mit UTL_HTTP Aufrufen:

<code sql>

DECLARE
    lo_req  UTL_HTTP.req;
    lo_resp UTL_HTTP.resp;
 BEGIN
    UTL_HTTP.SET_WALLET ('file:D:\oracle\wallet','mywalletpwd');
    lo_req := UTL_HTTP.begin_request('https://www.pipperr.de/');
    lo_resp := UTL_HTTP.get_response(lo_req);
    dbms_output.put_line(lo_resp.status_code);
   UTL_HTTP.end_response(lo_resp);
END;

ERROR at line 1:
ORA-29273: HTTP request failed
ORA-28759: failure to open file

</code>

Als erstes prüfen, ob die Lese Rechte auf der Wallet zum User der DB passen unter dem die Oracle Instance läuft!

Unter Windows läuft ja der Oracle Prozess unter einen anderen User, in meinem Fall unter "oracle_admin", das Wallet wurde aber unter meinen User anlegt, dann klappt das nicht! 

Rechte für oracle_admin vergeben, und schon kann das Wallet gelesen werden!

**ORA-29106: Cannot import PKCS #12 wallet**

Falsches Password eingegeben.  .-(



**ORA-29024: Certificate validation failure** 

Nun kann die Wallet geöffnet werden, es klappt allerdings immer noch nicht, jetzt aber ein anderer Fehler:

<code sql>
select UTL_HTTP.REQUEST('https://www.pipperr.de',null,'file:D:\oracle\wallet','mywalletpwd') FROM DUAL;

ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
</code>

Test Case erzeugt einen "ORA-29024: Certificate validation failure" siehe dazu unter 12c auch UTL_HTTP Fails With ORA-29024 After Importing WebServer's Certificate into the Oracle Wallet (Doc ID 1682823.1), obwohl meine Websithe TLS 1.2  anbietet.

siehe auch https://oracle-base.com/articles/misc/utl_http-and-ssl


Teste jetzt mal mit Google Mail, erhalten aber wieder einen Zertifikatsfehler obwohl das Zertifikat hinterlegt ist:
<code plsql>
DECLARE
	c utl_smtp.connection;
BEGIN
	c := utl_smtp.open_connection(
		  host => 'smtp.gmail.com'
		, port => 465
		, wallet_path => 'file:D:\oracle\wallet'
		, wallet_password => 'mywalletpwd'
		, secure_connection_before_smtp => true);
	utl_smtp.starttls(c);
END;
/
ERROR at line 1:
ORA-29024: Certificate validation failure
</code>

Wallet per Kommandozeile auslesen:
<code bash>
orapki wallet display -wallet D:\oracle\wallet\
..
Subject:        CN=smtp.gmail.com,O=Google Inc,L=Mountain View,ST=California,C=US
</code>
Zertifikat ist da!


Es könnte aber auch daran liegen das die ganze Kette eines Zertifikates abgebildet werden muss, also Root CA usw. auch importieren!

**Root CA Zertifikate importieren**

Als erstes Google ausprobiert und alle 3 Zertifikaten nacheinander in die Wallet geladen, mit UTL_HTTP testet, das funktioniert!


Mit pipperr.de ist es aber immer noch der gleiche Fehler ....

Es stimmt also etwas mit den Zertifikaten nicht!

Im nächsten Schritt alle Zertifikate aus der Wallet entfernt und mit Chrome die Zertifikate als pb7 und inkl. der ganzen Kette exportiert.

Den Export per Kommando Zeile eingefügt:
<code bash>
orapki wallet add -wallet d:\oracle\wallet -trusted_cert -cert D:\wiki\apex\pipperr.de.p7b -pwd mywalletpwd
</code>


Wieder getestet, leider immer noch kein Erfolg.

Im nächsten Schritt ** NUR die beiden Root Zertifikate in der Wallet belassen**, die Wallet speichern UND in SQL*Plus NEUE Session starten!

**Es geht !!!!!**


<note tip>Nur die Root CA Zertifikate dürfen importiert werden! 
Nicht das eigentliche Zertifikat der Gegenstelle verwenden! </note>

Die Suche nach diesem Problem hat mich einen ganzen Nachmittag gekostet ... 
----

==== Mail Versandt mit UTL_SMTP ====


Für das Öffnen der Wallet und für die Verbindung zum Mailserver ist ein Password notwendig, es ist zum Empfehlen das Password nicht in Klarschrift sondern verschlüsselt zu hinterlegen => [[dba:passwort_in_psql_schuetzen|Passwörter und ähnliche Schlüssel in PL/SQL Packages schützen]].


Verbindung zum Mailserver aufbauen:
<code plsql>
DECLARE
  v_con     UTL_SMTP.connection;
  v_reply   UTL_SMTP.reply;
  v_replies UTL_SMTP.replies;
   
  -- Wallet
  v_wallet_path varchar2(256):='file:C:\oracle\client_wallet';
  -- not use cleartext password in your code!
  v_wallet_pwd varchar2(256):='mywalletpwd11';

  -- Mail Account 
  v_smtp_server varchar2(256):='mail.mydoamin.de';
  
  v_mail_username varchar2(200):='my_user@mydoamin.de';  
  -- not use cleartext password in your code!
  v_mail_pwd      varchar2(200):='<not_use_pwd_in_code>';
   
BEGIN
 
	-- Open the Connection
	-- use the Wallet
	-- Login to the mail server
 
	v_con := UTL_SMTP.open_connection(
          host            => v_smtp_server
        , port            => 465
        , wallet_path     => v_wallet_path
        , wallet_password => v_wallet_pwd
        , secure_connection_before_smtp => TRUE);  
 
	IF v_con.host is null
	THEN
      raise_application_error(-20000, '--Error :: utl_smtp.open_connection: '||SQLERRM);
	ELSE 
      dbms_output.put_line('--Info : Connect to ::'||v_con.host)   ;
	END IF;

	
	dbms_output.put_line('--Info : check with ehlo the features of the SMTP Gateway');

	v_replies := utl_smtp.ehlo(v_con, v_smtp_server);

	FOR i IN 1..v_replies.COUNT
	LOOP
		dbms_output.put_line('--Info :: EHLO :: '||v_replies(i).code||' - '||v_replies(i).text);
	END LOOP;
 
 
   --start secure Connection
   -- check if relay needed if secure_connection_before_smtp => TRUE with the connection 
   --v_reply:=UTL_SMTP.starttls(v_con);        
   --
   --IF v_reply.code != 220
   --THEN
   --   raise_application_error(-20001, '--Error :: utl_smtp.starttls: '||v_reply.code||' - '||v_reply.text);
   --else
   --    dbms_output.put_line('--Info :: utl_smtp.starttls: '||v_reply.code||' - '||v_reply.text);
   --END IF;
 
   dbms_output.put_line('--Info : login to the Mail server with the username::'||v_mail_username);
   
   v_reply:= UTL_SMTP.AUTH (
               c          => v_con
             , username   => v_mail_username
             , password   => v_mail_pwd
             , schemes    => utl_smtp.all_schemes);
			 -- Try first to use this features to avoid plain Text PWD => UTL_SMTP.NON_CLEARTEXT_PASSWORD_SCHEMES
    
	-- check if this was sucessfull!
    IF v_reply.code != 235
    THEN
       raise_application_error(-20002, '--Error :: utl_smtp.auth: '||v_reply.code||' - '||v_reply.text);
    else
       dbms_output.put_line('--Info :: utl_smtp.auth: '||v_reply.code||' - '||v_reply.text);
	END IF;  
 

	-- Create a basic E-Mail
	UTL_SMTP.helo(v_con, 'pipperr.de');
	UTL_SMTP.mail(v_con, 'info@pipperr.de');
	UTL_SMTP.rcpt(v_con, 'info@pipperr.de');
	
	UTL_SMTP.open_data(v_con);
	
	-- Header of the mail 
	UTL_SMTP.write_data(v_con, 'from'     || ': ' || '"sender" <info@pipperr.info>'  || UTL_TCP.crlf);
	UTL_SMTP.write_data(v_con, 'to'       || ': ' || '"recipient" <info@pipperr.de>' || UTL_TCP.crlf);
	UTL_SMTP.write_data(v_con, 'subject'  || ': ' || 'This is a Test Mail'           || UTL_TCP.crlf);   
	
	
	--Text of the Mail
	UTL_SMTP.write_data(v_con, UTL_TCP.crlf || 'hello, world!');
	
	-- Close the Mail
	UTL_SMTP.close_data(v_con);
	
	
	-- Close the connection to the mail server
	UTL_SMTP.quit(v_con);
 
EXCEPTION
  WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
		BEGIN
		  UTL_SMTP.quit(v_con);
		EXCEPTION
		  WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
			-- ignore if the server is meanwhile down
			NULL;
		  When others then
			raise_application_error(-20003,SQLCODE||' - '||SQLERRM);
	   END;
   When others then
     raise_application_error(-20004,SQLCODE||' - '||SQLERRM);
END;
/
</code>


**<fc #800000>Fehler: ORA-29279: SMTP permanent error: 503 5.5.1 EHLO/HELO first</fc>**
Die UTL_SMTP.starttls(v_con) verursacht das Problem, hmm, kann das an dem Mail Gateway liegen? 
Laut Doku sollte das so gehen?? Kommentiert man das aus, geht die Mail raus, ist aber auch alles richtig verschlüsselt??


In Arbeit, demnächst mehr, wenn ich das gelöst habe ....


12c UTL_SMTP => https://docs.oracle.com/database/121/ARPLS/u_smtp.htm#ARPLS074

----

==== Quellen ====



Support:

  * https://community.oracle.com/thread/2593054?start=0&tstart=0
  * Troubleshooting ORA-29024:Certificate Validation Failure (Doc ID 756978.1)
  * UTL_HTTP Fails With ORA-29024 After Importing WebServer's Certificate into the Oracle Wallet (Doc ID 1682823.1)

Web:
  * https://oracle-base.com/articles/misc/email-from-oracle-plsql#html
  * http://navtechno.blogspot.de/2015/09/send-html-mail-through-oracle-in-multi.html

StartTLS
  * https://mikepargeter.wordpress.com/2012/11/06/utl_smtp-starttls/

OpenSSL
  * https://langui.sh/2009/03/14/checking-a-remote-certificate-chain-with-openssl/