Random Posts

Homepage  ›  Uncategorized

How to Enable Auto Login in Oracle Wallet Using Orapki Updated FREE

How to Enable Auto Login in Oracle Wallet Using Orapki

Home » Articles » Misc » Hither

UTL_HTTP and SSL (HTTPS) using Oracle Wallets

Since Oracle 9i Release 2, the UTL_HTTP package has had the ability to access resource over HTTPS as well as HTTP. This article describes the method for enabling HTTPS admission from the UTL_HTTP package.

  • Access Control List (ACL)
  • Examination Unsecured Connection
  • Get Site Certificates
  • Create an Oracle Wallet Containing the Certificates
  • Test Secured Connection
  • Authentication
  • SSLv3, TLSv1 and POODLE

Access Control Listing (ACL)

If you lot are using Oracle 11g, yous will demand to provide an ACL to allow the UTL_HTTP package to interact with an external host. This is described hither.

  • Fine-Grained Admission to Network Services in Oracle Database 11g Release 1

Test Unsecured Connection

Earlier we start trying to configure SSL, lets see what happens if nosotros endeavour to access a HTTPS resource using the UTL_HTTP bundle. To do this, create the following procedure.

CREATE OR REPLACE PROCEDURE show_html_from_url (   p_url  IN  VARCHAR2,   p_username IN VARCHAR2 DEFAULT NULL,   p_password IN VARCHAR2 DEFAULT NULL ) AS   l_http_request   UTL_HTTP.req;   l_http_response  UTL_HTTP.resp;   l_text           VARCHAR2(32767); Brainstorm   -- Brand a HTTP request and get the response.   l_http_request  := UTL_HTTP.begin_request(p_url);    -- Use basic hallmark if required.   IF p_username IS Not NULL and p_password IS NOT NULL Then     UTL_HTTP.set_authentication(l_http_request, p_username, p_password);   END IF;    l_http_response := UTL_HTTP.get_response(l_http_request);    -- Loop through the response.   BEGIN     LOOP       UTL_HTTP.read_text(l_http_response, l_text, 32766);       DBMS_OUTPUT.put_line (l_text);     Finish LOOP;   EXCEPTION     WHEN UTL_HTTP.end_of_body THEN       UTL_HTTP.end_response(l_http_response);   END; EXCEPTION   WHEN OTHERS Then     UTL_HTTP.end_response(l_http_response);     RAISE; Cease show_html_from_url; /

This process works for a regular HTTP resource, but what happens if nosotros call information technology using a HTTPS resource? The following case uses "https://gb.redhat.com/".

Set up SERVEROUTPUT ON EXEC show_html_from_url('https://gb.redhat.com/');  * Mistake at line 1: ORA-29273: HTTP asking failed ORA-06512: at "SYS.UTL_HTTP", line 1527 ORA-29261: bad argument ORA-06512: at "Test.SHOW_HTML_FROM_URL", line 22 ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-29024: Certificate validation failure ORA-06512: at line ane  SQL>

The error stack shows the "ORA-29024: Certificate validation failure" error.

Get Site Certificates

In gild to brand connections to a secured resource, we need to get the necessary document. The easiest way to practice this is using a browser. The example below uses the Chrome browser.

Using the browser, go to the URL you are attempting to access from PL/SQL. In this case "https://gb.redhat.com/". Click the lock icon in the URL bar to display the certificate menu and click on the "Connection" tab.

Certificate Menu

Click the "Certificate data" link and click the "Certification Path" tab on the resulting dialog.

Certification Path

For the root node in the "Certification path", highlight the node and click the "View Document" button. On the resulting dialog, click the "Details" tab and click the "Copy to File..." button to save the certificate data.

Certificate Details

On the resulting sorcerer, exercise the following.

  • Click the "Side by side" button on the welcome screen.
  • Select the "Base-64 encoded X.509 (.CER)" selection and click the "Next" push. Other formats piece of work, but I've found this to be the near consistent.
  • Enter suitable file name and click the "Next" button.
  • Click the "Finish" push button.

A similar dialog is displayed in Firefox past clicking "URL Icon > More Information > View Certificate > Details Tab".

Thanks to Erik for pointing out I don't demand to download the intermediate certificates. Merely the root document.

Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/DB11G/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/DB11G/wallet -pwd WalletPasswd123 -auto_login

If the wallet countersign is too weak, you will get a message telling you so.

Invalid password.... PASSWORD_POLICY : Passwords must have a minimum length of viii  characters and contain alphabetic characters combined with numbers or  special characters.

In Oracle 11.2 the same issue causes a failure to create the wallet with the following message.

Unable to relieve wallet at /u01/app/oracle/admin/DB11G/wallet

With the wallet created, nosotros tin add the certificate we saved earlier.

$ orapki wallet add together -wallet /u01/app/oracle/admin/DB11G/wallet -trusted_cert -cert "/host/BaltimoreCyberTrustRoot.crt" -pwd WalletPasswd123

The root certificate may neglect to load with the following message, which tin be ignored. It just means it was already present by default.

Could non install trusted cert at/host/Builtin Object Token:GTE CyberTrust Global Root PKI-04003: The trusted certificate is already present in the wallet.

Test Secured Connection

Nosotros are now gear up to access the secured resource, just we must provide the UTL_HTTP package with the wallet details then information technology tin make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure. Repeating the previous test at present works successfully.

SET SERVEROUTPUT ON EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DB11G/wallet', 'WalletPasswd123'); EXEC show_html_from_url('https://gb.redhat.com/');  ... HTML output removed ...  PL/SQL procedure successfully completed.  SQL>

From Oracle 11gR2 onward, if you are using the -auto_login option on the wallet, you don't have to specify the wallet password. You just pass NULL instead of the password. Thanks to Jason in the comments for pointing this modify out!

SET SERVEROUTPUT ON EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DB11G/wallet', Nothing); EXEC show_html_from_url('https://gb.redhat.com/');  ... HTML output removed ...  PL/SQL process successfully completed.  SQL>

You may want to comprise this into the procedure.

CREATE OR Supersede PROCEDURE show_html_from_url (   p_url              IN  VARCHAR2,   p_username         IN  VARCHAR2 DEFAULT Nada,   p_password         IN  VARCHAR2 DEFAULT NULL,   p_wallet_path      IN  VARCHAR2 DEFAULT NULL,   p_wallet_password  IN  VARCHAR2 DEFAULT NULL ) AS   l_http_request   UTL_HTTP.req;   l_http_response  UTL_HTTP.resp;   l_text           VARCHAR2(32767); BEGIN   -- If using HTTPS, open a wallet containing the trusted root certificate.   IF p_wallet_path IS Non Null AND p_wallet_password IS Non NULL So     UTL_HTTP.set_wallet('file:' || p_wallet_path, p_wallet_password);   END IF;    -- Brand a HTTP request and get the response.   l_http_request  := UTL_HTTP.begin_request(p_url);    -- Use basic authentication if required.   IF p_username IS NOT Cypher and p_password IS Non Cipher THEN     UTL_HTTP.set_authentication(l_http_request, p_username, p_password);   Finish IF;    l_http_response := UTL_HTTP.get_response(l_http_request);    -- Loop through the response.   Begin     LOOP       UTL_HTTP.read_text(l_http_response, l_text, 32766);       DBMS_OUTPUT.put_line (l_text);     END LOOP;   EXCEPTION     WHEN UTL_HTTP.end_of_body THEN       UTL_HTTP.end_response(l_http_response);   END; EXCEPTION   WHEN OTHERS And so     UTL_HTTP.end_response(l_http_response);     RAISE; Terminate show_html_from_url; /

Hallmark

If you are accessing a site that requires authentication, y'all volition need to do one of two things depending on the type of authentication used.

If the site uses basic authentication, simply specify the credentials in the call to SHOW_HTOM_FROM_URL, which will use them in the UTL_HTTP.SET_AUTHENTICATION call.

SET SERVEROUTPUT ON EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/DB11G/wallet', 'WalletPasswd123'); EXEC show_html_from_url('https://gb.redhat.com/', 'username', 'countersign');  ... HTML output removed ...  PL/SQL procedure successfully completed.  SQL>

If the folio uses assimilate hallmark, and so you will demand to will need to install the digest_auth_api package, so make the following modification to the examination lawmaking.

CREATE OR REPLACE Procedure show_html_from_url (   p_url              IN  VARCHAR2,   p_username         IN  VARCHAR2 DEFAULT NULL,   p_password         IN  VARCHAR2 DEFAULT NULL,   p_wallet_path      IN  VARCHAR2 DEFAULT Zippo,   p_wallet_password  IN  VARCHAR2 DEFAULT Nothing ) As   l_http_request   UTL_HTTP.req;   l_http_response  UTL_HTTP.resp;   l_text           VARCHAR2(32767); Brainstorm   -- If using HTTPS, open a wallet containing the trusted root certificate.   IF p_wallet_path IS NOT NULL AND p_wallet_password IS Non Zilch And then     UTL_HTTP.set_wallet('file:' || p_wallet_path, p_wallet_password);   Terminate IF;    -- Make a HTTP request and get the response.   l_http_request  := digest_auth_api.begin_request(p_url          => p_url,                                                    p_username     => p_username,                                                    p_password     => p_password,                                                    p_method       => 'Get');    l_http_response := UTL_HTTP.get_response(l_http_request);    -- Loop through the response.   Brainstorm     LOOP       UTL_HTTP.read_text(l_http_response, l_text, 32766);       DBMS_OUTPUT.put_line (l_text);     End LOOP;   EXCEPTION     WHEN UTL_HTTP.end_of_body THEN       UTL_HTTP.end_response(l_http_response);   Terminate; EXCEPTION   WHEN OTHERS THEN     UTL_HTTP.end_response(l_http_response);     RAISE; END show_html_from_url; /

You tin can then call the test lawmaking in the same manner y'all did for basic authentication.

SET SERVEROUTPUT ON EXEC show_html_from_url('https://gb.redhat.com/', 'username', 'password', '/u01/app/oracle/admin/DB11G/wallet', 'WalletPasswd123');  ... HTML output removed ...  PL/SQL procedure successfully completed.  SQL>

SSLv3, TLSv1 and POODLE

With the publicity nearly the POODLE issues, many spider web masters are turning off SSLv3 support. Depending on your Oracle database version/patch, that can present a chip of a problem for people using UTL_HTTP to access HTTPS resources, as described hither.

  • UTL_HTTP Package Fails With ORA-29273 ORA-28860 When Using TLSv1 (Physician ID 727118.one) : Basically, older database releases only permit HTTPS using the SSLv3 protocol from UTL_HTTP. If you want to utilise the TLSv1 protocol you demand to make certain you are on a patched upward version of 11.2.

The MOS note for the following annotate has been removed/hidden, so it's possible this was a problems that is now fixed in 12.ane.0.2.

Interestingly, if y'all upgrade to Oracle 12c, you might have problems in the other management, since Oracle 12c prevents UTL_HTTP calls over HTTPS to anything older than TLSv1.2, as described here.

  • UTL_HTTP Gives Mistake Over HTTPS Using RDBMS 12.1.0.1.0 (Doc ID 1675966.1) So you might have problem accessing legacy systems, without reverting to HTTP.

For more information see:

  • orapki Utility
  • UTL_HTTP
  • Master Annotation For SSL/TLS (Doc ID 2229775.i)

Hope this helps. Regards Tim...

Back to the Pinnacle.

How to Enable Auto Login in Oracle Wallet Using Orapki

DOWNLOAD HERE

Source: https://oracle-base.com/articles/misc/utl_http-and-ssl

Posted by: knudsonrowleted.blogspot.com

Related Posts

There is no other posts in this category.
Subscribe Our Newsletter