Transparent Data Encryption (TDE) In Oracle 19c

Transparent Data Encryption (TDE) enables you to encrypt data so that only an authorized recipient can read it.

Use encryption to protect sensitive data in a potentially unprotected environment, such as data you placed on backup media that is sent to an off-site storage location. You can encrypt individual columns in a database table, or you can encrypt an entire tablespace.

To use Transparent Data Encryption, you do not need to modify your applications. TDE enables your applications to continue working seamlessly as before. It automatically encrypts data when it is written to disk, and then automatically decrypts the data when your applications access it.

Lets see how to configure TDE

1. Create a wallet/keystore location.

[code] [oracle@orcl19c admin]$ cd $ORACLE_BASE [oracle@orcl19c oracle]$ ls admin audit cfgtoollogs checkpoints diag fast_recovery_area oradata product recovery_area [oracle@orcl19c oracle]$ pwd /u01/app/oracle [oracle@orcl19c oracle]$ cd admin/ [oracle@orcl19c admin]$ ls orcl1 [oracle@orcl19c admin]$ cd orcl1/ [oracle@orcl19c orcl1]$ ls adump dpdump pfile xdb_wallet [oracle@orcl19c orcl1]$ mkdir wallet [oracle@orcl19c orcl1]$ cd wallet/ [oracle@orcl19c wallet]$ pwd /u01/app/oracle/admin/orcl1/wallet [/code]

2. update the wallet/keystore location in sqlnet.ora. It should look like.

[code] [oracle@orcl19c admin]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin [oracle@orcl19c admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/orcl1/wallet))) [/code]

3. Create keystore:

[code] [oracle@orcl19c wallet]$ pwd /u01/app/oracle/admin/orcl1/wallet [oracle@orcl19c wallet]$ ls [oracle@orcl19c wallet]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 18 20:01:01 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/orcl1/wallet/' IDENTIFIED BY walletpass#19c; keystore altered. SQL> !ls -lrt total 4 -rw-------. 1 oracle oinstall 2555 Apr 18 20:01 ewallet.p12 [/code]

4. Now open the keystore:

[code] SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#19c; keystore altered. [/code] 

5. Now activate the key:

[code] SQL> SET LINESIZE 100 SQL> SELECT con_id, key_id FROM v$encryption_keys; no rows selected SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#19c WITH BACKUP; keystore altered. SQL> SELECT con_id, key_id FROM v$encryption_keys; CON_ID KEY_ID ---------- ------------------------------------------------------------------------------ 1 ASOSR67lfE9mv12Vw4SWA9AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SET LINESIZE 200 SQL> COLUMN wrl_parameter FORMAT A50 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------------- -------------------------------------------------- ------------------------------ FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN PASSWORD SINGLE NONE NO 1 [/code]

6. create a encrypted a tablespace

[code] SQL> CREATE TABLESPACE ENCRYPT_TEST datafile '/u01/app/oracle/oradata/ORCL1/encrypt_test.dbf' size 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT); 2 3 4 Tablespace created. SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='ENCRYPT_TEST'; TABLESPACE_NAME ENC ------------------------------ --- ENCRYPT_TEST YES [/code]

7. Create a table with encrypted column:
[code] SQL> CREATE TABLE test_encrypt ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT ) tablespace ENCRYPT_TEST; 2 3 4 5 6 Table created. SQL> select * from dba_encrypted_columns where owner='TEST_ENC'; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL --------- -------------------- -------------------- ----------------------- TEST_ENC TEST_ENCRYPT SALARY AES 192 bits key YES SHA-1 [/code]


8. Enable Autologin:
[code] SQL> col WRL_TYPE format a20 col WRL_PARAMETER format a35 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID --------- ----------------------------------- ------------------------------ -------------------- --------- FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN PASSWORD SINGLE NONE NO 1 [/code]


Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet explicitly. To avoid this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.

[code] SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/orcl1/wallet/' IDENTIFIED BY walletpass#19c; keystore altered. SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- -------------------------------------------------------------- ---------------- FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN PASSWORD SINGLE NONE NO 1 SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 893385440 bytes Fixed Size 9140960 bytes Variable Size 234881024 bytes Database Buffers 641728512 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> SQL> col WRL_TYPE format a20 SQL> col WRL_PARAMETER format a35 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- ----------------------------------- ------------------------------ -------------------- FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN AUTOLOGIN SINGLE NONE NO 1 SQL> [/code]

we can see the wallet opened automatically and the wallet_type has been changed from PASSWORD TO AUTOLOGIN.

For multi-tenant database:

In a multi-tenant database (CDB), the Keystore has to be be created in the ROOT container (CDB$ROOT).

This single Keystore will be shared by all the associated PDBs as well as the CDB$ROOT container.

So for this we need to use CONTAINER=ALL clause to open and activate the keystore  in all pdbs.
[code] ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#19c CONTAINER=ALL; ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#19c WITH BACKUP CONTAINER=ALL; [/code]