Setup TDE Encryption In Oracle 19c Database

Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.

After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen.

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore.

We can configure Oracle Key Vault as part of the TDE implementation. This enables you to centrally manage TDE keystores (called virtual wallets in Oracle Key Vault) in your enterprise. For example, you can upload a software keystore to Oracle Key Vault, migrate the database to use Oracle Key Vault as the default keystore, and then share the contents of this keystore with other primary and standby Oracle Real Application Clusters (Oracle RAC) nodes of that database to streamline daily database adminstrative operations with encrypted databases.

Description:- 

• Oracle Transparent Data Encryption (TDE) is a specific database technology where the encryption is specifically done by the database in a way that is transparent to the application.
• Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.
• Transparent Data Encryption (TDE) ensures that sensitive data is encrypted, meets compliance requirements, and provides functionality that streamlines encryption operations.
• In a multitenant environment, you can configure keystores for either the entire container database (CDB) or for individual pluggable databases (PDBs).
• Transparent Data Encryption can be applied to individual columns or entire tablespaces.
• Wallet configuration in SQLNET.ORA therefore no longer needed
• Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.
• New with 19cClose of keystore even if SYSTEM, SYSAUX, TEMP and UNDO is encrypted
  Easier online conversion for non OMF datafiles

Note:-

In previous releases, the SQLNET.ENCRYPTION_WALLET_LOCATION parameter was used to define the keystore directory location. This parameter has been deprecated. Oracle recommends that you use the WALLET_ROOT static initialization parameter and TDE_CONFIGURATION dynamic initialization parameter instead.

TDE configuration in oracle 19c Database

Step:-1 Configure the Wallet Root

alter system set WALLET_ROOT=”${ORACLE_BASE}/admin/${ORACLE_SID}/wallet” scope=spfile;

for windows create pfile:

alter system set TDE_CONFIGURATION=”KEYSTORE_CONFIGURATION=FILE”;

Step 2: Create the password protected key store

administer key management create keystore identified by oracledbanotes;

administer key management create LOCAL auto_login keystore from keystore 'C:\oracle\orabase\admin\orcl\WALLET\TDE\' identified by oracledbanotes;

select * from v$encryption_wallet;

OPEN_NO_MASTER_KEY -> Keystore is already not OPEN use the below command to open

administer key management set keystore open force keystore identified by oracledbanotes container=all;

Step:-3 Setup the Master Encryption Key

administer key management set key using tag 'orcl_Tablespace_TDE' force keystore identified by oracledbanotes with backup using 'TDE_backup';

 col key_id for a45
 col tag for a35
 col creation_time for a30
 select key_id,tag,keystore_type,creation_time from v$encryption_keys;

Open targetpdb and setup tde for it with a different encryption key.

ALTER PLUGGABLE DATABASE TARGETPDB OPEN;
ALTER SESSION SET container=TARGETPDB;

administer key management set keystore open identified by oracledbanotes ;

administer key management set key using tag 'TARGETPDB_Tablespace_TDE' force keystore identified by oracledbanotes with backup using 'TARGETPDB_TDE_backup';

Step:-4 Now we go for Testing

Without encryption create tablespace

create tablespace tde_orcl_tbs datafile 'C:\oracle\orabase\oradata\ORCL\tde_tbs1.dbf' size 50M;

From 19c onwards no need go for Offline Encryption. This method creates a new datafile with encrypted data. Now encrypt the new tablespace

alter tablespace tde_orcl_tbs  encryption online encrypt;
select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;

select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;

create user bstest identified by bstest default tablespace target_tbs quota unlimited on target_tbs;
grant connect,resource to bstest;
create table BSTEST.tb_encrpt (c1 varchar2(10) encrypt) tablespace USERS;
select table_name     , column_name     , encryption_alg  from dba_encrypted_columns;  
select OWNER,table_name from dba_tables where tablespace_name in (select tablespace_name from dba_tablespaces where encrypted='YES');

create table bstest.test2 (col1 varchar2(100)) tablespace users;
insert into bstest.test2 values ('quick brown fox jumps over the lazy dog');

create user bstest2 identified by bstest2 default tablespace users quota unlimited on users;
grant connect,resource,select any table to bstest2;