Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_send_mail_tls

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung
Vorhergehende Überarbeitung
prog:plsql_send_mail_tls [2016/06/07 21:11] – [Mail Versandt mit UTL_SMTP] gpipperrprog:plsql_send_mail_tls [2016/06/07 21:18] (aktuell) gpipperr
Zeile 1: Zeile 1:
 +=====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 (
 +                        => 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/
 +