prog:plsql_send_mail_tls
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
prog:plsql_send_mail_tls [2016/06/07 21:11] – [Mail Versandt mit UTL_SMTP] gpipperr | prog:plsql_send_mail_tls [2016/06/07 21:18] (aktuell) – gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====PL/ | ||
+ | |||
+ | 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, | ||
+ | * Test Mail mit UTL_SMTP versenden | ||
+ | |||
+ | Um das unter Apex zu verwenden siehe auch hier [[prog: | ||
+ | |||
+ | ==== 12c ACL für den Mailversandt definieren ==== | ||
+ | |||
+ | Folgender Fehler wird geworfen, | ||
+ | |||
+ | 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): | ||
+ | BEGIN | ||
+ | DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( | ||
+ | | ||
+ | , lower_port => 465 | ||
+ | , upper_port => 465 | ||
+ | , ace => xs$ace_type(privilege_list => xs$name_list(' | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | siehe auch https:// | ||
+ | |||
+ | |||
+ | ---- | ||
+ | ==== Wallet mit dem richtigen Zertifikat anlegen==== | ||
+ | |||
+ | Tritt dieser Fehler auf, war die Wallet Konfiguration nicht erfolgreich: | ||
+ | < | ||
+ | ORA-29024: Certificate validation failure | ||
+ | </ | ||
+ | |||
+ | <note tip>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 " | ||
+ | |||
+ | Das Zertifikat des Mail Servers darf <fc # | ||
+ | |||
+ | Wie kommen wir aber jetzt an das Zertifikat heran? | ||
+ | |||
+ | == Zertifikat auslesen mit Python == | ||
+ | |||
+ | Siehe dazu https:// | ||
+ | |||
+ | Die beschriebene Python Lösung funktioniert allerdings mit Python 3.4 nicht, daher so abgefragt: | ||
+ | |||
+ | <code python> | ||
+ | #see https:// | ||
+ | |||
+ | import smtplib | ||
+ | import ssl | ||
+ | |||
+ | connection = smtplib.SMTP() | ||
+ | connection.connect(' | ||
+ | connection._host = ' | ||
+ | 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:// | ||
+ | |||
+ | <code bash> | ||
+ | set-item -path ENV: | ||
+ | set-item -path ENV: | ||
+ | |||
+ | #Verbindung zum Server aufbauen | ||
+ | # mit quit beenden! | ||
+ | |||
+ | .\openssl.exe s_client -showcerts -connect mail.your-server.de: | ||
+ | |||
+ | --- | ||
+ | Certificate chain | ||
+ | |||
+ | | ||
+ | -----BEGIN CERTIFICATE----- | ||
+ | |||
+ | .. | ||
+ | |||
+ | -----END CERTIFICATE----- | ||
+ | 1 s:/ | ||
+ | | ||
+ | -----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 | ||
+ | |||
+ | 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 " | ||
+ | |||
+ | {{ : | ||
+ | |||
+ | |||
+ | == 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 " | ||
+ | |||
+ | |||
+ | Wallet aus der DB mit UTL_HTTP Aufrufen: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | DECLARE | ||
+ | lo_req | ||
+ | lo_resp UTL_HTTP.resp; | ||
+ | BEGIN | ||
+ | UTL_HTTP.SET_WALLET (' | ||
+ | lo_req := UTL_HTTP.begin_request(' | ||
+ | lo_resp := UTL_HTTP.get_response(lo_req); | ||
+ | dbms_output.put_line(lo_resp.status_code); | ||
+ | | ||
+ | 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 " | ||
+ | |||
+ | Rechte für oracle_admin vergeben, und schon kann das Wallet gelesen werden! | ||
+ | |||
+ | **ORA-29106: | ||
+ | |||
+ | Falsches Password eingegeben. | ||
+ | |||
+ | |||
+ | |||
+ | **ORA-29024: | ||
+ | |||
+ | Nun kann die Wallet geöffnet werden, es klappt allerdings immer noch nicht, jetzt aber ein anderer Fehler: | ||
+ | |||
+ | <code sql> | ||
+ | select UTL_HTTP.REQUEST(' | ||
+ | |||
+ | ORA-29273: HTTP request failed | ||
+ | ORA-29024: Certificate validation failure | ||
+ | </ | ||
+ | |||
+ | Test Case erzeugt einen " | ||
+ | |||
+ | siehe auch https:// | ||
+ | |||
+ | |||
+ | 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 => ' | ||
+ | , port => 465 | ||
+ | , wallet_path => ' | ||
+ | , wallet_password => ' | ||
+ | , secure_connection_before_smtp => true); | ||
+ | utl_smtp.starttls(c); | ||
+ | END; | ||
+ | / | ||
+ | ERROR at line 1: | ||
+ | ORA-29024: Certificate validation failure | ||
+ | </ | ||
+ | |||
+ | Wallet per Kommandozeile auslesen: | ||
+ | <code bash> | ||
+ | orapki wallet display -wallet D: | ||
+ | .. | ||
+ | Subject: | ||
+ | </ | ||
+ | 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: | ||
+ | </ | ||
+ | |||
+ | |||
+ | 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! </ | ||
+ | |||
+ | 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: | ||
+ | |||
+ | |||
+ | Verbindung zum Mailserver aufbauen: | ||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | v_con | ||
+ | v_reply | ||
+ | v_replies UTL_SMTP.replies; | ||
+ | |||
+ | -- Wallet | ||
+ | v_wallet_path varchar2(256): | ||
+ | -- not use cleartext password in your code! | ||
+ | v_wallet_pwd varchar2(256): | ||
+ | |||
+ | -- Mail Account | ||
+ | v_smtp_server varchar2(256): | ||
+ | | ||
+ | v_mail_username varchar2(200): | ||
+ | -- not use cleartext password in your code! | ||
+ | v_mail_pwd | ||
+ | |||
+ | 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 | ||
+ | , wallet_password => v_wallet_pwd | ||
+ | , secure_connection_before_smtp => TRUE); | ||
+ | |||
+ | IF v_con.host is null | ||
+ | THEN | ||
+ | raise_application_error(-20000, | ||
+ | ELSE | ||
+ | dbms_output.put_line(' | ||
+ | END IF; | ||
+ | |||
+ | |||
+ | dbms_output.put_line(' | ||
+ | |||
+ | v_replies := utl_smtp.ehlo(v_con, | ||
+ | |||
+ | FOR i IN 1..v_replies.COUNT | ||
+ | LOOP | ||
+ | dbms_output.put_line(' | ||
+ | END LOOP; | ||
+ | |||
+ | |||
+ | | ||
+ | -- check if relay needed if secure_connection_before_smtp => TRUE with the connection | ||
+ | | ||
+ | -- | ||
+ | --IF v_reply.code != 220 | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | --END IF; | ||
+ | |||
+ | | ||
+ | |||
+ | | ||
+ | | ||
+ | , username | ||
+ | , password | ||
+ | , 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 | ||
+ | | ||
+ | else | ||
+ | | ||
+ | END IF; | ||
+ | |||
+ | |||
+ | -- Create a basic E-Mail | ||
+ | UTL_SMTP.helo(v_con, | ||
+ | UTL_SMTP.mail(v_con, | ||
+ | UTL_SMTP.rcpt(v_con, | ||
+ | |||
+ | UTL_SMTP.open_data(v_con); | ||
+ | |||
+ | -- Header of the mail | ||
+ | UTL_SMTP.write_data(v_con, | ||
+ | UTL_SMTP.write_data(v_con, | ||
+ | UTL_SMTP.write_data(v_con, | ||
+ | |||
+ | |||
+ | --Text of the Mail | ||
+ | UTL_SMTP.write_data(v_con, | ||
+ | |||
+ | -- 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, | ||
+ | END; | ||
+ | When others then | ||
+ | | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | |||
+ | **<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:// | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | |||
+ | |||
+ | Support: | ||
+ | |||
+ | * https:// | ||
+ | * Troubleshooting ORA-29024: | ||
+ | * UTL_HTTP Fails With ORA-29024 After Importing WebServer' | ||
+ | |||
+ | Web: | ||
+ | * https:// | ||
+ | * http:// | ||
+ | |||
+ | StartTLS | ||
+ | * https:// | ||
+ | |||
+ | OpenSSL | ||
+ | * https:// | ||
+ | |||