Forum Stats

  • 3,817,360 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

Categories

utl_http https certificate using pl/sql in a script works fine, but get 403 error when using procedu

J. André
J. André Member Posts: 10
edited Jun 4, 2014 4:18AM in SQL & PL/SQL

I have this script to use UTL_HTTP.

DECLARE

req   UTL_HTTP.REQ;

resp  UTL_HTTP.RESP;

value VARCHAR2(1024);

BEGIN

  req := UTL_HTTP.BEGIN_REQUEST('http://dba-oracle.com');

  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

  resp := UTL_HTTP.GET_RESPONSE(req);

  LOOP

    UTL_HTTP.READ_LINE(resp, value, TRUE);

    dbms_output.put_line(value);

  END LOOP;

  UTL_HTTP.END_RESPONSE(resp);

EXCEPTION

  WHEN UTL_HTTP.END_OF_BODY THEN

    UTL_HTTP.END_RESPONSE(resp);

END;

/

I adjusted the script to use the Oracle Wallet using https with certificate:

declare

  req    utl_http.req;

  resp   utl_http.resp;

  value  varchar2(1024);

  l_url  varchar2(2000) := 'https://******.*****.nl:443';

  l_data clob;

  cursor c_data is

    select clobdata

    from   post_file;

begin

  open c_data;

  fetch c_data into l_data;

  close c_data;

  utl_http.set_proxy('168.0.0.1:8080', '');

  utl_http.set_wallet('file:C:\wallet', '*******');

  req := utl_http.begin_request(l_url);

  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

  utl_http.set_header(req, 'content-length', length(l_data));

  utl_http.write_text(req, l_data);

  resp := utl_http.get_response(req);

  loop

    utl_http.read_line(resp, value, true);

    dbms_output.put_line(value);

  end loop;

  utl_http.end_response(resp);

exception

  when utl_http.end_of_body then

    utl_http.end_response(resp);

end;

/

This works fine and I get a good response.

But when I put it in a procedure I get:

HTTP Error 403.7 - Forbidden: SSL client certificate is required.

Internet Information Services (IIS)

How is this possible and how can I solve this?

Message was edited by: J. André

I now created the procedure in the sys scheme and it works there. So the question is: what rights are missing for the user scheme, which makes it impossible to use the procedure, but possible doing it by script?

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Paul Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    Don't you think it would be helpful to post the procedure?

    · Share on TwitterShare on Facebook
  • J. André
    J. André Member Posts: 10
    edited Jun 4, 2014 3:13AM

    Thank you for your reply. I didn't think so because it is the same as the script. But here it is:

    create or replace procedure sp_post_https

    as

      req    utl_http.req;

      resp   utl_http.resp;

      value  varchar2(1024);

      l_url  varchar2(2000) := 'https://******.*****.nl:443';

      l_data clob;

      cursor c_data is

        select clobdata

        from   post_file;

    begin

      open c_data;

      fetch c_data into l_data;

      close c_data;

      utl_http.set_proxy('168.0.0.1:8080', '');

      utl_http.set_wallet('file:C:\wallet', '*******');

      req := utl_http.begin_request(l_url);

      utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

      utl_http.set_header(req, 'content-length', length(l_data));

      utl_http.write_text(req, l_data);

      resp := utl_http.get_response(req);

      loop

        utl_http.read_line(resp, value, true);

        dbms_output.put_line(value);

      end loop;

      utl_http.end_response(resp);

    exception

      when utl_http.end_of_body then

        utl_http.end_response(resp);

    end;

    This is the exact response:

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

    <HTML><HEAD><TITLE>The page requires a client certificate</TITLE>

    <META HTTP-EQUIV="Content-Type" Content="text/html; charset=Windows-1252">

    <STYLE type="text/css">

      BODY { font: 8pt/12pt verdana }

      H1 { font: 13pt/15pt verdana }

      H2 { font: 8pt/12pt verdana }

      A:link { color: red }

      A:visited { color: maroon }

    </STYLE>

    </HEAD><BODY><TABLE width=500 border=0 cellspacing=10><TR><TD>

    <h1>The page requires a client certificate</h1>

    The page you are attempting to access requires your browser to have a Secure Sockets Layer (SSL) client certificate that the Web server will recognize. The client certificate is used for identifying you as a valid user of the resource.

    <hr>

    <p>Please try the following:</p>

    <ul>

    <li>Contact the Web site administrator if you believe you should be able to view this directory or page without a client certificate, or to obtain a client certificate.</li>

    <li>If you already have a client certificate, use your Web browser's security features to ensure that your client certificate is installed properly. (Some Web browsers refer

    to client certificates as browser or personal certificates.)</li>

    </ul>

    <h2>HTTP Error 403.7 - Forbidden: SSL client certificate is required.<br>Internet Information Services (IIS)</h2>

    <hr>

    <p>Technical Information (for support personnel)</p>

    <ul>

    <li>Go to <a href="http://go.microsoft.com/fwlink/?linkid=8180">Microsoft Product Support Services</a> and perform a title search for the words <b>HTTP</b> and <b>403</b>.</li>

    <li>Open <b>IIS Help</b>, which is accessible in IIS Manager (inetmgr),

    and search for topics titled <b>About Certificates</b>, <b>Using Certificate Trust Lists</b>, <b>Enabling Client Certificates</b>, and <b>About Custom Error Messages</b>.</li>

    </ul>

    </TD></TR></TABLE></BODY></HTML>

    · Share on TwitterShare on Facebook
  • Paul Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    Where is your database server? On the windows machine where c:\wallet is?

    · Share on TwitterShare on Facebook
  • J. André
    J. André Member Posts: 10
    edited Jun 4, 2014 3:31AM

    Yes, both on the same machine (Microsoft Windows Server 2008 R2 (64-bit)).

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

    · Share on TwitterShare on Facebook
  • Paul Horth
    Paul Horth Member Posts: 3,402 Gold Trophy

    Are you running these as the same user - with the correct ACL applied?

    · Share on TwitterShare on Facebook
  • J. André
    J. André Member Posts: 10

    Yes, same user and correct ACL applied. Also ran by sys and sysman, same results.

    · Share on TwitterShare on Facebook
  • Paul Horth
    Paul Horth Member Posts: 3,402 Gold Trophy 
    J. André wrote:

Yes, same user and correct ACL applied. Also ran by sys and sysman, same results.

    Sorry, I've run out of ideas at the moment - but I'll keep thinking about it.

    · Share on TwitterShare on Facebook
This discussion has been closed.