Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== 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