Oracle Transparent Data Encryption (TDE)

Transparent Data Encryption

The Transparent Data Encryption (TDE) feature was introduced in Oracle Database 10g Release 2 which allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system.

How to Setup

In order to show the encryption working we need to open a datafile in a HEX editor.

CONN sys/password AS SYSDBA

CREATE TABLESPACE tde_encryp_test

DATAFILE ‘/u01/oradata/DB10G/tde_encryp_test.dbf’ SIZE 1M AUTOEXTEND ON NEXT 64K;

Next, create a user with a quota on the new tablespace.

CREATE USER tde_user IDENTIFIED BY tde_user DEFAULT TABLESPACE tde_encryp_test;

ALTER USER tde_user QUOTA UNLIMITED ON tde_encryp_test;

GRANT CONNECT, RESOURCE TO tde_user;

GRANT CREATE TABLE TO tde_user;

Demo for Column without encryption

Create a test table and insert some data.

CONN tde_user/tde_user

CREATE TABLE tde_tab (

id    NUMBER(10),

data  VARCHAR2(100)

)

TABLESPACE tde_encryp_test;

INSERT INTO tde_test (id, data) VALUES (1, ‘This is the data to be encrypted!’);

COMMIT;

Flush the buffer cache to be sure that the data is written to the datafile.

CONN sys/password AS SYSDBA

ALTER SYSTEM FLUSH BUFFER_CACHE;

Open the datafile using a HEX editor and the sentence “This is a the data to be encrypted!!” is clearly visible.

Demo for Encrypted Column

A wallet must be created to hold the encryption key before creating the table with the encrypted columns.

A wallet is an encrypted container that is used to store authentication and signing credentials, including passwords, the TDE master key, PKI private keys, certificates, and trusted certificates needed by SSL. With TDE, wallets are used on the server to protect the TDE master key.

Oracle provides two different types of wallets: encryption wallet and (local) auto-open wallet. The encryption wallet (filename ‘ewallet.p12’) is the one recommended for TDE. It needs to be opened manually after database startup and prior to TDE encrypted data being accessed. Because data is encrypted in REDO logs, UNDO and TEMP tablespaces, the TDE master encryption key needs to be available to the database before it is opened:

CONN sys/password AS SYSDBA

startup mount;

ORACLE instance started.

Database mounted.

alter system set encryption wallet open identified by “wallet_pwd”;

System altered.

alter database open;

Database altered.

If the Wallet is not open, the database will return an error when TDE protected data is queried. The (local) auto-open wallet (filename ‘cwallet.sso’) opens automatically when an encrypted data is accessed; hence it can be used for unattended Data Guard (Oracle 10gR2: physical standby only; Oracle 11g: physical and logical standby) environments where encrypted data is shipped to secondary sites. Never delete the encryption wallet after creating an auto-open wallet, since otherwise master encryption key re-key operations will fail.
Oracle Database 11g Release 2 introduced the local auto-open wallet, which only opens automatically on the server it was created on.

The search order for finding the wallet is as follows:

  1. If wallet exists, the location is specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
  2. The default location for the wallet ($ORACLE_BASE/admin/$ORACLE_SID/wallet).

Although encrypted tablespaces can share the default database wallet, Oracle recommend you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

Add the following entry into the sqlnet.ora file on the server and make sure the specified directory has been created.

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=/u01/app/oracle/admin/DB10G/wallet_encryption/)))

The following command creates and opens the wallet.

CONN sys/password AS SYSDBA

–In Oracle 10g creating the wallet in sql prompt

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “encryption_pwd”;

— In Oracle 11g creating the wallet in sql prompt

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “encryption_pwd”;

Wallets must be reopened after an instance restart and can be closed to prevent access to encrypted columns.

— In Oracle 10g open the wallet in sql prompt

ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY “encryption_pwd “;

— In Oracle 11g open the wallet in sql prompt

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “encryption_pwd “;

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

Create a test table with an encrypted column and insert some data. Using the ENCRYPT clause on its own is the same as using the ENCRYPT USING ‘AES192’ clause, as AES192 is the default encryption method.

CONN tde_user/tde_user

DROP TABLE tde_tab;

PURGE RECYCLEBIN;

CREATE TABLE tde_tab (

id    NUMBER(10),

data  VARCHAR2(100)

)

TABLESPACE tde_encryp_test;

INSERT INTO tde_test (id, data) VALUES (1, ‘This is the data to be encrypted!’);

COMMIT;

Flush the buffer cache to make sure the data is written to the datafile.

CONN sys/password AS SYSDBA

ALTER SYSTEM FLUSH BUFFER_CACHE;

When the datafile is opened using a HEX editor only non-printable characters are present. The test sentence cannot be seen anywhere, but the data is still clearly visible from a database connection.

SELECT * FROM tde_tab;

ID DATA

———- ————————————————–

1 This is the data to be encrypted!

1 row selected.

 

Resetting the Master Key

Regenerate the master key only if it has been compromised. Frequent master key regeneration does not necessarily enhance system security. Security modules can store a large, but not infinite, number of keys, and frequent master key regeneration can exhaust all the available storage space.

SQL>ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “new_pwd”;

Adding or Removing Salt from an Encrypted Column

Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text to appear different when encrypted. By default, transparent data encryption adds salt before encrypting it.

To add or remove salt from encrypted columns, you again use the ALTER TABLE MODIFY command with either the SALT or NO SALT parameter specified with the ENCRYPT clause.

 Adding Salt to an Encrypted Column

CONN tde_user/tde_user

SQL>ALTER TABLE tde_tab MODIFY (first_name ENCRYPT SALT);

 

Adding Salt to an Encrypted Column

CONN tde_user/tde_user

SQL>ALTER TABLE tde_tab MODIFY (first_name ENCRYPT NO SALT);

Creating a Table with an Encrypted Column That Uses the Default Algorithm

By default, transparent data encryption uses AES with a 192-bit length key (AES192) algorithm.

CONN tde_user/tde_user

SQL>CREATE TABLE tde_emp_tab (

first_name VARCHAR2(128),

last_name VARCHAR2(128),

empID NUMBER,

salary NUMBER(6) ENCRYPT

);

Creating a New Table with an Encrypted Column Using 3DES168 and NO SALT

CONN tde_user/tde_user

SQL>CREATE TABLE tde_emp_tab (

first_name VARCHAR2(128),

last_name VARCHAR2(128),

empID NUMBER ENCRYPT NO SALT,

salary NUMBER(6) ENCRYPT USING ‘3DES168’

);

Adding Encrypted Columns to Existing Tables

CONN tde_user/tde_user

SQL>ALTER TABLE tde_emp_tab ADD (ssn VARCHAR2(11) ENCRYPT);

Encrypting Unencrypted Columns

CONN tde_user/tde_user

SQL>ALTER TABLE tde_emp_tab MODIFY (first_name ENCRYPT);

Turning Off Column Encryption

CONN tde_user/tde_user

SQL>ALTER TABLE tde_emp_tab MODIFY (first_name DECRYPT);

Changing the Encryption Key on Tables Containing Encrypted Columns

CONN tde_user/tde_user

SQL>ALTER TABLE tde_emp_tab REKEY;

Changing the Encryption Key and Algorithm on Tables Containing Encrypted Columns

CONN tde_user/tde_user

SQL>ALTER TABLE employee REKEY USING ‘3DES168’;

Supported Encryption Algorithms for Transparent Data Encryption

Algorithm Key Size Parameter Name
Triple DES (Data Encryption Standard) 168 bits 3DES168
AES (Advanced Encryption Standard) 128 bits AES128
AES 192 bits (default) AES192
AES 256 bits AES256

For integrity protection, the SHA-1 hashing algorithm is used.

The following datatypes can be encrypted using TDE:

  • CHAR
  • DATE
  • INTERVAL DAY TO SECOND
  • INTERVAL YEAR TO MONTH
  • NCHAR
  • NUMBER
  • NVARCHAR2
  • RAW
  • TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)
  • VARCHAR2

 Transparent Data Encryption Data Dictionary Views

Three data dictionary views maintain information about the encryption algorithms used to encrypt columns:

  • DBA_ENCRYPTED_COLUMNS
  • ALL_ENCRYPTED_COLUMNSShows the algorithm used to encrypt columns for all tables that are accessible to a particular user.
  • USER_ENCRYPTED_COLUMNSShows the algorithm used to encrypt columns for all tables in a particular user’s schema.

 

 

 

 

Tablespace Encryption (Introduced in Oracle Database 11gR1)

 

Creating Tablespace with TDE

Encrypted tablespaces are created by specifying the ENCRYPTION clause with an optional USING clause to specify the encryption algorithm. In addition, the default storage clause of ENCRYPT must be specified. For security reasons, a tablespace cannot be encrypted with the NO SALT option. The following statement creates an encrypted tablespace by explicitly naming the ‘AES256’ encryption algorithm in the USING clause.

CONN sys/password AS SYSDBA

SQL>CREATE TABLESPACE encrypted_tbs

DATAFILE ‘/u01/app/oracle/oradata/DB11G/encrypted_tbs01.dbf’ SIZE 1M

AUTOEXTEND ON NEXT 64K

ENCRYPTION USING ‘AES256’

DEFAULT STORAGE(ENCRYPT);

ALTER USER tde_user QUOTA UNLIMITED ON encrypted_tbs;

The ENCRYPTION keyword is used to specify the encryption algorithm. The ENCRYPT keyword in the storage_clause actually encrypts the tablespace.

The ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SQL>SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                         ENC

——————————                     —

SYSTEM                                               NO

SYSAUX                                               NO

UNDOTBS1                                         NO

TEMP                                                   NO

USERS                                                  NO

ENCRYPTED_TBS                                YES

6 rows selected.

SQL>

Regular tablespaces cannot be converted to encrypted tablespaces. Instead, data must be transferred manually using export/import, “ALTER TABLE … MOVE …” or “CREATE TABLE … AS SELECT * FROM …”.

 

 

 

Test Encryption

Create table and insert some values

CONN tde_user/tde_user

SQL>CREATE TABLE tde_tbs_tab (

id    NUMBER(10),

data  VARCHAR2(100)

)

TABLESPACE encrypted_tbs;

SQL>CREATE INDEX tde_tbs_idx ON tde_tbs_tab (data) TABLESPACE encrypted_tbs;

SQL>INSERT INTO tde_tbs_tab (id, data) VALUES (1,’This is the data to be encrypted!’);

SQL>COMMIT;

Flush the buffer cache to make sure the data is written to the datafile.

CONN sys/password AS SYSDBA

SQL>ALTER SYSTEM FLUSH BUFFER_CACHE;

When the file is opened using a HEX ‘This is the data to be encrypted!’  string is not visible in the table or index data within the encrypted tablespace.

When you are finished testing the encrypted tablespace, be sure to clean up the tablespace and associated datafile.

DROP TABLESPACE encrypted_tbs INCLUDING CONTENTS AND DATAFILES;