Using UTL_HTTP and an Oracle Wallet to Establish a Secure URL Connection (SSL)
by Jeff Hunter, Sr. Database Administrator
* Oracle Wallet
* Capture SSL Site Certificate
* Create Oracle Wallet
* Import Certificate into the Oracle Wallet
* Establish Encrypted Connection
* About the AuthorIntroduction
This article describes the steps necessary to establish a secure URL connection (SSL) using the UTL_HTTP Oracle PL/SQL package and an Oracle Wallet. In order to fully demonstrate the required steps, I will be establishing a secure URL connection to the web site https://www.centos.org/ from within PL/SQL.
In order to establish a connection to a secure URL from an Oracle database server, thefollowing tasks will need to be performed:
* Capture all required certificates from the SSL site
* Create an Oracle Wallet that is accessible on the database server
* Import the required certificate(s) of the SSL site into the Oracle Wallet
* Use the UTL_HTTP.SET_WALLET PL/SQL procedure before attempting to access the secure URL
An Oracle Wallet stores all of the encryption keys thatthe database can use and is required in order to access an SSL site using the UTL_HTTP PL/SQL package. Attempting to establish a secure URL connection without an Oracle wallet (and, of course, the required certificates from the SSL site) will result in the code throwing the Oracle error ORA-29024: Certificate validation failure:
CONNECT scott/tigerSET serveroutput ON DECLARE HTTP_REQ UTL_HTTP.REQ; HTTP_RESP UTL_HTTP.RESP; URL_TEXT VARCHAR2(32767); BEGIN DBMS_OUTPUT.ENABLE(1000000); HTTP_REQ := UTL_HTTP.BEGIN_REQUEST('https://www.centos.org/');UTL_HTTP.SET_HEADER(HTTP_REQ, 'User-Agent', 'Mozilla/4.0'); HTTP_RESP := UTL_HTTP.GET_RESPONSE(HTTP_REQ); -- Process Request LOOP BEGIN URL_TEXT := null; UTL_HTTP.READ_LINE(HTTP_RESP, URL_TEXT, TRUE);DBMS_OUTPUT.PUT_LINE(URL_TEXT); EXCEPTION WHEN OTHERS THEN EXIT; END; END LOOP; UTL_HTTP.END_RESPONSE(HTTP_RESP); END; / DECLARE * ERROR at line 1: ORA-29273: HTTP requestfailed ORA-06512: at "SYS.UTL_HTTP", line 1029 ORA-29024: Certificate validation failure ORA-06512: at line 8 |
Although this article will be developed using an Oracle database installed on the Linux operating environment, it is fully transferable to Microsoft Windows.
An Oracle Wallet is nothing more than a logical container (a singlefile named ewallet.p12) that stores all encrypted keys needed by the Oracle database in order to access SSL sites. This is not, however, the only use of an Oracle Wallet. It is also used by many of the more advanced security options in Oracle like Transparent Data Encryption (TDE) or PKI Credentials. These more advanced options are part of Oracle’s Advanced Security Option (ASO) and are onlyavailable when using Oracle Enterprise Edition.
Note that creating and importing keys into an Oracle Wallet and then using that Oracle wallet to establish a secure connection can be done using Oracle Standard Edition. Although I've seen it mentioned that Oracle Wallets only work with Enterprise Edition, this is incorrect. The Oracle Wallet simply stores keys which can be used by the Oracle...