How to create ACL(Access Control List) in Oracle

Below are some steps to create ACL in Oracle. Please change UserName in script with your own UserName for database.

Script:

begin

        dbms_network_acl_admin.create_acl (

                acl             => 'utlpkg3.xml',

                description     => 'Normal Access',

                principal       => 'UserName',

                is_grant        => TRUE,

                privilege       => 'connect',

                start_date      => null,

                end_date        => null

        );

end;

/

begin

  dbms_network_acl_admin.add_privilege (

  acl                         => 'utlpkg3.xml',

  principal             => 'UserName',

  is_grant              => TRUE,

  privilege             => 'connect',

  start_date         => null,

  end_date          => null);

end;

/

begin

  dbms_network_acl_admin.assign_acl (

  acl => 'utlpkg3.xml',

  host => 'localhost',

  lower_port => 22,

  upper_port => 55);

end;

/

commit;

/

Sample Script:

Add your email address before executing script.

declare

  l_mail_conn   UTL_SMTP.connection;

begin

  l_mail_conn := UTL_SMTP.open_connection('localhost', 25);

  UTL_SMTP.helo(l_mail_conn, 'localhost');

  UTL_SMTP.mail(l_mail_conn, ' Your email address');

  UTL_SMTP.rcpt(l_mail_conn, ' Your email address ');

  UTL_SMTP.data(l_mail_conn, 'This is a test message.' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.quit(l_mail_conn);

end;

/

Below is error if ACL is missing.

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 246
ORA-06512: at “SYS.UTL_SMTP”, line 115
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “XXX”, line 36
ORA-06512: at line 1