Secure the Shift: Migrating TDE from Oracle 11g to 19c with Auto-Login

 

Intro

We live in an era defined by data and artificial intelligence (AI), where organizations invest heavily in securing their IT infrastructures. As AI continues to advance, the protection of data has become more critical than ever. Modern businesses rely extensively on data analytics to drive decision-making, making safeguarding sensitive information a top priority for security engineers and database administrators.

Given its high value, data has also become a prime target for cyberattacks. To counter these threats, robust encryption mechanisms like Oracle Transparent Data Encryption (TDE) play a vital role in protecting data at rest especially as organizations increasingly move to the public cloud. Oracle Cloud offers TDE as a built-in feature at no additional cost, making it a widely adopted solution in cloud security strategies.

TDE is a core Oracle Database security feature that encrypts data stored on disk, ensuring that it cannot be read if the storage media is lost or stolen. This encryption is transparent to applications, meaning no changes to application code are required.

Purpose of TDE

TDE encrypts data stored in database files to protect it from unauthorized access in case of data theft or media loss. The encryption is transparent to applications, requiring no changes to the application code.

Key Features of TDE

a. Tablespace Encryption

• Encrypts entire tablespaces rather than individual columns.
• Simplifies management for databases with sensitive data spread across multiple columns or tables.

b. Column Encryption

• Encrypts specific sensitive columns in a table (e.g., Social Security Numbers, credit card numbers).
• Ideal for targeted protection where only a few fields are sensitive.

c. Integration with Oracle Wallet

• TDE uses an external Oracle Wallet or Key Vault to store encryption keys securely.
• Ensures that the encryption keys are not stored within the database itself, providing separation of duties.

Encryption Algorithms

• Supports industry-standard encryption algorithms such as:
• Advanced Encryption Standard (AES): Commonly used with key sizes of 128, 192, or 256 bits.
• Triple Data Encryption Standard (3DES): Legacy algorithm.

Transparent Nature

• Encryption and decryption are handled automatically by the Oracle Database.
• Data is encrypted before being written to disk and decrypted when read into memory, without user intervention.

Compliance and Regulations

• Helps organizations meet regulatory compliance requirements such as GDPR, HIPAA, PCI-DSS, and more.
• TDE ensures that sensitive data is encrypted and cannot be read directly from storage.

In this article, I’ll guide you through the process of upgrading an Oracle 11g database to 19c, with a focus on migrating Transparent Data Encryption (TDE) and enabling the auto-login feature.

Before beginning the upgrade, it's important to ensure that the existing TDE wallet files are copied to the appropriate location. In this scenario, TDE was already configured and in use on the Oracle 11g database.

Oracle 19c introduces two essential parameters that must be configured for TDE to function correctly:

1. WALLET_ROOT
2. TDE_CONFIGURATION

Since modifying the WALLET_ROOT parameter requires a database restart; each parameter will be applied sequentially during the planned downtime for the upgrade.

alter system set wallet_root='/var/opt/oracle/dbaas_acfs/TEST2PRD/wallet_root' scope=spfile sid='*';
-- restart database
alter system set TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE' scope=spfile sid='*';
- Dynamic parameter

Ensure that the tde folder exists under the specified wallet_root directory.

mkdir -p /var/opt/oracle/dbaas_acfs/TEST2PRD/wallet_root/tde

Copy the existing Oracle 11g wallet files to the following directory:    /var/opt/oracle/dbaas_acfs/TEST2PRD/wallet_root/tde directory.

Steps to configure TDE using 11g keys.

Note: Since the wallet files already exist, there’s no need to run the ADMINISTER KEY MANAGEMENT CREATE KEYSTORE command.

At this point, the wallet directory contains only the TDE key file:

[oracle@exaprd01-node01]$ ls -l /var/opt/oracle/dbaas_acfs/TEST2PRD/wallet_root/tde
total 4
-rwxr--r-- 1 oracle oinstall 1573 Dec  2 16:04 ewallet.p12
[oracle@exaprd01-node01]

Opening the Wallet and Enabling Auto-Login

You can now open the wallet and configure it for auto-login by executing the following SQL commands:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wallet_password";
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wallet_password" WITH BACKUP;
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/var/opt/oracle/dbaas_acfs/TEST2PRD/wallet_root/tde' IDENTIFIED BY "wallet_password";

After this, the wallet directory should contain three files:

• cwallet.sso – Auto-login wallet (SSO key)

• ewallet_<timestamp>.p12 – Backup of the original key

• ewallet.p12 – The main TDE master key file

[oracle@exaprd01-node01 ~]$ ls -l /var/opt/oracle/dbaas_acfs/TEST2PRD/wallet_root/tde
total 28
-rw------- 1 oracle oinstall 4262 Dec  7 04:41 cwallet.sso
-rwxr--r-- 1 oracle oinstall 1573 Dec  7 04:41 ewallet_2024120704413660.p12
-rw------- 1 oracle oinstall 4217 Dec  7 04:41 ewallet.p12
[oracle@exaprd01-node01 ~]$

Validating the Wallet Configuration

To confirm that the wallet is open and auto-login is properly configured, run the following queries in SQL*Plus:

SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TEST2PRD_EXA
SQL> set lines 600
col WALLET for a20
col WALLET_LOCATION for a80

select WRL_TYPE wallet,status,WALLET_TYPE,wrl_parameter wallet_location,KEYSTORE_MODE from v$encryption_wallet;SQL> SQL> SQL> SQL>

WALLET               STATUS                         WALLET_TYPE          WALLET_LOCATION                                                                  KEYSTORE
-------------------- ------------------------------ -------------------- -------------------------------------------------------------------------------- --------
FILE                 OPEN                           AUTOLOGIN            /var/opt/oracle/dbaas_acfs/ESOL2PRD/wallet_root/tde/                             NONE

SQL>

Conclusion

In this article, we covered the process of upgrading an Oracle 11g database to 19c while ensuring a smooth transition of Transparent Data Encryption (TDE) to auto-login. Properly handling the TDE wallet files and configuring the necessary parameters in Oracle 19c are crucial steps to maintaining security and compliance. By following these steps, you can successfully upgrade your database while leveraging the enhanced encryption management features of Oracle 19c.