Encrypting Data in Oracle 10g: Transparent Data Encryption (TDE)

SOURCE:

 http://oracleinsider.wordpress.com/2009/05/09/encrypting-data-in-oracle-10g-transparent-data-encryption-ted/

Encrypting Data in Oracle 10g: Transparent Data Encryption (TDE)

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

Using any HEX editor e.g. Ultraedit software allows the portion of data to be viewed present in the datafiles which ultimately breaches the security of sensitive company information. The short guide presented below provides steps for setting up a simple encryption strategy on sensitive data columns to prevent access of data from operating system e.g. using hex editors etc.

Steps of setting up Data Encryption

1. Setting up a Wallet
         a. Creating and Opening a Wallet
         b. Closing a Wallet to prevent access
2. Encrypting the desired Data Column
          a. Encrypting with Salt
          b. Encrypting with Non-Default Algorithm
          c. Encrypt for External Tables
          d. Generating a New key for the column
          e. Turning Off Column Encryption
3. Additional Resources

1. Setting up a Wallet

Creating and Opening a Wallet

In order to hold the encryption key of the data columns, a wallet must be created before any column could be encrypted. The parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora file provides the location of where the wallet is to be found. The default location of the wallet is $ORACLE_BASE/admin/$ORACLE_SID/wallet.

The following command creates and opens up the wallet (using sys account)

• Connect sys/pwd as sysdba
• ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “AnyPassword”;

To reopen the wallet, issue the following command
           • ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “AnyPassword”;

Closing the Wallet to prevent Access

In order to prevent access to sensitive data, the wallet must be closed using the following command

              • ALTER SYSTEM SET WALLET CLOSE;

2. Encrypting the desired Data Column

The default encryption method used in oracle is AES192. In order to encrypt the desired columns, use the ENCRYPT clause after every column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT
) tablespace employees;

If one requires to use any encryption method other than ‘AES192’, then use ‘ENCRYPT USING’ clause within the column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT USING ‘3DES168’
) tablespace employees;

ENCRYPT with SALT

For increased encryption security, use the ‘ENCRYPT SALT’ for Salt or ‘ENCRYPT NO SALT’ for No Salt command to specify random encrypted string in the generated key for the desired column.

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT NO SALT,
Pwd varchar2(10) ENCRYPT SALT,
SSN number ENCRYPT
) tablespace employees;

ENCRYPT for External Tables

Specify Encrypt identification key by using ‘ENCRYPT IDENTIFIED BY’ clause in the column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN varchar2(11) ENCRYPT IDENTIFIED BY ‘xc7If3t9’
) tablespace employees;

Generating a New key for the column

To re-generate a fresh key for the column, use the following command

• ALTER TABLE employee REKEY
OR
• ALTER TABLE employee REKEY USING ‘3DES168’;

Turning Off Column Encryption

By using the ‘DECRYPT’ clause, the column encryption can later be turned off as below:

• Alter table employees modify(ssn varchar2(11) DECRYPT);

3. For more information visit

• http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php
• http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm

Hope this effort helps in any manner!