Transparent Data Encryption (TDE) is used for both column and tablespace encryption.    It uses a software key store which is a container that stores the TDE master encryption key.   There exist one TDE master encryption key per database and the location is defined in the SQLNET.ora file.    In previous releases this key store was referred to as an Oracle Wallet.   Software key store must be open before you can create an encrypted column or tablespace and before you can store or retrieve encrypted data.  Once you open, it is open to all sessions until it is explicitly close it or database shutdown.  Transparent Data Encryption is designed to protect data stored on a disk or other media and protects data from access by other means outside of the database.  It protects backups on tape and from accessing data via the OS directly to datafiles.  TDE supports the encryption types:  3DES168, AES128, AES192, AES256.  You specify the encryption algorithm when creating encrypted column or tablespace.  By default TDE used AES192.   TDE adds a salt to plaintext before encrypt to increase security.   TDE also uses Message Authentication Code (MAC) to data for integrity checking and uses SHA-1 by default.

 

Column Encryption

Columns are encrypted and decrypted at the SQL layer and any service that bypasses the SQL layer cannot use TDE column encryption.  TDE column encryption cannot be used with the following features:

1. Index other than B-TREE
2. Range scan through indexes.
3. Synchronous change data capture.
4. Transportable tablespaces.
5. Columns used in foreign key constraints.

BUT you can encrypt these columns using DBMS_CRYPTO package.  You can encrypt the following datatypes: Note some size restrictions exist.

1. BINARY_DOUBLE
2. BINARY_FLOAT
3. CHAR
4. DATA
5. INTERVAL DAY TO SECOND
6. INTERVAL YEAR TO MONTH
7. NCHAR
8. NUMBER
9. NVARCHAR2
10. RAW
11. TIMESTAMP
12. VARCHAR2

Example of encrypted column with AES192:

 

CREATE TABLE emp (

ssn number,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Never salt a column that is an index and being encrypted.  Example:

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Change encrypt type by the USING clause. Example

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt USING ‘3DES168’);

 

You can use the ALTER TABLE ADD to add an encrypted column to existing table.  You can use the ALTER TABLE MODIFY to make an existing column encrypted.

 

 

Tablespace Encryption

You can encrypt a permanent tablespace to protect the data.  This encrypts all data blocks of any segment type.  Data of an encrypted tablespace is also encrypted in undo, redo and all temp space.   There is no overhead in encrypting tablespace.  Restricting of encrypting tablespaces:

1. You cannot encrypt an existing tablespace.
2. Encrypted tablespace cannot be moved to another database.
3. You must open key store after mount and before open to recover an encrypted tablespace.

 

Creation of encrypted tablespace example

 

CREATE TABLESPACE data_secret

DATAFILE ‘/u01/oradata/data_secret01.dbf’ size 1g

ENCRYPTION

DEFAULT STORAGE(ENCRYPT);

 

 

Larry Catt

OCP