TDE Column Level Encryption in Oracle 12c database (in Oracle E-Business Suite Release 12.2.4) - Part 1

This article is the series of steps to implementation  One of the Oracle Advanced Security Features called Transparent Data Encryption (TDE) at Column Level. The level encryption can be implemented on the highly sensitive columns of critical tables.

The brief details of this environment are Oracle EBS 12.2.4 with 2 Node RAC 12.1.0.2 setup.  I believe this procedure works well enough in environments other than EBS.

What is expected from this article?

It explains how the TDE Column-level encryption can be implemented on the proposed sensitive columns, also explains the implemented Wallet information, Encryption Algorithm, Columns that got encrypted, Procedure to perform backup and restore/duplicate the database in Encrypted environment. It will also help in managing the environment with useful commands.

It is assumed that no prior TDE setup is done and no prior Wallet locations exist in sqlnet.ora files.

Implementation Steps: - 

1. Set Wallet location in sqlnet.ora (Ifile) in both DB Nodes.

cd /u03/oracle/product/12.1.0.2/admin/ERPDB/wallet/

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/u03/oracle/product/12.1.0.2/admin/ERPDB/wallet)))


## If it is EBS environment then do the above change in IFILE and run the autoconfig in DB Servers.

2. Create Software Password-Based Keystore from the 1st node.

set linesize 200

col wrl_parameter format a60

select * from gv$encryption_wallet;

ALTER KEY MANAGEMENT CREATE KEYSTORE '/u03/oracle/product/12.1.0.2/admin/ERPDB/wallet' IDENTIFIED BY Password ;

select * from gv$encryption_wallet;

3. Open the Keystore in the DB instance.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Password;

select * from gv$encryption_wallet;

4. Create Master Encryption Key (1st node).

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY WITH BACKUP USING 'erpdb_key_bkp';

select * from gv$encryption_wallet;

5. Enable the Auto-login Wallet in the 1st node.

ADMINISTER KEY MANAGEMENT 
CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u03/oracle/product/12.1.0.2/admin/ERPDB/wallet' IDENTIFIED BY password;

===================================================================
COPY THE ABOVE GENERATED ALL THE KEYSTORES FROM WORKING NODE TO REMAINING DB NODE.
BOTH NODES SHOULD HAVE THESE IDENTICAL KEYSTORES IN WALLET LOCATION.
===================================================================

6. Copy Wallet via SCP command.

Node 1:-
cd /u03/oracle/product/12.1.0.2/admin/ERPDB/wallet
scp * orappt@DBHOSTNAME2:/u03/oracle/product/12.1.0.2/admin/ERPDB/wallet

## Check both locations have the same files with proper permissions and the same size.
## connect to 2nd Node DB and make sure the wallet is in an opened state.
# In EBS Database, Run the script $FND_TOP/sql/aftdeval.sql to generate the encryption script (Refer Doc ID 1585696.1) for more details.

6. Data Encryption (1st node).

spool Encrypt_Columns.log
set echo on;
--HR
ALTER TABLE HR.PER_PAY_PROPOSALS modify (PROPOSED_SALARY encrypt using 'AES256');
............
............
spool off;

7. Cross verify if all the proposed columns of the specific tables are encrypted with a proper encryption algorithm using the above query. At this point, the encryption implementation is completed.

col owner format a5
column table_name format a30;
column column_name format a30;
column encryption_alg format a25;
select owner,table_name
 , column_name
 , encryption_alg
 from dba_encrypted_columns order by owner;

Next Post will be on TDE Columns rollback procedure (Part 2)