How To Create Individual Wallet for databases sharing the same home on the same host

TDE PoC -- self test

Background:
===========

Two Databases on the same server sharing same ORACLE_HOME. How can we configure two Individual wallet for each database

two database: test1 , test2

hostname : db01.oracle.com
==============

Create wallet directories for both instances.

mkdir -p /u01/app/WALLET/test1  /u01/app/WALLET/test2

Change the sqlnet.ora as below (1 single entry for all database but the path will be translated on the basis of ORACLE_SID value)


pENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/u01/app/WALLET/$ORACLE_SID)))


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/WALLET/$ORACLE_SID' IDENTIFIED BY "Oracle123"; -- Keystore Created for test1 instance

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Oracle123"; -- Keystore opened for test1 instance

Now check wallet status on both the instance

open two terminal and set test1 on tty1 and test2 on tty2

on tty1 : instance test1

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN_NO_MASTER_KEY

on tty2 : instance test2

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
NOT_AVAILABLE


Inference:As we didnt create any Keystore for test2 instance hence keystore status is NOT_AVAILABLE


Now create a Keystore for test2  instance


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/WALLET/$ORACLE_SID' IDENTIFIED BY "Oracle123"; -- Keystore Created for test2 instance

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Oracle123"; -- Keystore opened for test2 instance

check the status of keystore in test2 :

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN_NO_MASTER_KEY



Now create master key in both the instance :

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "Oracle123" WITH BACKUP;  -- run this on both test1 and test2

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

On test1 Instance:
==================
SQL> select  utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64

 FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);

MASTERKEYID_BASE64
--------------------------------------------------------------------------------
AVYo2uBozE/DvzpLWTiu/4o=


SQL> !mkstore  -wrl /u01/app/WALLET/$ORACLE_SID -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AVYo2uBozE/DvzpLWTiu/4oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


on test2 Instance:
=================

SQL> select  utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64

 FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);

MASTERKEYID_BASE64
--------------------------------------------------------------------------------
Ab1JjU1zS0+Xv91R1bigDHM=

mkstore  -wrl /u01/app/WALLET/$ORACLE_SID -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = Ab1JjU1zS0+Xv91R1bigDHMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


SQL> create tablespace enc_test1 datafile '/u01/app/test1/TEST1/datafile/test1.dbf' SIZE 10M ENCRYPTION USING 'AES256'  DEFAULT STORAGE (ENCRYPT);

SQL> create tablespace enc_test2 datafile '/u01/app/test1/TEST1/datafile/test2.dbf' SIZE 10M ENCRYPTION USING 'AES256'  DEFAULT STORAGE (ENCRYPT);

SQL> create user test1 identified by Oracle123 default tablespace enc_test1;

SQL> create user test2  identified by Oracle123 default tablespace enc_test2;

SQL> grant connect,resource, unlimited tablespace to test1;

SQL> grant connect,resource, unlimited tablespace to test2;

CREATE TABLE TEST1.appdata(name varchar2(20),roll number);
insert into TEST1.appdata values ('Manoj',1);
insert into TEST1.appdata values ('John',2);
insert into TEST1.appdata values ('Scott',3);

CREATE TABLE TEST2.appdata(name varchar2(20),roll number);
insert into TEST2.appdata values ('Sajan',1);
insert into TEST2.appdata values ('Johnson',2);
insert into TEST2.appdata values ('Tiger',3);

Now from external client(Sql Developer) we loged into test1 and test2 instances and checked

select * from test1.appdata

NAME                       ROLL
-------------------- ----------
Manoj                         1
John                          2
Scott                         3

select * from test2.appdata
NAME                       ROLL
-------------------- ----------
Sajan                         1
Johnson                       2
Tiger                         3



Inference: From this PoC it is proved that we can access Individual database wallet from any application.