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

Packt Publishing reaches its 1000 titles

Hi Readers,

Packt Publishing is about to publish its 1000th title and they would like to celebrate this occasion by giving a surprise gift to their readers.

These gifts will only be revealed between 28th and 30th September, as when their 1000th title gets unveiled. The gifts are valid for only those readers who are currently registered on their website, and those who sign up for an account before 30th September.

You can find complete information about the event on http://www.packtpub.com/news/packt-publishing-reaches-1000-it-titles-and-celebrates-open-invitation

You can visit Packt Publishing Website http://www.packtpub.com to see their collection of books.

I hope you will get benefited.

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

Smart View timeout error message

If users are working with planning data forms on excel and receiving any of the below timeout error then the issue is with IE Settings.

Cannot connect to provider because: The request timed out. Contact your administrator to increase NetRetryCount and NetRetryInterval

The requeset timed out. Contact your administrator to increase netRetryCount and netRetryInterval.

When trying to save the data then users might see below error

“This form contains unsaved data. Select OK to continue without saving data, or Cancel this operation”

Follow below steps to fix the issue

Step1:- Create an empty text file and copy below contents

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"KeepAliveTimeout"=dword:00180000
"ReceiveTimeout"=dword:00dbba00
"ServerInfoTimeout"=dword:00180000

Step2: - Rename the file as LogOut.reg” and right click on the file  click on merge to update your registry

Step3:- Restart your computer (optional)

You can manually update/ add registry entries but it may corrupt the registry and this is the recommended approach.

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