From oracle 11gR2 onwards ACLs(Access control list) are mandatory to send mail from procedure using UTL_MAIL or UTL_SMTP. For this make sure XDB component is installed.
If XDB component is not installed Check - How to install XDB component in oracle.
Verify whether UTL_MAIL and UTL_SMTP is installed or not.
select object_name,object_type,owner from dba_objects where object_name in('UTL_MAIL','UTL_SMTP');
no rows selected.
As in our case, both utl_mail and utl_stmp are not installed.
Install the packages:
[host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlmail
SQL> @utlsmtp
SQL> @prvtmail.plb
SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
Set the SMTP_OUT_SERVER parameter
NOTE - Please check with your OS admin for this smtp mail server hostname/hostname.
SQL> alter system set smtp_out_server='smtp-server.mail.eraazi.com' scope=both;
System altered.
If smtp_out_server is set incorrectly ,then it will throw error -: ORA-29278: SMTP transient error: 421 Service not available
Now create ACLS
Suppose the user SCOTT want to send mail from procedure.
--- creating ACL as below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('scott_utl_mail.xml','Allow mail to be send','SCOTT', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml' ,'SCOTT', TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('scott_utl_mail.xml','*',25);
commit;
Test whether user SCOTT is able to send mail or not
SQL>conenct scott/tiger
connected
SQL>EXECUTE UTL_MAIL.SEND(SENDER=>'[email protected]',RECIPIENTS=>'rpatro@eraazi.com,[email protected]', MESSAGE=>'Hello World');
Dictionary tables for ACLS
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SEE - COLLECTION OF USEFUL DATABASE SCRIPTS
SEE NEW FEATURES OF ORACLE 12.2:
- Upgrade oracle database from 12cR1 to 12cR2 version:
- Convert non-partitioned table to partitioned table online
- Datapump New features - Oracle 12.2 Version
- Lock Account Automatically With INACTIVE_ACCOUNT_TIME
- PDB Lockdown Profiles in Oracle 12.2
- SQL*Plus History In Oracle 12.2
- Long Identifiers In Oracle 12.2
- DBMS_TNS package for tnsping in database Oracle 12.2
- Spool CSV In Oracle 12.2
- VARIABLE new feature in Oracle 12.2
- Move table online in Oracle 12.2
- Read only Partition in Oracle 12.2
- Split partition online oracle 12.2
ORACLE SECURITY