Known Issues When Apparently Correctly Configured ACLs Fail Don't Prevent the ORA-24247 Error (Doc ID 1074843.1)

Last updated on FEBRUARY 17, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

This note tries to put together known issues when ACLs that are correctly configured don't allow the access to the requested network services and the service access attempts fail with

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

1. The ACL is configured for a synonym or the IP of the invoked service name.

 

The ACL privileges check does not do any (reverse) name resolution, if the ACL is configured for an IP, using the hostname will not work and vice versa.

 

Example 1.
This example presents the incorrect setting and what to do to address the problem.

 
******************************************************************************
The query to be used is:
select utl_http.request('http://www.oracle.com') from dual;
 
The IP, as determined using the dig command:
dig www.oracle.com
;; ANSWER SECTION:
www.oracle.com.         278     IN      CNAME   www.oracle.com.edgesuite.net.
www.oracle.com.edgesuite.net. 15548 IN  CNAME   a398.g.akamai.net.
a398.g.akamai.net.      12      IN      A       63.97.94.10
a398.g.akamai.net.      12      IN      A       63.97.94.27
*/
*******************************************************************************
 
conn / as sysdba
 
drop user test cascade;
BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl ( 
    acl         => 'www.oracle.com.xml');
  COMMIT;
END;
/
 
create user test identified by test;
grant connect, resource to test;
 
connect test/test
select utl_http.request('http://www.oracle.com') from dual;
 
--failure...
/*
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
*/
 
--Create proper ACL and grant it to the test user:
conn / as sysdba
 
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'www.oracle.com.xml', 
    description  => 'ACL for testing purposes',
    principal    => 'TEST',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
  COMMIT;
END;
/
 
--The ACL is assigned as IP and not as the www.oracle.com address:
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'www.oracle.com.xml',
    host        => '63.97.94.*', 
    lower_port  => NULL,
    upper_port  => NULL);
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'www.oracle.com.xml',
    host        => 'www-proxy.uk.oracle.com', 
    lower_port  => NULL,
    upper_port  => NULL);
  COMMIT;
END;
/
 
-- As indicated above, the error persists:
 
conn test/test
 
begin
UTL_HTTP.SET_PROXY('http://www-proxy.uk.oracle.com:80','test.com');
end;
/
 
select utl_http.request('http://www.oracle.com') from dual;
-- failure again
/*
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
*/
 
--To fix the issue, the ACL is assigned as the actual address now: www.oracle.com:
conn / as sysdba
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'www.oracle.com.xml',
    host        => 'www.oracle.com', 
    lower_port  => NULL,
    upper_port  => NULL);
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'www.oracle.com.xml',
    host        => 'www-proxy.uk.oracle.com', 
    lower_port  => NULL,
    upper_port  => NULL);
  COMMIT;
END;
/
 
--Now everything works:
conn test/test
begin
UTL_HTTP.SET_PROXY('http://www-proxy.uk.oracle.com:80','test.com');
end;
/
 
select utl_http.request('http://www.oracle.com') from dual;
<expected http pages>
 

2. ACL for utl_inaddr.get_host_name or utl_inaddr.get_host_address without parameters is not functional

--Create an ACL that applies to the database server and try to use UTL_INADDR to resolve the name or the IP of the local server without parameters.

conn / as sysdba

drop user test cascade;
create user test identified by test;
grant connect, resource to test;
grant execute on utl_inaddr to test;

BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
acl => 'test.xml');
END;
/
commit;


BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'test.xml',
description => 'A test of the ACL functionality',
principal => 'TEST',
is_grant => TRUE,
privilege => 'resolve'
);

DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test.xml',
host => '10.171.112.104');
END;
/
commit;

conn test/test
set serveroutput on;
begin
dbms_output.put_line(utl_inaddr.get_host_name);
end;
/

--Failure:
/*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 2
*/
--Solution: Create the ACL for the localhost, not for the domain name or the IP address.
--This issue has been investigated in <Bug:9388057>
--The recommended solution is to assign the ACL to the 'localhost', as below:

conn / as sysdba

drop user test cascade;
create user test identified by test;
grant connect, resource to test;
grant execute on utl_inaddr to test;


BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
acl => 'test.xml');
END;
/
commit;


BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'test.xml',
description => 'A test of the ACL functionality',
principal => 'TEST',
is_grant => TRUE,
privilege => 'resolve'
);

-- Use the localhost string for the host parameter:
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'test.xml',
host => 'localhost');
END;
/
commit;

select host,acl from dba_network_acls;

conn test/test
set serveroutput on;
begin
dbms_output.put_line(utl_inaddr.get_host_name);
end;
/
--returns the expected localhost name

3. The ACLs are not functional when accessed services are behind a proxy.

4. Granting the ACL via roles does not work when the service is requested through from a PLSQL procedure

The example is the same as in #1, just grant the ACL to a given role. The example is truncated, it should be completed as in #1.


conn / as sysdba
create role acl_role;

BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'www.oracle.com.xml',
description => 'ACL for testing purposes',
principal => 'ACL_ROLE',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

grant acl_role to test;

connect test/test

--Accessing the service directly works well:
begin
UTL_HTTP.SET_PROXY('http://www-proxy.uk.oracle.com:80','test.com');
select utl_http.request('http://www.oracle.com') from dual;
end;
/

--Calling the service from plsql fails:
connect test/test

create or replace procedure test_acl_proc
proc_result varchar2(2000);
begin
UTL_HTTP.SET_PROXY('http://www-proxy.uk.oracle.com:80','test.com');
select utl_http.request('http://www.oracle.com') into proc_result from dual;
end;
/

-- run the procedure as both test_owner and test, it fails with 24247
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
...

 

5. Calling a PLSQL in another schema fails with ORA-24247 despite the granted ACL privilege to the invoker.

6. Multiple ACLs defined for the same host, using wildcards for different orders of precedence.

7. ACL properly defined, yet DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE shows that the user is not allowed to use the ACL.

SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('<acl_name.xml>', '<username>', 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual
/

PRIVILEGE
---------
DENIED

8. (RAC specific): Dropping a user account which has been granted ACL permissions causes other accounts using the same ACL to throw ORA-24247 errors

After dropping the user, the ACL privilege appears still granted to the user in DBA_NETWORK_ACL_PRIVILEGES. In the same time, the other user to which the ACL has been granted, are unable to use it with ORA-24247 errors. This issue reproduces on RAC only.

9. ACLs don't work when granted through roles

If granting the ACL to a role and grant the role to a user, the user fails to access the network service(s) associated with the ACL and receives the ORA-24247.
The issue is specific to 11.2.0.3 and does not reproduce in lower releases.
A strange side effect of this bug is that files larger than 50MB cannot be downloaded from https sites, even when the roles are not enabled.

10. http_proxy environment variable is set at database level.

In this scenario, the database would assume expect the ACL to be defined for the proxy server and would expectedly raise ORA-24247 if the ACL does not exist. It is a variant of scenario 3.

11. There is no ACL configured for local wallet access

If a remote webserver request a client side certificate stored in a local wallet, then this also needs ACL privileges, in the event 10937 trace info you will see lines like the following:

psdnopWalletAuth: SCOTT 447 file:/etc/oracle/wallet 0
psdnopPrivID: use-client-certificates 28318
psdnopGetWalletACL: file:/etc/oracle/wallet curr_time 1404748901
psdnopGetWalletACL: return new acl 0x7fa57ac9d4c0 [0] file:/etc/oracle/wallet timeout 1404749801 00000000000000000000000000000000
psdnopWaletAuth: no ACL matched
psdnopWaletAuth: denied

12. The user being granted the ACL privilege was previously identified globally.

If a user that is identified globally is switched to database authentication, the ACL ceases to work.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms