Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:apex_mail_acl

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
prog:apex_mail_acl [2018/03/08 10:21] – [Script um die ACL's zu überwachen] gpipperrprog:apex_mail_acl [2019/10/27 10:23] (aktuell) – [Script um die ACL's zu überwachen] gpipperr
Zeile 1: Zeile 1:
 +===== Oracle Apex 5.0 Mail Versandt mit SSL -Hinterlegen einer ACL's in 11g und 12c und Hinterlegen von SSL Zertifkaten=====
  
 +** 11.2016 **
 +
 +Um einen Mail Server zu hinterlegen, an den "INTERNAL" Workspace anmelden und dort die Daten des Mail Server eingeben.
 +
 +
 +Menüpunkt oben "Manage Instance", in "Manage Instance" "Instance Settings" wählen, Reiter "Email auswählen"
 +
 +{{ :prog:apex:apex_instance_mail_settings.png?400 | Apex Instance Mail Settings }}
 +
 +
 +Nach dem Eintragen der Mail Server Konfiguration, kann über den "Password vergessen" Dialog beim Login in Apex mit einem existierenden normalen User (NICHT den Admin User des Internal Workspaces nehmen!) getestet werden, ob der Mail Versandt funktioniert.
 +
 +Nach dem Anfordern eines neuen Passworts per Mail, wieder an den "INTERNAL" Workspace anmelden , Menüpunkt "Monitor Activity",  Auf "Monitor Activity"  "Mail Log" auswählen und prüfen, ob die Mail versandt wurde.
 +
 +Wenn hier nichts zu finden ist über "Manage Instance" "Mail Queue" die Mail Queue prüfen.
 +
 +In der Apex Mail Queue sollte keine Mails mehr stehen, falls doch ist meist das Problem die fehlende / falsche ACL: Access Control List.
 +
 +{{ :prog:apex:apex_mailqueue_fehler.png?direct&400 | Apex Mail Queue ACL fehler}}
 +
 +<fc #800000>Der Fehler ist meist ein:</fc>
 +
 +<code>
 +ORA-24247: network access denied by access control list (ACL)
 +</code>
 +
 +Je nach DB Version 11g oder 12c muss nun das Rechte das Netzwerk der Datenbank zu verwenden an Apex gegranted werden. 
 +
 +Die ACL nun für den Mail Server passend (Server Name/IP Adresse + Port ) anlegen und mit "Send all Mail" prüfen ob es dann auch klappt. (siehe weiter unten für die jeweilige DB die ACL Routinen als Beispiel).
 +
 +
 +Diese Rechte setzen und mit "Force send All Mail" einen erneuten Versandt antriggern.
 +
 +
 +Mit einer aktiven TLS/SSL Mail Konfiguration erhalten ich allerdings gleich mal den nächsten Fehler:
 +
 +<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>
 +
 +
 +----
 +
 +
 +==== Mailversandt per TLS/SSL - "ORA-29024: Certificate validation failure" beheben===
 +
 +Hier muss nun eine Oracle Wallet in der mit dem Root Zertifikaten des Mail Servers hinterlegt werden
 +
 +Siehe dazu:
 +
 +  * APEX_
 +    * => http://docs.oracle.com/database/121/AEADM/adm_mg_service_set.htm#AEADM214
 +  * Datenbank
 +    * => https://docs.oracle.com/cd/E11882_01/network.112/e40393/asowalet.htm#ASOAG160
 +
 +
 +Also eine Oracle Wallet anlegen, zum Beispiel in "D:\oracle\wallet\  und in den Apex "Instance Settings" in der Wallet Section hinterlegen.
 +
 +Das Zertifikat des Mail Servers darf <fc #ff0000>**NICHT**</fc> in der Wallet hinterlegt werden, wir brauchen aber die "Root Chain" hinter diesem Zertifikat!
 +
 +=== 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('mail.your-server.de')
 +connection._host = 'mail.your-server.de'
 +connection.ehlo()
 +connection.starttls()
 +
 +print( ssl.DER_cert_to_PEM_cert(connection.sock.getpeercert(binary_form=True)))
 +
 +</code>
 +
 +Nach einigen Ausprobieren mit den Zertifikaten habe ich dann erkennen müssen, das wir das aber nur brauchen, um die Zertifikat Kette rückwärts zu analysieren!
 +
 +=== Auslesen mit openssl unter Linux===
 +
 +<code bash>
 +openssl s_client -showcerts -mail.your-server.de:456 </dev/null 2>/dev/null|openssl x509 -outform PEM > mymail.pem
 +</code>
 +
 +=== Zertifikat hinterlegen ===
 +
 +
 +Jetzt das eigentliche Zertifikat NICHT im Wallet hinterlegen, 
 +
 +**Nur die Root Chain Zertifikate** entwender als Text in der Wallet als "Geschütztes Zertifkat" hinterlegt:
 +
 +{{ :prog:apex:apex_wallet_mail_zert.png?direct&300 | Oracle Wallet Zertifikat hinterlegen }}
 +
 +
 +
 +=== Wallet Location in Apex hinterlegen ===
 +
 +
 +
 +Die Wallet Location und das Passwort wird in Apex hinterlegt, Apex „Instance Settings“  Reiter "Wallet"
 +
 +{{ :prog:apex:apex5_mail_set_wallet.png?direct&300 | Wallet in Apex 5 Settings hinterlegen }}
 +
 +Auf den gesetzen Hacken bei "Check to confirm that you wish to change the wallet password" achten!
 +
 +
 +
 +=== Fehlersuche===
 +
 +Klappt aber nicht so wie gewünscht .... immer noch der gleiche Fehler.
 +
 +Wie kann nun der Fehler analysiert werden????
 +
 +
 +**Password nicht hinterlegt**
 +
 +Die Apex Parameter prüfen, ob das Password auch wirklich hinterlegt ist:
 +
 +<code sql>
 +connect / as sysdba
 +
 +SELECT  APEX_INSTANCE_ADMIN.GET_PARAMETER('WALLET_PATH') as WALLET_PATH
 +       , APEX_INSTANCE_ADMIN.GET_PARAMETER('WALLET_PWD') as WALLET_PWD
 + FROM DUAL
 + /
 +
 +</code>
 +
 +Beim ersten Mal war das Passwort leer, erst wenn in der Apex Admin Maske der Hacken für das Passwort gesetzt wird, wird auch mit diesen Befehlt das Passwort angezeigt! 
 +
 +<fc #800000>Security BUG?  Das Password wird in Klarschrift angezeigt!</fc>
 +
 +
 +**Der nächste 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 nächstes 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 meine 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-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 ... was für ein Mist......
 +
 +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)
 +
 +----
 +
 +
 +----
 +
 +
 +
 +
 +
 +
 +
 +
 +==== ACL einrichten in 12g ====
 +
 +
 +Auf den richtigen Port achten, bin mir ist SSL/TLS unter dem Port 465 zu ereichen.
 +<code plsql>
 +    BEGIN
 +        DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
 +            host => '<my_mailserver_ip>'
 +            ,  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
 +
 +Siehe dazu "Oracle Application Express Application Builder User's Guide -> Managing Application Security"
 +----
 +
 +==== ACL einrichten in 11g ====
 +
 +ACL anlegen:
 +<code plsql>
 +begin
 +  dbms_network_acl_admin.create_acl (
 +      acl         => 'utl_mail.xml'
 +    , description => 'Allow mail to be send'
 +    , principal   => 'apex_050000'
 +    , is_grant    => TRUE
 +    , privilege   => 'connect'
 +    );
 +    commit;
 +end;
 +</code>
 +
 +Privileg hinzufügen:
 +<code plsql>
 +--add privileges like the ‘resolve’ privilege:
 +begin
 + dbms_network_acl_admin.add_privilege (
 +   acl       => 'utl_mail.xml'
 +  , principal => 'apex_050000'
 +  , is_grant  => TRUE
 +  , privilege => 'resolve'
 +  );
 +  commit;
 +end;
 +</code>
 +
 +ACL Zuordnen auf den Name/IP Adresse des eigentlichen Mail Servers:
 +<code plsql>
 +begin
 +  dbms_network_acl_admin.assign_acl(
 +     acl  => 'utl_mail.xml'
 +   , host => '<my_smtp_sever>'
 +  );
 +  commit;
 +end;
 +</code>
 +
 +
 +----
 +
 +----
 +
 +
 +f==== Relay für Localhost Konfiguration====
 +
 +siehe => http://emailrelay.sourceforge.net/
 +
 +
 +----
 +
 +==== Script um die ACL's zu überwachen ====
 +
 +Siehe auch unter https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/acl.sql
 +
 + {{scrape>https://raw.githubusercontent.com/gpipperr/OraPowerShell/master/Ora_SQLPlus_SQLcL_sql_scripts/acl.sql}}.
 +
 +
 +----
 +==== Quellen ====
 +
 +11g
 +  * http://blog.whitehorses.nl/2010/03/17/oracle-11g-access-control-list-and-ora-24247/
 +
 +
 +SSL
 +  * http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_19.shtml
 +  * https://blog.iadvise.eu/2013/09/16/accessing-ssl-encrypted-websites-using-utl_http-and-oracle-wallet-manager/
 +  * http://all-dba.blogspot.de/2014/11/ora-29024-certificate-validation.html
prog/apex_mail_acl.txt · Zuletzt geändert: 2019/10/27 10:23 von gpipperr