Tablespace Level Encryption

Database Security

=========================

Day 6th

=========================

Tablespace Level Encryption (TDE)

=========================

• Encrypts the whole tablespace including all of its datafiles.
• All the encrypted datafiles shares the single encryption key.
• Tablespace level transparent data encryption is also recommended by Oracle.
• Encryption and decryption takes place of the data in SGA.
• Oracle Encrypts tablespace with default algorithm AES128 if no algorithm is specified explicitly.
• It is recommended not to encrypt system tablespaces.

Advantages of Tablespace level TDE

• Tablespace level TDE reduce the storage overhead because each datafile stores its encryption meta data in one block header not more than that.
• Tablespace level encryption encrypt data in tablespace as well as its redo data.
• Data remains encrypted while moving to undo and temporary tablespaces due to operations like sort and joins etc.
• Allows index range scans which is not allowed in column level TDE.
• Allows compression and oracle Advanced compression.
• It has not datatype columns restrictions.

Limitations of Tablespace level TDE

• Tablespace level TDE does not encrypt data outside the database like BLOBs.
• Tablespace level encryption can’t be done without no salt.
• If There are a lot of datafiles and data it would be subjected to performance issue.

States of tablespace encryption

We can encrypt tablespace in Online as well as offline mode.

Create New Encrypted Tablespace

SQL>> SELECT tablespace_name, encrypted from dba_tablespaces;  -- it will list tablespace names each having NO written in front.        

SQL>>CREATE TABLESPACE ENCTBS1 DATAFILE 'ENCTBS01.dbf' SIZE 10M ENCRYPTION USING 'AES192' ENCRYPT; --it will create encrypted tablespace ENCTBS1. If        

no algorithm is specified then oracle will create encrypted tablespace with AES128.

SQL>> SELECT tablespace_name, encrypted from dba_tablespaces WHERE encrypted='YES';  -- you will see the tablesapce ENCTBS1 means it is encrypted.        

=====================================

TABLESPACE_NAME ENC =

------------------------------ --- =

SYSTEM NO =

SYSAUX NO =

UNDOTBS1 NO =

TEMP NO =

USERS NO =

ENCTBS1 YES =

=====================================

SQL> select * from v$encrypted_tablespaces;        

========================================================================================================================================

TS# ENCRYPT ENC ENCRYPTEDK MASTERKEYID BLOCKS_ENCRYPTED BLOCKS_DECRYPTED KEY_VERSION STATUS CON_ID

---------- ------- --- ---------- -------------------------------- ---------------- ---------------- ----------- ---------- ----------

6 AES128 YES 1886F0F059 2CA08F155BE94F5CBF617004CDE5CD5A 0 0 0 NORMAL 1

ADE0FAF139

E6274DBB56

EB00000000

0000000000

0000000000

0000

=========================================================================================================================================

Encrypting Tablespace online

• All tablespaces except TEMPORARY tablespace can be encrypted online.

• Redo log is generated for each tablespace conversion.

• It is recommended not to encrypt tablespaces SYSUX and SYSTEM parallel to USER-DEFINED tablespace.

• Before Begin online tablespace encryption double check the following:

• Database is in read write mode.

• Auxiliary space equal to the size of the largest datafile in the database is available.

• Wallet or keystore is open.

SQL> CREATE TABLESPACE UNENC_TBS1 DATAFILE 'FILE1.DBF' SIZE 10M;        

SQL> SELECT TABLESPACE_NAME, ENCRYPTED, STATUS FROM DBA_TABLESPACES;        

SQL> ALTER TABLESPACE UNENC_TBS1 ENCRYPTION ONLINE USING 'AES192’        

  	  ENCRYPT FILE_NAME_CONVERT = ('FILE1.DBF', 'FILE1_ENC.DBF’);        

SQL> alter tablespace UNENC_TBS1 encryption online finish encrypt file_name_convert=('FILE1.DBF','FILE1_ENC.DBF', 'FILE2.DBF','FILE2_ENC.DBF');        

Encrypting Tablespace offline

Offline conversion is good when we don’t plan to change compatability.

Offline conversion can be done when either the tablespace is offline or the database is in mount stage.

SQL>> ALTER TABLESPACE ENC OFFLINE;        

SQL>> ALTER TABLESPACE ENC ENCRYPTION offline USING 'AES256' ENCRYPT;        

SQL>> ALTER TABLESPACE ENC ONLINE;        

SQL>> SELECT TABLESPACE_NAME, ENCRYPTED, STATUS FROM DBA_TABLESPACES;        

Thats it for today, hope you will have enjoyed incase of any problem

fell free to reach me thanks & regards Dost Muhammad Khalil