Transparent Data Encryption(TDE)

What is Transparent Data Encryption in oracle?

In Simple words, Transparent Data Encryption (TDE) is a feature in Oracle that allows you to encrypt sensitive data stored in tables and tablespaces. TDE encrypts the data at the storage level, so the data is transparently encrypted as it is written to disk and decrypted when it is read from disk.

This means that applications do not need to be modified to use TDE, and users do not need to be aware that the data is encrypted.

TDE uses a database encryption key to encrypt the data in the database, and this key is protected by a master key. The master key is encrypted using a certificate, which is stored in the database. When TDE is enabled, all data written to the database is encrypted using the database encryption key, and all data read from the database is decrypted using the same key.

TDE is useful for protecting sensitive data from unauthorized access, as it ensures that the data is encrypted at all times, even when it is being transmitted or backed up. It is also useful for compliance with data privacy regulations, such as the General Data Protection Regulation (GDPR) and the Health Insurance Portability, AADHAR Card,CREDIT Card etc.

Why need to deploy TDE?

Key points to remember

(TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.
After the data is encrypted, this data is transparently decrypted for authorized users.
TDE stores the encryption keys in a security module external to the database, called a Keystore / Wallet.
Transparent to applications because the encryption and decryption processes do not require any application changes.

Note: Sqlnet.ora file is very important to implement complete wallet.

Benefits of TDE

Providing highly level of security.
Storage Media encrypted so no worry for misuse of data .
No need to change at Application end.
Help in regulatory Compliance.
Zero downtime required to deploy on prod.

TDE Key Architecture

Data encryption keys are created and managed by TDE automatically
A master encryption key encrypts the data encryption keys
The master key typically is stored in Oracle Wallet or Oracle Key Vault

Tablespace Encryption vs Column Encryption

Column

Encrypt individual columns
Unique for all encrypted columns in a table (a bit overhead to manage)
Default Algorithm : AES 192
Execution Plans may be change for a table
Encrypted inside Buffer Cache, gets decrypted when moved to PGA

Tablespace

Encrypt the entire Tablespace (All Datafiles)
Unique Tablespace Key (Same key for all datafiles in a tablespace)
Default Algorithm : AES 128
Execution Plans (Oracle recommended)
Data stays decrypted inside Buffer Cache

Database detail before implement TDE

Database Version: Oracle Database 21c Express Edition Release 21.0.0.0.0 – Production Version 21.3.0.0.0

Database Name : XE
CDB Name : CDB$ROOT
PDB Database Name : XEPDB1
Decided to set default (root) location to save keys : “/opt/oracle/wallet” (use parameter WALLET_ROOT)
Location to save keys : /opt/oracle/wallet/tde (manually created)
Location of sqlnet.ora : /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
Parameters need to takecare : WALLET_ROOT , TDE_CONFIGURATION

Note : sqlnet.ora is most important file to configure oracle database wallet and network parameter setting

Can we implement TDE directly on PDB database?

In simple words “No” . We have to enable TDE feature on CDB first and then we implement changes on PDB.

Configure Transparent Database Encryption (TDE) for CDB

Capture current status / detail of XE database

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Jan 9 06:51:18 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> COLUMN NAME FORMAT A8
SQL> set colsep | pagesize 2000 trims on
SQL> set linesize 200
SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME    |CDB|    CON_ID
--------|---|----------
XE      |YES|         0

SQL> col cname format a10
SQL> select name cname,dbid, OPEN_MODE,OPEN_TIME from V$CONTAINERS C;

CNAME     |      DBID|OPEN_MODE |OPEN_TIME
----------|----------|----------|---------------------------------------------------------------------------
CDB$ROOT  |3006541124|READ WRITE|09-JAN-23 05.42.08.063 AM +00:00
PDB$SEED  |3937184287|READ ONLY |09-JAN-23 05.42.08.147 AM +00:00
XEPDB1    | 671033675|READ WRITE|09-JAN-23 05.42.12.950 AM +00:00

col WRL_PARAMETER format a10
col WRL_TYPE format a10
col wallet_type format a10
col STATUS format a20
col KEYSTORE_MODE format a50
set linesize 300 trims on colsep |
SQL> set linesize 300
SQL> select con_id,WRL_TYPE,wallet_type,STATUS,KEYSTORE_MODE from V$encryption_wallet;

    CON_ID|WRL_TYPE  |WALLET_TYP|STATUS              |KEYSTORE_MODE
----------|----------|----------|--------------------|--------------------------------------------------
         1|FILE      |UNKNOWN   |NOT_AVAILABLE       |NONE          <<<<<<<<<< CDB Status
         2|FILE      |UNKNOWN   |NOT_AVAILABLE       |UNITED
         3|FILE      |UNKNOWN   |NOT_AVAILABLE       |UNITED


SQL> select name cname,dbid,E.con_id,wrl_type,wallet_type,status,keystore_mode
  2  from V$ENCRYPTION_WALLET E , V$CONTAINERS C
  3  where E.con_id=C.con_id ORDER BY CON_ID;

CNAME     |      DBID|    CON_ID|WRL_TYPE  |WALLET_TYP|STATUS              |KEYSTORE_MODE
----------|----------|----------|----------|----------|--------------------|--------------------------------------------------
CDB$ROOT  |3006541124|         1|FILE      |UNKNOWN   |NOT_AVAILABLE       |NONE
PDB$SEED  |3937184287|         2|FILE      |UNKNOWN   |NOT_AVAILABLE       |UNITED
XEPDB1    | 671033675|         3|FILE      |UNKNOWN   |NOT_AVAILABLE       |UNITED

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE            |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-------------------------|---------|----------
FILE      |/opt/oracle/admin/XE/wallet             |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |NONE                     |UNDEFINED|         1  <<<<< Default Setting
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         2
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         3

SQL> show parameter WALLET_ROOT

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
wallet_root                         |string     |      <<<<<<<<<<<<<< Null need to set for base directory of wallet

SQL> col FILE_NAME format a70
SQL> select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1;  2    3    4

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
SYSAUX                        |/opt/oracle/oradata/XE/sysaux01.dbf                                   |ONLINE              |LOGGING  |YES|NO          <<<<< Encryption not enabled yet
SYSTEM                        |/opt/oracle/oradata/XE/system01.dbf                                   |ONLINE              |LOGGING  |YES|NO          <<<<< Encryption not enabled yet
UNDOTBS1                      |/opt/oracle/oradata/XE/undotbs01.dbf                                  |ONLINE              |LOGGING  |NO |NO          <<<<< Encryption not enabled yet
USERS                         |/opt/oracle/oradata/XE/users01.dbf                                    |ONLINE              |LOGGING  |NO |NO          <<<<< Encryption not enabled yet

SQL> show pdbs

    CON_ID|CON_NAME                      |OPEN MODE |RESTRICTED
----------|------------------------------|----------|----------
         2|PDB$SEED                      |READ ONLY |NO
         3|XEPDB1                        |READ WRITE|NO
SQL>
##### Create new tablespace "encryption_test" for encryption test
SQL> create tablespace encryption_test datafile
       '/opt/oracle/oradata/XE/encrypt_tb01.dbf' size 1G;  

Tablespace created.

SQL> select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
  2  from dba_tablespaces T ,
  3  dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1;  

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
ENCRYPTION_TEST               |/opt/oracle/oradata/XE/encrypt_tb01.dbf                               |ONLINE              |LOGGING  |NO |NO       <<<<< Newly created but encryption not set yet
SYSAUX                        |/opt/oracle/oradata/XE/sysaux01.dbf                                   |ONLINE              |LOGGING  |YES|NO
SYSTEM                        |/opt/oracle/oradata/XE/system01.dbf                                   |ONLINE              |LOGGING  |YES|NO
UNDOTBS1                      |/opt/oracle/oradata/XE/undotbs01.dbf                                  |ONLINE              |LOGGING  |NO |NO
USERS                         |/opt/oracle/oradata/XE/users01.dbf                                    |ONLINE              |LOGGING  |NO |NO

Create user scott, allocate tablespace,create table emp and insert few records.

SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE encryption_test TEMPORARY TABLESPACE TEMP;
CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE encryption_test TEMPORARY TABLESPACE TEMP
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


SQL> alter session set "_ORACLE_SCRIPT"=true;    <<<<<<<<Set parameter to resolve above problem

Session altered.

SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE encryption_test TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT, RESOURCE TO scott;

Grant succeeded.

SQL> ALTER USER scott QUOTA UNLIMITED ON encryption_test;

User altered.

#### Insert records for Scott user, Keep in mind still you are connected with SYS user 

SQL> CREATE TABLE scott.EMP
       (EMPNO NUMBER(4), ENAME VARCHAR2(10), MOBILE_NUMBER VARCHAR2(20)) tablespace encryption_test;  
Table created.

SQL> INSERT INTO scott.EMP VALUES
        (7566, 'JONES','+91-7448236762');
1 row created.
SQL> INSERT INTO scott.EMP VALUES
       (7156,'RSAINI', '+91-9558236444');
1 row created.
SQL> INSERT INTO scott.EMP VALUES
        (7156,'RSAINI', '+91-8286744864');  2
1 row created.

SQL> commit;                      <<<<<<<<<<<< Save data with commit
Commit complete.

SQL> show user;                   <<<<<<<<<<<< User Connected 
USER is "SYS"

Switch over log and Checkpoint to write data permanently in datafile.

SQL> show user;
USER is "SYS"

SQL> ALTER SYSTEM CHECKPOINT;
System altered.

SQL> /
System altered.
SQL> /
System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL>  select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1; 

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
ENCRYPTION_TEST               |/opt/oracle/oradata/XE/encrypt_tb01.dbf                               |ONLINE              |LOGGING  |NO |NO    <<<<<<<<<<< Still tablespace is not encrypted  
SYSAUX                        |/opt/oracle/oradata/XE/sysaux01.dbf                                   |ONLINE              |LOGGING  |YES|NO
SYSTEM                        |/opt/oracle/oradata/XE/system01.dbf                                   |ONLINE              |LOGGING  |YES|NO
UNDOTBS1                      |/opt/oracle/oradata/XE/undotbs01.dbf                                  |ONLINE              |LOGGING  |NO |NO
USERS                         |/opt/oracle/oradata/XE/users01.dbf                                    |ONLINE              |LOGGING  |NO |NO

SQL> ! <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<    See / Read data file contents, You can see it is not protected and it is proved HACKER can read data from normal datafile 
[oracle@localhost ~]$ strings /opt/oracle/oradata/XE/encrypt_tb01.dbf    <<<<<<<<<<<<<<<<<<< As discussed in past, you can see the inserted data / read content from datafile.
}|{z
ENCRYPTION_TEST
AAAAAAAA
RSAINI
+91-8286744864,
RSAINI
+91-9558236444,
JONES
+91-7448236762
^C
^Z
[1]+  Stopped                 strings /opt/oracle/oradata/XE/encrypt_tb01.dbf
[oracle@localhost ~]$ exit
exit
There are stopped jobs.
[oracle@localhost ~]$ exit
exit

Capture data and current status of wallet

Keep in mind to capture data for parameters

WRL_PARAMETER
WALLET_ROOT
ENCRYPTION_WALLET_LOCATION (From sqlnet.ora file)
Decide the physical location of database wallet and keystore (Software)

SQL> col WRL_PARAMETER format a40
SQL> col KEYSTORE_MODE format a25
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE          |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-----------------------|---------|----------
FILE      |/opt/oracle/admin/XE/wallet             |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |NONE                   |UNDEFINED|         1
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                 |UNDEFINED|         2
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                 |UNDEFINED|         3

SQL> ! ls -ltr /opt/oracle/admin/XE/wallet
ls: cannot access '/opt/oracle/admin/XE/wallet': No such file or directory


#### Change location of Wallet root location
SQL> alter system set WALLET_ROOT="/opt/oracle/wallet" scope=spfile; <<<<<< Parameter set in spfile

System altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE          |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-----------------------|---------|----------
FILE      |/opt/oracle/admin/XE/wallet             |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |NONE                   |UNDEFINED|         1
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                 |UNDEFINED|         2
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                 |UNDEFINED|         3

SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

WRL_PARAMETER                           |STATUS              |WALLET_TYP
----------------------------------------|--------------------|----------
/opt/oracle/admin/XE/wallet             |NOT_AVAILABLE       |UNKNOWN
                                        |NOT_AVAILABLE       |UNKNOWN
                                        |NOT_AVAILABLE       |UNKNOWN

SQL> SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;
no rows selected

###### All useful queries if you want to use 
SELECT * FROM V$ENCRYPTION_WALLET;
SELECT * FROM V$ENCRYPTION_KEYS;
SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

SELECT KEY_ID,KEYSTORE_TYPE,CREATOR,CREATOR_INSTANCE_NAME,CREATOR_PDBNAME FROM V$ENCRYPTION_KEYS;

Change the location of Wallet if you want

######### Set WALLET_ROOT parameter with new location, WALLET_ROOT is parameter can set in spfile and need to restart database.

SQL> alter system set WALLET_ROOT="/opt/oracle/wallet" scope=spfile;

System altered.

SQL> show parameter wallet

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
ssl_wallet                          |string     |
wallet_root                         |string     |

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE            |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-------------------------|---------|----------
FILE      |/opt/oracle/admin/XE/wallet             |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |NONE                     |UNDEFINED|         1
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         2
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         3


#### Restart Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area|1157625952|bytes
Fixed Size              |   9685088|bytes
Variable Size           | 402653184|bytes
Database Buffers        | 738197504|bytes
Redo Buffers            |   7090176|bytes
Database mounted.
Database opened.

SQL> alter system register;   <<< force to Register services with listener
System altered.

SQL> show parameter wallet
NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
ssl_wallet                          |string     |
wallet_root                         |string     |/opt/oracle/wallet
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE            |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-------------------------|---------|----------
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |NONE                     |UNDEFINED|         1
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         2
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         3

SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;                        <<<<<<<<<<<<<<<<<<<<<<<<<< Note the point (Software wallet)

System altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE            |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-------------------------|---------|----------
FILE      |/opt/oracle/wallet/tde/                 |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |NONE                     |UNDEFINED|         1    <<<<<<<< Note new directory created 
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         2
FILE      |                                        |NOT_AVAILABLE       |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         3

Change sqlnet.ora and create / verify dir

[oracle@localhost ~]$ ls -ltr /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
-rw-r-----. 1 oracle oinstall 188 Jan  9 14:30 /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
[oracle@localhost ~]$
###### See sqlnet.ora before modify / set paramterer
[oracle@localhost ~]$ cat /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@localhost ~]$


###### Backup of sqlnet.ora
[oracle@localhost ~]$ cp /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora_bkp

##### Modify Sqlnet.ora 
[oracle@localhost ~]$ vi /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora

##### See file after modification 
[oracle@localhost ~]$ cat /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cXE/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

#######encryption setting#### Modified contents########
ENCRYPTION_WALLET_LOCATION =
        (SOURCE = (METHOD = FILE)
          (METHOD_DATA =
          (DIRECTORY = /opt/oracle/wallet/tde)
         )
         )
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet/tde
ls: cannot access '/opt/oracle/wallet/tde': No such file or directory

###### Create directory which is set/showing at database level after restart 
[oracle@localhost ~]$ mkdir -p /opt/oracle/wallet/tde 
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet/tde 
total 0 
[oracle@localhost ~]$

Create the keystore

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/oracle/wallet/tde' IDENTIFIED BY welcome123;

keystore altered.

SQL> !
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet/tde
total 4
-rw-------. 1 oracle oinstall 2555 Jan 10 07:22 ewallet.p12                    <<<<<<<<<<<<<<<<<<<<< See newly created file
[oracle@localhost ~]$ exit
exit
#### Open Key Wallet
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY welcome123;

keystore altered.

#### Set Master Encryption key (Need always keystore password) 
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY welcome123 with backup;

keystore altered.

SQL> !ls -ltr /opt/oracle/wallet/tde
total 8
-rw-------. 1 oracle oinstall 2555 Jan 10 07:25 ewallet_2023011007255813.p12    <<<<<<<<<<<<<<<<<<<<< See another created file
-rw-------. 1 oracle oinstall 3995 Jan 10 07:26 ewallet.p12

SQL>
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE  |WRL_PARAMETER                           |STATUS              |WALLET_TYP|WALLET_OR|KEYSTORE_MODE            |FULLY_BAC|    CON_ID
----------|----------------------------------------|--------------------|----------|---------|-------------------------|---------|----------
FILE      |/opt/oracle/wallet/tde/                 |OPEN                |PASSWORD  |SINGLE   |NONE                     |NO       |         1
FILE      |                                        |CLOSED              |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         2
FILE      |                                        |CLOSED              |UNKNOWN   |SINGLE   |UNITED                   |UNDEFINED|         3

SQL> select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1;   2    3    4

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
ENCRYPTION_TEST               |/opt/oracle/oradata/XE/encrypt_tb01.dbf                               |ONLINE              |LOGGING  |NO |NO   <<<<<<<<<<<<<<<<See ENCRYPTED status before
SYSAUX                        |/opt/oracle/oradata/XE/sysaux01.dbf                                   |ONLINE              |LOGGING  |YES|NO
SYSTEM                        |/opt/oracle/oradata/XE/system01.dbf                                   |ONLINE              |LOGGING  |YES|NO
UNDOTBS1                      |/opt/oracle/oradata/XE/undotbs01.dbf                                  |ONLINE              |LOGGING  |NO |NO
USERS                         |/opt/oracle/oradata/XE/users01.dbf                                    |ONLINE              |LOGGING  |NO |NO

SQL> ALTER TABLESPACE ENCRYPTION_TEST ENCRYPTION online USING 'AES128' ENCRYPT;                  <<<<<<<<<<<<< enable ENCRYPTION on tablespace

Tablespace altered.

SQL>  select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1;  2    3    4

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
ENCRYPTION_TEST               |/opt/oracle/oradata/XE/encrypt_tb01.dbf                               |ONLINE              |LOGGING  |NO |YES <<<<<<<< See ENCRYPTED status After
SYSAUX                        |/opt/oracle/oradata/XE/sysaux01.dbf                                   |ONLINE              |LOGGING  |YES|NO
SYSTEM                        |/opt/oracle/oradata/XE/system01.dbf                                   |ONLINE              |LOGGING  |YES|NO
UNDOTBS1                      |/opt/oracle/oradata/XE/undotbs01.dbf                                  |ONLINE              |LOGGING  |NO |NO
USERS                         |/opt/oracle/oradata/XE/users01.dbf                                    |ONLINE              |LOGGING  |NO |NO

SQL>

See Encrypted / Decrypted data into datafile

See difference of Encrypted and Non-Encrypted tablespace datafile

You can see the “mobile” are appearing during read of datafile which is not safe.

Configure Transparent Database Encryption (TDE) for PDB

## Connect to database
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jan 10 20:36:22 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
		 
#### Connect to pdb database xepdb1
SQL>  Alter session set container=xepdb1;
Session altered.

#### Check current status of Keystore and Files

col WRL_PARAMETER format a10
col WRL_TYPE format a10
col wallet_type format a10
col STATUS format a20
col KEYSTORE_MODE format a50
col CNAME format a10
set linesize 300 trims on colsep |

SQL> select con_id,WRL_TYPE,wallet_type,STATUS,KEYSTORE_MODE from V$encryption_wallet;

    CON_ID|WRL_TYPE  |WALLET_TYP|STATUS              |KEYSTORE_MODE
----------|----------|----------|--------------------|--------------------------------------------------
         3|FILE      |UNKNOWN   |CLOSED              |UNITED

SQL> select name cname,dbid,E.con_id,wrl_type,wallet_type,status,keystore_mode
from V$ENCRYPTION_WALLET E , V$CONTAINERS C
where E.con_id=C.con_id ORDER BY CON_ID;  2    3

CNAME     |      DBID|    CON_ID|WRL_TYPE  |WALLET_TYP|STATUS              |KEYSTORE_MODE
----------|----------|----------|----------|----------|--------------------|--------------------
XEPDB1    |3507460321|         3|FILE      |UNKNOWN   |CLOSED              |UNITED                       <<<<< WALLET_TYPE unknow and Closed

#### Tablespaces are non-encrypted

SQL> col FILE_NAME format a70
SQL>  select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1; 

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
SYSAUX                        |/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf                            |ONLINE              |LOGGING  |YES|NO    <<<<<<< Encryption not enabled
SYSTEM                        |/opt/oracle/oradata/XE/XEPDB1/system01.dbf                            |ONLINE              |LOGGING  |YES|NO    <<<<<<< Encryption not enabled
UNDOTBS1                      |/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf                           |ONLINE              |LOGGING  |NO |NO    <<<<<<< Encryption not enabled
USERS                         |/opt/oracle/oradata/XE/XEPDB1/users01.dbf                             |ONLINE              |LOGGING  |NO |NO    <<<<<<< Encryption not enabled


#### See wallet_root Parameter 

SQL> show parameter wallet

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
ssl_wallet                          |string     |
wallet_root                         |string     |/opt/oracle/wallet                       <<<<< See root location of wallet

SQL> !
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet
total 0
drwxr-xr-x. 2 oracle oinstall 61 Jan 10 07:25 tde
[oracle@localhost ~]$ exit
exit

[oracle@localhost ~]$  ls -ltr /opt/oracle/wallet/tde   <<<<<<<<<<<<<<<<<<<<< Container Wallet files are already present 
total 8
-rw-------. 1 oracle oinstall 2555 Jan 10 07:25 ewallet_2023011007255813.p12 
-rw-------. 1 oracle oinstall 3995 Jan 10 07:26 ewallet.p12
[oracle@localhost ~]$ exit
exit

# Open the Keystore & Create a Master Encryption Key for PDB

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY welcome123;      <<<< Open keystore at CDB level 
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY welcome123
*
ERROR at line 1:
ORA-46661: keystore not open in root container

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY welcome123;   <<<<< Once keystore is opened at CDB level, then you can open it PDB level 
keystore altered.

SQL> !
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet/tde             <<<<<<<< Verify current files keystore files availavle in folder
total 8
-rw-------. 1 oracle oinstall 2555 Jan 10 07:25 ewallet_2023011007255813.p12
-rw-------. 1 oracle oinstall 3995 Jan 10 07:26 ewallet.p12

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle123 with backup;    <<<< If you try with other key 
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle123 with backup
*
ERROR at line 1:
ORA-46627: keystore password mismatch

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY welcome123 with backup;  <<<<< If you try with same key used at container level 
keystore altered.

SQL> !
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet/tde
total 16
-rw-------. 1 oracle oinstall 2555 Jan 10 07:25 ewallet_2023011007255813.p12
-rw-------. 1 oracle oinstall 3995 Jan 10 20:09 ewallet_2023011020092033.p12    <<<<< New file is created
-rw-------. 1 oracle oinstall 5467 Jan 10 20:09 ewallet.p12
[oracle@localhost ~]$ exit
exit

#### Verify if the Wallet has been opened in PDB

SQL> select con_id, WRL_TYPE,wallet_type,STATUS,KEYSTORE_MODE from V$encryption_wallet;    <<<< Re-verify status of wallet

    CON_ID|WRL_TYPE  |WALLET_TYP|STATUS              |KEYSTORE_MODE
----------|----------|----------|--------------------|--------------------
         3|FILE      |PASSWORD  |OPEN                |UNITED

SQL>  select name cname,dbid,E.con_id,wrl_type,wallet_type,status,keystore_mode
from V$ENCRYPTION_WALLET E , V$CONTAINERS C
where E.con_id=C.con_id ORDER BY CON_ID; 

CNAME     |      DBID|    CON_ID|WRL_TYPE  |WALLET_TYP|STATUS              |KEYSTORE_MODE
----------|----------|----------|----------|----------|--------------------|--------------------
XEPDB1    |3507460321|         3|FILE      |PASSWORD  |OPEN                |UNITED

SQL> select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1;  

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
SYSAUX                        |/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf                            |ONLINE              |LOGGING  |YES|NO   <<<<<<< Encryption not enabled
SYSTEM                        |/opt/oracle/oradata/XE/XEPDB1/system01.dbf                            |ONLINE              |LOGGING  |YES|NO   <<<<<<< Encryption not enabled
UNDOTBS1                      |/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf                           |ONLINE              |LOGGING  |NO |NO   <<<<<<< Encryption not enabled
USERS                         |/opt/oracle/oradata/XE/XEPDB1/users01.dbf                             |ONLINE              |LOGGING  |NO |NO   <<<<<<< Encryption not enabled

Create / Alter Tablespace encryption option

#### You can use following options for ENCRYPTION

SQL>  create tablespace ENCRYPTION_PDB_TBS_01 datafile '/opt/oracle/oradata/XE/XEPDB1/encryption_pdb_tbs_01.dbf' size 1m
   encryption using 'AES128' ENCRYPT;  

Tablespace created.

SQL> create tablespace ENCRYPTION_PDB_TBS_02 datafile '/opt/oracle/oradata/XE/XEPDB1/encryption_pdb_tbs_02.dbf' size 1m
   encryption using 'AES256' default storage (encrypt) ;  

Tablespace created.

SQL> create tablespace ENCRYPTION_PDB_TBS_03 datafile
       '/opt/oracle/oradata/XE/XEPDB1/encryption_pdb_tbs03.dbf' size 1m;    2

Tablespace created.

SQL>  ALTER TABLESPACE ENCRYPTION_PDB_TBS_03 ENCRYPTION USING 'AES256' ENCRYPT;

Tablespace altered.

SQL>

SQL>  select T.TABLESPACE_NAME,d.FILE_NAME,T.STATUS,T.LOGGING,T.FORCE_LOGGING,T.ENCRYPTED
from dba_tablespaces T ,
dba_data_files d
where T.TABLESPACE_NAME=D.TABLESPACE_NAME order by 1; 

TABLESPACE_NAME               |FILE_NAME                                                             |STATUS              |LOGGING  |FOR|ENC
------------------------------|----------------------------------------------------------------------|--------------------|---------|---|---
ENCRYPTION_PDB_TBS_01         |/opt/oracle/oradata/XE/XEPDB1/encryption_pdb_tbs_01.dbf               |ONLINE              |LOGGING  |NO |YES   <<<<< See tablespace is encrypted 
ENCRYPTION_PDB_TBS_02         |/opt/oracle/oradata/XE/XEPDB1/encryption_pdb_tbs_02.dbf               |ONLINE              |LOGGING  |NO |YES   <<<<< See tablespace is encrypted
ENCRYPTION_PDB_TBS_03         |/opt/oracle/oradata/XE/XEPDB1/encryption_pdb_tbs03.dbf                |ONLINE              |LOGGING  |NO |YES   <<<<< See tablespace is encrypted
SYSAUX                        |/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf                            |ONLINE              |LOGGING  |YES|NO
SYSTEM                        |/opt/oracle/oradata/XE/XEPDB1/system01.dbf                            |ONLINE              |LOGGING  |YES|NO
UNDOTBS1                      |/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf                           |ONLINE              |LOGGING  |NO |NO
USERS                         |/opt/oracle/oradata/XE/XEPDB1/users01.dbf                             |ONLINE              |LOGGING  |NO |NO

7 rows selected.

Issue / Problem during implementation of wallet and resolution

ORA-46661: keystore not open in root container

Connect to container and execute

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY welcome123;
keystore altered.

SQL>

Now connect to PDB and try to open KEYSTORE

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

keystore altered.

SQL>

ORA-46633 Description: creation of a password-based keystore failed

Cause: Password-based keystore could not be created at the specified location due to either incorrect file permissions for Oracle on the specified location or due to the nonexistence of the specified location.

You are connected to PDB and try to create a KEYSTORE

ORA-46627: keystore password mismatch

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle123 with backup;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle123 with backup
*
ERROR at line 1:
ORA-46627: keystore password mismatch

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY welcome123 with backup;

keystore altered.

SQL> !
[oracle@localhost ~]$ ls -ltr /opt/oracle/wallet/tde
total 16
-rw——-. 1 oracle oinstall 2555 Jan 10 07:25 ewallet_2023011007255813.p12
-rw——-. 1 oracle oinstall 3995 Jan 10 20:09 ewallet_2023011020092033.p12 <<<<<<<<<<<< New file is created
-rw——-. 1 oracle oinstall 5467 Jan 10 20:09 ewallet.p12
[oracle@localhost ~]$

Transparent Data Encryption(TDE)

Table of Contents

What is Transparent Data Encryption in oracle?

Why need to deploy TDE?

Key points to remember

Benefits of TDE

TDE Key Architecture

Tablespace Encryption vs Column Encryption

Database detail before implement TDE

Can we implement TDE directly on PDB database?

Configure Transparent Database Encryption (TDE) for CDB

Capture current status / detail of XE database

Create user scott, allocate tablespace,create table emp and insert few records.

Switch over log and Checkpoint to write data permanently in datafile.

Capture data and current status of wallet

Keep in mind to capture data for parameters

Change the location of Wallet if you want

Change sqlnet.ora and create / verify dir

Create the keystore

See Encrypted / Decrypted data into datafile

Configure Transparent Database Encryption (TDE) for PDB

Create / Alter Tablespace encryption option

Issue / Problem during implementation of wallet and resolution

Table of Contents