Configuring Transparent Data Encryption (TDE) in Oracle 19c

  1. Configure the Software Keystore Location.

Set the WALLET_ROOT and TDE_CONFIGURATION parameters.

-- Need to reboot for effect this parameter.
ALTER SYSTEM SET WALLET_ROOT='C:\ORACLE\admin\cdb1\wallet' SCOPE=SPFILE SID='*';

--No need to reboot
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*';

2. Create the Software Keystore.

---Creating a Password-Protected Software Keystore:

SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY password;

----Creating an Auto-Login Software Keystore

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'C:\ORACLE\admin\cdb1\wallet\tde' IDENTIFIED BY password;

3. Open the Keystore.

--open the keystore with following command:

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

Check the status of the keystore:

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

4. Set the master encryption key by executing the following command:


SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP USING 'key_backup';

----check the status of the keystore:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
OPEN

5. Encrypt the table or tablespace


Encrypt the table column by create or alter command:

-- 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 plaintext before encrypting it. It make more harder for hacker to hack data.
if you plan to index 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 keystore_password;
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 keystore_password WITH BACKUP USING 'emp_key_backup';

4. Create tablespace with encruption:

CREATE TABLESPACE securespace_2
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.