RDBMS -- Acknowledgement & Solution for ACL(ORA-24247) & Relay Access Denied errors

Normally writing about this kind of errors is not my style; but I will write about this one, because it is good to have this plsql to be available on my blog in case I need it + sometimes there are cases where we need to send email directly from the database without using notification mailer..

It is needless to say that ORA-24247 is an ACL error, and can be corrected by defining necessary ACLs..
Note that; the ACLs are there to protect the network services from being accessed by unprivileged users.
Stardart Oracle packages which can be used to access network services are : UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR and DBMS_LDAP..

On the other hand; we usually call these standart packages from our custom packages/function or procedures, whereas it is hard to find the source that actually triggers the ORA-24247 errors, sometimes.

If this is the case; we can use 24247 trace event to identify the source of these errors..
I mean we can set an event in the database to find the actual cause of an ORA-24247 error, as follows;
alter system set events='24247 trace name errorstack level 3';

After setting the event, there should be a new trace file generated in the trace directory,when the error reappears.. By analyzing the trace file (especially search for the word "user"  and "PL/SQL Call Stack"), we can see the failing session and the plsql callstack of it..

Okay. So far so good. So lets proceed with the solution ;
To overcome this type of errors, we connect into our database using SYSTEM accound and  check our current ACLs in the first place as follows;

SELECT host, acl, lower_port lport, upper_port uport
FROM DBA_NETWORK_ACLS ;

We check the output and we 'll see that there is no ACLs defined for enabling access to the server that we are trying reach.

Then, we create our ACL and associate it with the relevant user to make that user access the desired IP/host from our Oracle Database;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'mailserver.xml',
                                    description => 'mailserver icin xml',
                                    principal   => 'APPS',
                                    is_grant    => true,
                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'mailserver.xml',
                                       principal => 'APPS',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'mailserver.xml',
                                    host => '.....ip or hostname here....');
END;
/
COMMIT;

That 's it.. This should resolve the error...

Okay.. So far so good. But there is more..
After solving ORA-24247, we may encounter a different error.. One of these errors can be the one about the relays..
I m talking about "relay access denied" errors..
This errors may be caused by a wrong relay configuration in the SMTP server.. If this is the case; just contact the Mail Server Admin to make the configurations in the mail server, accordingly.
But, also we may encounter these relay errors, because of our code..
I mean; we may be missing some lines of plsql there..  Especially the lines needed for authentication..
That is ; some Mail Servers does not accept anonymous logins, that 's why we need to supply user/pass info after opening our connection to the Mail Server..
If this is the case; we just add the following lines in to our plsql.. We add them just after the line that we open our connection, and we modify the required strings according to our environment..

User/Pass Authentication in UTL_SMTP
utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'our Mail UserName' ))) );
utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'our Mail Password' ))) );

That's all for now.. I hope you 'll find it useful.