Inhaltsverzeichnis
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 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):= '<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; /
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
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:
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 !!!!!
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 ⇒ 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):='<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; /
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:
- 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:
StartTLS
OpenSSL