Oracle: Transparent Data Encryption

Oracle transparent data encryption (available from 10g Release 2) enables you to encrypt database columns and manage encryption keys. Transparent data encryption can be used to protect confidential data such as credit card and social security numbers. An application that processes sensitive data can use this feature to provide strong data encryption with little or no change to the application. Transparent data encryption is a key-based access control system. When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns. The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. No keys are stored in the clear. Follow these steps to implement encryption on the Database side.

1. Set the Master Encryption Key by issuing the following command

   ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password

No database columns can be encrypted wihtout setting the master encryption key first. This command automatically creates an oracle wallet and sets the password for the wallet. The wallet is also opened as a result of this command. Note that there must be a directory $ORACLEBASE/admin/$ORACLESID otherwise you will ge an error

ORA-28368: cannot auto-create wallet error

2. Open the wallet: The wallet must be opened explicitly after the database instance starts. When you create the wallet you also open the wallet for operation. After you create the wallet and set the password, every time you open the database, you'll have to open the wallet using the same password as follows:

   alter system set encryption wallet open authenticated by password;

   You can close the wallet like this:

   alter system set encryption wallet close;

   The wallet must be open for Transparent Data Encryption to work. If the wallet is closed, you can access all nonencrypted columns, but not encrypted columns (you will get a "wallet not open" error).
3. Create a table using CREATE TABLE as shown below

     CREATE TABLE "SCOTT"."ACCOUNT"
     ( "ACCOUNTID" VARCHAR2(40 BYTE),
    "NAME" VARCHAR2(40 BYTE),
    "SSN" VARCHAR2(40 BYTE)
     ) ;

4. Encrypt the columns: A column can be encrypted by altering the table with the following command
   The default algorithm for encryption is AES with 192-bit key. This can be changed to any other with the "using" clause added after encrypt, as shown below

   alter table accounts modify (ssn encrypt using 'AES128'); 

   Some other encryption algorithms that can be used are AES128, AES192, AES256, or 3DES168.

Do not, use transparent data encryption with these database features:

• Index types other than B-tree
• Range scan search through an index
• Large object datatypes such as BLOB and CLOB
• Original import/export utilities
• Other database tools and utilities that directly access data files

For further information refer to Oracle Advanced security administrator's guide.