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

The following error thrown while send SMS through Oracle PL/SQL Procedure

 

[Error] Execution (1: 1): ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1130

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

ORA-06512: at "FMS_ADMIN.SEND_SMS_RP_SS", line 29

ORA-06512: at line 1


Solution

An Access Control List (ACL) in Oracle is used to manage permissions for network services and control access to external resources. For example, it can restrict database access to web services, UTL_HTTP requests, or SMTP email services.

Here’s a step-by-step guide to creating an ACL in Oracle:


Steps to Create ACL

1. Create an ACL File

Use the DBMS_NETWORK_ACL_ADMIN package to create the ACL.

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => 'example_acl.xml', -- Name of the ACL file description => 'ACL for HTTP access', -- Description principal => 'USER_NAME', -- Database user or role is_grant => TRUE, -- TRUE for granting permissions privilege => 'connect' -- Privileges: 'connect', 'resolve' ); END; /

2. Assign Permissions

You can assign additional privileges to the ACL.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'example_acl.xml', principal => 'ANOTHER_USER', is_grant => TRUE, privilege => 'resolve' -- Allows DNS resolution ); END; /

3. Bind the ACL to a Host or IP Range

Bind the ACL file to a specific host or range of hosts.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'example_acl.xml', host => 'www.example.com', -- Specify host or IP address lower_port => NULL, -- Optional: Specify port range upper_port => NULL ); END; /
  • Host: You can specify the hostname (e.g., www.example.com) or IP address (e.g., 192.168.1.1).
  • Port Range: Use lower_port and upper_port to restrict access to specific ports.

4. Check the Assigned ACL

Query the DBA_NETWORK_ACLS view to verify the ACL binding.

SELECT * FROM DBA_NETWORK_ACLS;

5. Remove an ACL

To remove the ACL from a host, use:

BEGIN
DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL( host => 'www.example.com' ); END; /

To drop the ACL entirely:

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

Key Privileges

  • CONNECT: Allows making a connection to the network service.
  • RESOLVE: Allows resolving domain names to IP addresses.

Example Scenario: HTTP Access for User

Grant access to UTL_HTTP for SCOTT user to access www.example.com:

  1. Create ACL:

    BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => 'http_access_acl.xml', description => 'Allow HTTP access', principal => 'SCOTT', is_grant => TRUE, privilege => 'connect' ); END; /
  2. Assign ACL to the host:

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'http_access_acl.xml', host => 'www.example.com' ); END; /
  3. Verify:

    SELECT * FROM DBA_NETWORK_ACLS;

Notes

  • Ensure the user has the appropriate privileges to manage ACLs.
  • The DBMS_NETWORK_ACL_ADMIN package requires Oracle 11g or later.
  • Check for any conflicting ACLs using DBA_NETWORK_ACL_PRIVILEGES.

Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post

Most Popular Posts