How to configure GMAIL with Oracle Apex using stunnel (application express) 5.0.3

0down votefavorite

Hello All,

writing down as is it helps someone :-)

This was enabled by me on both linux & windows. no SMTP is required.

I am in the middle of project, so don't have much time for editing ....feel free to take care.

Background :

• Apex 5.0.3 is installed with Oracle XE 11.2 installed on Centos (linux) 6.x
• SMTP is not required to be enabled on centos. We will use gmail one directly.
• Solution is based on windows one published by : HÅVARD KRISTIANSEN at :http://monkeyonoracle.blogspot.com/2009/11/plsql-and-gmail-or-utlsmtp-with-ssl.html
• Will use stunnel to communicate with gmail directly.
• I am not going to go over Apex / Oracle XE installation.

1.Install stunnel as root :

<span class="pln" style="color: #2e3133;">yum install stunnel </span><span class="pun" style="color: #2e3133;">-</span><span class="pln" style="color: #2e3133;">y</span>

2.create a conf file for stunnel using nano or vi (to install nano : yum install nano -y )

<span class="pln" style="color: #2e3133;">nano </span><span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">etc</span><span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">stunnel</span><span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">stunnel</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">conf</span>

3.Enter the following to new stunnel.conf created:

<span class="pun" style="color: #2e3133;">;</span><span class="pln" style="color: #2e3133;"> </span><span class="kwd" style="color: #00008b;">Use</span><span class="pln" style="color: #2e3133;"> it </span><span class="kwd" style="color: #00008b;">for</span><span class="pln" style="color: #2e3133;"> client mode</span>
client = yes


[ssmtp]
accept  = 1925
connect = smtp.gmail.com:465

4.restart stunnel using whatever method, e.g kill -9 and start using the following command :

<span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">usr</span><span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">bin</span><span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">stunnel</span>

5.Log in to apex as admin : http://yourhost:port/ords/apex_admin

<span class="pln" style="color: #2e3133;">Go </span><span class="kwd" style="color: #00008b;">to</span><span class="pln" style="color: #2e3133;"> </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> Manage instance </span><span class="pun" style="color: #2e3133;">-></span><span class="pln" style="color: #2e3133;"> instance settings</span><span class="pun" style="color: #2e3133;">.</span>

6.Put the following settings :

<span class="pln" style="color: #2e3133;">SMTP Host Address </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> </span><span class="lit" style="color: #6b291b;">127.0.0.1</span><span class="pln" style="color: #2e3133;"> </span><span class="pun" style="color: #2e3133;">(</span><span class="kwd" style="color: #00008b;">or</span><span class="pln" style="color: #2e3133;"> your local</span><span class="pun" style="color: #2e3133;">)</span>
<span class="pun" style="color: #2e3133;"></span><span class="pln" style="color: #2e3133;">SMTP Host Port </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> </span><span class="lit" style="color: #6b291b;">1925</span><span class="pln" style="color: #2e3133;"> </span><span class="pun" style="color: #2e3133;">(</span><span class="kwd" style="color: #00008b;">as</span><span class="pln" style="color: #2e3133;"> u can see </span><span class="kwd" style="color: #00008b;">in</span><span class="pln" style="color: #2e3133;"> stunnel</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">conf above</span><span class="pun" style="color: #2e3133;">).</span><span class="pln" style="color: #2e3133;"> </span>
<span class="pln" style="color: #2e3133;">SMTP Authentication Username </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> your gmail username </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> etai</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">guday </span><span class="pun" style="color: #2e3133;">(</span><span class="pln" style="color: #2e3133;">WITHOUT </span><span class="pun" style="color: #2e3133;">@</span><span class="pln" style="color: #2e3133;">gmail</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">com</span><span class="pun" style="color: #2e3133;">)</span><span class="pln" style="color: #2e3133;"> </span>
<span class="pln" style="color: #2e3133;">SMTP Authentication Password </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> gmail password </span>
<span class="pln" style="color: #2e3133;"></span><span class="kwd" style="color: #00008b;">Use</span><span class="pln" style="color: #2e3133;"> SSL</span><span class="pun" style="color: #2e3133;">/</span><span class="pln" style="color: #2e3133;">TLS </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> <strong>NO </strong></span>
<span class="pln" style="color: #2e3133;"></span><span class="kwd" style="color: #00008b;">Default</span><span class="pln" style="color: #2e3133;"> </span><span class="pln" style="color: #2e3133;">Email </span><span class="kwd" style="color: #00008b;">From</span><span class="pln" style="color: #2e3133;"> Address </span><span class="pun" style="color: #2e3133;">:</span><span class="pln" style="color: #2e3133;"> etai</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">guday</span><span class="pun" style="color: #2e3133;">@</span><span class="pln" style="color: #2e3133;">gmail</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">com </span><span class="pun" style="color: #2e3133;">(</span><span class="pln" style="color: #2e3133;">including </span><span class="pun" style="color: #2e3133;">@</span><span class="pln" style="color: #2e3133;">gmail</span><span class="pun" style="color: #2e3133;">.</span><span class="pln" style="color: #2e3133;">com</span><span class="pun" style="color: #2e3133;">)</span>

7.Due to gmail restrictions YOU MUST enable : https://g.co/allowaccess to use the above method (with relevant gmail account)

8. Enable Oracle DB ACL by using example bellowhttps://www.dropbox.com/s/2ieaawy5gme9a50/email_configuration_acl_for_apex.txt?dl=0

9 . should work :-) didn't have time to test it further or refer to security issues etc