Transparent Data Encryption in Oracle

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarAshiwini
  • 02 Jul, 2024
  • 5 Mins Read

Transparent Data Encryption in Oracle

Transparent Data Encryption (TDE) in Oracle is a security feature designed to protect data at rest by encrypting database files. It ensures that data is encrypted before it is written to disk and decrypted when read into memory, without requiring changes to applications. Here’s an overview of how TDE works and its key components:

After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen.

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored.

TDE is using a two tier encryption key architecture consisting of :
Encryption Keys:
  • Master Encryption Key (MEK): This key is used to encrypt and decrypt the TDE table keys. It is stored in an external security module called Oracle Wallet or an external key management system.
  • TDE Table Keys: These keys are used to encrypt and decrypt the actual data in tablespaces or specific columns.
Tablespace Encryption:
  • TDE can encrypt entire tablespaces, meaning all data stored in a specific tablespace is automatically encrypted. This is typically simpler to manage and can be more efficient for encrypting large amounts of data.
The table and tablespace keys are encrypted using the master key. The master key is stored in an external security module (ESM) that can be one of the following:
  • Oracle Wallet:An Oracle Wallet is a secure software container used to store authentication and encryption keys. The master encryption key is stored in the Oracle Wallet, ensuring that the keys are not stored within the database itself, enhancing security.
  • a Hardware Security Module (HSM) –
    a device used to secure keys and perform cryptographic operations.
To start using  TDE the following operations have to be performed:
Set Up the Oracle Wallet

Create a Directory for the Oracle Wallet:

mkdir /u01/app/oracle/admin/orcl/wallet
Configure the Database to Use the Wallet:

1 .Add the following entries to your sqlnet.ora file:

(DIRECTORY = /u01/app/oracle/admin/orcl/wallet)

The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If we want to let Oracle manage a wallet in the default location then there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.

It is important to check that the location specified in sqlnet.ora or the default location exists and can be read/written by the Oracle processes.

2.) Generate a master key :


This command will do the following :

A.) If there is no wallet currently in the wallet location then a new wallet with the password “wallet_password” will be generated. The password is enclosed in double quotes to preserve the case of the characters. If the double quotes are not used then the characters of the password will be all in upper case. This command will also cause the new wallet to be opened and ready for use.

B.) A new master key will be generated and will be written to the wallet. This newly generated master key will become the active master key. The old master keys (if there were any) will still be kept in the wallet but they will not be active. They are kept there to be used when decrypting data that was previously encrypted using them .

To see the status of an wallet run the following query:

SQL> select * from v$encryption_wallet;
 WRL_TYPE      WRL_PARAMETER                              STATUS
 ----------    ------------------------------         ----------- 

   file          /u01/app/oracle/orcl/wallet               OPEN
3) .Enable encryption for a column or for an entire tablespace:
3.1) Create a table by specifying the encrypt option:
SQL> create table test(col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT) ;
3.2) Encrypt the column(s) of an existing table :
SQL> alter table test modify( col2 encrypt SALT ) ;

Note : If the table has many rows then this operation might take some time since all the values stored in col2 must be replaced by encrypted strings. If the access to the table during this operations is needed then useOnline Table Redefinition

3.3) Create an encrypted tablespace :

The syntax is the same as creating a normal tablespace except for two clauses:

We specify the encryption algorithm – in this case ‘AES256′. If we do not specify this, it will default to ‘AES128′. At the time of tablespace creation specify the encryption and default storage clause.

Define the encryption algorithem as ” using ‘algorithm’ ” along with the encryption clause. We can use the following algorithms while creating an encrypted tablespace.


If we don’t specify any algorithm with the encryption clause it will use AES128 as default.


SQL> create tablespace encryptedtbs datafile '/u01/app/oracle/orcl/wallet/encryptedtbs01.dbf' size 100M encryption using 'AES256' default storage(encrypt) ;

Note: An existing non encrypted tablespace cannot be encrypted. If we must encrypt the data from an entire tablespace then create a new encrypted tablespace and then move the data from the old tablespace to the new one TDE Master Key and Wallet Management .

Oracle Wallet Backup and Maintenance

The wallet is a critical component for Transparent Data Encryption (TDE) and must be backed up in a secure location, separate from the database backups. Losing the wallet or forgetting its password will make the encrypted data inaccessible. Ensure the wallet is backed up in the following scenarios:

  • When regenerating the master key
  • When backing up the database. Make sure that the wallet backup is not stored in the same location with the database backup
  • Before changing the wallet password
Supported Operations with Encrypted Tablespaces
  • Moving Tables:
    • Tables can be moved back and forth between encrypted and non-encrypted tablespaces.
  • Datapump Export/Import:
    • Datapump supports exporting and importing encrypted content and tablespaces.
  • Transportable Tablespaces:
    • Transportable tablespaces are supported using Datapump.
Unsupported Operations with Encrypted Tablespaces
  • SYSTEM, SYSAUX, UNDO, and TEMP Tablespaces:
    • Tablespace encryption cannot be applied to SYSTEM, SYSAUX, UNDO, and TEMP tablespaces.
  • Encrypting Existing Tablespaces:
    • Existing tablespaces cannot be encrypted. You must create a new encrypted tablespace and move data into it.
  • Traditional Export/Import Utilities:
    • Traditional export/import utilities do not support encrypted content.