How to encrypt the data (tablespace or column's table) using a software keystore previously known as Oracle Wallet

Tweet

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:
1) Configure the sqlnet.ora file to define a file system location for the Software Keystore
2) Create the Software Keystore 
3) Opening a Software Keystore 
4) Setting the TDE Master Encryption Key in the Software Keystore 
5) Encrypt the Data 

The first four steps are already described here. Now it's time to encrypt your data.

5) Encrypt the Data 
Once you have created an Oracle Wallet and set a TDE master key in it, you can proceed to encrypt your data. Let's start creating a new encrypted tablespace first and then a column's table. My current data files:

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/CDB001/system01.dbf
/app/oracle/oradata/CDB001/sysaux01.dbf
/app/oracle/oradata/CDB001/undotbs01.dbf
/app/oracle/oradata/CDB001/users01.dbf

The statement to create an encrypted tablespace:

SQL> create tablespace ts_encrypted datafile '/app/oracle/oradata/CDB001/ts_encrypted01.dbf' size 10M encryption default storage(encrypt);

Tablespace created.

Information about the encrypted tablespace available in the database.

SQL> select a.name, b.TS#, b.ENCRYPTEDTS, b.ENCRYPTIONALG, b.CON_ID from V$TABLESPACE A, V$ENCRYPTED_TABLESPACES B                     
  2  where A.ts# = B.ts#;

NAME      TS# ENCRYPTEDTS ENCRYPTIONALG CON_ID
------------ --- ----------- ------------- ------
TS_ENCRYPTED 5   AES128      YES           1

How is it possible to test if the data is encrypted or not ? I'm going to create a table on the USERS (unencrypted) tablespace and another on the TS_ENCRYPTED tablespace. Because the Oracle Wallet is already open I can create on the encrypted tablespace the t1_encrypted table and insert some rows in it.

SQL> create table t1_not_encrypted (text varchar2(255)) tablespace USERS;

Table created.

SQL> create table t1_encrypted (text varchar2(255)) tablespace TS_ENCRYPTED;

Table created.

SQL> insert into t1_not_encrypted values ('my name is marcov');

1 row created.

SQL> insert into t1_encrypted values ('the secrets of marcov');

1 row created.

SQL> commit;

Commit complete.

Flush the buffer cache to be sure all data is written to the datafiles.

SQL> alter system flush buffer_cache;

System altered.

I'm able to grep and see the text on the USERS tablespace, but not that one on the TS_ENCRYPTED tablespace.

[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/users01.dbf|grep "my name is"
my name is marcov
[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/ts_encrypted01.dbf|grep "secrets"
[oracle@localhost oracle]$

Let's see what happens when the Oracle Wallet is closed. The following command closes an open Oracle Wallet.

SQL> administer key management set keystore close identified by "0racl30racle3";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  CLOSED UNKNOWN     SINGLE    UNDEFINED 0

When the Oracle Wallet is closed you can query every tables but those based on an encrypted tablespace.

SQL> select * from t1_not_encrypted;

TEXT
-----------------
my name is marcov

SQL> select * from t1_encrypted;
select * from t1_encrypted
              *
ERROR at line 1:
ORA-28365: wallet is not open

You have to open again the Oracle Wallet to successfully execute the query

SQL> administer key management set keystore open identified by "0racl30racle3";

keystore altered.

SQL> select * from t1_encrypted;

TEXT
---------------------
the secrets of marcov

Let's see how a closed Oracle Wallet affects an encrypted column of a table. I'm going to create a new table with two columns: one is encrypted and the other is not encrypted.

SQL> create table c##marcov.t2_column_encrypted (text varchar2(255), text_encrypted varchar2(255) encrypt) tablespace USERS;

Table created.

SQL> insert into c##marcov.t2_column_encrypted values ('this column is not encrypted', 'the secrets of marcov');

1 row created.

SQL> commit;

Commit complete.

The Oracle Wallet is closed.

SQL> administer key management set keystore close identified by "0racl30racle3";

keystore altered.

When the Oracle Wallet is closed I can able to query the non-encrypted column.

SQL> select text from c##marcov.t2_column_encrypted;

TEXT
----------------------------
this column is not encrypted

But when I try to query the encrypted column it fails:

SQL> select * from c##marcov.t2_column_encrypted;
select * from c##marcov.t2_column_encrypted
                        *
ERROR at line 1:
ORA-28365: wallet is not open

I need first to open the Oracle Wallet

SQL> administer key management set keystore open identified by "0racl30racle3";

keystore altered.

Now I can query again the encrypted column of my table.

SQL> select * from c##marcov.t2_column_encrypted;

TEXT                         TEXT_ENCRYPTED
---------------------------- ---------------------   
this column is not encrypted the secrets of marcov

That's all.