Oracle Transparent Data Encryption (TDE) in Tablespace for Oracle Database 12c

Transparent Data Encryption enables you to encrypt data. Typically, you encrypt sensitive data, such as credit card numbers or Social Security numbers. 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.

This example provide a step by step guide on how to implement TDE in tablespace level.

     1.       Create a key-store directory that

$mkdir /u01/app/oracle/product/12.1.0.2/db_1/owm/tdewallet/$ORACLE_UNQNAME

       

      2.       Change directory of TNS files.

$mkdir /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

      3.       Copy the files to the above location

$cp /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

$cp /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

$cp /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

      4.       Change the environment variable of the database .bash_profile and add

export TNS_ADMIN=/u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORCLE_UNQNAME

      5.       Add the below entry top sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

  (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/product/11.2.0.4/db_1/owm/tdewallet/$ORACLE_UNQNAME

      6.       With user sys as sysdba create the keys-tore

SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/owm/tdewallet/$ORACLE_UNQNAME/' IDENTIFIED BY mypassword;

      7.       Check Wallet Status

SQL>SELECT * FROM v$encryption_wallet;

                  7.1   To manually open wallet

SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY mypassword;

                  7.2   To manually close wallet

SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY mypassword;

      8.       Enable Wallet with Backup

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY mypassword WITH BACKUP;

      9.       Check if Wallet is backup

SQL>SELECT con_id, key_id FROM v$encryption_keys;

      10.   Create auto login for the wallet

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/product/12.1.0.2/db_1/owm/tdewallet/$ORACLE_UNQNAME/ ' IDENTIFIED BY password;

      11.   Create Encrypted Tablespace

SQL>CREATE TABLESPACE <Tablespace Name> DATAFILE

  '<Datafile location and name>' SIZE 100M AUTOEXTEND ON NEXT 144M MAXSIZE UNLIMITED

LOGGING

ENCRYPTION USING 'AES256'

DEFAULT STORAGE (ENCRYPT)

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

      12.   Check Tablespace status

SQL>SELECT tablespace_name, encrypted FROM dba_tablespaces;