Skip navigation

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.

@Although a lot of time if published over the past period as Oracle 12c The number of systems in use, which is not much. The reason is that habits, operating system go to request a new structure, financial issues, etc. ... Although the reasons we 12c Oracle database administrators we await a form ready for use with the new features coming to. Questions in mind and with little basic information about Oracle 12c examples for beginners to this article. I will try to shed light on the signs.

 

What is Oracle 12c?

Oracle's development up to this time in hikayesini Oracle 9i RAC ourselves from the feature with

We can take as a starting point. After us comes with a different point in 10g Grid Computing Targeting low-cost hardware and storage, and using these features with 11g management relieved more elinibiraz amendment to the part of the database administrator. However, Oracle 12c Cloud through the structure with both architectural and logically emerged as a new product. Now you manage multiple databases on a single instance of them in front of us and we can easily A structure was presented we can move.

 

He took us in different foreign concept in this structure. If we examine them;

 

CDB: Container Database (Super Database). Located at the bottom of the system's main database work

It can be considered.

 

PDB: Pluggable Database (Sub Database). OLTP Online live or stand on a daily basis

We used the databases. It has transportable.

Oracle white paper on this concept of a single super multiple databases describing database

(multitenant database container) is defined as taking place within.

 

NON-PDB: Oracle databases are not portable feature of 12c.

PDB $ SEED: Container Pluggable databases on the stage of installation template database

The building used to be.

 

Container Database:

We call the Super Database and system databases in different hosts. At first glance

will look at the main differences are as follows.

 

• Under construction ROOT 'Oracle Meta System' and areas where the process.

• 'Common Users' ie areas where the management Once users.

• Instance logic in the area where the memory and process management.

• 1 to 255 up to the PDB and we can keep our main database.

• to provide management rights over the area and can give the PDB.

 

001.JPG

CDB $ ROOT / Root Files

Oracle Container called CDB $ ROOT under construction and is available in a structure where the database file.

 

001.JPG

The biggest innovation here;

- Oracle 12c is using common CF and redolog of existing PDB.

- Flashback Logs in this area, is located in Log an Archive.

- Pluggable databases of System, SYSAUX, Users, tempfiles takes place undo.

 

Pluggable Database (PDB)

The cornerstone of Oracle 12c can say that structural changes. You can now easily move to another building our database, we can increase the number of such request. Components are and management of this structure is as follows.

- Application of and TEMP (for use only in itself) tablespaces

- PDB users and rights

- Metadata of the PDB Systems

- The data of running applications

- Resource Plan

001.JPG

Oracle 12c belonging to users and these users must examine separately the rights Container Pluggable the database and the database.

 

Container Database:

Located at the heart of the system, which takes place in the early summer of this structure also has the authority to regulate the rights of other users and databases. Users in this building "Common Users" as pass. If we examine the characteristics of those users;

 

  • § Users are managed from Root Containers
  • Each user must start with the C ## first name.
  • Container applies to all users on the Pluggable.
  • Individual users are.
  • Creating user authorization CONTAINER we can do with the command line = ALL parameter.
  • Container database on the roles of "Common Role" is called. If we examine this structure;
  • Rooter managed Containers
  • CREATE ROLE - CONTAINER SET parameter is used
  • first name must start with the C ##.

Pluggable Database:We can think of this structure situated in logic before Oracle database 12c. Our users can be created on the database it is only used in this mantıkd yetkilendirilip. Basically, we have to examine the properties;

  • Pre - Oracle 12c
  • Pluggable user is
  • Pluggable may be on different databases with the same name.


Pluggable databases on the roles of "Local Role" is called. If we examine this structure;

  • Pluggable databases are managed.
  • We use the syntax CREATE ROLE
  • Pluggable which is available on the database created.

001.JPGIf we follow the above flow user will be revealing to us about the issue. Located on the left side ## and C SYS DBA our users are managed via the form located on the CDB $ ROOT Container structure is able to process all Pluggable databases. On the other hand Container pdbtst1 and maintains its own databases in pdbts2 Pluggable HR and NAT users. However, users are only able to process the same name in their database. If we examine the command line to create user and their rights;Create user c##dba identified by test1 container=ALL; Grant create session to c ## dBA container = ALL;Grant select any table to ## dBA container c = ALL; Pluggable use when connecting to the database are as follows:sqlplus sys / ******* @ pdbtst1 as sysdba;sqlplus sys / ******* @ pdbtst2 as sysdba; Pluggable Database Creation Pluggable veritabna the Container will use the system, let's create the structure. In this creation process can create Pluggable database in different ways. Prior to the transaction; - $ CBD is not necessary in root' Let's check where we areSQL> show con_n renovationCON_N renovationCDB $ ROOT - Let's see if our database is $ opening the questioning mode.NOTE: CDB always read-write mode $ SEED always be in the PDB is located in READ ONLY mode.

  1. 1. Create the PDB without FILE_NAME_CONVERT

This method of template as the existing data base of the Container done to create the required directory structure using the form in itself.CREATE PLUGGABLE DATABASE PDBTST1 ADMIN USER PDBTST1_ADMIN IDENTIFIED BY oracle ROLES = (connect,resource); *** We set the parameters for the session pdb_file_name_convert we connected prior to this.alter session set pdb_file_name_convert = 'PDB', 'PDBTST1'; NOTE: Create command will look like after alert.log information. Unusable and was created in NEW mode.

  1. 2. Create the PDB with FILE_NAME_CONVERT

 

FILE_NAME_CONVERT will create the database using the parameters we will provide the ability to create its own set of directories.

 

CREATE DATABASE PDBTST1

ADMIN USER IDENTIFIED BY PDBTST1_ADM the oracle

FILE_NAME_CONVERT = ('/ disk2 / app / oracle / oradata / ORCL / pdbseed', '/ disk2 / DBF');

 

Finally look at the condition of the Container Pluggable database on the database.

SQL> SELECT name, open_mo FROM v $ PDBs;

NAME in OPEN_MO

PDB READ ONLY $ SEED

NEW PDBTST1

 

Database Open- Close Modes

 

Oracle also one of the differences in the administrative database 12c and the opening and closing operation of the database. Basically, we can also make opening and closing operations of the other Pluggable data base Container database. If we examine each of these modes;

 

- Database Release Operations

The container can open a database or multiple databases with the following command.

 

ALTER PLUGGABLE DATABASE pdbtst1 OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

 

For the control of data base opened;

SQL> SELECT name, open_mo FROM v $ PDBs;

NAME in OPEN_MO

PDB READ ONLY $ SEED

PDBORCL READER WRITE

PDBTST1 READER WRITE

 

If we want to open the database container will be sufficient command we do in a normal database;

 

SQL> ALTER DATABASE OPEN;

SQL> STARTUP

 

- MOUNT - SHUTDOWN Operations

Logic applies to the shutdown of the opening process. Container databases in the script we used to a regular database.

SQL> ALTER DATABASE MOUNT;

SQL> STARTUP MOUNT

 

Container as in the database transaction is the same logic applies in the closure opening process. In short, we can do operations on Container.

 

ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE;

ALTER PLUGGABLE DATABASE ALL CLOSE;

ALTER PLUGGABLE DATABASE CLOSE;

SHUTDOWN IMMEDIATE ;

 

Finally, queries can still Pluggable databases on the container.

 

SQL> SELECT name, open_mo FROM v $ PDBs;

NAME in OPEN_MO

PDB READ ONLY $ SEED

PDBORCL READER WRITE

PDBTST1 MOUNTED