=====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: ORA-24247: network access denied by access control list (ACL) Auf den richtigen Port achten, bin mir ist SSL/TLS unter dem Port 465 zu erreichen. 12c Syntax um die ACL anzulegen: DECLARE v_mail_server varchar2(256):= ''; 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; / 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: ORA-29024: Certificate validation failure Hier darauf achten, das NUR und NUR die "Root Certifikat Chain" in der Wallet sein darf! NICHT das eigentliche Zertifikat der Gegenstelle! === 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 **NICHT** 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: #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))) 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. 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 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: 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 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: 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 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: 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 Wallet per Kommandozeile auslesen: orapki wallet display -wallet D:\oracle\wallet\ .. Subject: CN=smtp.gmail.com,O=Google Inc,L=Mountain View,ST=California,C=US 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: orapki wallet add -wallet d:\oracle\wallet -trusted_cert -cert D:\wiki\apex\pipperr.de.p7b -pwd mywalletpwd 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 !!!!!** Nur die Root CA Zertifikate dürfen importiert werden! Nicht das eigentliche Zertifikat der Gegenstelle verwenden! 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: 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):=''; 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" ' || UTL_TCP.crlf); UTL_SMTP.write_data(v_con, 'to' || ': ' || '"recipient" ' || 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; / **Fehler: ORA-29279: SMTP permanent error: 503 5.5.1 EHLO/HELO first** 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/