Hi,

I have a problem when making an https call inside a package. It doesn't appear to recognise the privileges granted to access the acl.

When I call utl_http.begin_request in an anonymous plsql block or in a procedure with authid defined as current_user I get a connection (albeit one complaining about missing certificates) but if the procedure is created with default settings (ie definer rights) it complains about the network access. I could understand it if I had created the privilege against a role, but not when creating it for a user. Is there any way around this as otherwise I am going to have to grant access to every user in the database individually



Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as sdddba@dev24_DRACO

SQL> create user acltest identified by acltest;
User created

SQL> grant connect to acltest;
Grant succeeded

SQL> grant dba to acltest;
Grant succeeded

SQL> declare
2 acl_nom varchar2(100) := '/sys/acls/domtest.xml';
3 begin
4 dbms_network_acl_admin.create_acl (
5 acl => acl_nom,
6 description => 'mytest acl',
7 principal => 'ACLTEST',
8 is_grant => true,
9 privilege => 'connect',
10 start_date => systimestamp,
11 end_date => null);
12
13 dbms_network_acl_admin.assign_acl(acl => acl_nom,
14 host => <myhost>,
15 lower_port => 443,
16 upper_port => 443);
17 END;
18 /
PL/SQL procedure successfully completed

SQL> declare
2 v_req utl_http.req;
3 begin
4 v_req := utl_http.begin_request(' https:// <myurl>',
5 'POST',
6 utl_http.http_version_1_1);
7 end;
8 /
declare
v_req utl_http.req;
begin
v_req := utl_http.begin_request('https:<myurl>',
'POST',
utl_http.http_version_1_1);
end;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at line 4

SQL> create or replace procedure myproc as
2
2 v_req utl_http.req;
3 begin
4 v_req := utl_http.begin_request(' https://preprod-ncd.mibhub.org.uk/ncd/api/v1_0/enquire',--'https://preprod-ncd.mibhub.org.uk/ncd/api/v1_0/enquire:443',
5 'POST',
6 utl_http.http_version_1_1);
7 end;
8 /
Procedure created

SQL> begin
2 myproc;
3 end;
4 /
begin
myproc;
end;
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 "ACLTEST.MYPROC", line 4
ORA-06512: at line 2

SQL> create or replace procedure myproc authid current_user as
2
2 v_req utl_http.req;
3 begin
4 v_req := utl_http.begin_request(' https://preprod-ncd.mibhub.org.uk/ncd/api/v1_0/enquire',--'https://preprod-ncd.mibhub.org.uk/ncd/api/v1_0/enquire:443',
5 'POST',
6 utl_http.http_version_1_1);
7 end;
8 /
Procedure created

SQL> begin
2 myproc;
3 end;
4 /
begin
myproc;
end;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at "ACLTEST.MYPROC", line 4
ORA-06512: at line 2

SQL> conn / as sysdba
Connected.

SQL> create user acltest identified by acltest;

User created.

SQL> grant connect to acltest;

Grant succeeded.

SQL> grant dba to acltest;

Grant succeeded.

SQL> declare
  2  acl_nom varchar2(100) := '/sys/acls/domtest.xml';
  3  begin
  4  dbms_network_acl_admin.drop_acl (acl => acl_nom);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> declare
  2  acl_nom varchar2(100) := '/sys/acls/domtest.xml';
  3  begin
  4  dbms_network_acl_admin.create_acl (
  5  acl => acl_nom,
  6  description => 'mytest acl',
  7  principal => 'ACLTEST',
  8  is_grant => true,
  9  privilege => 'connect',
 10   start_date => systimestamp,
 11   end_date => null);
 12
 13   dbms_network_acl_admin.assign_acl(acl => acl_nom,
 14   host => '*',
 15   lower_port => 443,
 16   upper_port => 443);
 17
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> connect acltest/acltest
Connected.

SQL> create or replace procedure acltest.myproc as
  2    v_req utl_http.req;
  3  begin
  4    v_req := utl_http.begin_request('https://preprod-ncd.mibhub.org.uk/ncd/api/v1_0/enquire','POST',utl_http.http_version_1_1);
  5  end;
  6  /

Procedure created.

SQL> grant execute on acltest.myproc to public;

Grant succeeded.

SQL> connect acltest/acltest
Connected.

SQL> exec acltest.myproc
BEGIN acltest.myproc; END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at "ACLTEST.MYPROC", line 4
ORA-06512: at line 1


SQL> connect scott/tiger
Connected.

SQL> exec acltest.myproc
BEGIN acltest.myproc; END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29024: Certificate validation failure
ORA-06512: at "ACLTEST.MYPROC", line 4
ORA-06512: at line 1


SQL>
SQL>