Section 4.5. Transparent Data Encryption in Oracle Database 10g Release 2

4.5. Transparent Data Encryption in Oracle Database 10g Release 2

When you store both the encryption key and the encrypted data in the database, another potential security hole opens upif the disks containing the entire database are stolen, the data becomes immediately vulnerable. One way around this problem is to encrypt all the data elements and store the keys separately on a different location away from the disk drives where the data resides.

If your database is completely isolated, you may not feel that you need to encrypt its data. However, you may still want to protect the data in case of a disk theft. One solution would be to create a view to show the decrypted value. In this case, if the key is stored elsewhere, a physical disk theft will not make the data vulnerable. This approach works, but it requires an extensive and elaborate setup.

To address these types of situations, Oracle has introduced a new feature known as Transparent Data Encryption (TDE ), available starting with Oracle Database 10g Release 2. TDE uses a combination of two keysone master key stored outside the database in a wallet and one key for each table. The same key is used for all rows in a table, and a unique key is generated for each table (as illustrated in Figure 4-7).

Figure 4-7. The Transparent Data Encryption model

With TDE, you may define a subset of columns as encrypted. For example, if a table has four columns, as shown in the figure, and columns 2 and 3 are encrypted, Oracle will generate a key and use it to encrypt those columns. On the disk, columns 1 and 4 will be stored as cleartext, and the other two as encrypted text.

When a user selects encrypted columns, Oracle transparently retrieves the key from the wallet, decrypts the columns, and shows them to the user. If the data on the disk is stolen, it cannot be retrieved without the keys, which reside in the wallet encrypted by the master key, which is not stored as cleartext itself. The result is that the thief can't decrypt the data, even if he steals the disks or copies the files.

The goal of Transparent Data Encryption (TDE) is to satisfy the need to protect data stored on media such as disks and tapes, a requirement for compliance with many national and international regulatory frameworks and rules such as Sarbanes-Oxley, HIPAA, Visa Cardholder Information Security Program, etc.

TDE is not a full-scale encryption solution and should not be used as such. Note, for example, that it decrypts the encrypted columns regardless of who actually selects them, a scenario that is not likely to satisfy your security needs. For more comprehensive solutions, you will need to build your own tool using the techniques described in this chapter.

To take advantage of TDE, add an ENCRYPT clause (available in Oracle Database 10g Release 2 only) to your table creation statement for each column to be encrypted:

     /* File on web: cr_accounts.sql */     CREATE TABLE accounts     (        acc_no       number       not null,        first_name   varchar2(30) not null,        last_name    varchar2(30) not null,        SSN          varchar2(9)             ENCRYPT USING 'AES128',        acc_type     varchar2(1)  not null,        folio_id     number                  ENCRYPT USING 'AES128',        sub_acc_type varchar2(30),        acc_open_dt  date         not null,        acc_mod_dt   date,        acc_mgr_id   number     );

Here I have decided to encrypt the columns SSN and FOLIO_ID using AES 128-bit encryption. The ENCRYPT USING clause in the column definition instructs Oracle to intercept the cleartext values, encrypt them, and then store the encrypted format. When a user selects from the table, the column value is transparently decrypted.

You cannot enable Transparent Data Encryption on tables owned by SYS.

4.5.1. Setting Up TDE

Before you start using TDE, you have to set up the wallet where the master key is stored and secure it. Here is a step-by-step approach to wallet management.

Set the wallet location.
When you enable TDE for the first time, you need to create the wallet where the master key is stored. By default, the wallet is created in the directory $ORACLE_BASE/admin/$ORACLE_SID/wallet. You can also choose a different directory by specifying it in the file SQLNET.ORA. For instance, if you want the wallet to be in the /oracle_wallet directory, place the lines in the SQLNET.ORA file as shown here. In this example, I assume that the default location is chosen.
```
     ENCRYPTION_WALLET_LOCATION =       (SOURCE=           (METHOD=file)           (METHOD_DATA=              (DIRECTORY=/oracle_wallet)))
```
Make sure to include the wallet as a part of your regular backup process.
Set the wallet password.
Now you have to create the wallet and set the password to access it. This is done in one step by issuing:
```
     ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "pooh";
```
This command does three things:
1. It creates the wallet in the location specified in Step 1.
2. It sets the password of the wallet as "pooh".
3. It opens the wallet for TDE to store and retrieve keys.
The password is case-sensitive, and you must enclose it in double quotes.
Open the wallet.
The previous step opens the wallet for operation. However, after the wallet is created once, you do not need to re-create it. After the database comes up, you just have to open it using the same password via the command:
```
     ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "pooh";
```
You can close the wallet by issuing the command:
```
     ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
```
The wallet needs to be open for TDE to work. If the wallet is not open, all non-encrypted columns are accessible, but the encrypted columns are not.

4.5.2. Adding TDE to Existing Tables

In the example in the previous section, you saw how to use TDE while creating a new table. You can encrypt a column of an existing table, as well. To encrypt the column SSN of the table ACCOUNTS, specify:

     ALTER TABLE accounts MODIFY (ssn ENCRYPT);

This operation does two things:

It creates a key for the column SSN.
It converts all values in the column to encrypted format.

The encryption is then performed inside the database. By default, the AES (with 192-bit key) algorithm is used for the encryption. You can choose a different algorithm by specifying it in the command. For instance, to choose 128-bit AES encryption, you would specify:

     ALTER TABLE accounts MODIFY (ssn ENCRYPT USING 'AES128');

You can choose AES128, AES256, or 3DES168 (168-bit Triple DES algorithm) as parameters. After encrypting a column, let's look at the table:

     SQL> DESC accounts      Name      Null? Type      --------- ----- ------------      ACC_NO          NUMBER      ACC_NAME        VARCHAR2(30)      SSN             VARCHAR2(9) ENCRYPT

Note the clause ENCRYPT after the datatype. To find the encrypted columns in the database, search the new data dictionary view DBA_ENCRYPTED_COLUMNS.

What about the performance impact of TDE? There is no overhead when working with non-encrypted columns. You can expect to see a small amount of overhead when accessing encrypted columns. If encryption is no longer required, you can turn it off for that column by specifying:

     ALTER TABLE account MODIFY (ssn DECRYPT);

4.5.3. Performing TDE Key and Password Management

What if someone discovers your TDE keys somehow? You can simply re-create the encrypted values by issuing a simple command. While you are at it, you may also want to choose a different encryption algorithm such as AES256. You can do both by issuing:

     ALTER TABLE accounts REKEY USING 'aes256';

If someone discovers the wallet password, you can change it using a graphical tool called Oracle Wallet Manager. From the command line, type owm, which brings up the tool as shown in Figure 4-8. From the top menu, choose Wallet Open and choose the wallet location you have specified. You will have to supply the password of the wallet. After that, choose Wallet Change Password to change the password. Note that changing the password does not change the keys.

Figure 4-8. Oracle Wallet Manager

4.5.4. Adding Salt

Encryption is all about hiding data, and sometimes encrypted data is easier to guess if there is repetition in the cleartext. For example, a table that contains salary information is quite likely to contain repeated values, and, in that case, the encrypted values will also be the same. Even if an intruder can't decrypt the actual values, she will be able to tell which entries have the same salary, and this information may be valuable. To prevent such a thing from happening, salt is added to the data, which makes the encrypted value different even if the input data is the same. TDE, by default, applies salt.

In some cases, patterns of data may improve your database performance, and adding salt may degrade it. With certain indexes, for example, a pattern may establish the b-tree structure and make the searching of LIKE predicates faster, as in the following query:

     SELECT ... FROM accounts WHERE ssn LIKE '123%';

In this case, b-tree indexes will have to travel along only one branch to get the data, because all account numbers start with the digits 123. If salt is added, the actual values will be stored all over the b-tree structure, making index scans more expensive, and the optimizer will most likely choose a full table scan. In such cases, you will have to remove the salt from the indexed columns. You can do so by specifying:

     ALTER TABLE accounts MODIFY (ssn ENCRYPT NO SALT);

Removing the salt does not significantly affect the security, so the risk of vulnerability probably does not outweigh the performance benefits of indexing.

You cannot use TDE on columns that have any of the following characteristics:

Those with datatypes BLOB or CLOB
Those used in indexes other than regular b-tree indexes, such as bitmap indexes, function-based indexes, etc.
Those used as partitioning keys

Lack of support for TDE in these cases is also another reason why TDE is not a candidate for all types of encryption.