*****************************************************************************
* Description: Config Oracle UTL_MAIL package, and access control list [ACL]
* Date: 04:27 PM EST, 09/21/2018
*****************************************************************************

		 
<1> Network Access Control List:
     |
     |__ o. A network access control list (ACL) is an optional layer of security for your VPC that acts as a firewall for controlling traffic in and out of one or more subnets. 
     |      You might set up network ACLs with rules similar to your security groups in order to add an additional layer of security to your VPC.
     |
     |__ o. One database can have multiple ACL, but one target host can only have one ACL.
	
	
	
	
	
<2> Install UTL_MAIL Package as SYSDBA:
     |
     |__ CMD> sqlplus / as sysdba
     |
     |__ SQL> @?/rdbms/admin/utlmail.sql
     |
     |__ SQL> @?/rdbms/admin/prvtmail.plb	 
	
	
	
	
	
<3> Grant Execute Permission to Individual User:
     |
     |__ SQL> grant execute on UTL_MAIL TO User_1;
              grant execute on UTL_MAIL TO User_2;	 
	
	
	
	
	
<4> Setting Oracle database parameter for database to be aware of SMTP server:
     |
     |__ SQL> alter system set SMTP_OUT_SERVER='UTILITIES.emeralit.local';
              alter system set SMTP_OUT_SERVER='PCASW1P.FFNET.LOCAL:587';


	
	
	
	
<5> Sample PL\SQL Code for Sending Email via UTL_MAIL package:
     |
     |__ SQL> BEGIN
                     UTL_MAIL.send(
                             sender     => 'email@emeralit.com',
                             recipients => 'amos.geng@emeralit.com',
                             cc         => '',
                             bcc        => '',
                             subject    => 'UTL_MAIL Test',
                             message    => 'If you get this message it worked!'
                     );
              END;
              /
	 
	 

	 

<6> If the error "ORA-24247: network access denied by access control list (ACL)" coming up, please following below procedure to create ACL.




<7> Creating ACL in Oracle database. The principal could be a role:
     |
     |__ SQL> BEGIN 
                     DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 
                             acl => 'ACL_NAME.xml', 
                             description => 'ACL_DESCRIPTION', 
                             principal => 'DB_USERNAME_1_AS_ACL_OWNER', 
                             is_grant => TRUE, 
                             privilege => 'connect', 
                             start_date => null, 
                             end_date => null
                     ); 
             END;
             /





<8> Adding more username or privilege to exsiting ACL:
     |
     |__ SQL> BEGIN
                     DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
                             acl       => 'ACL_NAME.xml',
                             principal => 'DB_USERNAME_2_AS_ACL_OWNER',
                             is_grant  => true,
                             privilege => 'connect'
                     );
             END;
             /

			 
			 


<9> Assigning ACL to an existing network resource:
     | 
     |__ SQL> BEGIN
                     DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
                             acl => 'ACL_NAME.xml',
                             host => 'UTILITIES.emeralit.local', 
                             lower_port => 25,
                             upper_port => null
                     ); 
             END;
             /
			 
			
			
	
	
<10> Drop ACL:
     |
     |__ SQL> BEGIN
                     DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
                             'ACL_NAME.XML'
                     );
             END;
             /
			 


			 
<11> ACL dynamic views contains ACL metadata:
     |
     |__ SQL> select * from dba_network_acls;
	 
			 
			 
			 

			 
Reference:
     |
     |__ o. http://dba-oracle.com/t_ora_24247_network_access_denied_by_access_control_list_tips.htm
     |
     |__ o. https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
     |
     |__ o. https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#CHDJFJFF