7 Replies Latest reply on Jun 8, 2012 9:21 AM by jcoves

    Changing SMTP server from Apex Application

    jcoves
      Hi,

      One of our customer's requirements is to let application users decide from which SMTP server their e-mails will be sent.

      I've been searching around and I've found it would be possible to achieve this by:

      1) Granting execute on APEX_INSTANCE_ADMIN to our application schema:
       grant execute on APEX_INSTANCE_ADMIN to <SCHEMA>; 
      2) Then executing APEX_INSTANCE_ADMIN.SET_PARAMETER:
      begin
        execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = APEX_040100'; 
        APEX_040100.APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS','SMTP.XXX.XX');
      end;
      I don't particularily like this solution because it opens a security hole. Even though, I tried to implement it but I get the following error:
      Error que empieza en la línea 1 del comando:
      begin
      execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = APEX_040100';
      APEX_040100.APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS','SMTP.XXXX.XXX');
      end;
      Informe de error:
      ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at "SYS.OWA_UTIL", line 356
      ORA-06512: at "SYS.HTP", line 1368
      ORA-06512: at "SYS.HTP", line 1443
      ORA-06512: at "SYS.HTP", line 1735
      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 298
      ORA-20987: User  requires ADMIN privilege to perform this operation.
      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 693
      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 1008
      ORA-06512: at "APEX_040100.WWV_FLOW_INSTANCE_ADMIN", line 98
      ORA-06512: at "APEX_040100.WWV_FLOW_INSTANCE_ADMIN", line 113
      ORA-06512: at line 3
      06502. 00000 -  "PL/SQL: numeric or value error%s"
      *Cause:    
      *Action:
      1) Do you know what am I doing wrong?
      2) Do you have any alternatives to achieve the same goal?

      Thanks

      Edited by: jcoves on 01-jun-2012 1:39
        • 1. Re: Changing SMTP server from Apex Application
          Prabodh
          ORA-20987: User requires ADMIN privilege to perform this operation.
          Means the APP_USER , the one running this application, needs to have Apex Workspace Administrator privileges.

          Regards,
          • 2. Re: Changing SMTP server from Apex Application
            joelkallman-Oracle
            JCoves,

            I don't think this is going to work for you. You said that your requirement is to let application users decide the SMTP server, as in per message. But the path you're going down will change the setting for the entire instance. If the push mail queue job is already running and someone changes this instance setting, this will take effect for the next message that may ready to be pushed from the queue.

            Your parsing user has not been granted the APEX_ADMINISTRATOR_ROLE role. But quite honestly, I cannot even recommend this from a security perspective.

            As you'll see, the call to APEX_MAIL.PUSH_QUEUE has parameters for SMTP host and port. But those are ignored and are only there for legacy purposes. PUSH_QUEUE used to support specification of these values, but they were (correctly) removed for security reasons.

            I'm afraid to say - the only usable alternative I can recommend is to send mail using something like UTL_MAIL, although that has its own challenges.

            Joel
            • 3. Re: Changing SMTP server from Apex Application
              jcoves
              Hi Joel,

              Thanks for your answer. I've thought about what you say. We have the following scenario:

              1) One single instance for our application (so there's no problem in changing smtp configuration at instance level)
              2) All e-mails are sent via custom PROCEDURE which finally uses APEX_MAIL to send and push queue.
              3) The idea is letting only one mail procedure executing at the same time (via MUTEX) so the procedure itself, first locks avoiding other users to send e-mail until it finishes. Then modifies smtp configuration, sends e-mail via APEX_MAIL and pushes queue. After that, restores previous SMTP configuration.

              I don't have it implemented yet, but that was the idea. Other option would be to use a custom SEND_MAIL procedure via UTL_MAIL (as you said) but I don't like that because it could introduce a lot of bugs.

              Thanks
              • 4. Re: Changing SMTP server from Apex Application
                joelkallman-Oracle
                Hi JCoves,

                1) As long as this is the only application on the instance and you don't have to worry about other applications / users pushing the mail queue via APEX_MAIL.PUSH_QUEUE (and not via your method), then this should probably work.

                2) I also recommend disabling the Scheduler job ORACLE_APEX_MAIL_QUEUE. This job runs every 5 minutes, and this has the potential of circumventing your method to exclusively push the queue.

                Joel
                1 person found this helpful
                • 5. Re: Changing SMTP server from Apex Application
                  jcoves
                  Hi Prabodh,

                  How do I grant apex workspace administrator privileges? I thought grating APEX_ADMINISTRATOR_ROLE role to my schema was enough.

                  Thanks
                  • 6. Re: Changing SMTP server from Apex Application
                    jcoves
                    Ok, I solved it, that was a bug with APEX 4.1 (bug #12551169) I upgraded to APEX 4.1.1 and APEX_INSTANCE_ADMIN.SET_PARAMETER now is working fine!
                    • 7. Re: Changing SMTP server from Apex Application
                      jcoves
                      Hi,

                      Finally I decided to create a custom SEND_MAIL procedure in order to achieve sending e-mails from custom SMTP servers. Here is my blog entry with all the information in case it could be helpful to other people:

                      http://www.poveravoce.net/blog/?p=190

                      Edited by: jcoves on 08-jun-2012 2:21