Let’s look at below in detail
What is Oracle Transparent Data Encryption (TDE)? How do I migrate existing clear data to TDE encrypted data? How to find details for encryption/encrypted objects? How to Enable TDE in 12C? Best Practices for TDE
What is Oracle Transparent Data Encryption (TDE)?
Oracle Advanced Security Encryption -TDE(Transparent Data Encryption) (From 10gR2)
allows administrators to encrypt sensitive data (i.e. Personally Identifiable Information or PII)
by protecting it from unauthorized access via encryption key if storage media, backups, or datafiles are stolen.
TDE supports two levels of encryption
1)TDE column encryption ( From 10gR2)
2)TDE tablespace encryption ( From 11gR1)
TDE uses a two tier encryption key architecture, consisting of a master key and one or more table and/or tablespace keys. The table and tablespace keys are encrypted using the master key. The master key is stored in the Oracle Wallet. When TDE column encryption is applied to an existing application table column, a new table key is created and stored in the Oracle data dictionary. When TDE tablespace encryption is used, the individual tablespace keys are stored in the header of the underlying OS file(s).
How do I migrate existing clear data to TDE encrypted data?
You can migrate existing clear data to encrypted tablespaces or columns using Online/Offline. Existing tablespaces can be encrypted online with zero downtime on production systems or encrypted offline with no storage overhead during a maintenance period. Online conversion is available on Oracle Database 12.2.0.1 and above Offline conversion on Oracle Database 11.2.0.4 and 12.1.0.2. In 11g/12c,you can use DBMS_REDEFINITION(Online Table Redefinition) copy existing clear data into a new encrypted tablespace background with no downtime.
How to find details for encryption/encrypted objects?
gv$encryption_wallet
V$ENCRYPTED_TABLESPACES
DBA_ENCRYPTED_COLUMNS
select * from gv$encryption_wallet; ---(gv$wallet) select * from dba_encrypted_columns; select table_name from dba_tables where tablespace_name in (select tablespace_name from dba_tablespaces where encrypted='yes'); select tablespace_name, encrypted from dba_tablespaces where encrypted='yes'; select et.inst_id, ts.name, et.encryptionalg, et.encryptedts from v$encrypted_tablespaces et, ts$ ts where et.ts# = ts.ts#;
How to Enable TDE in 12C?
Step 1: Set KEYSTORE location in $TNS_ADMIN/sqlnet.ora
Step 2: Create a Password-based KeyStore
Step 3: Open the KEYSTORE
Step 4: Set Master Encryption Key
Step 5: Encrypt your Data
--> Make sure compatible parameter value greater than 11.2.0.
5.1 Encrypt a Columns in Table
5.2 Encrypt Tablespace
-->Note: Encrypting existing tablespace is not supported.( Do online/Offline Conversion)
Step 1: Set KEYSTORE location in $TNS_ADMIN/sqlnet.ora
==> created the directory mkdir -p /u01/app/oracle/admin/SLOB/tde_wallet ==>declared it in sqlnet.ora ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/SLOB/tde_wallet)))
Step 2: Create a Password-based KeyStore
==> sqlplus sys/password as sysdba
administer key management create keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;
Step 3: Open the KEYSTORE
administer key management set keystore open identified by oracle;
Step 4: Set Master Encryption Key
administer key management set key identified by oracle with backup; ==> Optionally created an auto-login wallet Administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;
Step 5: Encrypt your Data
–> Make sure compatible parameter value greater than 11.2.0.
===> 5.1 Encrypt a Columns in Table
create table job(title varchar2(128) encrypt);->Create a table with an encrypted column,By default it is AES192 create table emp(name varchar2(128) encrypt using '3DES168', age NUMBER ENCRYPT NO SALT);--Create a column with an encryption algorithm Alter table employees add (new_name varchar(40) ENCRYPT);--Encrypt an existing table column alter table employees rekey using '3DES168'; --Change the Encryption key of an existing column
===> 5.2 Encrypt Tablespace
Note: Encrypting existing tablespace is not supported.( Do online/Offline Conversion)
CREATE TABLESPACE D_CRYPT DATAFILE '+DATA' SIZE 10G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT); Alter table TABLE_WITH_PAN move online tablespace D_CRYPT;
For 11g, We use orapki wallet & encryption key commands are used as below
orapki wallet create -wallet <wallet location> -pwd "<wallet password>" -auto_login ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1"; ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "welcome1";
Best Practices for TDE
1)Avoiding accidentally deleting the TDE Wallet files on Linux
Go to Wallet Directory and set set the ‘immutable’ bit
chattr +i ewallet.p12 ( encrypted wallet ) chattr +i cwallet.sso ( auto-open wallet )
Using Data Pump with TDE
By default,data in the resulting dump file will be in clear text, even the encrypted column data.
To protect your encrypted column data in the data pump dump files, you can password-protect your dump file when exporting the table.
expdp arup/arup ENCRYPTION_PASSWORD=pooh tables=accounts
Importing a password-protected dump file
impdp arup/arup ENCRYPTION_PASSWORD=pooh tables=accounts table_exists_action=replace
References
http://www.catgovind.com/oracle/how-to-enable-transparent-data-encryption-tde-in-oracle-database/
TDE best practices in EBS https://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf
http://psoug.org/reference/tde.html
https://www.oracle.com/database/technologies/security/advanced-security.html
https://www.oracle.com/technetwork/database/security/tde-faq-093689.html
Encrypting Tablespaces By Arup Nanda https://www.oracle.com/technetwork/articles/oem/o19tte-086996.html
Encrypt sensitive data transparently without writing a single line of code https://blogs.oracle.com/oraclemagazine/transparent-data-encryption-v2
https://blogs.oracle.com/stevenchan/using-fast-offline-conversion-to-enable-transparent-data-encryption-in-ebs
https://juliandontcheff.wordpress.com/2017/06/02/twelve-new-features-for-cyber-security-dbas/
Transparent Data Encryption (TDE) (Doc ID 317311.1)
Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1)
Quick TDE Setup and FAQ (Doc ID 1251597.1)
Managing TDE Wallets in a RAC Environment (Doc ID 567287.1)
How to Export/Import with Data Encrypted with Transparent Data Encryption (TDE) (Doc ID 317317.1)
Using Transportable Tablespaces to Migrate Oracle EBS Release 12.0 or 12.1 Using Oracle Database 12.1.0 (Doc ID 1945814.1)
Using Transparent Data Encryption (TDE) Column Encryption with Oracle E-Business Suite Release 12 (Doc ID 732764.1)
My NoteBook 