Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dominic.

Asked: April 04, 2016 - 4:22 pm UTC

Answered by: Connor McDonald - Last updated: December 13, 2019 - 3:22 am UTC

Category: Database - Version: 11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

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

and we said...

I'm not sure I'm following your question, but here's an example of definers right being carried through to other users, ie, I only granted the ACL to ACLTEST, yet SCOTT can execute the proc, eg


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>


and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

April 05, 2016 - 8:09 am UTC

Reviewer: Dominic Morris from UK

Hi Connor, thanks for the quick reply.

My problem is that if I create the acl and procedure while logged in as acltest, I need to define the procedure with authid current_user to get the connection even when I call it while logged in as acltest.

Do I need to create the acl as sysdba or is it that I need to grant execute on dbms_network_acl_admin directly to acltest rather than relying on the dba role to execute it?

thanks

Dominic
Connor McDonald

Followup  

April 05, 2016 - 11:56 am UTC

Hmmm...I'm not sure to be honest. I've always created them as sysdba.

Try it and let us know.

April 06, 2016 - 1:50 pm UTC

Reviewer: Dominic Morris

Neither of those worked unfortunately. I found an existing acl that is used to access an http site, and assigned the https host I am trying to reach with a port range of 1 to 999. All the code to access both these web services is in the same package but the call to the http service works while the https call generates the ORA-24247 error. I think I am going to have to go to support with this one

Dominic

April 20, 2016 - 10:51 am UTC

Reviewer: Dominic

It turns out I had created my ACL incorrectly. when calling a url of format <subsection>.<companyname>.co.uk, that is what value that needs to be assigned to the acl, not just <companyname>.co.uk (although I assume *.<companyname>.co.uk would work)

The question still remains why the acl did allow a user to connect to the service using the incomplete url specification unless it is because the user calling it in an anonymous plsql block was a dba and therefore could bypass the ACL?


Deepening the question

December 03, 2019 - 12:31 am UTC

Reviewer: Arão Benjamin from São Paulo, Brazil

Hi, I'm having the exact problem Dominic is reporting.

My Release is 11.2g.

I have a stored procedure that uses UTL_HTTP to request from an SSL address, created by user ABC. Also, I created an ACL with a Wallet with the Certificate of the website with the privileges of connect and resolve to user ABC.

It works fine if I define my procedure with Invokers Right and call it from an anonymous block with user ABC. Once I call it from a Trigger created by user ABC, or define the procedure with Definers Right, calling it from anywhere, I get the error ORA-24247.

I can't figure out why the DB don't recognize ABC as the owner of the procedure when I set it to Definers Right.
Connor McDonald

Followup  

December 13, 2019 - 3:22 am UTC

can you give us an end to end test case

More to Explore

DBMS_NETWORK_ACL_ADMIN

More on PL/SQL routine DBMS_NETWORK_ACL_ADMIN here