This content has been marked as final. Show 7 replies
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.
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.
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.
Hi JCoves,1 person found this helpful
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.
How do I grant apex workspace administrator privileges? I thought grating APEX_ADMINISTRATOR_ROLE role to my schema was enough.
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!
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:
Edited by: jcoves on 08-jun-2012 2:21