Home » RDBMS Server » Server Administration » Restrict email recipients (Oracle Database Standard, 12.1.0.2, RHEL 7)
icon1.gif  Restrict email recipients [message #683964] Wed, 10 March 2021 11:45 Go to next message
jtaylor75
Messages: 3
Registered: March 2021
Junior Member
Hello,

I'm trying to find a way to restrict Oracle to only allow email recipients in one or two specific domains. I have far too many users to build a white list of all email addresses, I'm hoping to be able to restrict by domain: e.g only send to recipients with @example.com addresses?

Running Oracle Database Standard 12.1.0.2 on RHEL 7. I have configured sendmail on the OS to only allow specific domains and that is working for anything sent through the OS, but just had an email sent directly from the database and it was not blocked by the sendmail config.
Re: Restrict email recipients [message #683965 is a reply to message #683964] Wed, 10 March 2021 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just allow to send mails from a custom procedure which will check what you want.

Re: Restrict email recipients [message #683966 is a reply to message #683965] Wed, 10 March 2021 13:41 Go to previous messageGo to next message
jtaylor75
Messages: 3
Registered: March 2021
Junior Member
Can you expand on that at all? Apologies, I am the infrastructure administrator, I deal mostly with the server, Oracle installation, patching, and general config. I don't deal with creating stored procs or much at all inside the database. I ran your reply by my database developer and he isn't sure how that would work either.
Re: Restrict email recipients [message #683967 is a reply to message #683964] Wed, 10 March 2021 14:04 Go to previous messageGo to next message
John Watson
Messages: 8622
Registered: January 2010
Location: Global Village
Senior Member
jtaylor75 wrote on Wed, 10 March 2021 17:45
Hello,

I'm trying to find a way to restrict Oracle to only allow email recipients in one or two specific domains. I have far too many users to build a white list of all email addresses, I'm hoping to be able to restrict by domain: e.g only send to recipients with @example.com addresses?

Running Oracle Database Standard 12.1.0.2 on RHEL 7. I have configured sendmail on the OS to only allow specific domains and that is working for anything sent through the OS, but just had an email sent directly from the database and it was not blocked by the sendmail config.
Oracle sends emails to an SMTP server, such as your sendmail. So it CANNOT send mail to somewhere that sendmail does not permit. You must have configured Oracle to send email to a different SMTP server, one that does not have the same restrictions.
Re: Restrict email recipients [message #683968 is a reply to message #683966] Wed, 10 March 2021 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jtaylor75 wrote on Wed, 10 March 2021 20:41
Can you expand on that at all? Apologies, I am the infrastructure administrator, I deal mostly with the server, Oracle installation, patching, and general config. I don't deal with creating stored procs or much at all inside the database. I ran your reply by my database developer and he isn't sure how that would work either.
The standard way to send email from Oracle is to call UTL_MAIL package.
Instead of this you can revoke all privileges on this package from everyone but a single account which will create a procedure with same parameters than UTL_MAIL.SEND (for instance) and then grant EXECUTE privilege to other accounts on this procedure.

So instead of calling:
UTL_MAIL.SEND (sender=>..., recipients=>..., subject=>..., message=>...);
you will call
My_Send (sender=>..., recipients=>..., subject=>..., message=>...);
procedure which will be defined as (simplified example):
CREATE OR REPLACE PROCEDRE My_Send (
  SENDER     VARCHAR2,
  RECIPIENTS VARCHAR2,
  SUBJECT    VARCHAR2,
  MESSAGE    VARCHAR2)
IS
BEGIN
  <code to check RECIPIENTS and raise an exception if not correct>
  UTL_MAIL.SEND (sender=>sender, recipients=>recipients, subject=>subject, message=>message);
END;
/
Re: Restrict email recipients [message #683969 is a reply to message #683968] Wed, 10 March 2021 18:12 Go to previous message
jtaylor75
Messages: 3
Registered: March 2021
Junior Member
Thank guys, this is all above my Sys Admin level. I will refer your comments to my Oracle developer. I was hoping for a simple config option. Wishful thinking with Oracle I know...
Previous Topic: Oracle Database Migration
Next Topic: Transactions are failing due to insufficient space in the undo tablespace
Goto Forum:
  


Current Time: Wed Oct 20 17:17:19 CDT 2021