Steps to configuring the TDE for encrypt the table or tablespace in Oracle

Transparent Data Encryption (TDE) is a way to encrypt sensitive data that you store in tables and tablespaces. TDE encrypts sensitive data stored in data files which will not able to access from OS or disk theft.TDE stores the encryption keys external to the database called a keystore.

Types of KeyStores:
Password-based software keystores: are protected by using password you created. You use password to open.
Auto-login software keystores: are protected by system generated password, not need to put password it automatically opened when accessed.
Local auto-login software keystores: re auto-login software keystores that are local to the computer on which they are created. Not open from another computer.

Permission or privilege Required to use TDE:

1. Granted the ADMINISTER KEY MANAGEMENT system privilege
2. In Addition CREATE TABLE, ALTER TABLE, CREATE TABLESPACE permission required.

Encrypt sensitive data at

1. Column level.
2. Tablespace level.

Note: All of the TDE table keys are located together in the colklc column of the ENC$ data dictionary table. No keys are stored in plaintext.

Steps to configure a Software Keystore for encrypt the table or tablespace upto 18c database:

1. Create a keystore in regular system or ASM by making entry in SQLNET.ORA file.

Note: Make a following entry in SQLNET.ORA file upto 19c. After 19c we need to configure parameters in init file.

-- Use for normal disk, Change the directory location according to your need.
ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/etc/ORACLE/WALLETS/orcl))

---- If you are using ASM disk then only use this entry.
ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=+disk1/mydb/wallet)))

2. Create the Software Keystore.

Creating a Password-Based Software Keystore: After executing the following command, one file is created at location: ewallet.p12 file

Syntax:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'location of keystore' IDENTIFIED BY keystore_password;

Example: 
---create the keystore in the /etc/ORACLE/WALLETS/orcl directory:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;

Creating an Auto-Login or a Local Auto-Login Software Keystore:
Before creating auto login you must need to create password based Keystore means execute above steps first.
After executing the following command, one file is created at location: cwallet.sso file.

Syntax: 
ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE ' location of keystore' IDENTIFIED BY keystore_password;

Example:
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;

3. Open the Software Keystore.

Check the status of the keystore:

SELECT STATUS FROM V$ENCRYPTION_WALLET;

Open the keystore by executing following command:

Syntax:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT]

Example:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;

4. Set the Software TDE Master Encryption Key.

Check the status of the keystore:

SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
OPEN_NO_MASTER_KEY

Set the Software TDE Master Encryption Key with following command:

Syntax:

ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = ALL | CURRENT];

Example:

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystorepassword WITH BACKUP USING 'key_backup';

5. Encrypt the table or tablespace.

Encrypt the table column:

-- Create a new table with encrypted column
CREATE TABLE employee (
empID NUMBER,
salary NUMBER(10) ENCRYPT);

--Adding new encrypted column to table 
ALTER TABLE employee ADD (salary NUMBER(10) ENCRYPT);


--Modify the exiting column present in table to encrypt
ALTER TABLE employee MODIFY (salary ENCRYPT);
	 
Note: By default, TDE adds salt to make more secure if you want to create index on the encrypted column, then you must use the NO SALT parameter.

CREATE TABLE employee (
     empID NUMBER,
     salary NUMBER(10) ENCRYPT NO SALT);

Disable encryption for column as :

ALTER TABLE employee MODIFY (first_name DECRYPT);

Encrypt the Tablespace:

1. Check the compatibility parameter, it must be 11.2.0.0 minimum value.

2. Open wallet at mount stage before open 

STARTUP MOUNT;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY keystorepassword;
ALTER DATABASE OPEN;

3.  Set the Tablespace TDE Master Encryption Key. If already done then no need to do in step 4.

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystorepassword WITH BACKUP USING 'key_backup';

4. Create tablespace with encryption:

CREATE TABLESPACE securespace_2
DATAFILE '/u02/oradata/db01.dbf'
SIZE 200M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);