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

In this post I want to talk about a common error with Oracle 11g, especially 11.2.0.1.0. We were getting this error on all versions of our application after upgrading from 10.2 to 11.2 oracle database on Linux 5.4 64 bit.

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)

Details

This problem is bug 13375884 associated with 11g databases but corrected in latest patch sets. Don't panic! Just follow instructions.

cause

The problem is common to Oracle 11g because prior to 11gR1, you can run PL/SQL code making calls to Oracle Supplied Packages without error. These packages include:

•UTL_TCP
•UTL_HTTP
•UTL_SMTP
•UTL_MAIL

However, after upgrading to a 11g version, this error comes because of inaccessible external network privilege for users calling the packages.

In 11gR1 the Oracle Database enhanced the level of security when users attempt to access External Network Services by implementing Access Control Lists (ACL) using the new DBMS_NETWORK_ACL_ADMIN package. The PL/SQL packages listed above were affected. For more information on this change to Oracle Database Security please review Oracle documentation.


see Oracle documentation on Fine Grain access control at ACL.

Temporary fix

To resolve this issue temporarily, run this script to grant privilege to the user so that the user can access external network service.

BEGIN

-- Only uncomment the following line if ACL "network_services.xml" has already been created
--DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'network_services.xml',
description => 'NETWORK ACL',
principal => 'QUEEN',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'network_services.xml',
principal => 'QUEEN',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'network_services.xml',
host => '*');

COMMIT;

END;


permanent fix

Apply patch 11.2.0.3.0 or later to permanently resolve this network issue.

1. Download the patchset from Oracle Metalink. The detail is "Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER"

2. unzip the software

3. run Installer

4. Run DBUA to configure database to higher version

More on this later.

TEMPFILE ERROR ON ORACLE 10r2/LINUX

Hi folks. Here is one of the errors you can encounter on your job as a Database Java1istrator.
I was running a full export of a database (software) and I ran into this error. I hope sharing this error and the solution will help someone out there.

Environment
Linux 4.9
Oracle 10.2
Database name: software

Error
Linux Error: 2: No such file or directory
Additional information: 3

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6234
----- PL/SQL Call Stack -----
object line object
handle number name
0x34ec54e8 14916 package body SYS.KUPW$WORKER
0x34ec54e8 6293 package body SYS.KUPW$WORKER
0x34ec54e8 2339 package body SYS.KUPW$WORKER
0x34ec54e8 6854 package body SYS.KUPW$WORKER
0x34ec54e8 1259 package body SYS.KUPW$WORKER
0x5803b23c 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_06" stopped due to fatal error at 18:54:47


[oracle@software ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 26 18:58:12 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

SQL> desc v$tempfile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)

SQL> select name, status from v$tempfile;

NAME STATUS
-------------------------------------
/u10/oradata/software/temp01.dbf ONLINE

/u10/oradata/software/java1temp01.dbf ONLINE

/u10/oradata/software/java2temp01.dbf ONLINE

/u10/oradata/software/java3temp01.dbf ONLINE

4 rows selected.

Here comes the errors . From here you have a clue where the problem is and what you need to do to resolve this issue

SQL> desc dba_tempfile;
ERROR:
ORA-04043: object dba_tempfile does not exist


SQL> desc dba_tempfiles;
ERROR:
ORA-04043: object dba_tempfiles does not exist


SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
ERROR:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u10/oradata/software/java301.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
no rows selected

Solution

From this error, you can tell that there is a problem with a tempfle named '/u10/oradata/software/java301.dbf'. What we need to do is figure out how to get that tempfile online and alter the user presently using this tempfile to use the default tempfile from TEMP tablespace.

Crosscheck the default and temporary tablespaces in the database. This will give you an idea of where to go to get the issue resolved

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
JAVA1
JAVA1TEMP
JAVA2
JAVA2TEMP
JAVA3
JAVA3TEMP


Check for the user(s) presently using the JAVA3TEMP that has issues

SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';

USERNAME
------------------------------
JAVA3

Change the temporary tablespace of user JAVA3 from JAVA3TEMP to TEMP. This will ensure that the user can use the default temporary tablespace

SQL> alter user JAVA3
2 temporary tablespace TEMP;

User altered.

Check to ensure that the user is properly altered to use TEMP instead of JAVA3TEMP

SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';

no rows selected

Take the problematic tempfile offline

SQL> alter database tempfile '/u10/oradata/software/java3temp01.dbf' offline;

Database altered.

Please drop a comment if you have any questions. Thanks and watch this space for more posts!