TDE -Transparent Data Encryption in Oracle 19c Database
TDE (Transparent Data Encryption) in Oracle is a feature that enables you to encrypt sensitive data stored in database columns, tablespaces, and backups. The encryption process is transparent to applications that access the data, which means that users and applications can continue to work with the data without any changes to the way they access it.
When TDE is enabled, the data is automatically encrypted when it is written to disk and decrypted when it is read from disk. This helps protect the data from unauthorized access by individuals or applications that may try to steal or manipulate the data.
When Working on Public Cloud TDE is an important security feature that should be enabled on the database to encrypt the data. In Oracle Cloud DBCS this is enabled.
Data can be encrypted at the column level or tablespace level.
Key Components in TDE Architecture
Oracle TDE architecture involves the use of an Oracle Wallet to store encryption keys, a Master Encryption Key to encrypt and decrypt tablespace and column encryption keys, and the use of tablespace encryption keys and column encryption keys to encrypt and decrypt data.
The architecture of TDE involves several key components.
The first component is the master key, which is used to protect the certificate or asymmetric key used to encrypt the data. The master key is stored within the database management system and is used to protect the other keys used in the encryption process.
The second component is the certificate or asymmetric key, which is used to encrypt and decrypt the data. This key is protected by the master key and is used to encrypt the data as it's written to disk and decrypt it as it's read back into memory.
The third component is the data file, which is where the encrypted data is stored. When TDE is enabled, the data is encrypted before it's written to the data file and decrypted as it's read back into memory. This means that the data is always encrypted when it's stored on disk, providing an additional layer of security.
The fourth component is the database engine, which is responsible for encrypting and decrypting the data. When TDE is enabled, the database engine encrypts the data using the certificate or asymmetric key, and then writes the encrypted data to the data file. When the data is read from the data file, the database engine decrypts the data using the same key.
Lets Setup a TDE in an 19c Oracle Database.
1) Check the parameters in 19c database
SQL> show parameter tde_configuration
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
tde_configuration string
SQL> show parameter wallet_root
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
wallet_root string
2) In earlier releases the SQLNET.ENCRYPTION_WALLET_LOCATION parameter was used to define the keystore directory location. This parameter has been deprecated. Oracle recommends that you use the WALLET_ROOT static initialization parameter and TDE_CONFIGURATION dynamic initialization parameter instead
3) Enable the Wallet root
3.1 Create a parameter file from spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
spfile string /u01/app/oracle/product/19c/dbhome_1/dbs/spfileORCL.ora
SQL> create pfile from spfile;
File created.
3.2 Create a wallet root directory
[oracle@db19c ocidb]$ mkdir -p ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
[oracle@db19c ocidb]$ cd ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
[oracle@db19c wallet]$ pwd
/u01/app/oracle/admin/ORCL/wallet
3.3 Now set the parameter wallet_root
SQL> alter system set wallet_root='/u01/app/oracle/admin/ORCL/wallet' scope=spfile;
System altered.
3.4 Restart database and validate the wallet_root parameter
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3674209272 bytes
Fixed Size 9141240 bytes
Variable Size 721420288 bytes
Database Buffers 2936012800 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL> show parameter wallet_root
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
wallet_root string /u01/app/oracle/admin/ORCL/wallet
4) Set TDE Configuration parameter
SQL> alter system set TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE';
System altered.
SQL> show parameter TDE_CONFIGURATION
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
tde_configuration string KEYSTORE_CONFIGURATION=FILE
5) Create Password Protected Software Key Store
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet
total 0
SQL> administer key management create keystore identified by welcome123;
keystore altered.
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet
total 4
drwxr-x---. 2 oracle dba 4096 Mar 10 04:57 tde
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet/tde
total 4
-rw-------. 1 oracle dba 2555 Mar 10 04:57 ewallet.p12
If you observe a tde folder is created under the wallet root directory.
the keystore location can be find from
select * from V$ENCRYPTION_WALLET;
6) Create the auto-login or local auto-login keystore
SQL> administer key management create LOCAL auto_login keystore from keystore '/u01/app/oracle/admin/ORCL/wallet/tde/' identified by welcome123;
keystore altered.
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet/tde
total 8
-rw-------. 1 oracle dba 2555 Mar 10 04:57 ewallet.p12
-rw-------. 1 oracle dba 2600 Mar 10 05:03 cwallet.sso
An sso file for autologin get created.
set pages 200
set lines 200
col WRL_PARAMETER for a40
Col WRL_TYPE for a10
Col status for a20
select * from v$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE /u01/app/oracle/admin/ORCL/wallet/tde/ OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 1
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 2
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 3
7) Setup and create a Master Key Encryption
SQL> select key_id,tag,keystore_type,creation_time from v$encryption_keys;
no rows selected
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet/tde
total 8
-rw-------. 1 oracle dba 2555 Mar 10 04:57 ewallet.p12
-rw-------. 1 oracle dba 2600 Mar 10 05:03 cwallet.sso
SQL> administer key management set key using tag ‘Tablespace_TDE_FOA’ force keystore identified by welcome123 with backup using ‘FOA_TDE’;
keystore altered.
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet/tde
total 20
-rw-------. 1 oracle dba 2555 Mar 10 05:11 ewallet_2023031005110756_���FOA_TDE���.p12
-rw-------. 1 oracle dba 4203 Mar 10 05:11 ewallet.p12
-rw-------. 1 oracle dba 4248 Mar 10 05:11 cwallet.sso
SQL> select key_id,tag,keystore_type,creation_time from v$encryption_keys;
KEY_ID
------------------------------------------------------------------------------
TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
KEYSTORE_TYPE CREATION_TIME
----------------- ---------------------------------------------------------------------------
AUysVrT2TE/5v4SdWiGQmngAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
???Tablespace_TDE_FOA???
SOFTWARE KEYSTORE 10-MAR-23 05.11.07.737321 AM +00:00
SQL> set pages 200
set lines 200
col WRL_PARAMETER for a40
Col WRL_TYPE for a10
Col status for a20
select * from v$ENCRYPTION_WALLET;SQL> SQL> SQL> SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE /u01/app/oracle/admin/ORCL/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 3
If we see it has setup master key for CDB$ROOT & PDBs and they are open.
8) Now Lets create the master key encryption for our PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;
no rows selected
SQL> select * from v$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 3
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> administer key management set key using tag 'PDB1_Key' force keystore identified by welcome123 with backup using 'FOA_PDB1';
keystore altered.
SQL> select * from v$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 3
SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;
KEY_ID CREATION_TIME
------------------------------------------------------------------------------ ---------------------------------------------------------------------------
ACTIVATION_TIME
---------------------------------------------------------------------------
TAG
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AXo7bkdO/k88vzfHOb7RasoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 10-MAR-23 05.21.14.593726 AM +00:00
10-MAR-23 05.21.14.593729 AM +00:00
PDB1_Key
9) Take Wallet full backup
SQL> set pages 200
set lines 200
col WRL_PARAMETER for a40
Col WRL_TYPE for a10
Col status for a20
select * from v$ENCRYPTION_WALLET;SQL> SQL> SQL> SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
---------- ---------------------------------------- -------------------- -------------------- --------- -------- --------- ----------
FILE /u01/app/oracle/admin/ORCL/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 3
SQL> administer key management backup keystore using 'Walletfullbackup' force keystore identified by welcome123;
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/ORCL/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE YES 1
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED YES 2
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED YES 3
SQL> !ls -ltr /u01/app/oracle/admin/ORCL/wallet/tde
total 36
-rw-------. 1 oracle dba 2555 Mar 10 05:11 ewallet_2023031005110756_���FOA_TDE���.p12
-rw-------. 1 oracle dba 4203 Mar 10 05:21 ewallet_2023031005211438_FOA_PDB1.p12
-rw-------. 1 oracle dba 5851 Mar 10 05:25 ewallet_2023031005250604_Walletfullbackup.p12
-rw-------. 1 oracle dba 5851 Mar 10 05:25 ewallet.p12
-rw-------. 1 oracle dba 5912 Mar 10 05:25 cwallet.sso
v$ENCRYPTION_WALLET Fullybackup column will show Yes.
10) Convert Non Encrypted Table to Encrypted.
You can convert your table-spaces to encryption using Online or Offline method. Online method you will require twice the storage size as of your tablespace.
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- --------------------
SYSAUX
UNDOTBS1
USERS
TEMP
SYSTEM
For Online we use below command
alter tablespace users encryption online using 'aes256' encrypt file_name_convert = ('users01.dbf','users01_tde.dbf');
But, I am performing off-line method in this example.
SQL> alter tablespace USERS offline normal;
Tablespace altered.
SQL> alter tablespace USERS encryption offline using 'aes256' encrypt;
Tablespace altered.
SQL> alter tablespace USERS online;
Tablespace altered.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- --------------------
USERS AES256 YES NORMAL
SYSAUX
UNDOTBS1
TEMP
SYSTEM
11) Set parameter encrypt_new_tablespaces to make sure that the new table space has been created with encryption
SQL> show parameter encrypt
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string CLOUD_ONLY
SQL> alter system set encrypt_new_tablespaces='ALWAYS';
System altered.
SQL> show parameter encrypt
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string ALWAYS
2 Comments
Nice work Bro
ReplyDeleteExcellent
ReplyDeletePost a Comment