oracledba.help works great on your mobile device too!

oracledba.help

Transparent Data Encryption (TDE)

<- SpecialTopics

Overview

Oracle Transparent Data Encryption (TDE) enables you to encrypt sensitive application data on storage media, i.e. files at rest, completely transparent to the application. TDE addresses encryption regulations associated with PCI DSS and the like.

TDE column encryption enables encryption of new and existing table columns containing sensitive information. An existing non-encrypted tablespace however cannot be encrypted. If you wish to encrypt the data for an entire tablespace create a new encrypted tablespace and then move the data from the old tablespace to the new one. The advantage in encrypting an entire tablespace is that you do not need to make any changes at the table level, i.e. less administration and more scalable.

The Oracle database instance name in these examples is DB42.

To disable TDE go here.

Preparation

  1. Create wallet directory.
    Make sure that the wallet location exists and can be read/written by the Oracle processes.
    Default location: %ORACLE_BASE%\admin\DB42\wallet
    If required you can change it from the default by creating a sqnet.ora entry.
  2. Create a master key.
    SQL> alter system set encryption key identified by "MyWalletPassword";
    This will cause the file %ORACLE_BASE%\admin\DB42\wallet\ewallet.p12 to be created and open the new wallet.
  3. Check Status
    You can see the wallet status using:
    SQL> SELECT * FROM v$encryption_wallet;

Important Note

From this point on you must open the wallet before using any TDE components after each database bounce!
To open the wallet use: SQL> alter system set wallet open identified by "<password>";

You can set the wallet to auto-login so you don't need to open the wallet manually. To do this use orapki.

OS> orapki wallet create -wallet %ORACLE_BASE%\admin\DB42\wallet -auto_login
This will create the file ewallet.sso which is used by the wallet to auto-login.

Encrypt New Tablespace

This is done using the below tablespace creation options:

  • ENCRYPTION USING '<[AES128] | AES192 | AES256 | 3DES168>'
  • DEFAULT STORAGE(ENCRYPT)

Example:

CREATE BIGFILE TABLESPACE tde1
DATAFILE 'O:\oradata\dbf\tde1.dbf'
SIZE 100m REUSE
AUTOEXTEND ON NEXT 25m MAXSIZE 10g
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT)
LOGGING;

Display Encryption Status

SELECT tablespace_name, encrypted 
FROM dba_tablespaces  
WHERE tablespace_name LIKE '%&MyString%';

Encrypt Table Column

New

Create a table by specifying the encrypt option.

create table test(
   col1 number, 
   col2 varchar2(100) encrypt using 'AES256' NO SALT
);

Existing

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.

alter table test modify(col2 encrypt using 'AES256');

Display

SELECT * FROM dba_encrypted_columns ORDER BY table_name, column_name

Support Considerations

  • Don't use the Wallet Manager. Query the database using one of these two ways:
    • SQL> SELECT * FROM v$encryption_wallet
    • OS> orapki wallet display -wallet %ORACLE_BASE%\admin\DB42\wallet
  • For disaster recovery (DR) to another location you must copy the wallet files (ewallet.p12 and cwallet.sso)
    to the same location as in the primary database before using RMAN.
  • To change the wallet password:
    • OS> orapki wallet change_pwd -wallet %ORACLE_BASE%\admin\DB42\wallet
    • If you are using a wallet with auto login enabled, you must regenerate the auto login wallet after changing the password.
      • OS> orapki wallet create -wallet %ORACLE_BASE%\admin\DB42\wallet -auto_login

Important Note

You cannot change the wallet password in 11.2.0.3 because of a bug (ID 1302664.1). Scheduled to be fixed in 11.2.0.4.

<- SpecialTopics