Network Access Control List ORA-24247

Network Access Control List (ACL) is used in the Oracle 11g database it defines the permission, grants, and access to users for network-related services e.g. host, and port number. You can access the following packages such as utl_mail, utl_http, utl_smtp, utl_tcp, and UTL_INADDR.

For accessing the network-related services, we need to create the ACL with the package DBMS_NETWORK_ACL_ADMIN. Your user needs to grant permission on DBMS_NETWORK_ACL_ADMIN package

List the assigned ACL Privileges present in the database:

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

Error: ORA-24247: network access denied by access control list (ACL)

We are getting errors while we are executing the procedure for sending mail from SCOTT users.

exec send_mail('TEST','abcdefg@gmail.com');

begin
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 19
ORA-06512: at “SYS.UTL_SMTP”, line 163
ORA-06512: at “SCOTT.SEND_MAIL”, line 8
ORA-06512: at line 2

Solution:
Access control list error means there may be no permission to the user and need to create permission for the host. So, we follow the following steps to execute the send mail procedure successfully.

1. Grant execute to USER.

grant execute on utl_mail to scott;

2. Create ACL for granting SCOTT user permission for utl_mail package.
Create ACL function is used to grant or deny access to the network to the user. Has the following parameter details:

acl name of acl file
Description Description of ACL
Principal user/role mentioned for access
is_grant true for granting the access, false for denying the access
Privilege user needs the “connect” privilege for using the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL utility packages.
“resolve” is used for the UTL_INADDR package.
Startdate Startdate for network access to the user.
enddate end date for network access to the user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'test.xml',
    description  => 'A test of the UTL_MAIL',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

COMMIT;
END;
/

3. Package DBMS_NETWORK_ACL_ADMIN has added privileges Procedure for adding more users or roles to ACL
Additional users or roles are added to the ACL using the ADD_PRIVILEGE procedure. So, you can add more users or roles to the same permission as in TEST.xml

BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl         => 'test.xml',
    principal   => 'SCOTT2',
   is_grant    => FALSE,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);

COMMIT;
END;
/

4. Assign ACL procedure for Assigning the host or port for network access.
I tried to explain it with 3 examples for all hosts or specific hosts or all hosts and ports.
Parameters of Assign ACL procedure:
ACL – Name of ACL file
host – hostname, IP address you want to assign permission
lower_port => assign lower port
upper_port => assign upper port which range you need to access from lower to upper port.

--- Assign all host address in following procedure "assign_acl"
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'test.xml',
host => '*');
COMMIT;
END;
/

-- for only specific host and port number
Begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test.xml',
host => '192.168.1.100',
lower_port => 80,
upper_port => NULL);
commit;
end;
/

-- Assign all hosts and ports, it is used for testing purposes if there no info you have which host or port
Begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test.xml',
host => '*',
lower_port => 1,
upper_port => 9999);
commit;
end;
/

5. Check the ACL Privileges assigned

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;

6. Delete privileges from scott2 users

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege (
    acl         => 'test.xml',
    principal   => 'SCOTT2',
    is_grant    => FALSE,
    privilege   => 'connect');
  COMMIT;
END;
/

7. Drop the ACL

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl (
    acl         => 'test.xml');

COMMIT;
END;
/