Network Access Control List ORA-24247

Network Access Control List ORA-24247

Network Access control list (ACL) is used in Oracle 11g database
it defines the permission, grants and access to user for network related services e.g host,port number.
you can access the following package 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 package DBMS_NETWORK_ACL_ADMIN.
You user need to grant permission on DBMS_NETWORK_ACL_ADMIN package

List the assigned ACL Privileges present in 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 error while we are executing the procedure for sending mail from SCOTT user.

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 may no permission to user and need to create permission for host.So, we follow following steps to execute 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 for grant or deny the access on network to user. Has 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” used for the UTL_INADDR package.
Startdate Startdate for network access to user.
enddate enddate for network access to 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 add 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 user or roles to 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 Assign the host or port for network access.
I tried to explain it with 3 examples for all host or specific host or all host and port.
Parameters of Assign ACL procedure:
ACL – Name of acl file
host – hostname, ipaddress 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 port, it is used for testing purpose if 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;
/

Advertisements

2 thoughts on “Network Access Control List ORA-24247

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.