Oracle Wallet Manager is a tool that provides the accession of data by safety methods. By providing the management of crypted secured SSL certifications datas can  transfer with webservices in more secure way. Besides, with OWM We can crypto datas in the bade of column , table or tablespace by using the method of Transparent Database Encription (TDE). Hereby, nobody can access to our crypted datas  and also back up can not be read in the case of unwanted accessions.

In this article We will explain OWM,orapki tools and upload of SSL certifications and get/read the datas of webservices with the example procedures. especially due to the fact that most of the Database Admins meet this kind of situation, We will explain what you have to do step by step.

You can connect to any web service over https with Oracle database and get the required data. To achieve this you need to use Oracle Wallet Manager or orapki. Oracle Wallet Manager is a GUI utility and orapki is a command line utility.
You can use certificates safely when you load the certificates by defining password.

To get the certificate; In Internet Explorer, click Tools, then click Internet Options and Content tab. Under Certificates you need to export the .CER file (mysite.org.cer). Also you can manage certificates with mmc console.

You need to define the ACL (Access Control List) for the user in the database:

STEP 1 : First of all we will create ACL (Acces Control List)  as the following. We point out the user which is authorized to run web service. 

BEGIN

   DBMS_NETWORK_ACL_ADMIN.create_acl (acl           => '/sys/acls/https.xml',

description   => NULL,

principal     => 'MYUSER',

is_grant      => TRUE,

                                      privilege     => 'connect',

start_date    => NULL,

end_date      => NULL);

   COMMIT;

END;

We can drop ACL with “DROP_ACL” packages.

 

BEGIN

   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(

      acl => '/sys/acls/https.xml');

END;

STEP 2 :  We define “connect” grant in order to access to  web service  for “https://”  extentions.

BEGIN

   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (acl         => '/sys/acls/https.xml',

principal   => 'MYUSER',

is_grant    => TRUE,

                                         privilege   => 'connect');

END;

STEP 3We can assign ACL to hosts. In the host field , we can define hostname or IP (ex: www.oracle.com or 192.168.10.10)   and use ‘*’ for  all hosts.

BEGIN

   DBMS_NETWORK_ACL_ADMIN.assign_acl (

      acl          => '/sys/acls/https.xml',

      HOST         => '*',

      lower_port   => NULL,

      upper_port   => NULL);

   COMMIT;

END;

  • We can unassign as below:

BEGIN

   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(

     acl => '/sys/acls/https.xml',

     host        => '*');

END;

  • By the query as below, we can see grant of acl, host of acl and also all descriptions that we have done.


001.JPG

STEP 4You need to create wallet firstly. There are two options: OWM GUI utility or ORAPKI command utility.

  • To use the OWM GUI utility:

The default path of the wallet directory is “$ORACLE_HOME/owm/wallets/oracle” . You can start Oracle Wallet Manager by using ‘owm’ command at $ORACLE_HOME directory.

[oracle@testvm ~]$ owm

 

If your Operating system is Windows OS, you can run Wallet Manager from  “Start-> Programs-> Oracle home->Integrated Management Tools-> Wallet Manager”.

001.JPG

STEP 5We can define password when creating wallet for security.. As  you see screen shot in below , Complex Password will increase to Wallet Security.

001.JPG

001.JPG

STEP 6 : You need to save after creating wallet.

001.JPG

Now you can see automatically created wallet files under default wallet directory.

[oracle@testvm ~]$ cd $ORACLE_HOME/owm/wallets/oracle

[oracle@testvm oracle]$ ls -lrt

total 8

-rw-------. 1 oracle oinstall 3512 Apr 15 14:22 ewallet.p12

-rw-------. 1 oracle oinstall 3589 Apr 15 14:22 cwallet.sso

 

STEP 7: After creation of Wallet with Password , we can import/load it from “import Trusted Certificate” .


001.JPG

  • We select certification from path.

001.JPG

  • For Upload Certification, we can select “mysite.org.cer” certificate from “$ORACLE_HOME/owm/wallets/oracle ” then click OK.

001.JPG

STEP 8 : And finally  with thescreen as below we can see all details of certification, especially the datas such as “Subject Name”, “Expiration Date”and “Key Type” that can be seen in the first attention.


001.JPG


  • To use orapki command utility:

You can create wallet on any directory as  ‘/home/oracle/certificate’.

$> orapki wallet create -wallet /home/oracle/certificate -pwd mypassword -auto_login

Then you can add the certificate.

$> orapki wallet add -wallet /home/oracle/certificate -trusted_cert -cert mysite.org.cer -pwd mypassword

If you meet errors as below during the process of uploding of certifications, you can find ID of metalink document in order to examine and understand the errors.

  • If the

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1722

ORA-29024: Certificate validation failure

ORA-06512: at line 1

 

Related Knowledge in Metalink: Ora-28782 Using Utl_http.Request (Doc ID 1447858.1)

Using UTL_HTTP on a SSL Secured Web Site Fails With ORA-29024 (Doc ID 388301.1)

  • If no access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list:

ERROR at line 1:
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 line 3

Related Knowledge in Metalink:  ORA-24247 Error Using ACL through Listener (Doc ID 1430315.1)

ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later (Doc ID 1209644.1)

  • If there is no access from the database:

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1722

ORA-12535: TNS:operation timed out

ORA-06512: at line 1

Related Knowledge in Metalink:  Executing UTL_HTTP.REQUEST Fails on the Second Invocation With: ORA-29273 (Doc ID 1414923.1)

 

STEP 9 And we can check verified certificate following SQL statements;

SELECT sys.UTL_HTTP.request ('https://www.mysite.org.tr/myservice,null,'file:/home/oracle/certificate/','mypassword') FROM DUAL;

Result:

  1. SYS.UTL_HTTP.REQUEST('HTTPS://

--------------------------------------------------------------------------

<!doctype html><html itemscope="" itemtype="http://schema.org/WebPage" lang="tr"><head><meta content="/images/mysite_favicon_128.png" itemprop="image"><title>Mysite</title><script>(function(){window.mysite={kEI:'LAsYmfGciuaQGKrIKQAA',kEXPI:'3700261,3700362,4011559,4017578,4020347,4021338,4028717,4028932,4029515,4029666,4029815,4030091,4030440,4031242,4031304,4031391,4031622,4031691,4031735,4031737,4031789,4032032,4032059,4032159, ,authuser:0,kSID:'c9c918f0_10'};mysite.kHL='tr';})();(function(){mysite.lc=[];……………………

STEP 10 : Here is a sample procedure to call a web service:

CREATE OR REPLACE PROCEDURE "MYUSER"."ADDRESS_SEARCH" (No in varchar2,

Kod in out varchar2) IS

  soap_request varchar2(30000);

  soap_respond varchar2(30000);

  http_req utl_http.req;

  http_resp utl_http.resp;

  resp varchar2(30000);

  VAR_URL varchar2(100) := 'https://www.mysite.org.tr/myservice?wsdl';

BEGIN

  begin

     soap_request := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:kps="http://mysite.org.tr">

   <soapenv:Header>

  <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

   <wsse:UsernameToken wsu:Id="sample"

xmlns:wsu="http://schemas.xmlsoap.org/ws/2003/06/utility">

<wsse:Username>004|mysite</wsse:Username>

   </wsse:UsernameToken>

  </wsse:Security>

  </soapenv:Header>

   <soapenv:Body>

      <kps:AddressSearch>

         <params>

            <No>'||No||'</No>

<cacheStyle>2</cacheStyle>

         </params>

      </kps:AddressSearch>

   </soapenv:Body>

</soapenv:Envelope>';

  exception

      when others then

                null;

       --return http_resp.status_code;

  end;

 

   begin

      utl_http.set_wallet('file:/home/oracle/certificate/','mypassword');

      http_req:= utl_http.begin_request(VAR_URL, 'POST', 'HTTP/1.1');

      utl_http.set_header(http_req, 'Content-Type', 'text/xml;charset=utf-8');

      utl_http.set_header(http_req, 'Content-Length', length(soap_request));

      utl_http.set_header(http_req, 'SOAPAction','""');

      utl_http.write_text(http_req, soap_request);

      http_resp:= utl_http.get_response(http_req);

      utl_http.read_text(http_resp, soap_respond);

      utl_http.end_response(http_resp);

   exception

      when others then

      null;

    --return http_resp.status_code;

   end;

 

END;

 

  • § Here is the PLSQL web service through above procedure:

 

DECLARE

  NO VARCHAR2(32767);

  KOD VARCHAR2(32767);

 

BEGIN

  NO := 25110000000;

  KOD := 34;

  MYUSER.ADDRESS_SEARCH ( NO, KOD );

  COMMIT;

END;

In conclusion, with this article we tried to explain  how to upload certifications by using OWM and orapki tools, how to call sample webservices and also which accession descriptions are used in these process. We expect that this article  will be beneficial for your studies.