Oracle TDE Encryption at Tablespace and Table Column Level

The security of data in Oracle Database is ensured through robust authentication, authorization, and auditing mechanisms. A critical aspect of data storage involves datafiles, which are essential components residing in secondary storage drives like HDD or SSD at the operating system level. In the unfortunate event of these disks being stolen, it can lead to a compromise of data integrity.

To mitigate such risks, Oracle introduced Transparent Data Encryption (TDE), a concept designed to safeguard data by encrypting it at the datafile or tablespace level. Furthermore, TDE allows encryption at the table column level, providing a comprehensive security solution. This blog post will delve into the implementation of TDE encryption at the tablespace level within a Container Database (CDB) and at the table column level within a Pluggable Database (PDB). Explore with us as we uncover the intricacies of enabling TDE encryption to enhance data security in Oracle Database environments.

In this blog post, we are going to discuss about enabling TDE encryption at at tablespace level in CDB and Table column level in PDB.

Below are Topics will be covered on this blog post.

set parameters, create key store, convert keystore to AUTOLOGIN, open keystore, Set master encryption key for enabling TDE
Enable TDE encryption for tablespace USERS at CDB Level
Enable TDE encryption for table column CREDITCARDINFO.CARDNUMBER at PDB Level.

set parameters, create key store, convert keystore to AUTOLOGIN, open keystore, Set master encryption key

[oracle@madan21 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde

[oracle@madan21 ~]$ ls -ltd $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde

drwxr-xr-x 2 oracle dba 6 Jan 13 21:32 /u01/app/oracle/admin/cdb21c/wallet/tde

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- ———– ———- ———-

2 PDB$SEED READ ONLY NO

3 ORCL21C READ WRITE NO

SQL> alter system set wallet_root=’/u01/app/oracle/admin/cdb21c/wallet’ scope=spfile;

System altered.

Bounce database to reflect the changes of wallet_root.

SQL> show parameter wallet_root

NAME TYPE VALUE

———————————— ———– ——————————

wallet_root string /u01/app/oracle/admin/cdb21c/wallet

set parameters required for keystore creation

—set parameters—-

SQL> ALTER SYSTEM SET TDE_CONFIGURATION=”KEYSTORE_CONFIGURATION=FILE” SCOPE = BOTH;

System altered.

—create keystore—

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/u01/app/oracle/admin/cdb21c/wallet/tde’ IDENTIFIED BY Oracle123;

keystore altered.

—–convert keystore to AUTOLOGIN—-

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/u01/app/oracle/admin/cdb21c/wallet/tde’ IDENTIFIED BY Oracle123;

keystore altered.

—-open keystore—-

SQL> administer key management set keystore open force keystore identified by Oracle123;

keystore altered.

—–Set master encryption key—–

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Oracle123 WITH BACKUP ;

keystore altered.

SQL> col WRL_PARAMETER for a40

SQL> col WRL_PARAMETER for a60

SQL> set linesize 200

SQL> select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;

CON_ID WALLET_TYPE WRL_PARAMETER STATUS

———- ——————– —————————————— —————–

1 AUTOLOGIN /u01/app/oracle/admin/cdb21c/wallet/tde/ OPEN

2 AUTOLOGIN OPEN

3 AUTOLOGIN OPEN_NO_MASTER_KEY

—–No Tablespaces are encrypted at this instant———–

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME ENCRYPT ENC STATUS

———— ——- — ———-

SYSAUX

UNDOTBS1

USERS

TEMP

SYSTEM

14 rows selected.

Enable Tablespace “Users” TDE encryption at CDB Level

—–To Enable default encryption for newly created tablespaces———

SQL> alter system set encrypt_new_tablespaces = ALWAYS scope = both;

System altered.

—–Offline tablespace “USERS” encryption——–

SQL> alter tablespace USERS offline normal;

Tablespace altered.

SQL> ALTER TABLESPACE USERS ENCRYPTION offline USING ‘AES256’ ENCRYPT;

Tablespace altered.

SQL> alter tablespace USERS online ;

Tablespace altered.

———Verify tablespace Users encrypted or not at CDB Level———

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME ENCRYPT ENC STATUS

—————- ——- — ———-

USERS AES256 YES NORMAL

SYSAUX

UNDOTBS1

USERS

TEMP

SYSTEM

14 rows selected.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- ————– ———- ———-

2 PDB$SEED READ ONLY NO

3 ORCL21C READ WRITE NO

Encrypt specific column of Pluggable database ORCL21C table “CREDITCARDINFO”

—— Encrypt specific column of Pluggable database ORCL21C schema called “bank_admin” —

SQL> alter session set container=ORCL21C;

Session altered.

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME ENCRYPT ENC STATUS

————– ——- — ———-

SYSAUX

UNDOTBS1

USERS

TEMP

SYSTEM

SQL> select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;

CON_ID WALLET_TYPE WRL_PARAMETER STATUS

———- ——————– ——————- ——————————

3 AUTOLOGIN OPEN_NO_MASTER_KEY

SQL> show parameter wallet_root

NAME TYPE VALUE

———————————— ———– ——————————

wallet_root string /u01/app/oracle/admin/cdb21c/wallet

—— Connect to Container CDB21C and open wallet for all PDBs ———-

SQL> administer key management set keystore open force keystore identified by Oracle123 CONTAINER=ALL;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Oracle123 WITH BACKUP CONTAINER=ALL;

keystore altered.

SQL> select inst_id, con_id, wallet_type, status, wrl_parameter from gv$encryption_wallet order by 1,2;

INST_ID CON_ID WALLET_TYPE STATUS WRL_PARAMETER

———- ———- ——————– ——– ——————————————

1 1 AUTOLOGIN OPEN /u01/app/oracle/admin/cdb21c/wallet/tde/

1 2 AUTOLOGIN OPEN

1 3 AUTOLOGIN OPEN

———- Connect to PDB and enable TDE column encryption —————

SQL> alter session set container=ORCL21C;

Session altered.

SQL> SELECT con_id, key_id FROM v$encryption_keys;

CON_ID KEY_ID

———- ——————————————————-

3 AW96fJk0Tk8Hvx0YQUL6xQ0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME ENCRYPT ENC STATUS

———– ——- — ———-

SYSAUX

UNDOTBS1

USERS

TEMP

SYSTEM

SQL>

SQL> conn bank_admin/Oracle123@orcl21c

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

——————– ————- ———-

CUST_ORDER_INFO TABLE

CREDITCARDINFO TABLE

SQL> desc CREDITCARDINFO

Name Null? Type

——————- ——– ——————————

CARDID NOT NULL NUMBER

CARDNUMBER VARCHAR2(16)

CARDHOLDERNAME VARCHAR2(100)

EXPIRYDATE DATE

CVV VARCHAR2(4)

BILLINGADDRESS VARCHAR2(255)

CARDTYPE VARCHAR2(20)

——- Enable Column level encryption for existing column ———-

SQL> ALTER TABLE CREDITCARDINFO MODIFY (CARDNUMBER ENCRYPT);

Table altered.

SQL> desc CREDITCARDINFO

Name Null? Type

————————– ——– ——————————

CARDID NOT NULL NUMBER

CARDNUMBER VARCHAR2(16) ENCRYPT

CARDHOLDERNAME VARCHAR2(100)

EXPIRYDATE DATE

CVV VARCHAR2(4)

BILLINGADDRESS VARCHAR2(255)

CARDTYPE VARCHAR2(20)

——-Verify TDE Table Column Encryption status ———————-

SQL> conn sys/Oracle123@orcl21c as sysdba

Connected.

SQL> column table_name format a20

SQL> column column_name format a20

SQL> column encryption_alg format a20

SQL> set linesize 200

SQL> select table_name

, column_name

, encryption_alg

from dba_encrypted_columns where table_name=’CREDITCARDINFO’;

TABLE_NAME COLUMN_NAME ENCRYPTION_ALG

——————– ——————– ——————–

CREDITCARDINFO CARDNUMBER AES 192 bits key

SQL>

Hope you understood how to enable TDE encryption at tablespace level and table column level to protect data at OS level.

Madan Mohan Reddy