Manage Access Control List in Oracle 11g and ORA-24247

Oracle 11g offers fine-grained access to network services (ACL) and the packages used to access external network resources are restricted.

The 11g allows access to external packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, UTL_INADDR, DBMS_LDAP, but the access must be granted explicitly. Please note that ACLs are stored in XML DB and user must install XML DB for the use of ACL, if not installed.

The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).

     • ACL - Name of access control list in xml file and relative path will be “/sys/acls”
     • Description - Description of the ACL.
     • Principal - To whom the privilege is granted or revoked
     • Is_grant - Indicates weather the privilege is granted (TRUE) or denied (FALSE).
     • Privilege – Network privilege, Use ‘connect’ for access and ‘resolve’ for UTL_INADDR name/IP resolution.
     • Position – Position of ACL
     • Start_date – Start date of the ACL , the default value is NULL.
     • End_date – End date of an ACL.

Uses might see below error when they upgrade their databases from Oracle 10g to 11g. This is expected behavior in Oracle 11g

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

This is expected behavior in 11g, if any of the UTL_% packages referred or used in any user defined programs. To resolve this issue user must explicitly grant the access using DBMS_NETWORK_ACL_ADMIN package.

For ex: - A used defined new send_mail PL/SQL program or existing program (After upgrading to 11g) failed with ORA-24247

DECLARE

mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

DECLARE

*
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 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7

To resolve the issue user need to create ACL and grant required access, follow the below steps.

Step1:- Create New Access Control List

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_smtp.xml',
description => 'Allow UTL_SMTP to send mails',
principal => 'TEST_USER',
is_grant => TRUE,
privilege => 'connect',
Start_Date => Null,
End_Date => Null);
end;

Step2:- Add privilege to Access control list

You can add the privilege like ‘resolve’ to the ACL.

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => ‘TEST_USER’,
is_grant => TRUE,
privilege => 'resolve');
end;
/

Step3:- Assign Access control List

begin
dbms_network_acl_admin.assign_acl(
acl => 'utl_smtp.xml',
host => 'localsmtp.oracleracexpert.com',
lower_port => 25,
upper_port => NULL);
end;
/

Step4:- Check the permission

Check the required permission is granted to the user “TEST_USER” using below query

SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
4 DECODE(
5 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'resolve'),
6 1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
7 FROM dual;

Connect     Resolve
----------- -------
DENIED DENIED

The access is not granted and user still receives error “ORA-24247: network access denied by access control list (ACL)”.

You must “COMMIT” the changes in order to work the Network ACL’s.

Now user should be able to see the changes using below queries

SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
4 DECODE(
5 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'resolve'),
6 1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
7 FROM dual;

Connect      Resolve
------------ -------
GRANTED GRANTED

SQL> select * from dba_network_acls where acl like '%utl_smtp%%';

HOST LOWER_PORT UPPER_PORT ACL ACLID
------------------------ ---------- ---------- ------------------------------ ---------------------------------------------
localsmtp.oracleracexpert.com /sys/acls/utl_smtp.xml C6B2CCC62AC30707E04025AC80DA7FA3

Now user defined send_mail program is working fine.

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/
PL/SQL procedure successfully completed.

Manage the Network ACLs as below

Add a user or role to newly created Access control list (ACL) – Using ADD_PRIVILEGE procedure adding privilege to the user “SCOTT”

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => ‘SCOTT’,
is_grant => TRUE,
privilege => 'connect');
end;
/

Remove a user or role to newly created Access control list (ACL) – Using DELETE_PRIVILEGE procedure deleting a privilege in an access control list from user “SCOTT”

begin
dbms_network_acl_admin.delete_privilege (
acl => 'utl_smtp.xml',
principal => ‘SCOTT’,
is_grant => FALSE,
privilege => 'connect');
end;
COMMIT;
/

Drop Access control list (ACL) – Using DROP_ACL procedure dropping an access control list (ACL).

begin
dbms_network_acl_admin.drop_acl (
acl => 'utl_smtp.xml');
COMMIT;
end;
/

Unassign Access control list (ACL) – Using UNASSIGN_ACL procedure un-assigning the access control list (ACL) from a host.

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => 'hostname’);
COMMIT;
end;
/

You can query below views for ACL and privilege information

DBA_NETWORK_ACL_PRIVILEGES describes the network privileges defined in all access control lists that are currently assigned to network hosts.

DBA_NETWORK_ACLS describes the access control list assignments to network hosts.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com