6 Replies Latest reply: Apr 26, 2014 10:25 AM by jstem1177 RSS

    Change APEX SMTP Server from APEX 4.2.2

    jstem1177

      Hello All,

       

      We I've gone through all the post and tried every trick in the book. Maybe I'm at wits end and forgot sometething.

       

      Short and sweet. I'm trying to change SMTP_HOST_ADDRESS from APEX ON-DEMAND PROCESS.

       

      Just to root out privileges, I've granted the below to both the owner of the procedure and the APEX_PUBLIC_USER and rebooted the database and apex.

       

      grant apex_administrator_role to mtz;
      grant execute on apex_instance_admin to mtz;
      grant apex_administrator_role to apex_public_user;
      grant execute on apex_instance_admin to apex_public_user;
      

       

      As mentioned, in other threads, there does not seem to be any problems executing this directly from SQL Developer.

       

      BEGIN
              APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', '192.168.2.1');
              COMMIT;
      END;
      

       

      Just to be sure, I added a change of schema, which from some reason works in SQL developer but not inside the ON-DEMAND PROCESS.

       

      BEGIN
              execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = APEX_40200';
              APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', '192.168.2.1');
              COMMIT;
              execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = APEX_PUBLIC_USER';
      END;
      

       

      I've been at this for several hours, and just don't understand why it just simply will not work! Unfortunately, I really don't have any other options and need to implement it this way

       

      Thansk in advance for any assistance or advice.

       

      Jan S.

        • 1. Re: Change APEX SMTP Server from APEX 4.2.2
          jariola

          Hi,

           

          I'm just curious what is use case for this ?

           

          APEX do not work with roles.

          I think It is very insecure if you grant those privileges to APEX_PUBLIC_USER.

           

          Regards,

          Jari

          • 2. Re: Change APEX SMTP Server from APEX 4.2.2
            jstem1177

            Hello,

             

            Im building an application and my end users know absolutely nothing of APEX, workspaces etc. However they understand perfectly configuration and settings. So I've got a window with the 5-6 parameters that pertain to SMTP server settings and this allows them to configure it without ever needing to switch to the INTERNAL workspace, logins with different ADMIN privileges etc

             

            I know about the security issue, this was simply to show that even with that it doesn't work.

             

            Jan S.

            • 3. Re: Change APEX SMTP Server from APEX 4.2.2
              jariola

              Hi,

               

              Ok,

               

              I just think it that e.g. APEX_INSTANCE_ADMIN api , is not meant for develop side.

              It is more for instance admins (dba), when you have run only instance.

               

              You can always code your own email api using e.g. UTL_STMP

               

              Regards,

              Jari

              • 4. Re: Change APEX SMTP Server from APEX 4.2.2
                Mike Kutz

                 

                Short and sweet. I'm trying to change SMTP_HOST_ADDRESS from APEX ON-DEMAND PROCESS.

                .

                 

                Don't do that.

                Configure your SMTP_HOST_ADDRESS once as the DBA/APEX Admin and leave it be.

                Even if you could get it to work, that won't solve the ACL problem... and you really do not want to change ACLs on the fly.  (that is an indication of a horrible design)

                 

                Some things really need to be done via SQL*Plus/SQL*Developer.

                Configuring a run-time-only APEX instance seems to be one of them.

                 

                MK

                • 5. Re: Change APEX SMTP Server from APEX 4.2.2
                  fac586

                  jstem1177 wrote:

                   

                  Short and sweet. I'm trying to change SMTP_HOST_ADDRESS from APEX ON-DEMAND PROCESS.

                   

                  Just to root out privileges, I've granted the below to both the owner of the procedure and the APEX_PUBLIC_USER and rebooted the database and apex.

                   

                  1. grant apex_administrator_role to mtz; 
                  2. grant execute on apex_instance_admin to mtz; 
                  3. grant apex_administrator_role to apex_public_user; 
                  4. grant execute on apex_instance_admin to apex_public_user; 

                   

                  As mentioned, in other threads, there does not seem to be any problems executing this directly from SQL Developer.

                   

                  1. BEGIN 
                  2.         APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', '192.168.2.1'); 
                  3.         COMMIT
                  4. END

                   

                  Just to be sure, I added a change of schema, which from some reason works in SQL developer but not inside the ON-DEMAND PROCESS.

                   

                  1. BEGIN 
                  2.         execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = APEX_40200'
                  3.         APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', '192.168.2.1'); 
                  4.         COMMIT
                  5.         execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = APEX_PUBLIC_USER'
                  6. END

                   

                  I've been at this for several hours, and just don't understand why it just simply will not work!

                  It won't work because—as documented—ALTER SESSION SET CURRENT_SCHEMA does not change the current user or grant the session user any additional privileges:

                  This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session.

                  The two blocks above are therefore equivalent, as no schema object qualifiers are involved.

                  • 6. Re: Change APEX SMTP Server from APEX 4.2.2
                    jstem1177

                    Hello All,

                     

                    Wow thank you for all the feedback, including pros and cons in application designs.

                     

                    I opted to use a little of everything and include 1 section in software installation script which allows the user to configure the SMTP server details. However, I've add the possibility to change this also once the application has been installed by using a scheduler job which calls an external script (EXTERNAL) which then runs a simple bash file connecting the the database as my schema user and executes procedures directly in SQL*Plus.

                     

                    Thanks once more to everyone.

                     

                    Jan S.