Environment
Oracle XE 11gr2
APEX 5.1.2
Windows 7
Hello
I am getting Error message ORA-24247: network access denied by access control list (ACL)
for a Webservice call. This was working fine before, the only difference I can think of was the upgrade of APEX from 5.0 to 5.1.
I will outline my steps for anybody to point me in the right direction.
1. After configuring a reverse proxy setup I test my URL with curl
curl -D- -u <username>:<password> -X GET -H "Content-Type: application/json" http://NUK20002443/atlassian/rest/api/latest/issue/DBRPT-1965
returning HTTP/1.1 200 OK and JSON result, I now want to test on the Database
2. Create a new Oracle user for my Webservice call
-- Connect as SYS as SYSDBA User
drop user WS_TEST CASCADE;
-- Create the new Schema/User
CREATE USER WS_TEST IDENTIFIED BY ws_test
DEFAULT TABLESPACE APEXBOOK
TEMPORARY TABLESPACE TEMP;
-- Grant Role Priviledge
GRANT CONNECT TO WS_TEST;
ALTER USER WS_TEST DEFAULT ROLE CONNECT;
GRANT EXECUTE ON UTL_HTTP TO WS_TEST;
3. Check execute privilege for UTL_HTTP
4. Drop existing ACL and re-create
BEGIN
BEGIN
DBMS\_NETWORK\_ACL\_ADMIN.DROP\_ACL(acl => 'webservice-WS\_TEST.xml');
exception
when others then null;
END;
-- Privilege to create ACL and connect to the schema
dbms_network_acl_admin.create_acl(
acl => 'webservice-WS\_TEST.xml',
description => 'Access to schema WS\_TEST to connect to \*.theglobaldraw.atlassian.net',
principal => 'WS\_TEST', -- DB Schema (grantee)
is\_grant => true,
privilege => 'connect',
start\_date => systimestamp,
end\_date => null
);
commit;
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
('webservice-WS\_TEST.xml'
,'WS\_TEST', TRUE,'resolve');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
('webservice-WS\_TEST.xml'
,'APEX\_050100', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
('webservice-WS\_TEST.xml'
,'APEX\_050100', TRUE, 'resolve');
commit;
dbms_network_acl_admin.assign_acl(
acl => 'webservice-WS\_TEST.xml',
host => '\*.theglobaldraw.atlassian.net',
lower\_port => 80,
upper\_port => 80
);
dbms_network_acl_admin.assign_acl(
acl => 'webservice-WS\_TEST.xml',
host => '\*.theglobaldraw.atlassian.net',
lower\_port => 443,
upper\_port => 443
);
commit;
END;
/
5. Query the dba_network_acls and dba_network_acl_privileges tables
6. Test the URL on the Database with simple query
select utl_http.request('http://NUK20002443/atlassian/rest/api/latest/issue/DBRPT-1965') from dual;
Receiving Error
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
7. Test the URL on the Database using apex_web_service.make_rest_request
select apex_web_service.make_rest_request(
p_url => 'http://NUK20002443/atlassian/rest/api/latest/issue/DBRPT-1965'
,p_http_method => 'GET'
,p_username => <Hidden>
,p_password => <Hidden>
) from dual;
Receiving Error
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 "APEX_050100.WWV_FLOW_WEB_SERVICES", line 666
ORA-06512: at "APEX_050100.WWV_FLOW_WEB_SERVICES", line 880
ORA-06512: at "APEX_050100.WWV_FLOW_WEBSERVICES_API", line 239
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
Can any body help with this error or the correct setup of my ACL?
Kind Regards
Ade Adekoya