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.
2. Assign Permissions
You can assign additional privileges to the ACL.
3. Bind the ACL to a Host or IP Range
Bind the ACL file to a specific host or range of hosts.
- 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
andupper_port
to restrict access to specific ports.
4. Check the Assigned ACL
Query the DBA_NETWORK_ACLS
view to verify the ACL binding.
5. Remove an ACL
To remove the ACL from a host, use:
To drop the ACL entirely:
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
:
Create ACL:
Assign ACL to the host:
Verify:
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
.